Profit and Loss Excel Reports for Xero

Home / Report Templates / Profit and Loss Excel Reports for Xero

The Profit and Loss Excel Reports for Xero is a simple dashboard that provides a series of reports to analyse your Profit and Loss statements in Excel. For a Power BI version of these reports, see the Profit and Loss Power BI Reports for Xero.

The dashboard includes:

• Monthly Profit and Loss Report
• Monthly Profit and Loss Forecast Report and Chart
• Profit and Loss Statement
• Budget Analysis Report
• Budget Variance By Month Report

Not only this, but it can also consolidate data from multiple Xero Organisations (data files) when using the Consolidation OData Feed.

While this is a fully working and standalone dashboard, it can also be used as a starting point that can be further customised or modified as required to fit whatever business rules you need. It is not locked in any way. Because of this, it can also be used to learn how to create power queries, how to create DAX formulas, etc.

Reports Included

Monthly Profit and Loss Report

The Monthly Profit and Loss Report is a simple report that displays Accounts down the page and Months across the page.

Monthly Profit and Loss Forecast

The Monthly Profit and Loss Forecast is a simple report that displays Accounts down the page and Months across the page.

It calculates Forecast values by using a parameter entered on the Input sheet to control the cut off between actuals and budget values.

Monthly Profit and Loss Forecast Chart

The Monthly Profit and Loss Forecast Chart uses the same data as the Monthly Profit and Loss Forecast but display it as a chart instead.

Profit and Loss Statement

The Profit and Loss statement displays Month-to-Date (MTD) and Year-to-Date (YTD) actual values for your accounts and includes a % of Sales column.

Budget Analysis

The Budget Analysis report compares Actual vs Budgets and includes Variance and Variance %. Values are both included Month-to-Date (MTD) and Year-to-Date (YTD).

Budget Variance by Month

The Budget Variance by Month is similar to the Budget Analysis report but displays months across the page.

Requirements

To use the report, you will need to:

1. Have an OdataLink Subscription or Trial Account.
2. Connect OdataLink to your Xero Organisation.
3. Configure a Model that includes the following endpoints:

• Accounts
• AccountsClass
• AccountsTotals
• AccountsTypes
• BudgetSummary
• DataFiles
• DatesByMonth
• JournalsAccrual
• ProfitAndLossMultiPeriodTable
• ProfitAndLossByTrackingCategory
• TrackingCategories

Download and Setup

We provide three versions of this dashboard.

1. The first works at the company-wide level. It downloads your Profit and Loss and Budget data for your whole company.
2. The second allows you to report by Tracking Categories. It uses the ProfitAndLossByTrackingCategory endpoints and others to download Profit and Loss and Budget data by tracking categories in order to produce the Profit and Loss values required.
   Note that this version MUST use the Consolidation Odata Feed.
3. The third allows you to report by Tracking Categories. It downloads Journals data and Budget data by tracking categories in order to produce the Profit and Loss values required.
   Note that this version MUST use the Consolidation Odata Feed.

You can download the Excel workbook from the following links:

1. Profit and Loss Reports for Xero
2. Profit and Loss Reports by Tracking Categories for Xero
3. Profit and Loss Reports by Tracking Categories for Xero (Journals)

Once downloaded, you can connect it to your own data using this article.

How to Refresh the reports

To refresh the reports, follow these steps.

1. Go to the Inputs sheet to enter the Parameters driving this report.
2. Enter the Date Start and Date End for the months you wish to report on.
   This can be any date range but the larger it is, the longer the refresh will take to run.
3. Enter the Date Forecast.
   This is used as the cut off point where the forecast report and chart uses your budget data to complete the forecast.
4. (optional) If using the Tracking Categories version of the report, enter the name of your Tracking Category 1 and 2.
5. Select the Budget Source from the following values:
   Choosing Budget will pull budget values from Xero.
   Choosing Actual Last Year will use last years actual values as a pseudo-budget.
   Choosing None will not calculate budget values.
6. Select the Budget Variance from the following values:
   Choosing Standard will calculate the variance as Actual less Budget.
   Choosing Impact will calculate the variance differently for income and expense accounts so that a positive value is positive to your business.
   Choosing Remaining will calculate the variance as Budget less Actual and is useful for Not-For-Profits (NFP) that receive grants and must spend to a budget.
7. Select the Budget Variance Percent from the following values:
   Choosing Actual will use actual values as the numerator for the division.
   Choosing Variance will use variance values as the the numerator for the division.
   In both case, the denominator is the budget values.
8. Select whether to Download Budget Data.
   Choosing No will speed the refresh process.
9. Go to the Data tab in the ribbon, click the Refresh All button and wait for the data to download.
10. Select a Worksheet and use the Filters to display the data you want.

Find an issue with the template?

Let us know of any issues you find with the template. We built it to use the least amount of data possible but this might not suit everyone.