Design layouts for financial data workbooks (Excel Reporting)

You can use Financial Statement Designer to configure the layout of your financial data Excel reports; for example, to change which 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, and set up category codes for your nominal accounts.

• 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 category codes 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.
    • 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 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 Data Designer

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 you want it to appear on the layout.
   • To add a nominal category:

     1. Select the nominal account from the Nominal Report Categories list.

        Note: If you can't see a particular nominal account displayed in this list, you will need to set up the category codes for that account.

     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.

Make your reports available

Make the reports are available to users by adding the reports to the appropriate roles.

1. Open System Administration.
2. Select Roles, then right-click the appropriate role and select Features.
3. Select the reports in Features > Excel Reporting > Reports > Financial Data Pack.