Financial Reports

Alchemex 7.1

Home > Report Manager > Working with Reports > Advanced Reports > Financial Reports

Financial Reports Pack

The Financial Reports is one of the many standard reports that ship with Sage Intelligence Reporting but probably the most complex due to its wide functionality scope.

Simply put, the Financial Reports embodies a collection of different formatted Balance Sheet and Income Statement reports as run for at a specific period in addition to multiple period trial balances.

There are many standard types of Financial reports:

Accessing the Financial Reports

By default the Financial reports can be found in the Report Manager in the Financials folder.

Note: You can create as many different layouts as required to complete your report set within this workbook.

Selecting a reporting date: (Change Month)

A reporting date is the date which controls the display of values on any layouts you have generated within the workbook. Example: If it is now the month of September, and you wish to have the Income statement display values to the end of August, then by selecting the reporting date as August, you would achieve this.

Method

  • Microsoft Excel 2003:
    1. Click on the Change Month button located on the toolbar at the top left of your Microsoft Excel screen, above the name box.
    2. Click the downward arrow and select the month to view.
  • Microsoft Excel 2007:
    1. Click on Add-Ins on the menu bar, then click on Change Month on the Custom Toolbar.
    2. Click the downward arrow and select the month to view.

NOTE: The reporting date is totally separate to the period range chosen at run time. The period range chosen at runtime, only has an effect on the transactions that are displayed when using the Drill Down Account Feature and has no effect on the display of values on generated layouts. See "Using the Drill Down Account Feature".

Cannot find the toolbar to change the month:

  1. Microsoft Excel 2003: Press Alt-F8 (or Select, Tools, Macro, Macros, from the menu bar).

    Microsoft Excel 2007: Press Alt-F8 (or Select Developer, then Macro from the menu bar).

  2. Select the macro named Build FA toolbar.
  3. Click the Run button to run the macro.
  4. The toolbar should now be visible again.

Adding New Accounts to your existing report layouts:

If you have added new accounts into your general ledger within your accounting system, then the next time you run this report Sage Intelligence Reporting will synchronise the new list of accounts it extracts from your accounting system with the chart of accounts already listed on the Lookup sheet within this workbook. If it finds any difference between them, then Sage Intelligence Reporting will prompt you as follows:

  1. To add the new accounts it has detected to the Lookup sheet - in which case you select YES.
  2. To highlight any accounts it finds that no longer exist within your accounting system by shading the account number in red - say YES.

Once this has been done you will then need to link these new accounts to the required report layouts you have included in the workbook. The new accounts will be placed below the last account displayed on the Lookup sheet.

The instructions below will assist you with this process:

  1. You may leave the new accounts (green shaded account numbers) exactly where they appear on the Lookup sheet.
  2. Select the sheet that contains the layout to which you wish to Add the New Account.
  3. Select the row where the first New Account should be inserted.
  4. From the Menu Bar:
    • Microsoft Excel 2003: Choose Report Tools > Add Accounts. A list of accounts that are not in the layout should appear.
    • Microsoft Excel 2007: Choose Add-Ins > Report Tools > Add Accounts. A list of accounts that are not in the layout should appear.
  5. Use the Type drop down to filter by Account Type (for example Income Statement/Balance Sheet) and the other filter text boxes to help locate the specific accounts.
  6. Select the accounts that you wish to insert at the specified row (Note: you can select a different row in Microsoft Excel while the Add Accounts form is active).
  7. Click the Insert button. The accounts will be inserted at the specified row and the formulae for the rest of the row added.
  8. Click the Cancel button once all accounts have been inserted.

Note: The Add Accounts function has to have a row to copy formulae from. By default it assumes the row above the row that is being copied into. If this row does not contain the formulae that references back to the Lookup Sheet then the insert will not pick up the formulae. It is important to note that if the row above the Insert At Row does not contain the correct formulae then you must press the previous button to navigate to the Copy From Row dialog and select the row that contains the correct formulae.

Deleted Accounts:

The accounts highlighted with red shading on the Lookup sheet represent the accounts which have now been deleted from your accounting system. If you leave these accounts on the Lookup sheet then each time you run the report, Sage Intelligence Reporting  will detect these accounts and prompt you once again to highlight them. If they are already highlighted then you can ignore the prompt by selecting NO.

To prevent this prompt on these accounts each time you run the report, you will need to remove them from the Lookup sheet.

Remove the deleted accounts as follows: On the Lookup sheet, it is best to delete only the account number, then delete the blank row which will appear on the report layouts where this row is linked.

Using the Drill-Down Report Tool:

This report has been setup with a pre-defined Drill Down definition enabling you to drill down to a selected general ledger account.

To drill down to the General Ledger Transactions you would require access to the Sage Intelligence Reporting report named "GL Transactions by Account". Ensure that this report is available in the Report Manager.

  1. To use this pre-defined Drill Down Definition, click on any cell in a row that has an account no present in column A, on any Income Statement or Balance sheet within the workbook.
  2. From the Microsoft Excel Menu bar:
    • Microsoft Excel 2003: select Report Tools.
    • Microsoft Excel 2007: select Add-Ins > Report Tools.
  3. Then select Drill Down.

    The Select Drill Down window will display, showing the pre-defined definition which contains the definition code of GL_001. Ensure that this pre-defined definition is highlight in blue (click anywhere on the line if not highlighted).

    Click the Execute button.

    The GL Transactions by Account report will run and display in its own window for you to view.

  4. This pre-defined Drill Down Definition will always drill down to one period, being the period relating to the month currently selected in the workbook. To drill down to a different period, use the Change month button first to select a different month, then execute the drill down definition.

Note: The Drill Down Report Tool is only available in Microsoft Excel if Sage Intelligence Reporting is installed on the PC.