Excel guide · 4 min read
How to build a retail sales plan by month in Excel
=B2/SUM($B$2:$B$13), add any adjustments (for example, moving some Easter share from April to March) in a column that nets to zero, then multiply the annual plan by each month's share: =$J$1*E2. A check cell confirms the months add back to the annual plan exactly.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Sales 2026 | 2026 share | Adjustment | 2027 share | Plan 2027 | Plan (rounded) | Annual plan 2027 | 620000 | |
| 2 | Jan | 38400 | 0.066002062564 | 0.066002062564 | 40921.27879 | 40900 | ||||
| 3 | Feb | 34100 | 0.0586112066 | 0.0586112066 | 36338.948092 | 36300 | Adjustments net to 0 | 0 | ||
| 4 | Mar | 41200 | 0.07081471296 | 0.02 | 0.09081471296 | 56305.122035 | 56300 | Months add to plan | 0 | |
| 5 | Apr | 52600 | 0.090409075284 | -0.02 | 0.070409075284 | 43653.626676 | 43700 | Rounding gap | -100 | |
| 6 | May | 47900 | 0.082330697834 | 0.082330697834 | 51045.032657 | 51000 | ||||
| 7 | Jun | 45300 | 0.077861808182 | 0.077861808182 | 48274.321073 | 48300 | ||||
| 8 | Jul | 43800 | 0.075283602613 | 0.075283602613 | 46675.83362 | 46700 | ||||
| 9 | Aug | 49500 | 0.085080783774 | 0.085080783774 | 52750.08594 | 52800 | ||||
| 10 | Sep | 42100 | 0.072361636301 | 0.072361636301 | 44864.214507 | 44900 | ||||
| 11 | Oct | 46700 | 0.080268133379 | 0.080268133379 | 49766.242695 | 49800 | ||||
| 12 | Nov | 61800 | 0.10622206944 | 0.10622206944 | 65857.683053 | 65900 | ||||
| 13 | Dec | 78400 | 0.13475421107 | 0.13475421107 | 83547.610863 | 83500 | ||||
| 14 | Total | 581800 | 1 | 0 | 1 | 620000 | 620100 |
Download this sheet as an Excel practice file (.xlsx)
Start from last year's monthly sales
An annual sales plan is one number. Buying, staffing and open-to-buy need it month by month, and retail is seasonal: this boutique did 38,400 in January 2026 and 78,400 in December. Splitting next year's 620,000 plan evenly would give 51,667 a month, too much stock and staff in February and far too little in December.
The usual starting point is last year's pattern. B2:B13 holds 2026 sales by month (581,800 in total). If your history is a sales log by date, build this column with SUMIFS on a month start and end date for each row. Use a year that wasn't distorted by a store closure, a stock-out or a one-off event, or average the shares of two or three years.
Each month's share of the year
Divide each month by the year's total. The $ signs lock the SUM range so it stays on B2:B13 as you fill down. The shares add up to exactly 100%, which you can confirm in C14.
December carried 13.5% of 2026 sales and February only 5.9%. That pattern is what you'll spread the new plan over.
Result: 0.066002062564 shows as 6.6% with Percent format
6.6%: January's 38,400 out of 581,800 for the year.
Adjust for a holiday that moves
Easter fell on 5 April in 2026 and falls on 28 March in 2027, so some Easter trade moves from April into March. Last year's shares can't know that. Put share-point adjustments in their own column instead of overwriting the shares: +2% for March and -2% for April here. The 2027 share in E is the 2026 share plus the adjustment.
How big the adjustment should be is your call, from your own sales around past holidays. What matters for the arithmetic is that the adjustments net to zero (J3), so the shares still add up to 100%. The same column handles an extra trading weekend, a new promotion moved to a different month or a refit that closes the store for a week.
Result: 0.09081471296 shows as 9.1% with Percent format
9.1%: March's 7.1% share from 2026 plus the 2-point Easter adjustment.
Spread the annual plan
Put the annual plan in one input cell (J1: 620,000, about 6.6% above 2026) and multiply it by each month's 2027 share. The $J$1 reference stays locked as you fill down. March gets 56,305 and April 43,654, instead of the 43,905 and 56,054 they'd get on last year's shares alone: the 2-point move is worth 12,400 of planned sales.
If the plan is by department, do the same per department: get each department's monthly history with SUMIFS on the department and the month dates, for example =SUMIFS(Sales,Dept,"Womenswear",Date,">="&DATE(2026,3,1),Date,"<"&DATE(2026,4,1)), and spread each department's annual plan over its own shares. Departments often peak in different months.
Result: 56305.122035 shows as 56,305 with Number format
56,305: March 2027's planned sales, 9.1% of the 620,000 plan.
The check cells and rounding
J4 subtracts the annual plan from the sum of the months; it should be 0. If it isn't, a share formula has lost its $ signs, a month is missing, or the adjustments don't net to zero (J3).
Plans are usually shown in round numbers. ROUND(F2,-2) rounds each month to the nearest 100, but the rounded months add up to 620,100, 100 more than the plan (J5). Rounding each month independently almost never adds back exactly. The common fix is to let one month take the difference: make December the plan minus the other eleven rounded months.
Result: 83400
83,400: December's rounded plan that makes the twelve months add back to exactly 620,000.
Where the monthly plan goes next
Monthly planned sales drive the rest of a merchandise plan: the stock you need at the start of each month from a stock-to-sales ratio, and how much you can still buy each month in an open-to-buy. Revisit the shares each season against actual sales; if a month runs well ahead or behind, re-spread the rest of the year's plan over the remaining months' shares.
For the percentage-of-total pattern used here in other contexts, see percentage of total in Excel.
Common mistakes
Splitting the annual plan into twelve equal months
Use last year's monthly shares. Retail months can differ by more than 2 to 1.
Overwriting last year's shares with guesses
Keep the history in one column and the adjustments in another, so you can see and undo every change.
Adjustments that don't net to zero
Check SUM of the adjustment column is 0, or the months will no longer add up to the annual plan.
Rounding every month and losing the total
Round all months but one, and make that month the plan minus the others.
FAQ
How do I spread an annual sales plan by month in Excel?
Calculate each month's share of last year's sales with =month/SUM(year) using $ on the range, then multiply the annual plan by each share. The months add back to the plan because the shares add up to 100%.
How do I adjust a monthly sales plan for Easter moving?
Add an adjustment column in share points, move share from the month Easter left to the month it moves into, and check the adjustments add to zero.
Should I use one year or several years of history?
One clean year is fine. If a year had unusual events, average the monthly shares of two or three years so a one-off doesn't shape the whole plan.
Why don't my rounded monthly figures add up to the annual plan?
Rounding each month separately creates small differences that add up. Let one month absorb the difference so the total matches exactly.
Functions used
Related guides
- How to calculate open-to-buy in Excel
- How to calculate the stock-to-sales ratio in Excel
- How to calculate percentage of total in Excel
- How to calculate weeks of supply in Excel
- How to calculate initial markup in Excel
- How to calculate fuel cost and mileage in Excel
Updated