Automated budget forecasting system
Pull data together from across your company to automate your budget forecasting and share up-to-date insights in a dashboard. This workflow structure is ideal for teams working across multiple locations and offices.
Combine data automatically
Gather live financial data from different parts of the business automatically. No manual work or data retrieval.
The entire team on the same page
Automated dashboards ensure that every department has access to up-to-date data, whenever they need it.
Filter data and control access
Provide different stakeholders with the data that’s relevant to them and keep private data in separate spreadsheets.
How to build this workflow
This example shows multiple offices worldwide, but this system could also be applied to different departments, or both, depending on your business needs.
Make sure all your actual expenses are in Google Sheets. Use one Google Sheet per office. Each of these Google Sheets should have the same column and row structure — just like a database. For instance, column A = date, column B = currency, column C = amount, and so on.
Not using Google Sheets? You can easily extract your actual expenses from a database. Once you’ve extracted the data into an Excel or CSV file, drag and drop it into a new Google Drive folder. Inside Sheetgo you can connect these Excel or CSV files to Google Sheets. For guidance on how to automate these steps, read our blog post on how to upload Excel/CSV to Google Sheets.
Use Sheetgo to create a consolidate connection to bring data from multiple Google Sheets into one central master sheet. There are two ways to do this:
1) If you want to consolidate fewer than 5 Google Sheets into one:
Use Sheetgo to create a connection and choose the consolidate connection. As source input, select the 5 Google Sheets and choose +create spreadsheet as your destination.
2) For more than 5 Google sheets, choose consolidate from folder. This will consolidate all the files from that Google Drive folder into a single master sheet.
Your income spreadsheet must be separated from your expenses spreadsheet. Your budget spreadsheet is where you will create your models per office.
If you don’t already have a separate forecasting spreadsheet, create one and keep it empty for now.
Connect the master expenses spreadsheet and the income spreadsheet to the forecasting spreadsheet using Sheetgo connections.
Now that the forecasting spreadsheet is being fed by the income and expense sheets, you can start creating dashboards to illustrate your finances visually.
Use expandable formulas for your dashboard. That way you’ll only have to build it once and it will work automatically with every update.
Inside Sheetgo, enable automatic updates daily. This will give you an up-to-date budget forecasting system that updates daily, automatically.
Need help? Talk to our experts.
Do you need assistance designing your workflow? Looking for a more complex budget forecasting system or don’t have the time and resources to build it yourself? Speak to our business automation experts about custom workflow design and implementation.