Budget & Estimation Sheet
Implemented using Google Sheets & Google Scripts
I was first introduce to this Style & Design of budget tracking during my time at Florida state university. Though Initially created in Microsoft excel, I redesigned the sheet in google sheets to take advantage of the cloud & live editing abilities of Google Suite. To recreate the various functions of the original sheet, I delved into google scripts and coded several unique functions.
Overview page of the departmental estimating sheet- aggregates all pages in the spreadsheet to display costs of material and labor
Each unit page has a reference key column that recalls data for the selected material pulling data points such as cost or weight.
To store all of these data points there is a sheet with programmed buttons that jump to the reference page leaving the sheet row uncluttered. There are also programmed quality of life buttons to auto sort everything and hide all category pages.
Each category page is populated with important information that will be pulled by both unit pages as well as the purchase list page.
The Purchase sheet is a simple but efficient shortcut to gather my entire list of estimated materials, their price points, and which vendor the quote references to streamline my purchasing process.
Where numbers fail, visuals prevail! I often find Directors are more responsive and understanding when provided visualizations rather than a wall of text and numbers.
With the numerous cell references across different sheets within this one spreadsheet deleting a unit from the sheet can cause several issues. I coded this function to create a listing in the Google Sheets Menu Bar to safely delete units.
My supervisor requested I develop a Master Spreadsheet to track our department's budget in a similar manner to my production estimation sheet. The overview page allows for a quick glance to monitor the budget throughout the year.
To best understand the nuances of our production season each event records initial budget allotments, approved estimates, and final actuals.
Our various revenue streams are divided into 4 categories; Ticket sales, Venue Rentals, Fundraising/Scholarship, and the Endowment.
Each department sheet only requires the department head to input single line items throughout the year, making the added responsibility minimally taxing.