Alternating Fiscal Calendar Structures – what on Earth are you on about?!? Let me explain, using a real-world example that may be more common than you realize.
Most restaurants utilize a 4 week month, which becomes known as a period, and there are 13 periods in a year. There are a few reasons why the restaurant industry has adopted this calendar which include comparability across periods/years, inventory management, and payroll accounting. 4 weeks in a period * 13 periods in a year = 52 weeks. Nice and round, right? Unfortunately a year is not exactly 52 weeks. To account for this discrepancy, every few years a 53rd week is included in the fiscal calendar. When this occurs, the 13th period includes 5 weeks instead of 4.
So, the question becomes how to handle shifting calendar logic in a Hyperion Planning application? Particularly when all business rules are set to run based on logic that all periods have 4 weeks in period, dealing with a single period that has an extra week in it every few years becomes an infrequent but major issue.
Consider the following requirements:
- 53rd week will not be subject to standard calculations when it occurs, all values will be manually entered. 53rd week data must roll up automatically into the 13th period.
- 53rd week must be visible during data entry
- 53rd week must not be visible for reporting
- Application only has period granularity down to the period, not the week
To handle the above requirements and minimize application administration, alternate hierarchies and substitution variables are employed for forms, reports, and where necessary, calculations.
Substitution variables are used to reference the period dimension on all forms and reports. If a form is used for data entry, the “Period_Entry” Subvar is referenced. If a form or report is used for reporting purposes, the “Period_Report” subvar is referenced.
These subvars point to one of 3 hierarchies in the period dimension. The first is a standard hierarchy with 13 periods, which is referenced by both data entry and reporting subvars in standard 52-week years.
The second hierarchy is used by the Period_Entry subvar during 53 week years. It shares period 1-13 with the hierarchy above, but it also includes a “14th” period. This 14th period is the 53rd week which exists for data entry purposes only.
The final hierarchy is referenced by the “Period_Report” subvar during 53rd week years for reporting. It contains shared members for periods 1-12, and a “ P13’ “ member. The “ P13’ “ member is a dynamically calculated member formula which adds up the base P13 and P14 members: P13’ = P13+P14;
Now we’ve discussed both the substitution variables and the hierarchies they reference – here is a cheat sheet which details how they interact given the two different fiscal year structures:
Next we will be looking at two forms, one that is used for data entry (named Data Entry) and another that is used for reporting (named Data Report). These are simplistic examples but the concept displayed here can be applied to any sort of form or Financial Report.
The Period dimension is referenced as follows in the “Data Entry” form:
The Period dimension is referenced as follows in the “Data Report” form:
When the subvars are set for a standard 52 week year, the forms are displayed as follows:
Above, the Entry and Data report forms both show 13 periods, in this case the values for each period are consistent across the entire year as very simple calculations were used to build up periods based on 4 weeks.
Now, when the substitution variables are flipped to their 53rd week settings, the forms look different:
In the data entry form, the 14th period is visible. One week’s worth of data was entered in this field.
In the Data Report form, you cannot see the 14th period, but you can see the special P13’ member. It sums up P13 and P14 as shown on the data entry form.
This method is one way to manage fiscal calendars that have different structures. In this case we are solving for a 53rd week which occurs every few years in the restaurant industry’s fiscal calendars. By changing just 2 substitution variables, every report, form and calculation can adapt to address wether a standard calendar year, or a non-standard calendar year is currently in play.
Blog post by James Renger of Key Performance Ideas.