Based the principle that easy and friendly to apply this model in practice to forecast C1 product of MAD Ltd, especially provide information for the people that don’t equipped with any skills on VBA and forecasting knowledge. This model using solver, Holt-Winters forecasting method and VBA to achieve automatically calculation to obtain the result of next quarter or next whole year forecast.
This report will first give a guidance on how to use this model and the accuracy of this model. Then illustrate what is forecasting system, computer programming and VBA and how these theory apply in this model. Finally, indicated the maintain process of this model, and the limitations of this model if user want to conduct further process
Guidance of this forecasting model
In order to obtain accuracy amount of C1 product of next quarter, manager should insert the observation data of current quarter of C1 amount first by click the bottom at the front of the excel. Then it will ask user to insert the data of current quarter sales amount. Tap in actual number of current quarter sales amount, then excel will ask user to make sure the data had been taped in is correct, if its incorrect, model will be exist automatically, user should press the bottom again to insert correct data. If the data had been inserted is correct, excel will show up a result output box which is due to excel running solver to minimum error of forecasting error. Finally, excel will come out a message box of C1 product of next quarter forecasting amount and forecasting total cost.
As the data is updating quarterly, update current quarter sale units will improve the accuracy to predict next quarter sales unit. This model assumes MAD Ltd will forecasting next whole year sales amount when current insert observation data is at fourth quarter. For instance, if current time of computer is 20th December 2013, the insert data should be current sale units which are the last quarter of 2013. Then excel will ask for user if they want to predict 2014 whole year amount or only want to predict the amount of first quarter in 2014. Excel will calculate automatically and show up 2014 whole year sale units and cost or 2014 first quarter sale units and cost. If current time is from 1st January to 30th September, excel will only show the prediction of next quarter sale amount and cost amount.
The observation of actual sale amount, forecast units of next year and next quarter will be insert into different column in the excel “forecasting” worksheet, and the cost amount of observation and forecasting will be insert in this worksheet separately. Every time insert new actual data, all the calculation result will be update into next row, which means that data will be saved and continuous update. Therefore user could easily compare the sale unit among observation, forecast and annual forecast.
Click the bottom “Forecasting C1 Product”
Insert actual current quarter sale units of...