EN
E-commerce

How Do You Forecast Sales and Demand? A Course on Seasonality, Moving Averages and Order Quantity

How do you forecast sales? Moving average, seasonality index, growth assumption and order quantity calculation: a course with examples for e-commerce sellers.

Contents 14 sections
  1. What will you be able to do by the end of this training?
  2. Lesson 1: What are sales forecasting and demand forecasting, and why do them?
  3. Lesson 2: How do you prepare sales data for forecasting?
  4. Lesson 3: What is a moving average, and why is it misleading for seasonal products?
  5. Lesson 4: How is a seasonality index calculated?
  6. Lesson 5: How do you produce next year's monthly forecast with a growth assumption?
  7. Lesson 6: How do you go from a forecast to an order quantity?
  8. Lesson 7: How do you forecast a new product with no history?
  9. Lesson 8: How do you measure forecast error and set up a monthly routine?
  10. What are the most common mistakes in sales forecasting?
  11. How does NetKasam make sales forecasting easier?
  12. Mini quiz: Test what you have learned
  13. Conclusion: Start with one product
  14. Frequently Asked Questions About Sales Forecasting

November is coming. Last year you sold 340 units of your best-selling product, and stock ran out two weeks early. How much will you order this year? Order too little and you lose sales; order too much and in January you're left with goods sitting in the warehouse and cash tied up. Answering this question with calculation rather than intuition is called sales forecasting, or demand forecasting. The good news: you don't need big software or statistical knowledge; an Excel sheet and the right sequence are enough.

This article is not a narrative but an 8-lesson practical course. In each lesson you'll find what to do, why you're doing it and how to tell you've done it right. We'll work through cleaning the data, the moving average, the seasonality index, the growth assumption, going from forecast to order quantity, forecasting new products and measuring forecast error with numbers, using a single product. At the end there is a mini quiz and a monthly routine.

What will you be able to do by the end of this training?

In short: You will clean your sales data, forecast a product's monthly demand with a seasonality index and a growth assumption, turn the forecast into an order quantity and measure the forecast error.

Course mapSix steps of sales forecasting
  1. 01
    DataPull net sales month by month and mark out-of-stock and campaign months.
  2. 02
    PatternFind the product's seasonality and growth pattern.
  3. 03
    ForecastMultiply the monthly average by growth and apply the month's index.
  4. 04
    OrderAdd safety stock to the forecast and deduct stock on hand.
  5. 05
    MeasureCompare actual sales with the forecast and find the error rate.
  6. 06
    CorrectUpdate the index and the growth assumption.

The 8 lessons of the course: (1) what sales forecasting is, (2) how to prepare the data, (3) the moving average and its limits, (4) the seasonality index, (5) next year's forecast with a growth assumption, (6) from forecast to order quantity, (7) a product with no history, (8) forecast error and the monthly routine.

Lesson 1: What are sales forecasting and demand forecasting, and why do them?

In short: Sales forecasting means calculating how much you'll sell in a given period by looking at patterns in past data. Its purpose is to buy the right amount of stock, use cash correctly and plan resources such as campaigns and shipping in advance.

Demand is the quantity customers want to buy; sales are the quantity you were actually able to sell. If you ran out of stock, sales are lower than demand. This gap is the most common forecasting mistake and is covered in Lesson 2. The cost of not forecasting goes both ways:

MistakeResultCost
Forecast too lowStock runs out, sales are lost, marketplace ranking dropsLost profit, lost customers
Forecast too highGoods sitting in the warehouse, cleared with discountsTied-up cash, storage and shrinkage costs
No forecastGut-feel orders every month, fluctuating cashUnpredictable payment schedule

Think about it together with concepts like inventory turnover and inventory cost: you can find the role of inventory turnover in the article the role of inventory turnover in e-commerce success, and how to calculate inventory cost in how to calculate inventory cost.

Lesson 2: How do you prepare sales data for forecasting?

