Budget vs Actual spreadsheet template in Google Sheets

budget-vs-actual-spreadsheet-template

Written by Laura Tennyson

Nov 3, 2020

Budget vs Actual analysis is a bit like a financial reality check for your business. Essentially, it involves comparing your budget with what you’ve actually earned and spent.

Taking the time to carry out budget vs actual analysis will give you a better understanding of your company’s current performance. In addition, it will help you to create better forecasts in the future.

If there are significant differences between the budgeted figures and actual figures (termed “favorable” or “unfavorable variance”) this can flag up potential problems. A favorable variance means that income was higher than expected, or outgoings were lower. In contrast, an unfavorable variance means that income was lower than forecast, or outgoings were higher.

An unfavorable variance is often the result of a one-off issue. This may be due to external factors. For example, supply chain disruption might force the company to spend more on an expensive alternative. On other occasions, variances can signify deeper problems in the company. A significant disparity between budgeted income and actual income could indicate a weakness in your sales department, for instance.

Gathering data for analysis

The greatest challenge when creating a variance report (and a reason why many SMEs don’t do it well — or do it at all!) is collecting all the necessary data.

Firstly, pulling together information from different departments is time-consuming. Secondly, someone has the tricky task of analyzing it all. For this system to work effectively, you need each department to record their data accurately in a compatible, accessible format.

There’s plenty of specialist FP&A (financial planning and analysis) software out there for budgeting, forecasting, and analysis. However, if you’re running a startup or SME there’s no need to invest in expensive new tools.

It’s likely that you already manage your sales, income, and expenses data in spreadsheets. A budget vs actual spreadsheet template in Google Sheets can help you transform that data into an automated budget tracker.

budget-vs-actual-budget-analysis-tab

Why use Google Sheets for budget vs actual analysis?

  • Flexible: Google Sheets allows you to manage and analyze all your financial data in the way that suits you. Whenever you need to change the system you can simply adapt the spreadsheets — without accounting software or IT support.
  • Accessible: Cloud-based and updated in real-time, administrators and managers can access and update Google Sheets at any time, from anywhere.
  • Compatible with everything: All software packages integrate with Google Sheets, so if your company has data stored in other programs, you can easily import that information.
  • Easy to use: Your colleagues probably already use spreadsheets and Google Sheets is intuitive and user-friendly. As a result, the system is easy to maintain and onboarding your team is quick and simple.

How to get started

The Sheetgo budget vs actual spreadsheet template is a pre-built workflow. It allows you to monitor budgets, analyze profitability, and control spending.

It’s suitable for companies of all shapes and sizes, providing teams with a user-friendly way to collect and share income and expenses data. At the same time, it generates automated reports for accountants and company directors.

What’s a spreadsheet-based workflow template?

A standard spreadsheet template is a single file with pre-built charts and formulas, but a Sheetgo workflow template is a complete system. It consists of multiple Google Sheets files that transfer data between them. This allows different departments to record and submit their financial data privately while enabling managers to collect data from across the company.

A spreadsheet-based workflow template gives you:

  • Organization and data privacy: Each file carries out one specific task, for data protection and accuracy. Instead of working in one shared spreadsheet, colleagues record financial data in their own personal files. The template exports this data to the manager’s master sheet automatically.
  • Scalability: You can expand a Google Sheets workflow as your company grows. When you want to add another input spreadsheet, just copy one of the files and connect it to the workflow.
  • Integration: Google Sheets is compatible with everything, so you can easily import and merge data from other software and departments. In a few clicks, you can connect data from Sales, HR, or other company workflows to create an integrated financial management system.

Get an automated actual vs forecast income report:

budget-vs-actual-income-sheet-report

What you get with this template:

When you install the Sheetgo Budget vs Actual spreadsheet template, three Google Sheets files with pre-built connections are saved to your Google Drive:

  • One Expenses spreadsheet. Share this file with the person(s) responsible for payments. These expenses might include fixed monthly costs, one-off purchases, or other outgoings.
  • One Income spreadsheet. Share this file with the person(s) responsible for processing income. Every time an invoice is paid, they can log it in this spreadsheet.
  • One Budget vs Actual master sheet, for the CFO, accountant, or administrator. Here they get an automated analysis of forecasted and actual income and expenses data from across the company.
Budget vs Actual Spreadsheet Template: Diagram

Are you working alone or running a small business?

