How to Build a Simple Budget Template in Google Sheets

A budget is an estimation of business costs to revenues for a certain period. It allows entrepreneurs to plan their future expenses and balance them with income. So, you can track how much you spend to avoid sinking into a debt pit. Budgeting can be done in different ways, including the use of dedicated software. But we bet on Google Sheets. This is a sweet-spot solution, especially if you already use an accounting app, such as Xero or Quickbooks. In this blog post, we’ll figure out why and how to build a budget plan in a spreadsheet.

Simple budget template in Google Sheets: DIY and integrate with Xero

Here is a business budget template Google Sheets, which you can download and adjust for your requirements. Ours were to build an annual budget, integrate it with Xero, and visualize the budget balance. Now we’re going to explain what the template consists of and how you can synchronize it with your Xero account. 

Income and Costs tab in Google Sheets budget template

We decided to use separate sheets for both the Income and Costs sections. This is required to arrange data imported from Xero. Both sheets will look identical, with just a difference in naming:

• Column A – the name of costs/income source.
• Columns B to Q – breakdown of the amount of costs/income by months and quarters (columns E, I, M, and Q).
• Column R – a dividing column.
• Column S – the amount of costs/income per year.
• Each row contains a specific type of cost/income source; the ending row shows the total value.

Load data from Xero or another source to Google Sheets

For this Google Sheets budget template, we imported data from Xero, namely Invoices and Accounts. Xero Invoices contain all the information about costs and revenues. Data about Xero Accounts is needed to categorize costs and revenues by type. However, you can use another source to load data to your budget template. 

In our case, we opt for Coupler.io – a data integration solution to automate exports of data from multiple sources to Google Sheets, Excel, and BigQuery. It provides a number of ready-to-use integrations, such as Xero, QuickBooks, HubSpot, etc. Also, you can use the JSON integration by Coupler.io to connect to JSON APIs and fetch data from the sources that are not currently on the list.

Feeding the Xero raw data into the budget plan

Now we can fill in the Income and Costs sheets with the imported raw data. 

Step 1: Raw data preparation

We need to create two additional columns in the Xero Invoices sheet: Account Name and Payments Month. 

• Account.Name – we need this to identify the names of accounts specified in the LineItems.AccountCode column of the Xero Invoices sheet. Use the following formula to create a new column:

={"Account.Name";
  ARRAYFORMULA(
   IF(
    LEN(AF2:AF)=0,,VLOOKUP(AF2:AF,{'Xero Accounts'!B2:B, 'Xero Accounts'!C2:C},2,false))
 )
}

• Payments.Month – we need this for sorting income and costs payments by month. Use the following formula to create a new column:

={"Payments.Month"; 
  Arrayformula(
   IF(
    Len(AQ2:AQ)=0,,(Month(AQ2:AQ)))
 )
}

AQ2:AQ – the Payments.Date column of the Xero Invoices sheet.

Step 2: Filling in the Costs and Income sheets with raw data

Step 2.1: Costs description

You don’t have to specify the types of your costs manually. Clear the cells in the A column (up to Total costs) and apply the following formula to the A1 cell of the Costs sheet:

={"Costs description";
  Filter('Xero Accounts'!C2:C, ('Xero Accounts'!E2:E="EXPENSE")+('Xero Accounts'!E2:E="DIRECTCOSTS"))
}

Xero Accounts'!C2:C – the Name column of the Xero Accounts sheet.

Xero Accounts'!E2:E – the Type column of the Xero Accounts sheet.

If you have other types of costs except for EXPENSE and DIRECTCOSTS in your Xero company, add them to the FILTER function in the formula.

Step 2.2: Income source

Clear the cells in the A column (up to Total income) and apply the following formula to the A1 cell of the Income sheet:

={"Income source";
 Filter('Xero Accounts'!C2:C,('Xero Accounts'!E2:E="SALES")+('Xero Accounts'!E2:E="REVENUE")+('Xero Accounts'!E2:E="OTHERINCOME"))
}

Xero Accounts'!C2:C – the Name column of the Xero Accounts sheet.

Xero Accounts'!E2:E – the Type column of the Xero Accounts sheet.

If you have other types of income source except for REVENUE, SALES and OTHERINCOME in your Xero company, add them to the Filter function in the formula.

Step 2.3: Costs values by type and by month

Now we need to fill in the cells with monetary values of costs filtered by type and by month. Unfortunately, we can’t use ARRAYFORMULA in this case, so you’ll have to copy the following formula to all the cells:

=SUMIFS('Xero Invoices'!$F$2:$F,'Xero Invoices'!$A$2:$A,"ACCPAY",'Xero Invoices'!$AX$2:$AX,$A$2:$A,'Xero Invoices'!$AY$2:$AY,"1")

'Xero Invoices'!F2:F – the AmountPaid column of the Xero Invoices sheet.

'Xero Invoices'!A2:A,"ACCPAY" – the Type column of the Xero Invoices sheet filtered by ACCPAY condition.

