By using the consolidated Financial Report Designer in Sage Intelligence Reporting, you can compare the financial information from different companies all in the same workbook. There may be times when the companies you are looking at have different fiscal years, for example, one may run from January to December and the other from April to March. This can make things tricky when trying to line up the data. You may want to compare the information based on reporting period or by calendar month and depending on the approach, there may be different challenges. In this tip we’ll investigate this problem, look at the different scenarios and provide solutions.
To do this we have used an instance of the consolidated Financial Report Designer report, which has been run out in Sage 100. You can download it here. We’ve created some sample layouts in different sheets to illustrate the solutions and what follows is an explanation of them. If you are not using Sage 100 then the Sage Intelligence formulas in the workbook won’t return values. This however isn’t necessary to understand the workings and you can still double click on a formula to see the cells it references. Sage 100 has the sample companies ABC and ABX, which have the same fiscal years. However, I have edited some of the source information in the workbook to make them appear as if they are different.
Reporting based on the same period for a given year
This scenario is straightforward; you only have to make sure you’ve run out the Report Designer for the required year, set the year and period in your report headings and then base the financial formulas for both companies on that information. This is shown in the sheet Report Period.
Reporting based on a calendar month
Let’s say you want to report on October last year, this could result in different fiscal years and periods for each of the companies involved and so you would have to calculate what those values are for each of them. We have come up with two solutions for this.
Solution 1
The first is to maintain a lookup sheet which compares the fiscal year and period for each company to the calendar year and period. I have created an example in sheet Lookup1. In it ABX follows the calendar year, i.e. it runs from January to December, whereas ABC is offset and runs from April to March.
The layout in sheet Report Month1 works off this lookup. Given the year and month you want to report on (rows 1 and 2), the corresponding year and month for ABX and ABC are calculated (rows 5 and 6). The financial formulas (rows 8 to 12) then reference these values.
This is an intuitive approach to the problem however it means extra work in maintaining the lookup sheet. Also, the Excel functions in rows 5 and 6 used to lookup the years and months are complicated in that they use range formulas.
Solution 2
This solution is ideal and can be found in sheet Report Month2.
The Settings sheet in the Report Designer (which is usually hidden) gives the current fiscal year and month for each company. In this example, if you are currently in January 2020 then ABX will be in period 1 2020 while ABC will be in period 10 2019.
The logic in the layout (Report Month2) then says, given the calendar year and month you want to report on, subtract it from the current calendar year and month to work out the difference between the two (in months). Then subtract that number of months from the current year and period for ABX and ABC to give the corresponding reporting year and period for each company.
So the layout starts by stating the current calendar year and period. We’ve typed the values in directly for demonstration purposes but in a real layout it would be better to use date functions for this. The period value just returns the numeric value for the given month.
We then enter the calendar year and period that we want to report on. This would obviously never be higher than the current year and period.
Next, the difference between the two is calculated in months and that value is converted into years and months.
We subtract that number of years and months from the current year and period for both ABX and ABC as given in the Settings sheet. The formulas here become complicated because they need to take into account instances where the number of months to subtract is higher than the period you are subtracting from, another year needs to be deducted. The financial formulas are based on the results.
