IBM Planning Analytics Tips & Tricks: 445

July 6, 2021

Time is an important aspect when implementing planning models. Calculations often use time to spread an annual amount throughout the year. However, the definition of “spread” does not always entail a simple approach.

Sometimes clients want to spread an annual value evenly through the year, which leads to an easy “divide by 12” calculation. But sometimes clients want to spread the values based on weeks. This is where calculations get a bit more complicated.

A standard year includes 52 weeks. However, not all months contain the same number of weeks and we do not want the spread to include any decimals. As a result, we cannot simply divide 52 by 12 months. We can, however, easily divide 52 by 4 quarters and calculate that each quarter has 13 weeks.

But we again face the same challenge since 13 weeks divided by 3 months results in a value with decimals. This is where accounting standardization comes into play. There are three standard methods to determine how to spread the number of weeks throughout a quarter.

4-4-5
4-5-4
5-4-4

Each of these methods define how many weeks are in the first month, how many weeks are in the second month, and how many weeks are in the third month. For example, the 4-4-5 method ensures that a calendar based year always has 4 weeks of data in fiscal January, 4 weeks of data in fiscal February, and 5 weeks of data in fiscal March.

There are advantages and disadvantages to this approach:

Advantages:

Comparisons are easy since the end date of the period is always the same day of the week
- Every period is the same length which makes quarter vs. quarter comparisons useful
- Comparisons can easily be made to the same period in the prior year
- Comparisons can easily be made to a similar period in a different quarter
Since the number of weeks in a month are consistent, it is very easy to calculate weekly averages

Disadvantages:

Month by month comparisons are flawed because one month is 25% longer than the other two
The year only includes 364 days (7 days x 52 weeks), so a 53rd week will need to be added every five or six years … which can make year over year comparisons difficult
This approach can also lead to other complex accounting practices such as accruals

IBM Planning Analytics, powered by TM1, is full of new features and functionality. Need advice? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. We post new Planning Analytics Tips & Tricks weekly in our Knowledge Center and in newsletters.

Author

Lee Lazarow

View all posts