Back to Blog

How to Build a Simple Budget Template in Google Sheets and Synchronize It With Xero

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.

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 details-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. 

Simple budget template: 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 

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 – name of costs/income source.
  • Columns B to Q – break-down 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 costs/income source; the ending row shows the total value.

Raw data from Xero

We imported two entities from Xero: 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. We’ll focus on this a bit later. Now, check out how to pull data from Xero.

Importing data from Xero into Google Sheets

  • Install Coupler.io from the G Suite Marketplace. This is a Google Sheets add-on that lets you import data from various sources, including Pipedrive, Airtable, CSV files and more. 
  • Once installed, open Coupler.io in the Add-ons tab of the budget template (or your spreadsheet), click +Add Importer and select Xero.
  • Set up the Xero importer by filling in the required and, if necessary, additional fields. For more on this, refer to the Coupler.io knowledge base
  • Once you’ve filled in all the required fields, click Connect to connect your Xero account to Coupler.io. 
  • At the end, click Add Importer and run it afterwards to pull your data to Google Sheets. 

Here’s how it looks:

You can also check out our blog, Xero to Google Sheets Integration, to learn a real-life use case of Xero data import.

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))
 )
}

AF2:AF – the LineItems.AccountCode column of the Xero Invoices sheet.

Xero Accounts'!B2:B – the Code column of the Xero Accounts sheet.

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

  • 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).

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

Coupler.io let’s you automate data re-syncs. With Automatic data refresh enabled, you won’t have to manually update your budget plan to track how things are going. All you need to do is choose the frequency for data re-syncs in your Xero importer, as follows:

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 (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.

To wrap up

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. It’s expected that Quickbooks importer will be released soon! Happy accounting and good luck!

Back to Blog

Access your data
in a simple format for free!

Start Free