Analyzing Financial Statements in Microsoft Excel

In Business Central, you can see KPIs and get overviews of the company's financial state. You can also open lists in Excel and analyze the data there. But you can also export heavy financial statements such as the balance sheet or the income statement to Excel, analyze the data, and print the reports.

In the Business Manager and Accountant Role Centers, you can choose which financial statements to view in Excel from a drop-down menu in the Reports section of the ribbon. When you choose a statement, it will be opened in Excel or Excel Online. An add-in connects the data to Business Central. However, you have to sign in with the same account that you use with Business Central.

Getting the Overview and the Details in Excel

In the ribbon, choose the relevant Excel report, and let it open so you can get the overview that you were looking for. In this version of Business Central, we offer the following Excel reports:

  • Balance Sheet
  • Income Statement
  • Cash Flow Statement
  • Retained Earnings Statement
  • Aged Accounts Payable
  • Aged Accounts Receivable

Let's say you want to dig deeper into your cash flow. From the Business Manager or Accountant Role Center, you can open the Cash Flow Statement report in Excel, but what actually happens is that we export the relevant data for you and create an Excel workbook based on a predefined template. Depending on your browser, you might be prompted to open or save the workbook.

In Excel, you see a tab where the data is laid out for you on the first worksheet. All the data that was exported is also present in other worksheets in case you need it. You can print the report right away, or you can modify it until you have the overview and the details that you want. Use the Business Central Excel Add-in to further filter and analyze data.

Understanding the Excel templates

The predefined Excel reports are based on the data in the current company. For example, the demonstration company has set up the chart of accounts to list three cash accounts under Current Assets: 10100 Checking account, 10200 Saving account, and 10300 Petty Cash. The accounts have the Account Subcategory field set to Cash, and it's their combined amount that shows up as Cash in the Balance Sheet Excel report.

Additional sheets in the Excel workbook show the data behind the report. But to find out what is hiding behind the groupings in the Excel reports, you might have to go back to Business Central and apply filters to the lists, for example.

The Business Central Excel add-in

Your Business Central experience includes an add-in for Excel. Depending on your subscription, you are logged in automatically, or you must specify the same login details that you use for Business Central. For more information, see Viewing and Editing in Excel From Business Central.

With the add-in, you can get fresh data from Business Central, and you can push changes back into Business Central. However, the ability to push data back to the database is disabled for the financial Excel reports in the list above.

See Also

Viewing and Editing in Excel From Business Central
Finance
Setting Up Finance
The General Ledger and the Chart of Accounts
Working with Business Central

Note

Can you tell us about your documentation language preferences? Take a short survey.

The survey will take about seven minutes. No personal data is collected (privacy statement).