Question

In: Operations Management

Use Excel to calculate the values to fill in the empty boxes. Feel free to add...

Use Excel to calculate the values to fill in the empty boxes. Feel free to add additional tables and calculations as
needed. Please use the assignment 1 discussion board to ask questions. Once completed, save this file and
upload it in Canvas.
Historical Demand Data 2012 to 2016:
The table reproduced below is the demand data for a company (aggregated) for the previous five years.
2012 2013 2014 2015 2016
Q1 11632 15034 16117 15565 16470
Q2 22509 26824 24169 20151 42858
Q3 21646 13314 14505 13392 19278
Q4 11355 10698 11176 10613 13934
Annual Demand 67,142 65,870 65,967 59,721 92,540
Average Quarterly Demand 16,785.50 16,467.50 16,491.75 14,930.25 23,135.00
Forecasting Using Moving Average Methods
Using the historical demand data above, you are to determine the total annual demand forecast for 2016 and 2017 using:
Ø the three-period moving average forecasting method
Ø the three-period weighted moving average method with weights of .6, .3, and .1 use .6 for most recent period (Use .6 for most recent period)
Enter your forecast results in the following tables.
2016 Annual Forecast Using a Moving Average 2016 Annual Forecast Using a Weighted Moving Average 2017 Annual Forecast Using a Moving Average 2017 Annual Forecast Using a Weighted Moving Average
63,852.67 62209.7 72742.67 80037
Calculate a Time Series Linear equation using the all of the above demand data:
Using the historical demand data for 2012 through 2016, create a linear equation with the year as the independent variable and the annual volume as the dependent variable.
(Excel can be quite useful here, consider the Slope and Intercept functions)
Enter your linear equation in text from here:     y = a + bX
Calculated 2017 Annual Forecast from Linear Equation:   x=2017
Forecasting Using an Exponential Smoothing Method and Seasonal Factors:
Using the historical demand data for 2012 through 2016 given on the first page you are to:
Ø Using the exponential smoothing forecasting method with an alpha value of 0.7, forecast the total annual demand for 2017. Start your forecast calculations with the total annual demand for 2012 and a starting forecast for 2012 that is the  
    same as the 2012 total annual demand. After you have the annual forecast for 2012, use the average seasonal factors determined in the first part above to calculate the quarterly demand forecasts for 2017.
Ø Determine the average seasonal factors for each quarter. Remember that you will first need to calculate the total annual demand and then average quarterly demand for each year of data as shown in lecture.
Ø Determine the MAD, CFE, and MAPE errors between the annual forecast values using exponential smoothing for 2013 to 2016 and the actual annual demand data for 2013 to 2016. Enter the values in response to the three questions below.
2012 2013 2014 2015 2016 2017
Actual Annual Demand 67,142 65,870 65,967 59,721 92,540 alpha= 0.7
Forecasted Annual Demand 67,142 67142 66251.6 66052.38 61620.414
Forecast Error -1,272 -285 -6,331 30,920
Values Seasonal Factor for each Quarter 2017 Quarterly Forecast Index 2012 2013 2014 2015 2016
Quarter 1 0.89 Q1 0.69 0.90 0.96 0.93 0.98
Quarter 2 1.63 Q2 1.34 1.60 1.44 1.20 2.55
Quarter 3 0.98 Q3 1.29 0.79 0.86 0.80 1.15
Quarter 4 0.69 Q4 0.68 0.64 0.67 0.63 0.83
Totals 4.19
What is the MAD value for the exponential smoothing forecast? Answer =
What is the CFE value for the exponential smoothing forecast? Answer =
What is the MAPE value for the exponential smoothing forecast? Answer =
Forecasting using trend with regression:
Calculate forecasts for 2017, 2016 and 2015 using a linear regression of the previous three actual demand values.  
(Hint: You will need to calculate three different linear equations.)
2012 2013 2014 2015 2016 2017
Actual Annual Demand 67,142 65,870 65,967 59,721 92,540 slope -587.5
Forecasted Annual Demand 67,142 67,142 66,252 66,052 61,620 intercept
Forecast Error -6,331 30,920
What is the MAD value for the trend with regression forecast? Answer =
What is the CFE value for the trend with regression forecast? Answer =
What is the MAPE value for the trend with regression forecast? Answer =

Solutions

Expert Solution

Formulas:

A10 =AVERAGE(C6:E6)

B10 =E6*0.6+D6*0.3+C6*0.1

E10 =AVERAGE(D6:F6)

F10 =F6*0.6+E6*0.3+D6*0.1

B13 =ROUND(INTERCEPT(B6:F6,B1:F1),2)&" + "&ROUND(SLOPE(B6:F6,B1:F1),2)&"X"

D15 =-8921657.8+4464.7*2017

C20 =B20+(B19-B20)*$F$17 copy to C20:F20

C21 =C19-C20 copy to C21:F21

C22 =ABS(C21) copy to C22:F22

C23 =C22/C19   copy to C23:F23

E25 =AVERAGE(C22:F22)

