Common formulas: seasonality
Formulas that raise or lower a value in named months, quarters, or by last year's pattern.
Many businesses sell more in some months than in others. A seasonality formula applies a factor to a baseline. The factor depends on the month of the period.
The main formulas come from the Seasonal Revenue template. To start from it, go to Scenario planning → New model, type Seasonal Revenue, and press Enter.
A factor for named months
Seasonal Revenue has three inputs: Baseline monthly revenue (100,000), Peak factor (1.3), and Off-peak factor (0.9).
| Variable | Formula |
|---|---|
| Seasonality factor | if month in [11, 12, 1] then Peak factor else Off-peak factor |
| Seasonal revenue | Baseline monthly revenue * Seasonality factor |
| Annual revenue | sum(Seasonal revenue[all]) |
month gives the calendar month of the period, from 1 to 12. in tests if the value is in the list in square brackets. In November, December, and January, revenue is 130,000. In the other months, it is 90,000. Over 12 months, the total is 1,200,000.
[all] reads every period of the model. Annual revenue is a full year only when the model has 12 periods. In a longer model, sum the last 12 periods:
sum(Seasonal revenue[t-11:t])Other ways to pick the months
| Need | Formula |
|---|---|
| All months except June, July, and August | if month not in [6, 7, 8] then Peak factor else Off-peak factor |
| One quarter | if quarter = 4 then Peak factor else Off-peak factor |
| Your fiscal year | Use fiscalmonth in place of month |
quarter gives the calendar quarter, from 1 to 4. fiscalmonth counts from the first month of your fiscal year. To set that month, select Model settings and turn on Use fiscal years. Then select Apply changes.
Repeat last year's pattern
When the model has a year of actuals, the forecast can follow the same month of last year:
Revenue[t-12] * (1 + Growth rate)[t-12] reads the period 12 periods back. This formula needs 12 earlier periods in the model. In the first 12 periods, Revenue[t-12] is blank, so the formula gives zero. Set the model start at least 12 months before the first forecast month.
Patterns in Formula templates
In the formula bar, select Formula templates and search for these names:
| Template | Example after you fill it |
|---|---|
| Seasonality as variance to average | Revenue * Seasonality |
| Rate in named months | if month in [11, 12, 1, 2] then 90% else 100% |