In short: Before you start forecasting, pull net sales (after cancellations and returns) per product, month by month; mark months that were out of stock, had campaigns or received a one-off large order. Bad data spoils a good method.

  1. Choose the period: At least 12 months, preferably 24. One year of data reveals seasonality, but reliability is lower.
  2. Use net sales: Use units after returns and cancellations, not the number of orders.
  3. Combine channels: Add up store, marketplace and e-commerce website sales per product.
  4. Mark out-of-stock months: On days when stock ran out, sales don't reflect demand; adjust that month or make a note.
  5. Filter out one-off orders: An exceptional order from a single wholesale customer is not regular demand.

In NetKasam, you can create a sales report with the filters and criteria you want, save it and export it in Excel format (reports). The inventory movements report helps you find out-of-stock days (inventory management). Since marketplace and website orders are gathered in a single panel, you don't need to combine channels by hand (marketplace integration).

Lesson 3: What is a moving average, and why is it misleading for seasonal products?

In short: A moving average uses the average of the last few months as next month's forecast. It is sufficient for products with flat sales; for seasonal products it lags, forecasting too little at the peak and too much during the decline.

The 12-month net sales of our example product are below (1,800 units in total, a monthly average of 150 units; the numbers are examples).

Chart12-month net sales of the example product: November is more than twice the average
  1. January100 units
  2. February90 units
  3. March110 units
  4. April130 units
  5. May140 units
  6. June130 units
  7. July120 units
  8. August110 units
  9. September150 units
  10. October170 units
  11. November340 units
  12. December210 units

Prepared with sample values.

A three-month moving average forecasts a month using the average of the previous three months. Let's compare the results with reality:

MonthActual sales3-month moving average forecastDifference
October170126.7 (Jul, Aug, Sep)−43,3
November340143.3 (Aug, Sep, Oct)−196,7
December210220.0 (Sep, Oct, Nov)+10,0

In November, the forecast falls 196.7 units short, which means stock runs out in mid-November. A moving average doesn't see the peak, because it rises after the peak. What's more, if you take the average of the last three months for January, you get (170 + 340 + 210) ÷ 3 = 240 units, while the product sold only 100 units in January. This error leaves the product overstocked in January. The solution is to calculate seasonality separately.

Lesson 4: How is a seasonality index calculated?

In short: Seasonality index = month's sales ÷ average monthly sales. Above 1 is an above-average month, below 1 is a below-average month. The index turns the seasonal pattern into a number and can be reused every year.

FormulaFive formulas for sales forecasting
  1. Monthly average = Annual net sales ÷ 12
  2. Seasonality index = Month's sales ÷ Monthly average
  3. Next year's monthly average = This year's monthly average × (1 + Growth assumption)
  4. Monthly forecast = Next year's monthly average × Month's index
  5. Forecast error = |Actual − Forecast| ÷ Actual

The growth assumption is your decision; it is set by past growth, the campaign plan and inventory capacity.

In the example, the monthly average is 1,800 ÷ 12 = 150 units. The indexes:

MonthSalesIndex (sales ÷ 150)Interpretation
January1000,67A third below average
February900,60Lowest month of the year
March1100,73Recovery
April1300,87Close to average
May1400,93Close to average
June1300,87Close to average
July1200,80Summer lull
August1100,73Summer lull
September1501,00Average
October1701,13Start of the season
November3402,27Peak
December2101,40Year-end

The indexes add up to about 12 over 12 months (12 months × an average of 1). This is a validation check: if the total is very different from 12, a month was entered wrong. A good method is to average the product's index over two years of data, so that an exceptional event in a single year (for example, a one-off wholesale order) doesn't distort the index.

Recommended ReadingHow to Do an ABC Analysis? A Guide to Inventory Classification, the Pareto Rule and Identifying Dead Stock

Lesson 5: How do you produce next year's monthly forecast with a growth assumption?

In short: Next year's monthly average = this year's average × (1 + growth). Each month's forecast is this average multiplied by that month's index.

Derive the growth assumption from past growth, the new channels and campaigns you're planning and your inventory capacity; don't use a number with no basis. In the example, let's assume 20% growth (chosen only to illustrate the method): next year's monthly average is 150 × 1.20 = 180 units.

MonthIndexAccountForecast (units)
January0,67180 × 0,667120
February0,60180 × 0,600108
October1,13180 × 1,133204
November2,27180 × 2,267408
December1,40180 × 1,400252

