Concept: Spread Formula Functions
Spreads are meant to handle the uneven allocation of a value over a period of time. The classic use case involves 52 weeks in a year with 12 months.
Why Use the Spread Function
You can’t evenly distribute 52 weeks into 12 months. But you can distribute the weeks evenly into each quarter:
52 weeks / 4 quarters = 13 weeks in each quarter.
Because each quarter has 3 months, you can divide the 13 weeks by 3 months:
13 weeks / 3 months = 4 weeks per month with a remainder of 1.
How do you account for the extra month per quarter?
You can use the 4-4-5, 4-5-4, 5-4-4 structure. These structures place the extra week into the 1st month (5-4-4) , 2nd month (4-5-4), or 3rd month (4-4-5) of each quarter. The “heavier” month is represented by the 5, or the month with 5 weeks, rather than 4 weeks.
The Spread functions don’t distribute the value to other periods. Instead, they return the value for the specific period based on the location within the imagined spread. To distribute the value, you can enter a spread function in a default formula or a shared formula so that it repeats for every period, which effectively distributes the value. Or you can copy the formula into all time periods.
Adaptive Planning's Spread Functions
Adaptive Planning offers 3 spread functions:
- Spread445 (N, M)
- Spread454 (N, M)
- Spread544 (N, M)
N is the value to allocate and M is the number of periods over which to spread the value. You can remove the M and the function calculates upon a 12 month spread. The spread functions, uses the value you enter for M to calculate the number of weeks based on a month > quarter system with 13 weeks in each quarter.
How We Calculate Spread Functions
For M, we recommend that you enter a value that divides evenly into 12 or is a multiple of 12. Although you can use other numbers, the math isn’t as straightforward.
Here's how our calculations work:
- Calculate the number of weeks based on the value of M:
- 3 = 1 quarter = 13 weeks
- 6 = 2 quarters = 26 weeks
- 12 = 4 quarters = 52 weeks
- 24 = 8 quarters = 104 weeks
- And so on.
- Divide the value of N by the calculated number of weeks.
- Multiply by either 4 or 5 depending on the location of the current period and the spread function:
- For 4-4-5: Multiply by 5 for Mar, Jun, Sept, Dec. Multiply by 4 for all other periods.
- For 4-5-4: Multiply by 5 for Feb, May, Aug, Nov. Multiply by 4 for all other periods.
- For 5-4-4: Multiply by 5 for Jan, Apr, July, Oct. Multiply by 4 for all other periods.
Examples Showing the Math of Spread Functions
Expression | Result for 1st months: Jan, Apr, Jul, Oct | Result for 2nd months: Feb, May, Aug, Nov | Result for 3rd months: Mar, Jun, Sept, Dec |
|---|---|---|---|
Spread445 (10,000) | = (10,000 / 52) * 4 = 769 | = (10,000 / 52) * 4 = 769 | = (10,000 / 52) * 5 = 962 |
Spread445 (10,000, 6) | = (10,000 / 26) * 4 = 1,538 | = (10,000 / 26) * 4 = 1,538 | = (10,000 / 26) * 5 = 1,923 |
Spread454 (12,000) | = (12,000 / 52) * 4 = 923 | = (12,000 / 52) * 5 = 1,154 | = (12,000 / 52) * 4 = 923 |
Spread454 (12,000, 3) | = (12,000 / 13) * 4 = 16,000 | = (12,000 / 13) * 5 = 20,000 | = (12,000 / 13) * 4 = 16,000 |
Spread544 (200, 3) | = (200 / 13) * 5 = 77 | = (200 / 13) * 4 = 62 | = (200 / 13) * 4 = 62 |
Spread544 (200, 24) | = (200 / 104) * 5 = 10 | = (200 / 104) *4 = 8 | = (200 / 104) *4 = 8 |