This tip will assist you with creating a very basic Rolling Income Statement formula using the Sage Intelligence Financial Report Designer. We will be using the main accounts to report from, with current period figures, and our example database is from the Sage 100 ABX demo company as an example.
- First, make a copy of, then run, the Financial Report Designer
- Once the Excel worksheet has opened, notice the Report Designer task pane to the right of your worksheet.
- Set up your worksheet with Company, Year and Current Period by dragging the Current Year and Current Period from the Formulas group in the Report Designer task pane on the right
- Drag-and-drop the Main Accounts from the Lists group in the task pane. Set up your Income Statement formatting. (For more information on how to access the Sage Intelligence Report Designer, visit the Sage Intelligence Learning Portal then find your Sage Solution, select Videos, scroll to the bottom of the list of videos to find the Sally Series).
- Reference the current year and press F4 once to make it an absolute value. (In the picture below, C2 is the cell in which the year formula has been dragged from the task pane)
- Add the “Period” heading in cell D7 and reference the current period in cell D8
The formulas must change for the next column, in order to reflect the previous period, and we need to validate both the year and the period, using standard Excel IF functionality.
VALIDATING THE PERIOD
The first period is simply equal to the current period in the heading area.
However, calculating the previous period means that we need to take into account the change from January (period 1 of the current year) to December (period 12 of the previous year).
To achieve this, we use the Excel =IF formula to say that if the period in the previous column minus 1 is less than or equal to 0, take the result and add 12, otherwise simply enter the result.
Examples:
When the period is March 2010, the previous period is February 2010, and the formula will be false (3 – 2 is not less than or equal to zero), so we use the second criteria and simply subtract 1 from March.
Formula: =IF(D$8-1<=0,D$8-1+12,D$8-1) Calculation: 6 – 1 = 5 Period: 5
When the period is January 2010, the previous period is December 2009, and the formula will be true (1 – 1 is equal to zero), so we use the first criteria, subtracting 1 and adding 12 to the period. We will deal with the year change below.
Formula: =IF(D$8-1<=0,D$8-1+12,D$8-1) Calculation: 1 – 1 = 0 Period: 1 – 1 + 12 = 12
The reason for subtracting 1 and adding 12 becomes clear if you look at what happens when the period is December and you do NOT subtract the 1.
Formula: =IF(D$8-1<=0,D$8+12,D$8-1) Calculation: 1 – 1 = 0 Period: 1 + 12 = 13
VALIDATING THE YEAR
The first-year column is simply referencing the current year.
In the example above, when the current period is January 2010 and the previous year is December 2009, the year check ensures that the correct year is used.
Again, this is an Excel =IF formula, which states that if the period in the previous column minus the period in this column is less than or equal to 0, then subtract 1 from the current year, otherwise enter the current year.
Examples:
When the first period is March 2010, the previous period is February 2010, and the formula will be true for the second criteria.
Formula: =IF(D$8-E$8<=0,$C$2-1,D$6) Result: 6 – 5 = 1
When the first period is January 2010, the previous period must be December 2009, and the formula will be true for the first criteria because the result is -11, so we need to subtract 1 from the current year.
Formula: =IF(D$8-E$8<=0,$C$2-1,D$6) Result: 1 – 12 = -11
Finally, drag both formulas across to the right for the number of previous periods required
Drag-and-drop the Actual formula onto your worksheet in the same row as your first account and change the cell to Accounting format. For more information on how to use the Sage Intelligence Report Designer visit the Sage Intelligence Learning Portal to find your Sage Solution.