How to design layouts for financial data reports (Excel Reporting)

This topic describes financial statements for Excel Reporting. If you want to print financial statements directly from Sage 200, see Financial reports.

You can use the Financial Statement Designer to configure the layout of your financial data spreadsheets used with Excel reporting; for example, to change which nominal accounts are displayed, and how they are grouped and totalled.

Plan the statement layout

Before you create a new financial data layout, you should plan the structure of the layout using the report categories you've already set up.

Plan how the statement will be organised, and decide which nominal accounts will be displayed and how they will be grouped and totalled. When you design the layout, you will create the groups (totals) first, and then add the nominal accounts to each group.
Ensure that you set up report categories for your nominal accounts, so that they have the appropriate Report Type and Category Type for the type of layout. To do this, see Create and amend report categories.
- To include the category on the Asset and Liability layout, set the category Report Type as Balance Sheet.
  
  To choose whether the value is a credit or debit for on the Excel report, set the Category type to either Asset for a debit or Liability for a credit.
- To include the category on the Revenue and Cost layout, set the category Report Type as Profit and Loss.
  
  To choose whether the value is a credit or debit on the Excel report, set the Category type to either Expense for a debit or Income for a credit.

Create a new financial statement layout

To create a new financial statement layout:

Open: Excel Reporting > Financial Statement Designer.

You can create a new layout, or edit a copy of an existing layout.
- To create a new layout, click Add.
- To copy an existing layout, select the layout and click Copy.
Enter the layout Name.
Set the Layout Type as either Revenue and Cost or Asset and Liability.

The Layout Type indicates if the layout is used for the Assets and Liability Reporting report or the Revenue and Cost Reporting report.
Click Edit to design the statement layout.
Set up the statement layout in the Edit Financial Data Layout window.
- To create a new group (total):
  1. Select where you want to add the group in Layout Details.
  2. Click Add Group.
  3. Click once on the new group to change its heading, as you want it to appear on the layout.
- To add a report category:
  1. Select the group where you want to add the report category in Layout Details.
  2. Select the nominal account from the Nominal Report Categories list.
    
    The Category Type shows if it is a Income or Expense (for Profit and Loss), or a Asset or Liability (for Balance Sheet).
    
    The Report Type shows if it is a Balance Sheet type for the Asset and Liability report, or a Profit and Loss type for the Revenue and Cost report.
    
    Note: If you can't see a particular nominal account displayed in this list, you will need to set up the report category code for that account.
  3. Click Add to Group to add the category to the selected position in the layout.
- To move a report category inside its group, or a group inside a group, select the item and click Move Up and Move Down.
- To move a report category or group to a different group, click and drag the item to the new group heading.
- To delete a category or an entire group from the layout, select the item and click Remove from Layout.

Groups in the wrong order?

Groups in the second level of the layout (or below) are sorted in alphabetical order in the Excel report , so they will not be in the same order as the layout. To ensure that groups are displayed in your preferred order in the report, consider using a letter or number prefix on the group name so that they appear in the correct order when sorted alphabetically.

Groups in the first level of the layout will be displayed in the same order in the Excel report, and are not sorted alphabetically.

Check the Excel reports

To check the changes to your financial statement layout, update and view the Excel reports:

Open: Excel Reporting > Excel Reports.

To refresh the layout and information in the report, select the appropriate report and click Update.

The reports are named Assets and Liability Reporting and Revenue and Cost Reporting.
To open the report in Excel, select the report and click View.

If you have created more than one layout for Asset and Liability or Revenue and Cost, the Excel report will include multiple layouts. To display each layout in the report, select the layout from the Layout Name slicer.