'Xero Invoices'!AX2:AX,A2:A – the Account.Name column (created by us) of the Xero Invoices sheet filtered by type of costs specified in A2:A.

'Xero Invoices'!AY2:AY,"1" – the Payments.Month column (created by us) of the Xero Invoices sheet filtered by month. "1" refers to January, "2" refers to February, and so on.

Here is how you can do this fast:

• Insert the formula in the B2 cell.
• Select the B2 cell, hold Shift and select the range B2:B? (? is the row number with the last value of Costs Description). Press the Copy Down shortcut (Ctrl+d) to copy the formula to these cells. Read more about Google Sheets shortcuts.

• Repeat this manipulation for each column, having replaced “1” with the respective month number.

Step 2.4: Income values by type and by month

Do the same for the Income sheet using a slightly modified formula:

=SUMIFS('Xero Invoices'!$F$2:$F,'Xero Invoices'!$A$2:$A,"ACCREC",'Xero Invoices'!$AX$2:$AX,$A$2:$A,'Xero Invoices'!$AY$2:$AY,"1")

The only difference is that we filter invoices by ACCREC condition. 

Step 2.5: Formulas for quarters and totals

Apply formulas for total values (total sum), as well as quarter values (sum of costs for three months in the quarter).

Googe Sheets budget balance dashboard

The final flourish of our simple budget template Google Sheet is the Budget Balance sheet. It’s a dashboard showing the general status of your business. We placed the following indicators on it:

• Budget balance (difference between income and costs): We referenced the total values on the Costs and Income sheets and calculated the difference. We applied a Scorecard chart for visualization.

• Breakdown of costs vs. income by month: We referenced the values on the Costs and Income sheets and placed them in the table. We applied a Line chart for visualization.

• Top 3 types of costs and top 3 income sources with names and values: Here, we used the power of the QUERY function and applied the following formulas:
  • for Costs: =Query(Costs!A2:S23,"Select A,S where A<>''Order by S Desc limit 3")
  • for Income: =Query(Income!A2:S23,"Select A,S where A<>''Order by S Desc limit 3")

Read our blog post about Google Sheets Query Function to learn more data manipulation capabilities.

Budget tracking automation in Google Sheets

If you load your data to Google Sheets using Coupler.io, you can automate data refresh on a schedule. With this function enabled, you won’t have to manually update your budget plan to track how things are going. All you need to do is customize the frequency for automatic data refresh:

For example, here is how it will work with monthly data refresh enabled:

Which Google Sheets quirks are useful for building a business budget? 

You can download and use our budget template as is. Also, you can customize and adapt it using numerous quirks and features (the phrase borrowed from Doug DeMuro:) available with Google Sheets. 

Email sending

You can send email inquiries to your teammates or employees using the comment feature. For this, use the @ symbol before an email address with your comment and mark the Assign box:

Data protection

You can set editing permissions for a data range. For this, select a range of cells, right-click on it and go to Protect range. Here, you can choose who is allowed to edit the data.

Conditional formatting

This Google Sheets feature allows you to format your data based on specified conditions. For example, you can set that if the value in a certain cell is below zero, it will be marked red. Otherwise, it will be marked green. To enable this feature, select a cell or a cell range, right-click on it and go to Conditional formatting.

GROWTH function for prediction

The GROWTH function on Google Sheets can predict how a range of values will progress. For this, you’ll need at least three data points above zero. 

Note: We do not recommend relying entirely on predictions made using the GROWTH function.

Why you should use Google Sheets for budgeting

Let’s say you’re a small business owner who does all their accounting in Xero. It’s a wonderful tool, which, unfortunately, is not meant for budgeting at all. Actually, Xero provides a Budget Manager, but it’s very rough in terms of functionality:

That’s why Xero users look for other ways to do budgeting. Apart from dedicated tools, such as Mint, Google Sheets is where you can do a relatively comprehensive and detail-sufficient budget. Here are a few reasons for that: 

Customizable

Most budgeting apps in the market provide an all-in-one financial dashboard. This means you get a settled functionality and set of features. If you want to customize the way to track your budget, you should opt for spreadsheets. 

You can use a ready-to-use budget Google Sheets template or sketch out a custom one. The app offers multiple functions and data visualization options for that. 

Cloud-based

With your budget plan in the cloud, you can access it from any device and location. Moreover, you can share it with your team or employees. This feature is crucial for entrepreneurs in the XXI century.  

Free

Some tools provide limited functionality for free, so you can pay and unlock all the features later. With Google Sheets, you get the full value of the app for free. 

To wrap up with the DIY budget template in Google Sheets

The main idea of this blog post is that you don’t have to buy specialized software for budgeting. Google Sheets combined with Coupler.io can be a powerful solution for tracking your spending. If you don’t use Xero, check out other available integrations on the Coupler.io main page. Happy budgeting and good luck with your data!

Back to Blog