Two years of quarterly sales: seasonal indices and how far to trust them
Kirti imports and sells a seasonal garden product through the four quarters of the year. Audited sales, in ₹ lakh, are: 2022 - Q1 40, Q2 52, Q3 60, Q4 80; 2023 - Q1 48, Q2 60, Q3 70, Q4 92. Only eight quarters of clean data exist, because the firm was incorporated in 2022. Kirti must set a purchase budget for each quarter of 2024 and has been told that a multiplicative seasonal model with a linear trend is the simplest defensible description of these numbers. Her accountant proposes computing seasonal indices by the method of simple averages - averaging the same quarter across the two years and dividing by the grand average - and then applying these indices to a trend value for 2024. Kirti is uneasy: the fourth quarter dominates her cash flow, and if the Q4 seasonal index is even 5 points wrong she will either overstock or lose sales. She wants the indices, a first-pass 2024 forecast, and a frank statement of how much confidence two years of data can support.
1. Compute the quarterly averages, the grand average and the four seasonal indices by the method of simple averages. Verify that they are internally consistent.
Read the worked answer
Quarterly averages across the two years:
| Quarter | 2022 | 2023 | Average |
|---|---|---|---|
| Q1 | 40 | 48 | 44 |
| Q2 | 52 | 60 | 56 |
| Q3 | 60 | 70 | 65 |
| Q4 | 80 | 92 | 86 |
Grand average = (44 + 56 + 65 + 86) / 4 = 251 / 4 = 62.75.
Seasonal index = (quarter average / grand average) × 100:
- Q1: 44 / 62.75 × 100 = 70.12
- Q2: 56 / 62.75 × 100 = 89.24
- Q3: 65 / 62.75 × 100 = 103.59
- Q4: 86 / 62.75 × 100 = 137.05
Check: 70.12 + 89.24 + 103.59 + 137.05 = 400.00 ✓ - the indices average to 100, as they must. Q4 is about 37% above a typical quarter; Q1 about 30% below.
2. Estimate the 2024 trend value from the yearly averages and produce forecasts for Q1 and Q4 of 2024 using the indices above. State the assumption you are making about the trend.
Read the worked answer
Yearly averages: 2022 = 232/4 = 58.0; 2023 = 270/4 = 67.5. The increase is 9.5 per year.
Linear trend extended: 2024 average = 67.5 + 9.5 = 77.0.
Forecasts (multiplicative model: forecast = trend × index/100):
- Q1 2024 = 77.0 × 0.7012 = ₹54.0 lakh
- Q4 2024 = 77.0 × 1.3705 = ₹105.5 lakh
(Q2 ≈ 77.0 × 0.8924 = ₹68.7 lakh; Q3 ≈ 77.0 × 1.0359 = ₹79.8 lakh; the four sum to ₹308 lakh, matching 4 × 77.0.)
Assumption. That the underlying level rises by a constant ₹9.5 lakh per year and that the seasonal pattern is proportional to that level. The proportionality is at least consistent with the data: year-on-year growth per quarter is Q1 +20.0%, Q2 +15.4%, Q3 +16.7%, Q4 +15.0% - roughly even in percentage terms, which is what a multiplicative model requires (an additive model would need roughly equal absolute increases, and it does not get them: +8, +8, +10, +12).
3. Kirti worries that a 5-point error in the Q4 index would ruin her stocking. Quantify that risk and advise her on how much weight the two-year record can bear, including a better method and what extra data would buy.
Read the worked answer
Quantifying the worry. On a 2024 trend of 77.0, a Q4 index of 137.05 gives ₹105.5 lakh. A 5-point error either way gives indices of 132.05 and 142.05, i.e. forecasts of 77.0 × 1.3205 = ₹101.7 lakh and 77.0 × 1.4205 = ₹109.4 lakh - a swing of about ±₹4 lakh on the single quarter that drives cash flow. That is a real exposure, and it is not alarmist.
How much confidence the data support. The Q4 index is the mean of just two numbers, 80/62.75-ish in effect; one bumper or one spoiled Christmas moves it several points. Worse, the simple-average method does not remove trend: each quarterly average mixes one low-trend year with one high-trend year, so the indices are only approximately trend-free, and with two years there is no way to test that. The 'linear trend' is literally a line through two points - no evidence at all that growth is linear, and if 2024 growth halves, every forecast is overstated by roughly ₹4–5 lakh.
Advice.
- Use a ratio-to-moving-average method with a 4-quarter centred moving average as soon as a third year exists; it separates trend from season properly, which simple averages cannot.
- Budget Q4 on a range (₹101–110 lakh), commit to non-cancellable orders only up to the lower end, and keep a supplier option for the upper end.
- Track the 2024 quarterly actuals against these indices; if Q1 2024 lands far from ₹54 lakh, revise before the Q4 buy is placed.