Forecasting sales is useful for many reasons, such as inventory management, investor relations, and setting expense and marketing budgets. But producing accurate forecasts can be confusing. In this post, I’ll explain how to generate forecasts using Microsoft Excel.
To start, identify the objective. What is the purpose of your analysis? Then gather data and run a forecasting model.
First, establish the timeline. Are you looking to predict the next 12 months, the next five years, or the next 30 days? For a 12-month analysis, it is best to have at least three years of data to establish seasonality trends. If you only have a few months of data, use it to estimate the next 30 days or so.
Next, gather the data based on the type of forecast.
|Sales by geography||Sales volume by desired region (i.e., country, state).|
You can predict sales for most key metrics as long as you have the historical data.
Once you have the data in a table form, use the TREND function in Excel for your predictions, as follows.
= TREND(Historical Sales, Historical Timeline, Forecast Timeline)
In the Excel screenshot, below, the formula is:
Click image to enlarge.
Note that the TREND formula in Excel is linear. But, for most ecommerce companies, sales are not consistent throughout the year. Sales in October, November, and December can account for most of the annual total. Building seasonality effects into a forecasting model is a bit more complicated.
- Step 1. Calculate the average historical sales per month.
- Step 2. Compute a seasonality adjustment for each month.
- Step 3. Multiply the seasonality…