ElyxAI

Demand forecast in Excel: SKU units against stock on hand

Demand PlannerDemand Forecast

A demand forecast on this page is one row per SKU, in units. You type three history periods in units, a seasonality factor, the stock on hand, and, if you already have one, a statistical forecast in units. When that statistical figure is filled, the forecast uses it. When it is blank, the forecast is the average of the three periods times the factor you typed. The gap is stock minus the forecast. Short means the gap is negative. The first screen is a sheet named Demand. Row 1 is the header. Row 2 is one SKU you can check by hand: history 100, 100, and 100 units, seasonality 1, stock 80, statistical forecast blank. The forecast is 100 units. The gap is -20. The flag is short. There is no price, no revenue, and no currency on that screen. This is not a sales revenue forecast. The sales rep page and the financial analyst page answer money. Linking them is only so you can see they are a different intent. Do not paste a price into this sheet and multiply. A factor of 1.2 raises units by 20 percent. It does not raise a sales target. Leave history blank and the simple forecast stays blank. Do not invent units to fill the row. Elyx can write the forecast, the gap, and the flag. You still type the units and the stock. The trial lasts 7 days, with no card.

The Problem

The forecast fails when it is a revenue number with a SKU label. A cell says 10000 for next month. Nobody can tell whether that is units or money, and nobody can subtract stock from it. A revenue target is not a demand forecast. A second failure is hiding seasonality inside the history. If you both inflate the history and type a factor above 1, the average is counted twice. Type the history as the units that happened. Type the factor separately. If a statistical model already gave you units for this period, type those units in StatUnits and leave the factor out of the result. The formula does not multiply StatUnits by the factor. A third failure is deleting a SKU that is covered. Covered means stock is at least the forecast. The row stays so you can see the units. Deleting it removes the check.

Benefits

Each SKU is one row of units, not a price.

The simple forecast is the average of three unit periods times the factor you typed.

A statistical unit forecast, when you type one, replaces that average.

The gap is stock on hand minus the forecast units.

Short means the gap is negative. Covered means stock covers the units.

Step-by-Step Tutorial

1

Create the list and the header

Name the sheet Demand. In row 1 type: SKU, Units1, Units2, Units3, Season, StatUnits, Forecast, OnHand, Gap, Flag. SKU is the item code. Units1, Units2, and Units3 are history in units, oldest to newest. Season is a factor you type. Use 1 when you do not want a lift. StatUnits is optional: a statistical forecast already expressed in units for this period. Forecast, Gap, and Flag are formulas. OnHand is the stock you typed. Freeze row 1. Do not merge cells. Do not add a price column that a formula reads.

One row per SKU for the period you are planning. A second period is a new row, or a copy of the sheet named with the month. Do not stack two forecasts in one row.

2

Lock the flag words in the formula, not in a note

The flag words are short and covered. They come from the formula, not from a word you type. Do not add a status column of synonyms. If you need a family or a planner name, put it in a column after Flag that no formula reads. The morning count only reads Flag.

Season is a number. Do not type peak in the Season column.

3

Enter one SKU you can check by hand

On row 2 type a SKU, units 100, 100, and 100, season 1, StatUnits blank, on hand 80. You will predict forecast 100, gap -20, flag short. On row 3 use the same history, season 1.2, StatUnits blank, on hand 150. You will predict forecast 120 and flag covered, because 150 is at least 120. On row 4 type StatUnits 90 and leave season as 1.2. You will predict forecast 90, not 108. The typed statistical units replace the average. They are not multiplied again.

If you have no history and no StatUnits, leave the unit cells blank. The forecast stays blank.

4

Write the unit forecast

Forecast is blank when the SKU is blank. If StatUnits in F2 is filled, the forecast is that number. Otherwise it is blank when there is no history or the season factor is blank. Otherwise it is the average of B2:D2 times the factor in E2. Use =IF(A2="","",IF(F2<>"",F2,IF(OR(COUNT(B2:D2)=0,E2=""),"",AVERAGE(B2:D2)*E2))). Format Forecast as a number. This is units. AVERAGE ignores text. A history cell with a currency symbol pasted as text is not a number, so it drops out of the average.

=IF(A2="","",IF(F2<>"",F2,IF(OR(COUNT(B2:D2)=0,E2=""),"",AVERAGE(B2:D2)*E2)))

