MNGT 377 – Production/Operations Management
Instructions
The file “Unit 1 Review Project Template.xlsx” contains a handful of data sets on separate worksheets; use these data sets while you complete the problems below. For each topic, you will see a brief introduction to the problem and then a list of instructions, labeled in italics e.g. Moving Average Part 1.
Note that ALL of your work should lie inside of the yellow-shaded cells. Also, make sure you read the problem thoroughly and complete all of the instructions for each topic before moving on to the next worksheet. Lastly, be aware that hard-coding (typing numbers directly into cells or formulas) will result in ZERO credit for the question. To incentivize you in this regard, the data sets are tied to random-number generation functions in Excel, so they will constantly update. If you try to solve the problem by hand, the problem will change as you enter the answer, and you will find your answer is then incorrect.
Moving Average
There are three tables on the “Moving Average” worksheet for you to use while computing Naïve, Moving Average, and Weighted Moving Average Forecasts.
Moving Average Part 1 Complete the yellow-shaded cells in column D to perform a Naïve Forecast. Remember, don’t just type the values from columns C into the formulas, use references or else your formulas won’t work when the data values update!
Moving Average Part 2 Complete the yellow-shaded cells in column H, rows 7 through 13, to calculate a three-period moving average. Note that H4:H6 are grayed out, so you shouldn’t try to fill in anything in those cells.
Moving Average Part 3 Complete the yellow-shaded cells in column H, rows 20 through 26, to calculate a three-period weighted moving average, using the weights in D17:D19. I strongly recommend using the SUMPRODUCT() function to perform the multiplication and subsequent addition. Use the largest weight (D19) for the most-recent period and the smallest weight (D17) for the least-recent period used in the forecast. Again, there are grayed-out cells that you should not complete; only enter formulas into the yellow-shaded cells.
Exponential Smoothing
Use the data on the “Exponential Smoothing” worksheet to compute an Exponential Smoothing forecast for the various smoothing constants (alpha values) specified in D3:H3. As always, skip the grayed-out cells, and your formulas should work for any set of data that appears.
Hint: this section is made considerably easier through the use of partially-absolute cell references. If you are unfamiliar with absolute cell references, check out this tutorial on relative references. The video is a little under 4 minutes long (the runtime is 5:24, but the last 1:30 is off topic) and does a good job at succinctly covering what you need to know. In this case, if you row-lock (the row doesn’t change) the smoothing constant and column-lock (the column doesn’t change) the demand, you can use the fill handle to copy the formula from D5 to the rest of the yellow-filled cells.
I’ve gone ahead and provided a line chart that illustrates the different forecasts generated by each smoothing constants. This is already complete, so you don’t need to do anything with it.
Measures of Error
The “Exponential Smoothing” worksheet contains three tables for you to use while computing the Mean Absolute Deviation (MAD), Mean Squared Error (MSE), and Mean Absolute Percent Error (MAPE) respectively. There aren’t any tricks here, but be sure to pay attention to the column labels so you don’t skip steps.
Exponential Smoothing with Trend
The “Exponential Smoothing w Trend” worksheet contains the necessary information to compute the Forecast Including Trend, FITt. As usual, do not alter cells that have a gray background, and constrain your solutions to the yellow-filled cells.
Least Squares Projections
The “Least Squares” worksheet contains the necessary information to follow the Least Squares procedure for computing Trend Projections. As usual, do not alter cells that have a gray background, and constrain your solutions to the yellow-filled cells.
As an aside, there is a much easier way to compute regression equations – actually, there are two easier ways, but I strongly prefer one of them – but those will be covered in MNGT 379 – Business Analytics, so we are going to stick with doing things manually. That is, explicitly defining what Excel does to compute the values of the regression equation.
Seasonality
The “Seasonality” worksheet contains the necessary information to determine appropriate seasonality indices and create seasonal forecasts for both regular and Trend-Adjusted data. Parts 1-4 direct you to compute the regular (non-trend-adjusted) forecast while Parts 5 and 6 provide the instructions to compute the trend-adjusted version.
Seasonality Part 1 Start by using the AVERAGE() function to compute the average demand for each month, entering the result in column G.
Seasonality Part 2 Next, compute the average demand per month across all thirty-six (36) months in the data set and enter the result into each cell in column H (every cell in the range H6:H17 should contain the same value). This can easily be computed by either applying the AVERAGE() function to all thirty-six (36) months of the original data set or to the twelve (12) Average Demands you computed in Seasonality Part 1.
Seasonality Part 3 After that, use the previous two results to compute the Seasonal Index for each month. Enter this result into column I.
Seasonality Part 4 Combine the result from Seasonality Part 3 and the Estimated Demand in Year 4 (this number is already divided by 12 to provide a per-month rate) to compute a seasonally-adjusted forecast for each month, and enter the results in column K.
Seasonality Part 5 From there, use the values for a and b provided in cells M18:M19 to compute a trend-adjusted forecast for each month of year 4, and enter the results in column M. Note: three years will have elapsed before Month 1 begins in year 4, so Month 1 be considered month 37 for the purposes of the trend projection.
Seasonality Part 6 Finally, use your previous results in columns I and M to compute a Trend-Adjusted Seasonal Forecast for year 4, and put the results into Column N.
Tracking Signals
The “Tracking Signals” worksheet contains the necessary information to calculate tracking signals for ten periods’ worth of data. The provided template follows the same layout and process as from slide 53 in the lecture slides, so refer to that if you need guidance in filling out this table. Of course, pay close attention to the column labels as they outline the process to follow to compute the tracking signals.
