|Home | Training | Services | Testimonials | Contact | About Us|
|Sales Forecasting Software: Forecasting using Excel|
Forecasting in Excel
The most common approach to forecasting is the use of time series forecasting methods such as exponential smoothing where historical seasonality and trend are identified and used as the basis for a forward projection to create a forecast. Forecast Solutions has experience in building these methods in Excel to good effect.
Another approach to forecasting is based on causal and effect. A causal analysis can be carried out to quantify the effects of drive factors, such as pricing, weather or economic indices. Following a suitable analysis it is possible to incorporate the resulting formulae into Excel to create a forecasting system.
An advantage of Excel forecasting is that it is possible to combine the results of a causal analysis with time series projection, something not usually possible in proprietary forecasting software.
The advantage of forecasting in Excel is that there is total flexibility in designing a system to meet the precise needs of the business. Proprietary software will usually have a number of strong features, but it is hard to match the versatility Excel forecasting. For example there is a very common problem in proprietary software that the analysis of seasonality, particularly with weekly sales data, results in a very ragged seasonal profile that is not credible and not helpful for planning purposes. With a system built in Excel it is possible to deploy simple methods to overcome this problem.
Forecast Solutions can work to improve an existing forecasting process based on Excel or we can develop a completely new solution. A solution in completely standard Excel is often sufficient, but we can partner with Excel and VBA specialists such as Perspica to provide a more user friendly interface if such is desirable.
Two level working
It is often necessary to provide visibility of the forecast at two different levels of detail. For example, a FMCG company that supplies the major high street supermarkets will probably want to work on the forecast at the SKU x Account level, particularly if there is a busy promotional programme. Then there will be a need to review the forecast at the total SKU level, perhaps then feeding the result through to a stock replenishment planning process. These facilities can be provided within the Excel system, including an effective method for dealing with promotional planning.
Stock Replenishment Planning
The forecast at SKU level can be picked up and used for stock replenishment planning. Analysis of historical data or forecast accuracy, combined with the replenishment lead-time and the required customer service target enables safety stock to be suggested in terms of quantity or days cover. The current position is updated with a latest actual stock count plus the dated inbound stock arising from previous replenishment orders. The system then suggests the new replenishment order to be placed and creates a stock development plan going forward. The user can override the safety stock to be used or the actual replenishment order to be placed.
Forecasting Solutions Training Courses
Forecast Solutions offers a number of training courses and workshops. Although the courses are not intended to promote the use of any particular software they do include a number of worked examples using Microsoft Excel. These will be particularly helpful to those who already carry out forecasting in Excel or intend to do so in the future. Delegates are left with a number of working templates for common forecasting methods.
Specialist Forecasting Software
As mentioned, there is a point at which increased complexity and the need for a more sophisticated approach triggers the need for specialist demand forecasting software. We are happy to help improve the use of any existing proprietary software. Alternatively we can review requirements for software and help select the most appropriate sales forecasting software for the business.
Factors that may tilt the balance in favour of specialist forecasting software rather than Excel include: