Trial Balance/Budget Excel (report)

The Trial Balance/Budget Excel report shows general ledger data subtotaled by general ledger account for net change, percent of budget net, net budget, balance, percent of budget balance and budget balance. Amounts are in local currency (LCY).

Set Date Filter to select the reporting period and Budget Filter to select the budget. Data is summarized by G/L account and the two global dimensions. Net budget covers the selected period, and budget balance covers dates through the ending date. The percentages show actual amounts divided by budget amounts, not percentage variances.

The Excel layout includes these report and data worksheets:

  • Trial Balance Budget (Print)
  • Trial Balance Budget (Analysis)
  • TrialBalanceBudgetData, hidden by default

Use the worksheets to analyze data in different ways.

Tip

If you configure OneDrive for system features, the Excel workbook opens in your browser in Excel for the web. Otherwise, the workbook is downloaded to your device.

Note

This report does the calculations when you view it in Excel for the web, or when you download and open it. If a banner displays text about external data connections, you might need to select the Enable content button to load data. Enable content only if you trust the file. The report doesn't connect to any external data sources. Some layouts use Power Query to prepare the analysis worksheets. Depending on your organization's security settings, you might also need to right-click a PivotTable cell in Excel for Windows and select Refresh to update data in the report.

Trial balance budget worksheets

The Trial Balance Budget (Print) and Trial Balance Budget (Analysis) worksheets compare actual amounts with budget amounts in LCY. They don't provide separate debit and credit columns.

Filters and slicers let you zoom in on the balance as seen from global dimensions, or by account categories and subcategories.

[Screenshot of the Trial Balance vs. Budget analysis worksheet.]

Tip

The data is shown in an Excel PivotTable. Select any cell in the PivotTable to show the Field List, where you can arrange fields. You can also group or ungroup values and filter data in the PivotTable. Learn more in the following articles:

TrialBalanceBudgetData data worksheet

This worksheet shows the raw data used in the report. Depending on the report, the worksheet shows individual entries or aggregated amounts.

Use this worksheet for data analysis assisted by built-in tools in Excel, such as Excel Copilot when it's available to you, or the what-if analysis tools. You can use Forecast Sheet in Excel for Windows if the data includes a date or time series with consistent intervals and corresponding numeric values.

[Screenshot of the TrialBalanceBudgetData worksheet with report data.]

Copilot availability depends on your license and your organization's settings. Learn more in Get started with Copilot in Excel.

Other worksheets in the workbook

The Business Central platform always adds three system worksheets to Excel reports:

  • TranslationData, with text needed for multilingual reports.
  • CaptionData, with text from Business Central fields needed for multilingual reports.
  • Aggregated Metadata, with data about the report, and when and how it was run.

All three worksheets are hidden by default. Learn more in System Excel sheets.

Use cases

Use Excel to view a snapshot of your chart of accounts at a given point to check the net change and closing balance compared to the budget.

Use the report to assess how well you're following your financial plans and identify variances between actual and budgeted amounts. The report is useful for financial analysis and decision-making at the end of an accounting period or fiscal year.

Financial analysts use the report to:

  • Analyze the financial performance of the business. Review the variances between actual and budgeted amounts of each account in the chart of accounts.
  • Identify areas to save cost or generate revenue, and make recommendations for improving financial performance.
  • Use the report data to prepare financial reports for stakeholders such as investors or executives.

Accountants use the report to:

  • Use the report data in comparison with budget amounts to prepare financial statements, such as income statements, balance sheets, and cash flow statements.
  • Compare actual net changes and balances with budgeted amounts.
  • Analyze the financial health of the business. Review the variances between the actual and budgeted amounts of each account in the chart of accounts.

Controllers use the report to:

  • Monitor the financial performance of the business. Review the variances between actual and budgeted amounts for each account in the chart of accounts.
  • Identify areas to save cost or generate revenue, and make recommendations for improving financial performance.
  • Use the report data to prepare financial reports for internal and external stakeholders such as board members or regulatory agencies.

Try the report

Try the report here: Trial Balance/Budget Excel

Tip

If you hold down the Ctrl key while you select the report link, the report opens on a new browser tab. The new tab lets you stay on the current page while you explore the report on the other browser tab.

Make the report your own

You can create your own report layouts with Excel, either from scratch or based on an existing layout. To modify the layout for the report, export the layout (not the workbook that resulted from running the report) from Business Central, make your changes, and then import the layout into Business Central.

Learn more in Task 1: Create the Excel layout file.

Alternative reports

There are several other ways to analyze your balance. Learn more in the following articles:

Contributors

Microsoft maintains this article. The following contributors provided some or all of its contents.

Balance Sheet (Power BI)
Trial Balance/Budget (legacy)
Key finance report overview
Ad hoc analysis on finance data
Power BI finance app
Financial Reporting Financial analytics overview

Find free e-learning modules for Business Central here