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

Note: 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 include the category on the Asset and Liability layout:
    • Set the Report Type as Balance Sheet.
    • Set the Category type to either Asset or Liability. This also controls whether the values are displayed as Debit (Asset) or Credit (Liability).
  • To include the category on the Revenue and Cost layout:
    • Set the Report Type as Profit and Loss.
    • Set the Category type to either Expense or Income. This also controls whether the values are displayed as Debit (Expense) or Credit (Income).

Create a new financial statement layout

To create a new financial statement layout:

Open: Excel Reporting > Financial Statement Designer.

• A list of financial data layouts are displayed, which can be used for Excel Reporting.

1. 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.
2. Enter the layout Name.
3. Set the Layout Type as either Revenue and Cost or Asset and Liability.
4. Click Edit to design the statement layout.
5. Set up the layout in the Edit Financial Data Layout window.
   • To create a new group (total):
     1. Click Add Group.
     2. Select the new group and edit its title, as it will appear on the layout.
   • To add a nominal category:

     1. Select the category from theNominal Report Categories list.

        Note: These categories are set in the Report Categories screen.

     2. Check that the correct group is highlighted in the Layout Details.

     3. Click Add to Group to add the category to the current selected group in the layout.

        You can also drag-and-drop the report category onto the group title in the layout.

   • To delete a category from the layout, select the category and click Remove from Layout.
   • To move the report category in the layout, use Move Up and Move Down to change the item's position within the group.

Check the results

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

Open: Excel Reporting > Excel Reports.

1. To refresh the layout and information in the report, select it and click Update.
2. To open the report in Excel, select it and click View.