Sales Forecasting Template
Sales Forecasting Excel Template will provide a projection of what your business sales are going to be in the future!
Big companies in FMCG industries invest a lot of money in ERP systems and training to get their employs capable about how to use those systems. This demand planning excel tool has the advantage to work as the same way as Forecasting module, it has the versatility to add new SKU’s (Stock Keeping Unit) with their own automatic code number created and calculate Sales Forecast according to Holt-Winter’s Exponential Smoothing Method.
We have built this Sales Forecasting Template to help you to calculate and release your sales according to the corrected historical data monthly, in the same way, ERP systems works.
With this tool, you have the chance to calculate and release your sales forecasting. Here is what you can do:
- You can add or create new products in a simple Sales History Data Worksheet with an automatic code number for each SKU.
- For mature products, you can also upload your corrected sales history data in related Worksheet.
- In Set Forecast Worksheet, you can calculate your sales forecast by changing the statistical constants: Level, Trend, and Seasonality according to your knowledge as demand planner to reduce MAPE (Mean Average Percentage Error).
- You can adjust your final forecast on a quarterly basis watching changes in the dynamic
SALES FORECASTING TEMPLATE FEATURES:
Basically, this Excel template consists of 3 main parts:
Now, let’s check the three main sections of our Sales Forecasting Template:
In this sheet, you can add or create new products by filling the blank fields. In this case, new products must be forecasted under expected sales assumptions according to Min. Expected Sales and Max. Expected Sales which represents our sales history basis.
This section works as a simple Master Data setting up an automatic code when you save your new product. As Master Data, statistical constants for Level, Trend, and Seasonality will take 0.3 as default value. You will be able to change these values when you execute sales forecast on each new product. Other constants for quarter adjustments on final forecast are going to be set up on 100% as the default value; you can also change these values on forecast execution.
In case you have Mature Products as data coming from another system, you can also import this data by pressing the UNLOCK DATA button and fields will be unlocked to paste your own Sales History Data.
We recommend at least 4 years of corrected history for an accurate forecast. In case you don’t have enough sales history, we recommend you complete missing values with moving average.
Warning!: Do not input zero values as sales history; in that case, complete the sales history with moving average. Save your input data by pressing the SAVE CHANGES button.
Finally, for uploaded mature products, you should set up the values of the statistical: Level, Trend, and Seasonality and Quarter Adjustments on 100% in Final Forecast worksheet.
Set Forecast (Winter’s Exponential Smoothing)
In this section, you are going to be able to run and calculate your Sales Forecast. The only thing you have to do is select any product from object list.
After that the default constant values will appear on Current Constant(s) fields; now you are able to change statistical constant values: Level, Trend, and Seasonality at the right side scrolling at the object list with values between 0,001 and 1,000.
Dynamic Chart will change automatically. We recommend lower values to cover corrected history as much as possible and get more accurate forecast reducing MAPE (Mean Average Percentage Error).
**Take into account the constant values close to 1,000 which will set your calculation as Naive Forecast.
Furthermore, as a Demand Planner, you may know special promotions lead by marketing colleagues or some fluctuations on market share; in this case, you are able to adjust your calculated forecast on the quarterly basis with final sales forecast at the right side on “Error Indicators & Adjustments”
Finally, do not forget to save your sales forecast pressing the “SAVE FORECAST” button.
Final Forecast (Last Estimated Forecast)
In the last section of the Sales Forecasting Template you will see your forecast values after saving your executed forecast. Your new forecast is now 48 months sales forecast in the planning horizon and you will able to change it anytime you want, just get back Navigation Menu and press the “Set Forecast” button.
Finally, do not forget to update your real sales according to section “SALES HISTORY DATA” at the beginning of each month. Updating your real sales history will be your basis to run the next Sales Forecast with your new Sales Forecasting tool.
SALES FORECASTING TEMPLATE FEATURES SUMMARY
- Unique Excel template to perform Sales Forecast by SKU
- Works on Windows
- Ready-to-use Sales Forecasting
- No installations needed
- Dynamic Charts
- Compatible with Excel 2010 and later versions