Consolidating your financials in a multi company business is a common requirement. The Sage Intelligence Consolidation Financial Report Designer makes this easy providing the data and functionality you need to assess how your entire company is doing, all in the familiar environment of Microsoft® Excel®.
Through the combination of data contained in the Designer and Excel functionality, you can streamline things even further allowing you to flip through your company’s fiscal years and periods with only a few mouse clicks.
In this tip we’ll look at how to use a drop down list to easily view your layouts by company. You can then extend this idea to other parts of your reports. We’ll use Sage 300 to demonstrate this.
Not all Sage Intelligence integration’s have a Consolidation Report Designer. If yours doesn’t, then take a look at the following document which shows you how to add both the connection and report: Adding a Consolidation Connection and Report.
After you have run out a copy of your Consolidation Designer, making sure that all the companies you want to include have been selected in the Database Consolidation List, unhide the Settings sheet and you’ll see your companies listed. We’re going to use this as the basis for the drop down.
To create it, follow these steps:
1. Either generate or go back to the layout you want to edit and select the cell that contains the company code/name.
2. Select the Data tab on the ribbon and click Data Validation.
3. In the dialogue, select List from the Allow drop down.
4. Then in the Source field, use the collapse button to go to the Setting worksheet and select the cells that contain your company codes/names (whether you use codes or names depends on the accounting package you’re using).
5. Make sure that Ignore blank and In-cell dropdown are selected and click OK.
Now if you click on the company cell again in your layout you’ll be presented with a list of companies to choose from rather than having to type them out each time you want to change them.
Note: If you are switching between companies with different currencies then you may need to change the currency field as well. But now you can use the same method we’ve just described to create a drop down for it as well.
Tip: If you want to view the overall balances for all companies, then you can leave the company and any other necessary fields blank.
You can also extend this idea further, for example with Fiscal Years. Although the Consol Designer’s source sheets don’t contain the available fiscal years in a neat format, if you open a new worksheet and type them below each other then you can create a list from that data as well.
Now you can analyze your entire company’s financial position in a more efficient way at the click of a few buttons.