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 in order 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 "nave" 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.

A: Moving Average Forecast - Explanation of Process

1. Please watch the following 4-minute video by Scmprofrutgers covering the, "Moving Average Forecast in Excel":Moving Averages (YouTube Video)

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.

Row 1	Column A Period	Column B Month	Column C	Column D Forecast
Row 2	1	Jan
Row 3	2	Feb	100
Row 4	3	March	65	Repeat as noted in each forecast row
Row 5	4	April	125	Formula to Type: =round(average(C2:C4),0) Answer: 72
Row 6	5	May		Formula to Type: =round(average(C3:C5),0) Answer: 97
Row 7	6	June	75	Formula to Type: =round(average(C4:C6),0) Answer: 97
Row 8	7	July	80	Formula to Type: =round(average(C5:C7),0) Answer: 100
Row 9	8	August	95	Formula to Type: =round(average(C6:C8),0) Answer: 85
Row 10	9	September	55	Formula to Type: =round(average(C7:C9),0) Answer: 83
Row 11	10	October	90	Formula to Type: =round(average(C8:C10),0) Answer: 77
Row 12	11	November	125	Formula to Type: =round(average(C9:C11),0) Answer: 80
Row 13	12	December	125	Formula to Type: =round(average(C10:C12),0) Answer: 90
Row 14	13	January		Formula to Type: =round(average(C11:C13),0) Answer: 113

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. If you need to have extra guidance, please re-watch the following 4-minute video covering the, "Moving Average Forecast in Excel".

Moving Averages (YouTube Video)

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

Month	Demand	Forecast
Jan	100
Feb	200
March	130
April	250
May	200
June	150
July	160
August	190
September	110
October	180
November	250
December	250
January

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.