The November forecast is 408 units: 20% above last year's 340 units. A moving average would have given 143 units for the same month; the 265-unit difference between them means running out of stock and losing sales. In marketplace campaigns, sales can rise far above a normal month; take the campaign effect from the index of a campaign month in your own historical data rather than assuming an estimated rate.

Lesson 6: How do you go from a forecast to an order quantity?

In short: Order quantity = forecast sales + safety stock − stock on hand − orders in transit. The lead time (days from order to delivery) sets the forecast period: the order is placed at least one lead time before the sales.

Safety stock is the buffer that protects sales when the forecast misses; for how to calculate it, see the article how to calculate safety stock. In the example, let's take safety stock for November as 20% of the forecast, about 82 units; stock on hand is 150 units, with no orders in transit.

ChartOrder quantity for November: from a 408-unit forecast to a 340-unit order
  1. November forecast408 units
  2. Safety stock82 units
  3. Total stock needed490 units
  4. Stock on hand−150 units
  5. Order to place340 units

Prepared with example values. The safety stock ratio is set according to your own demand fluctuation.

If the lead time is 30 days, you should place this order by early October at the latest; an order that arrives at the November peak is a late order. On the cash side, write the payment schedule for the order amount into your cash flow statement. If you can place half the order before the November peak and half in mid-November, you split the risk of forecast error.

If you have more than one product, focus your forecasting effort on the products that bring in the most revenue. Find your class A products with ABC analysis (ABC analysis) and do detailed forecasting only for them; a simple rule is enough for class C.

Lesson 7: How do you forecast a new product with no history?

In short: If there is no historical data, take the first three months of the most similar product as a model, place a small initial order and update monthly based on actuals. Forecasting a new product is uncertain by nature; the goal is not the right number but learning fast.

  1. Choose a similar product: Find the first 3 months of sales of a product in the same category, at a similar price and in a similar channel.
  2. Adjust the scale: Correct for differences in the new product's page, advertising and price with a conservative ratio.
  3. Start small: Size the first order to cover the first two months, not the full three months of forecast sales.
  4. Track every week: Compare actual sales weekly for the first 8 weeks.
  5. Start the index after the first 3 months: Calculate the seasonality index once a full year has passed.

The same logic applies when opening on a new marketplace or an overseas market; for the pricing side, see the article how to set prices on an overseas marketplace.

Lesson 8: How do you measure forecast error and set up a monthly routine?

In short: Forecast error = |actual − forecast| ÷ actual. As a first target, you can set a range of, for example, 10%–20 (the acceptable limit depends on the product and your margin); if you always forecast too low or always too high, your growth assumption is wrong.

MonthForecastActualMistakeError rate
Month 110011010%9,1
Month 213012010%8,3
Month 320025050%20,0
Average%12,5
Takeaways from the exampleFour numbers from the example forecast
  • 150average monthly sales (units)
  • 2.27November seasonality index
  • 408next year's November forecast (units)
  • 12.5%example average forecast error

Measuring error tells you two things: the average error shows how reliable the method is, and the direction of the error shows which way the assumption is off. If actuals are above the forecast three months in a row, raise the growth assumption; if below, lower it.

When?What to do?Output
First week of every monthEnter last month's net sales and calculate the forecast errorError table
Every monthOpen the critical stock report and identify items that need a new order based on lead timeOrder list
Once a quarterUpdate the growth assumption and the safety stock ratioNew assumptions
Once a yearRefresh the seasonality index with two years of dataUpdated index table
Recommended readingHow Do You Route Orders Across Multiple Warehouses? A Course on Shipping Cost Matrix, Cheapest Warehouse Rule, Stock Constraints, Order Split Cost and Warehouse Transfers

What are the most common mistakes in sales forecasting?

  1. Forecasting with gross sales. The number of orders before returns and cancellations inflates demand.
  2. Counting an out-of-stock month as demand. If stock ran out, real demand is higher than sales.
  3. Applying a moving average to a seasonal product. It misses the peak and leaves excess stock in January.
  4. Choosing a growth assumption with no basis. The number should come from past growth and the plan.
  5. Forecasting all products with the same care. Effort should go to the products that bring in the most revenue.
  6. Not measuring the forecast. If the error isn't calculated, the method doesn't improve.
  7. Relying on a single number. Also work out the lowest and highest likely forecasts and set safety stock accordingly.