If you think this automated budget template could be useful in your company but you’re not a manager, share this article with your boss so they can set up the system.

If you’re working alone and you want to analyze your budget in one spreadsheet, try our basic, one-file template.

How to get the workflow template

Click the blue button below to install the budget vs actual spreadsheet template and then

  1. Log in to Sheetgo with your Google account. As this template is based in Google Sheets, the files will be saved to your Google Drive.
  2. Click Use template.
  3. Wait a few moments while Sheetgo installs the template and creates the connections.
Budget vs Actual template

Workflow template to track income & expenses, comparing actual spending vs budget.

The budget vs actual master sheet opens inside Sheetgo.

To open the Income or Expenses file, click on Workflow in the side-bar on the right-hand side of the screen and double-click on either of the files.

budget-vs-actual-workflow-bar

How to use the budget vs actual spreadsheet template

Step 1: Prepare the template

First of all, you need to select which year you want to view reports for.

In the Budget vs Actual master sheet, go to the Instructions tab and enter the year in the Input box.

budget-vs-actual-input-year

Step 2: Set up and share the Income file

Go to Workflow or Files on the right-hand side and double click on the spreadsheet called Income. This is the file where your colleague(s) will enter the forecasted and actual income.

The spreadsheet contains an Instructions tab, outlining how to use the template.

Note that the template contains sample data to show you how the system works. You (or your colleagues) can simply delete or overwrite this information.

Firstly, your colleague(s) should enter the company’s income sources in the Categories Input tab. This might include products, services, or other revenue streams.

They can enter up to 100 categories along with an Operating Year for each.

budget-vs-actual-categories-input

Next they should fill out the Forecast Detail tab, entering how much income each category is due to generate each month.

budget-vs-forecast-detail

When this is complete, they should delete the sample data in the Income Detail tab.

Every time an invoice is paid, the person responsible should log it here.

budget-vs-actual-income-detail

Check out the automated Income reports

Once your colleague(s) starts entering data into the forecast and income tabs, the template automatically generates reports in the Actual vs Forecast, Yearly Income, and Yearly Forecast tabs.

budget-vs-actual-yearly-forecast-report

To share the Income spreadsheet with your colleague (or a team of people) from inside Sheetgo, just click on Edit in new and the spreadsheet will open in a new tab in your browser.

Share it with them using the green Google Sheets Share button.

budget-vs-actual-edit-in-new

Step 3: Set up and share the Expenses file

The other input spreadsheet in the workflow is the Expenses file.

Here the colleague(s) responsible for spending and payments should record the budgeted expenses and the actual amount spent.

As with the Income spreadsheet, they should first enter the expenses categories in the Categories Input tab.

In the Budget Detail tab, they enter the monthly budget for expenses.

In the Expenses Detail tab, they enter the actual amount spent, every time a bill or invoice is paid.

The template automatically combines the budget and actual data to generate the Yearly Expenses, Actual vs Budget, and Yearly Budget reports.

budget-vs-actual-expenses-sheet-report

Step 4: Update the workflow

When you have shared the Income and Expenses files with your colleagues and they have started entering their data, you need to pull that information back to the master sheet.

To do this, click on Workflow to open the menu bar and click Run.

This will update all the connections in the workflow and import the latest expenses and income data into the master sheet.

budget-vs-actual-run-workflow

Step 5: Automate

Now that the system is up and running you can automate the entire process. This will ensure that you always have up-to-date data in your reports.

The Sheetgo connections transfer the data at regular intervals without you having to open a spreadsheet.

Click Automate on the menu bar and select how often you want updates.

budget-vs-actual-automatic-updates

Optional: Use Google Forms to input data

It’s easy to adapt the template. You can also automate data entry into the Expenses and Income sheets using Google Forms. As well as saving your team time, Forms ensure consistency and data validation. Learn more about how to use Google Forms.

Customized finance automation

By connecting Google Sheets, Sheetgo allows you to build your own automated data management system for any business process.

If you’re looking for a larger budget management solution or you need help customizing the budget vs actual template for your business, book a help session with our automation experts.

Looking for a basic template?

If you’re self-employed or managing budgets alone, we also offer a single-file template. This template does not consolidate information from multiple spreadsheets — but it provides a simple way to manage all your expenses and income data in one file. Click here to get a copy.

Did you like this post?

If you found this post helpful, share it with your colleagues and friends via the social media buttons on the left!

You may also like…

Share This