Overview of Sheetgo’s Budgeting Template
This system is designed for a CFO or controller to collect budget inputs from a companies department heads so that they can create a company wide budget. It includes a central budget for the CFO / controller and individual budget sheets that the department heads will fill out with their spend request. It consolidates inputs from all departments to create a centralized dashboard complete with the budget breakdown negotiated with each department. It only takes a few minutes to configure and requires few simple inputs.
Components of the system
Google apps script
If you already have a Google (G Suite) account the only thing you’ll need to do is to install Sheetgo to enable you to connect data in your spreadsheets. Click here to install and log in using your Google account.
The picture above shows the basic dataflow of this budgeting solution. The CFO or budget controller will generate a Master Budget in which he/she will determine the total budget, the allocation between departments, the responsible managers, and the cost categories.
The system will automatically send individual emails to the responsible manager of each department with a link to an individualized department budget spreadsheet.
Once the department budgets are filled in, the CFO or controller will consolidate in the inputs from all departments using Sheetgo in the Master Budget which includes a dashboard for an overview of the company budget and a quick analysis.
Implementing your budgeting template
Once you have a G Suite account and have installed Sheetgo follow the steps below.
Step 1) Create your master budget
Click THIS LINK and click on the “make a copy” button to create a copy of the template in you Google Drive (if you don’t find it just to to “My Drive)
Note: we recommend you place your Master Budget in the proper folder of your Google Drive hierarchy as it will create a sub folder for the department budgets.
Step 2) Fill out your company inputs
Go to the “inputs” tab of your newly created Master Budget and fill out the following data to personalize your system:
- Budget categories
- Department names
- Contact emails for the head of each department
- Budget allocation for each department
- Year you are budgeting
- Total budget for the company
Step 3) Send the budget requests to each department
Once the final categories and budget allocations for each department are input you can proceed to request the budgets from department head. To do this, select Budget -> Send Budget Requests from the menu item we’ve created specifically for this template.
The first time you run this script you will likely receive a warning that “This app isn’t verified” as we have not yet certified it with Google, in order to bypass this warning and send the Budget Requests to your colleagues then click on Advanced and Go to Budget (unsafe).
Step 4) Managers fill out budget request
Send Budget Requests will automatically create a new folder in the same Drive location where your Master Budget is, called “Department Budgets”. Inside you’ll find the individual spreadsheets for the department.
Each responsible department head will receive an email with a link to his/her department spreadsheet, with the assigned budget and ready to fill in.
Step 5) Create a connection to consolidate department budgets
Now you will use Sheetgo to consolidate all of the department budgets in a single tab of your Master Budget. To do this, you need to create a Consolidate From Folder connection.
- Open Sheetgo by clicking Add-ons -> Sheetgo -> Start and click the + to create a new connection.
- Search for you source folder, Department Budgets, that was automatically created before.
- Instead of choosing one single file, click on Select this Folder.
- Once it loads the tabs, choose the Budget Request tab as source.
- In Settings it is IMPORTANT that you don’t change the connection name, it must be SG_Budget Request.
- Select the Filter option, enable it, select Query Language and paste the following text: SELECT A,B,C,D,E,F,G,H,I,J,K,L,M.
- Save the connection by clicking on the green check mark button.
When complete your connection should look like the image below.
This way Sheetgo will create only one new tab in the Master Budget with the consolidated data from each department’s spreadsheet.
Step 6) Update budgeted data from each department
CONGRATULATIONS!!! You now have your Budget system completed. Once the heads of each department complete their budgets, you just need to run Sheetgo in your Master Budget and update the consolidate connection we created in the prior step.
Once updated, you will be able to see company statistics of your budget in the Dashboard tab of the Master Budget.