How does NetKasam make sales forecasting easier?

  • Net sales in one place. Store, marketplace and website orders come together in the same panel; with order management, you don't gather channels by hand.
  • Saved and exportable reports. You save the sales report with its criteria, export it to Excel and paste it into your forecast table (reports).
  • Inventory movements and turnover. The inventory movements report shows out-of-stock days; the inventory turnover and critical stock reports show when to order (inventory management).
  • Critical stock level. You can set a critical stock level for your product and make purchases when needed.
  • Tracking inventory centrally. To keep marketplace inventory in sync, see the article how to sync marketplace inventory.
  • Bookkeeping is free. e-Invoice, customer and supplier accounts, inventory, cash, bank and expense management are free in every plan; see the pricing page for details.

Mini quiz: Test what you have learned

In short: Answer the 5 questions below, then compare with the answer key. 4 or 5 correct shows you have completed the training.

  1. If a product sells 2,400 units a year, what is its monthly average? If December sold 480 units, what is the December index?
  2. If you assume 10% growth next year, what are the new monthly average and the December forecast?
  3. If the forecast was 300 and the actual was 250, what is the forecast error rate?
  4. Why does a moving average forecast too little at the peak for a seasonal product?
  5. Why shouldn't you use an out-of-stock month as is?

Answer key:

  1. 2,400 ÷ 12 = 200 units; December index 480 ÷ 200 = 2.4.
  2. 200 × 1.10 = 220 units; December forecast 220 × 2.4 = 528 units.
  3. |250 − 300| ÷ 250 = %20.
  4. The previous months are lower than the peak; the average comes out low and only catches the peak after it has passed.
  5. Because stock ran out, sales don't reflect demand; the month looks lower than real demand and the forecast comes out short.

Frequently Asked Questions About Sales Forecasting

How do you forecast sales?

You pull 12–24 months of net sales per product, calculate the monthly average and a seasonality index for each month, add a growth assumption for the coming year and find the monthly forecast as average × index. You compare it with actual sales and correct it month by month.

What is a seasonality index?

It is the ratio of a month's sales to average monthly sales. A month above 1 sells above average, and a month below 1 sells below average. For example, for a product with an average of 150, a month with 340 sold has an index of 2.27.

What is a moving average for?

It uses the average of the last few months as next month's forecast. It works for products with flat sales; for seasonal products it catches the peak late and over-forecasts during the decline.

What is the minimum amount of data needed?

At least 12 months are needed to see seasonality; two years of data make the index more reliable. For new products with shorter data, the first months of a similar product are used.

How large should the forecast error be?

There is no fixed limit; it depends on the product and your margin. As a first target, a range of 10%–20, for example, can be set. What matters is that the error shrinks over time and doesn't always point in the same direction.

How do you use sales data when you ran out of stock?

On out-of-stock days, sales don't reflect demand. Calculate that month's daily average based on in-stock days and multiply by the number of days, or mark the month as adjusted.

Can I pull a sales report in NetKasam and export it to Excel?

Yes. You can create reports with the filters and criteria you want, save them, and export them in Excel format or print them.

How do I use the critical stock level?

You set a critical stock level for your products and make a purchase when inventory falls to that level. It's recommended to set the critical level together with the supply lead time and the estimated daily sales.

Conclusion: Start with one product

Sales forecasting is a habit, not a piece of software. Choose the product that brings in the most revenue, pull the last 12 months of net sales, calculate the monthly average and monthly indexes, add your own growth assumption and find your order quantity for November. A month later, write down the forecast error: even if the forecast misses, your second attempt will be better.

Bookkeeping Is Now Completely Free

Manage your bookkeeping and e-Invoice operations with NetKasam without paying a thing. Customer and supplier accounts, inventory, collections and more in one panel, completely free.

Start Freee-Commerce Integration

Read similar articles

19 free tools

Commission, shipping, VAT and profit calculations in your browser, no sign-up needed.

All tools