Part 3 of 3: Create and customize reports easily with the Financial Report Designer

6 minute read time.

You’ve reached the final part of this Sage Intelligence 3-part series. Let’s recap what we have been through so far. In Part 1, you learned how to get started with Sage Intelligence Reporting so that you can have accurate and timely financial statements available to understand and run your business effectively. Then, in Part 2, you discovered how to customize your reports giving you the ability to represent your data visually.

You’ll now learn how to crunch your financial numbers quickly with the Financial Report Designer in Sage Intelligence Reporting. We know that creating the perfect financial reports can take hours, if not days, to complete, especially if it needs to be customized to make sense for your particular business. Using the Financial Report Designer, you can:

  • Run multiple reports at once
  • Easily flip from one report to another without having to close, open/reopen or minimize windows
  • Get all financial reports neatly arranged together
  • Edit the standard reports or even create new reports from scratch

Let’s start by showing you how to use the Report Designer to run multiple financial reports and create a template for future use.

1.) Run the Financial Report Designer
–  In your Sage 50 solution, open the Sage Intelligence Report Manager and run the Financial Report Designer 1-0
In this example, we will be using demo data from our company Bellwether Garden Supply.

– A window will open on the right-hand pane, prompting you to Enter Report Parameters, click the ellipses button.

– Since we want to be able to switch from one year to the next without having to run the report all over, click Select All to choose all years.

Note, the available years may be different for your company.

– Click OK (this takes you back to the Report Parameters window)
– Click OK again to run the report
– The report will now run and loads in Excel

2.) Next, we will run your financial layouts and position them all in the same workbook using the task pane:
– Sage Intelligence will let you run as many different financial layouts within the same workbook as you need, ready to reuse at any time. We will start by running the Balance Sheetlayout.

– On the right-hand side of your loaded Excel workbook, there is a task pane. We will use this to run our reports. Make sure you are in the layouts section of the task pane.

– Run the balance sheet by simply holding your mouse down on the balance sheet (selecting it) and dragging it into any cell within the Excel workbook. (The cell could even have data in it or not – doesn’t matter, it won’t be overwritten as a new worksheet will be created).

– Another way to run out the Balance Sheet layout is by accessing the BI Tools tab in the Excel menu, clicking Quick Generate and then choosing the report you would like to generate (in this case, the Balance Sheet).

– A window will pop-up notifying you that the financial report layout is being generated.

– Finally, we get a confirmation that the report layout was generated successfully. Click OK.
– The resulting window should look like the image below. You have now successfully generated a Balance Sheet.

3.) Edit your parameters to dynamically adjust your report
– Sage Intelligence will let you edit/update report parameters so the data within your financial layout can automatically adjust based on the new settings. Remember when we selected all years? Scroll to the top left-hand corner of your report. The fiscal year is currently set to 2019. Change it to 2018.
– Hit ENTER on your keyboard

– The report now adjusts and Balance sheet numbers for the year 2018 are calculated. This saves you a lot of time as there is no need to go back into your accounting program to run the report all over again just to change the year. The same works for changing periods, budget types and companies (in the case of consolidation).

4.) Run an additional report (or two or three or more) alongside your existing one:
– Sage Intelligence gives you the ability to run multiple reports at the same time and store them as a template for future use.
– Let’s run the Trial Balance report using one of the two steps we used to run the Balance Sheet (either via the task pane on the right or the BI Tools tab>Quick generate option)
– The Trial Balance report layout is created in a new worksheet within the same workbook
– Change the year and hit ENTER on your keyboard. The numbers are adjusted accordingly.
– Notice the tabs at the bottom enabling you to easily switch from the Balance Sheet to the Trial Balance at the click of a button.

5.) Switch views to analyze both summarized and detailed versions of your information:
– At the top left-hand corner of the Excel worksheet (just under the cell listing) there are two views. The first view displays the summary while the second displays the detailed report view.
– By default, view number 1 is loaded, showing a summary. Click view number 2 for a more detail version of your information.

– A more detailed and expanded view is now available showing the actual numbers that make up the totals and allowing you to drill down on them (drilling down creates a new worksheet showing individual numbers, years, GL accounts etc.)

6.) The Income Statement
– Let’s run out the Income Statement – Act vs Prior using the Task Pane (or Quick generate button).
 Change the dates (remember, dates are independent of report layout so you can view 2019 on one report and 2018 on another at the same time).

– You have successfully created a financial report package with three or more reports. All you need to do now is go back into the Report Manager and save this template. All your changes (formatting and tabs included) will be saved.

How to save your Excel template:
Once you have finished customizing your Excel report, you need to save it as a template in the Report Manager.
– In the Report Manager, click on your Report
– In the ribbon, click the “Save Excel template” button

– A pop-up message will appear with a list of all your open Excel workbooks. Make sure you select the correct workbook.
– Then click OK.
– Another pop-up box will appear prompting you with regards to parameters.
– Select Yes, then, specify a name for your template and click OK.
Your template is now saved and linked to your report for future use.

As you can see, the Financial Report Designer is extremely beneficial to your business and gives you the ability to:

  • Run multiple reports with just a few button-clicks
  • Edit parameters on the fly without having to rerun your reports
  • Switch from one report to another by just switching tabs
  • Making changes to a report (formatting, custom calculations etc.) and saving as a template for future use
  • Consolidate reports and easily switch from one company to the other without ever having to load that company in Sage 50. Simply select the desired company from the parameters.
  • Further customize your reports as the output is in Excel, so you can use Excel add-ins to further assist with Financial Analysis (charts, graphs, forecasting, slicers etc.)

We hope you’ve enjoyed this 3-part series and gained some important knowledge on how Sage Intelligence Reporting can help you become the champion of your financials and finally get them to work for you and your business!

Don’t forget to subscribe to our Tips and Tricks
Stay in the loop! Join our monthly Excel and/or Sage Intelligence tips and tricks newsletter to help you with your business reporting. Join Now >

 

Get your free additional reports!
Have reports to create, but no time to create them? We’ve got you covered! With a suite of additional reports available for you to access at anytime from anywhere, you’ll have more time to run and manage your business and do what you love! Find out more >

Parents Comment Children
  • Hi, if you are using the Layout Generator as described in this article then make sure you are running out data for the prior year you need.  When the report runs to Excel you can right-click on one of the tabs at the bottom of the sheet then select 'Unhide' then select the sheet with the balances (in Sage 100 it is Beginning Balances) to see if you have figures for that year.