How to Create a Financial Statement with Sage Intelligence Designer

If you haven’t looked at the Report Designer for Sage Intelligence yet, you are missing out! Sage Intelligence replaces the Financial Reporter of earlier Sage versions and offers a lot more functionality! The Report Designer is the Intelligence add-in for Excel that interfaces with the General Ledger. This is a fabulous tool that helps you design Financial Reports quickly and easily, especially using the Layout Generator. The drag and drop interface provides default layouts that let you pick and choose the features and data you want on your report. It also includes the ability to email reports, have multiple reports within a single excel file and (with the connector license) allows consolidated financials for multiple databases.

To check if you have access to the Report Designer, go to Intelligence Reporting / License Manager. This will let you know if you have access to this module. If you don’t see the License Manager, you will need to check with your administrator to verify your access to Sage Intelligence and the Report Designer.

Sage Intelligence Designer 1

Open the Report Manager and locate the Designer folder. These are reports created using the Report Designer that come with your Sage program. You can use them as they are, or you can copy them and make modifications to them. The report called Financial Report Designer is a clean template that is available as a starting point for designing your own reports. Below are some activities, that will show the capabilities of Report Designer. Go through them, have some fun, and let us know if you have any questions!

These activities are designed to illustrate the capabilities of the Report Designer and expose the user to as many of the features as possible. Explore the options as you go along – and try new things! Your reports are limited only by your imagination…

1. Copy the Report Designer Template and run the newly copied report
  - Open Report Manager.
  - Open the Designer folder.
  - Right click on the Financial Report Designer report and select ‘copy’
  - Highlight the folder where you want the copied report to be, right click and ‘paste’.
  - Right click on your copied report and ‘rename’ it. Accept the default for the report template option.
  - Highlight your new report and select ‘Run’ from the top toolbar. Select all fiscal years to be included on the report.
  - Once the report is generated, you will see the Instructions sheet.
2. Design a report
  - Click on the BI Tools tab and select Manage Layouts to open the Layout Generator.
  - Highlight IS-Actual vs Prior and click ‘Copy’. Give your layout a name and click OK.On the Layout Design screen, enter the layout options.
    - Company Code – SAMINC
    - Fiscal Year – 2019
    - Currency – USD
    - Currency Type – Functional
    - Ensure the Show Account Detail option is selected and turn on the option to Show Subtotals at Bottom
    - Go to the text columns screen next and select your Account Description fields.
    - Go to the columns screen and click ‘Clear All’ to remove all previously defined columns.
    - Add your own columns to the report by clicking on the preset values in the FORMULAS screen
    - Actual YTD 01, Actual YTD 02, Actual YTD 03
    - Prior Actual YTD 01, Prior Actual YTD 02, Prior Actual YTD 03
    - Budget 01, Budget 02, Budget 03
    - Prior Budget 01, Prior Budget 02, Prior Budget 03
    - If you want to remove a column from the layout, right click on the field in the top section
    - Drag and drop your fields so that they are organized in the following way, and add spacers
    - Click on Calculations. Right click on Qtr 1 Actual and click Edit. This shows you what the calculation is. Click Cancel.
    - Click on Add Calculation. Click on calculation and create the formula Qtr 1 Actual / Qtr 1 Budget and then click on ‘set %’ and Save. Give it the name Qtr1 Actual as %age of Budget
    - From the CALCULATIONS screen, add your new calculation to the report. The new calculation will be at the bottom of the list.
    - Move to the Rows screen. Click on Clear All to remove the existing rows. Next, click on ‘row sets’ at the top of the screen and click Add to create a new row set.
    - In Description, enter Sales Revenue, select Account for type, and enter 4000 to 41?? For the rule. Create additional lines as follows:
    - Note that the ? is a wild card, representing any value. You can use () and + and – to add or subtract specific accounts from a range. The preview screen lists all accounts in the rule – so you can check to ensure you are getting the results you are looking for.
    - Save the new row set and go back to the layout design and the rows. Select your new row set and under Account Rows, add all the rows to your layout. Add a spacer under Other revenue and Cost of Sales.
    - Right click on Sales Revenue and select the option to Toggle Switch Sign. Do the same for Other Revenue.
    - Click on CALCULATION ROWS and Add Calculation. Add Sales Revenue and Other Revenue and Save as Total Revenue.
    - Add another calculation: select Total Revenue (created in step r) from the calculation rows section and subtract Cost of Sales from the account rows section. Save as Gross Profit.
    - Add another calculation: add Operating Expenses, Amortization/Depreciation Interest Expenses and Other Income and Expenses as Total Expenses.
    - Add another calculation: Gross Profit minus Total Expenses minus Income Taxes as Net Profit.
    - Add each of these formulas to your rows. They will be added at the bottom and you will need to drag and drop them into the appropriate positions. Add spacers where needed.
    - Save the Layout and Generate. Change the Fiscal Year in column C2 to 2020 and watch the numbers change.Format as necessary using Excel functions.
    - Format as necessary using Excel functions.
3. Go back to the Report Manager. Make sure your report is highlighted and click on Save Excel Template from the top ribbon. Select the appropriate workbook to convert to template and specify the template name. Recommended best practice is to use the same name as the actual report. If it asks to replace the existing file, say Yes.