E26 =SUM(C21:F21)

E27 =AVERAGE(C23:F23)

B33 =-8921657.8+4464.7*B31 copy to B33:F33

C34 =C32-C33 copy to C34:F34

C35 =ABS(C34) copy to C35:F35

C36 =C35/C32 copy to C36:F36

E38 =AVERAGE(C35:F35)

E39 =SUM(C34:F34)

E40 =AVERAGE(C36:F36)

keosha answered 1 year ago
Next > < Previous

Related Solutions

Use Excel to calculate the values to fill in the empty boxes. Feel free to add...
Use Excel to calculate the values to fill in the empty boxes. Feel free to add additional tables and calculations as needed. Historical Demand Data 2012 to 2016: The table reproduced below is the demand data for a company (aggregated) for the previous five years. 2012 2013 2014 2015 2016 Q1 11632 15034 16117 15565 16470 Q2 22509 26824 24169 20151 42858 Q3 21646 13314 14505 13392 19278 Q4 11355 10698 11176 10613 13934 Annual Demand 67,142 65,870 65,967 59,721...
Type or paste question here Fill in the empty cells and the values required in the...
Type or paste question here Fill in the empty cells and the values required in the last row of the table. These values will help you answer subsequent questions and calculate the Pearson’s r and the linear regression equation. (2 decimals) X Y ()( 3 3 -2.00 -4.00 4.00 16.00 8.00 6 9 1.00 2.00 1.00 4.00 2.00 5 8 0.00 1.00 0.00 1.00 0.00 4 3 -1.00 -4.00 1.00 16.00 4.00 7 10 2.00 3.00 4.00 9.00 6.00 5...
The given table contains values of Total Benefits and Total Costs for a period of five years. Calculate all other values and fill the table. Use the provided excel file.
The given table contains values of Total Benefits and Total Costs for a period of five years. Calculate all other values and fill the table. Use the provided excel file. 
Fill in the empty cells in the following table in order to calculate the standard error of the estimate.
  Fill in the empty cells in the following table in order to calculate the standard error of the estimate.  X Y       3 3 6.00 -3.00 9.00 6 9 7.50 1.50 2.25 5 8 7.00 1.00 1.00 4 3 6.50 3.50 12.25 7 10 8.00 2.00 4.00 5 9 7.00 2.00 4.00 --------------- --------------- --------------- --------------- Sum = 32.5 Note: Sum in the last column is , which is SSresidual. F. Use the formula and information on...
It is common in many industrial areas to use a filling machine to fill boxes full...
It is common in many industrial areas to use a filling machine to fill boxes full of product. This occurs in the food industry as well as other areas in which the product is used in the home, for example, detergent. These machines are not perfect, and indeed they may A, fill to specification, B, underfill, and C, overfill. Generally the practice of underfilling is that which one hopes to avoid. Let P(C) = 0.052 while P(A) = 0.940. (a)...
• Implement the codes must use the LinkedList implementation • Add an additional empty node (“dummy...
• Implement the codes must use the LinkedList implementation • Add an additional empty node (“dummy node”) that connects the end of the list with the beginning, transforming the list to a circular list Code in c++ The Josephus problem is named after the historian Flavius Josephus, who lived between the years 37 and 100 CE. Josephus was a reluctant leader of the Jewish revolt against the Roman Empire. When it appeared that Josephus and his band were to be...
It is common in many industrial areas to use a filling machine to fill boxes full of product.
 It is common in many industrial areas to use a filling machine to fill boxes full of product. This occurs in the food industry as well as other areas in which the product is used in the home, for example, detergent. These machines are not perfect, and indeed they may A, fill to specification, B, underfill, and C, overfill. Generally the practice of underfilling is that which one hopes to avoid. Let P(C) = 0.034 while P(A) = 0.960. (a) What...
Use Excel to generate 100 N (10,5) distributed random values (that is 100 values of a...
Use Excel to generate 100 N (10,5) distributed random values (that is 100 values of a Normally distributed random variable with mean 10 and standard deviation 5). Plot histogram for your data. See Appendix on how to generate these values.
Use the information in the 1st table (Weights & Periods) to fill in the missing values...
Use the information in the 1st table (Weights & Periods) to fill in the missing values in the second table Weights Applied Period 5 Last month 3 Two months ago 1 Three months ago Month Actual Sales 3-Month Weighted Moving Average January 100 February 150 March 130 April 160 (  (  * 100 ) + (  *  ) + (  *  ) /  = 133 May 120 (  (  * 150 ) + (  *  ) + (  *  ) /  = 133 June 110 (  (  * 130 ) + (  *  ) + (  *  ) /  = 133...
Suppose MPC is 0.3, use the spending multiplier to fill in the values for an autonomous...
Suppose MPC is 0.3, use the spending multiplier to fill in the values for an autonomous government spending of $700. Year Autonomous Spending National Income Change in Income Year 1 $ 700b N/A Year 2 Year 3 Year 4 What are some of the problems with the spending multiplier? (10 Points) [You must review the literature about the multiplier]
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT