Cellwise Start free

Excel guide · 4 min read

How to build a retail sales plan by month in Excel

Quick answerWork out each month's share of last year's sales with =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.

RowABCDEFGHIJ
1MonthSales 20262026 shareAdjustment2027 sharePlan 2027Plan (rounded)Annual plan 2027620000
2Jan384000.0660020625640.06600206256440921.2787940900
3Feb341000.05861120660.058611206636338.94809236300Adjustments net to 00
4Mar412000.070814712960.020.0908147129656305.12203556300Months add to plan0
5Apr526000.090409075284-0.020.07040907528443653.62667643700Rounding gap-100
6May479000.0823306978340.08233069783451045.03265751000
7Jun453000.0778618081820.07786180818248274.32107348300
8Jul438000.0752836026130.07528360261346675.8336246700
9Aug495000.0850807837740.08508078377452750.0859452800
10Sep421000.0723616363010.07236163630144864.21450744900
11Oct467000.0802681333790.08026813337949766.24269549800
12Nov618000.106222069440.1062220694465857.68305365900
13Dec784000.134754211070.1347542110783547.61086383500
14Total581800101620000620100

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.

=B2/SUM($B$2:$B$13)

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.

=C4+D4

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.

=$J$1*E4

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.

=J1-SUM(G2:G12)

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

Updated