StatUnits is already the unit forecast you accept, including whatever seasonality was in that model. Do not also raise the factor and expect this formula to apply it.

5

Write the gap and the flag

Gap is blank when the forecast or the stock is blank. Otherwise it is stock minus forecast. With OnHand in H2 and Forecast in G2, use =IF(OR(G2="",H2=""),"",H2-G2). A negative gap means the forecast units exceed stock. The flag is =IF(I2="","",IF(I2<0,"short","covered")). Zero is covered: stock equals the forecast. Short is only a negative gap.

=IF(I2="","",IF(I2<0,"short","covered"))

Do not multiply the gap by a price. The flag would then depend on money.

6

Count the SKUs that are short

On a sheet named Morning, the short rows are =COUNTIFS(Demand!J:J,"short"). Covered rows are =COUNTIFS(Demand!J:J,"covered"). A blank flag is neither. The header says Flag, not short. Write the rule next to the count: units, this period, stock you typed. Sort by Gap, smallest first, when you want the largest shortages at the top. The count does not require that sort.

=COUNTIFS(Demand!J:J,"short")

Do not count visible rows by hand after a filter and type over the formula.

7

Read a short row as units to replenish, not as revenue missed

If Flag is short, the forecast units are above the stock you typed. That is a quantity gap. It does not say what the sales team will bill. Fix the history, the factor, the statistical units, or the stock, whichever cell is wrong. Deleting the SKU hides the gap. A covered row stays too, so a later stock count can move it to short without you rebuilding the list.

Filter Flag to short for the replenishment list. Filter to covered when you need the rest of the book.

8

Check three rows before you share the count

History 100, 100, 100, season 1, stock 80, StatUnits blank: forecast 100, gap -20, flag short. The same history, season 1.2, stock 150: forecast 120, gap 30, flag covered. StatUnits 90 with season 1.2 and stock 90: forecast 90, gap 0, flag covered. The factor is ignored once StatUnits is filled. If a check fails, fix the numbers before you send the short count. The count is only this sheet. It is not a revenue forecast.

Write the three checks on Morning if more than one person updates the SKUs.

Template Features

Simple unit forecast

Average of the three history periods times the seasonality factor. Blank if either input is missing.

=IF(A2="","",IF(F2<>"",F2,IF(OR(COUNT(B2:D2)=0,E2=""),"",AVERAGE(B2:D2)*E2)))

Statistical units when you typed them

StatUnits replaces the average. It is not multiplied by the factor again.

Gap against stock

On hand minus forecast units. Blank if either is blank.

=IF(OR(G2="",H2=""),"",H2-G2)

Short or covered

Short when the gap is negative. Covered when stock is at least the forecast.

=IF(I2="","",IF(I2<0,"short","covered"))

Short count

COUNTIFS reads the flag word.

=COUNTIFS(Demand!J:J,"short")

Concrete Examples

Flat history, stock below the average

You want the simple forecast when you did not type a statistical figure.

Units 100, 100, 100. Season 1. StatUnits blank. On hand 80.

Result: Forecast is 100 units. Gap is -20. Flag is short. No price is involved.

A seasonality factor you typed

The same history should lift by 20 percent in units for this period.

Units 100, 100, 100. Season 1.2. StatUnits blank. On hand 150.

Result: Forecast is 120 units. Gap is 30. Flag is covered. 1.2 changed units, not revenue.

A statistical unit forecast replaces the average

Another model already gave you 90 units. You do not want the sheet to season that number again.

StatUnits 90. Season 1.2. On hand 90. History still 100, 100, 100.

Result: Forecast is 90. Gap is 0. Flag is covered. The average and the factor are not applied.

Pro Tips

One meaning for a unit

Do not mix cases and eaches in the same column. Convert before you type. The average cannot know the pack size.

Seasonality is a factor, not a month name

Type 1.2 or 0.8. A word such as summer will blank the simple forecast because it is not a number. If every SKU shares one factor this month, point E2 at one labeled cell so a later edit is obvious.

Revenue forecasts stay on their own pages

Sales rep forecasting and financial analyst forecasting are linked below because the intent is different. Do not rename this sheet Sales and paste prices into it.

Text units do not average

If a history cell was pasted as text, COUNT and AVERAGE skip it or fail. Re-enter the number. Then read the forecast again.

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: SKU demand forecast in units, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions