Sales Forecasting and Demand Planning Template
Demand Planning & Sales Forecasting Template in Excel. Projection of future revenue based on historical sales data. SKU-Level Sales Forecast.
Big companies in FMCG industries invest a lot of money in ERP systems and training to get their employees capable of how to use those systems. This demand planning template in excel has the advantage to work in the same way as the Forecasting module, it has the versatility to add new SKUs (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 the 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 a demand planner to reduce MAPE (Mean Average Percentage Error).
- You can adjust your final forecast on a quarterly basis by watching changes in the dynamic
DEMAND PLANNING TEMPLATE MAIN FEATURES
Basically, this Excel template consists of 3 main parts:
Now, let’s check the three main sections of our Sales Forecasting Template:
1. Sales History
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 of this demand planning template, statistical constants for Level, Trend, and Seasonality will take 0.3 as the default value.
You will be able to change these values when you execute the sales forecast for each new product. Other constants for quarter adjustments on the 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 in this template for accurate demand planning and sales forecasting. In case you don’t have enough sales history, we recommend you complete missing values with the moving average.
Warning!: Do not input zero values as sales history; in that case, complete the sales history with the 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, Seasonality, and Quarter Adjustments on 100% in the Final Forecast worksheet.
2. 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 the object list.
After that the default constant values will appear on the Current Constant(s) fields; now you are able to change statistical constant values: Level, Trend, and Seasonality on the right side-scrolling at the object list with values between 0,001 and 1,000.
Dynamic Chart will change automatically. On this demand planning template, we recommend lower values to cover corrected history as much as possible and get a 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 a Naive Forecast.
Furthermore, as a Demand Planning tool, you may know special promotions lead by marketing colleagues or some fluctuations in market share; in this case, you are able to adjust your calculated forecast on a quarterly basis with the final sales forecast on the right side of “Error Indicators & Adjustments”
Finally, do not forget to save your sales forecast by pressing the “SAVE FORECAST” button.
3. 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 on the planning horizon. And you will be 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.
DEMAND PLANNING 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 2007 and later versions