Budget vs Actual spreadsheet template in Google Sheets

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 a ready-to-use budget tracker.

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 Budget vs actual template from Sheetgo is one Google Sheets file. You make a copy of it in your own Google Drive and it’s yours to edit: nothing to connect, nothing to set up and no Sheetgo account needed.

Your plan and your results sit side by side in that single file. The Forecast tabs hold the budget you set, the Actual tabs hold what really happened, and the Analysis tabs work out the difference between the two as you type. It suits companies of all shapes and sizes, and because it’s an ordinary spreadsheet you can rename the categories, add columns or switch the currency to match how your business runs.

What you get with this template

Make a copy of the Budget vs actual template and a single Google Sheets file lands in your Drive. Alongside a Read me tab that explains how the file fits together, you get:

• Inputs: the year you want to analyze. The rest of the file keys off it.
• Income Forecast: the income you planned, one row per month and one column per income category.
• Income Actual: the income that really came in, one row per invoice — client, invoice number, amount, date and category.
• Expenses Forecast: the same idea on the cost side, your planned spend month by month.
• Expenses Actual: what you actually paid out, bill by bill.
• Budget Analysis: the headline comparison. Actual against expected operating profit, income against forecast and expenses against budget, month by month plus year to date.
• Income Analysis: forecast next to actual for every income category and every month, with the under/over amount and the variation %.
• Expenses Analysis: the same breakdown for your costs.

Because it’s all one file, everyone who touches the figures can work in the same place: share it from Google Sheets the way you would share any other spreadsheet, and give the person handling income, the person handling payments and the accountant or director whatever edit or view access makes sense for them.

How to use the Budget vs actual spreadsheet template

Open the template and click Make a copy to save it to your own Google Drive. Then work through the tabs in the order below.

How the template works

The Read me tab explains how the file is put together, so it’s the place to start. Before you fill anything in, open the Inputs tab and set Year to analyze to the year you’re reporting on. The analysis tabs use that year to lay out their monthly columns, so it’s worth getting right before you type anything else.

Fill out the Income tabs

Open the Income Forecast tab and enter how much income each category is due to generate each month. The column headers are your income categories, so rename them to match what you actually sell.

The file arrives with sample figures in it so you can see how everything behaves. Delete the sample rows before you start entering your own.

Next, fill out the Income Actual tab. This one is a running log rather than a grid: one row per invoice, with the client, invoice number, amount, date and category. Use the same category names you used in Income Forecast, otherwise the analysis has nothing to line your actuals up against.

Check the Income Analysis report

Once you start entering data into the Income Forecast and the Income Actual tabs, the Income Analysis tab fills in as you go. Every income category gets a forecast column and an actual column for each month, with the under/over amount, the variation % and a year-end total. This is where a variance stops being a number and turns into something you can act on: one client who paid late, one category you consistently overestimate.

Fill out the Expenses tabs

The cost side of the file works the same way, with the budgeted expenses and the actual amount spent kept on separate tabs.

In the Expenses Forecast tab, enter the monthly budget for each expense category.

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

The formulas compare the two sides for you and fill in the Expenses Analysis tab, category by category and month by month, in the same layout as the income side.

Check out the Budget Analysis

The Budget Analysis tab pulls both sides together. It puts your actual operating profit against the expected figure, your income against forecast and your expenses against budget, for every month of the year plus a year-to-date column. It’s the view to open first, because it tells you which side of the business the gap is on; Income Analysis and Expenses Analysis then tell you which category caused it.

That’s the whole loop: your plan in the Forecast tabs, reality in the Actual tabs, and the variances calculated for you in the Analysis tabs.

Frequently asked questions