Reference no: EM133168799

Forecasting Case Study:  Boone Factory Forecasting Model

Boone Factory is a new, modernized factory in the heart of the Midwest that has been producing widgets for the past two years. There has been success because the widgets have been skillfully manufactured and demand has been good. Management and employees are proud of their production facility geared towards "lean" operations along with the high-quality widgets which are "benchmarked" by other widget manufacturers.

Management, led by the CEO, Mr. James Boone, is very concerned because demand has been very dynamic with highs and lows that they want to gauge better to reduce inventory, better match demand, and have the appropriate production workers at the factory. Management, along with all the employees, are very much aware of the hallmarks of operations management through training and brainstorming sessions to build streamlined processes and innovation.

Forecasting has been used to some extent by management including the "naïve" method, along with qualitative methods including consumer surveys, and executive/salesforce opinions. Management would like to review the historical widget demand and produce a Moving Average Demand Forecast given demand in the last three periods for the year.

1.  Review the step-by-step exercise presented below.
2. We are doing a Moving Average for three periods; this means that we start with period 4 (April) in order to calculate the prior three periods. 
3. We are simply doing an average and rounding it to a whole number.
4. You can press "Control+C" for copy on the April forecast formula. Go to the next row in the same column press "Shift" and use your mouse to cover the rest of the column starting in May all the way down to the end of the column, and then press "Control+V" to paste the copied information into these cells.
5. The formulas and answers are provided in Column D.
6. You will have a clear visual of the pattern we are using.  For the April forecast, once you have the formula inserted, you can simply copy the formula down to each row down.
7. The explanations provided in the Column D "Forecast" column is simply to show you what happens for each period.
8. To see if your formulas are working, go on the Forecast cell that you want to check and press "F2". 
9. You do not have to do the line chart in Excel as indicated in the video.

1. This is what the Excel workbook looks like for the exercise above:

B:   Your Turn! 

Time to give you some hands-on practice in calculating Moving Averages.  Please complete the following calculations using the yellow "Template" tab on the provided Excel template: 

1. Review the step-by-step exercise above provided by your instructor noted in section A above, and complete the following Moving Average exercise, located in the template:

1. To see if your formulas are working, go on the Forecast cell that you want to check and press "F2". 
2. You do not have to do the line chart in Excel as indicated in the video.