A high-tech company wants to forecast the demand for their Kindle e-readers (in 000), based on...

  

 

Transcribed Image Text:

A high-tech company wants to forecast the demand for their Kindle e-readers (in 000), based on the data below. (2 marks) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 ե 14 15 Demand (000s) 3600 3400 3400 3350 3200 2950 3050 3000 2900 2650 1850 2250 2100 1900 1850 1.1 The company wants to choose a forecasting method for its data and considers Simple Moving Average (4 period and 7 period) and Weighted Moving Average (4 period with w₁=0.4, W₂ = 0.3, W3 = 0.2 and Wa = 0.1). 1.1.1 Using the above forecasting methods, forecast demand till month 15. 1.2 Make a scatter plot of the forecasts obtained with the three methods (4 and 7 period simple moving average and 4 period weighted moving average), together with the actual demand. Comment on the graphs obtained. 1.3 For each of the three methods above, calculate MAD and MAPE. Comment on these values. Which forecasting method should you choose? Why? Question 2 Use the data provided in Question 1, the Analyst would like to establish a Linear Regression model for the demand, Using Excel, please run linear regression on this data (1 mark) 2.1 2.2 What is the Coefficient of Determination (R Squared)? What does it mean? Use the model generate to predict the demand for Month # 16 Question 3 A supply chain analyst is analyzing past sales data. He obtained the following numbers: Week 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Demand 3810 3770 3680 3780 4200 3920 4010 3980 3860 3680 3700 3770 3930 4010 4240 He thinks that two methods could be used to forecast future sales: linear regression and weighted moving average. (1 mark) 3.1 Using Excel, please run linear regression on this data. Based on the results you obtain, do you think that linear regression is appropriate in this case? Why? 3.2 For the weighted moving average, the analyst wants to calculate a four-month weighted moving average with weights 0.4, 0.3, 0.2 and 0.1. Please apply the weighted moving average methods to calculate the sales forecast in weeks 5-15. APPENDIX: Suggestions for implementing Question 1-3 in Excel: 1. Create a single excel file for all three questions 2. Create a separate excel worksheet for each question 3. Make a table in Excel (one column for the month, another with the given demand data) Rest of the columns in excel sheet can be used for you working. 4. 5. Label each column with headings and other information so that it is easily understandable 6. Solutions to the discussion aspects of the questions should be submitted in a word file with appropriate reference to the analysis in excels workbook A high-tech company wants to forecast the demand for their Kindle e-readers (in 000), based on the data below. (2 marks) Month 1 2 3 4 5 6 7 8 9 10 11 12 13 ե 14 15 Demand (000s) 3600 3400 3400 3350 3200 2950 3050 3000 2900 2650 1850 2250 2100 1900 1850 1.1 The company wants to choose a forecasting method for its data and considers Simple Moving Average (4 period and 7 period) and Weighted Moving Average (4 period with w₁=0.4, W₂ = 0.3, W3 = 0.2 and Wa = 0.1). 1.1.1 Using the above forecasting methods, forecast demand till month 15. 1.2 Make a scatter plot of the forecasts obtained with the three methods (4 and 7 period simple moving average and 4 period weighted moving average), together with the actual demand. Comment on the graphs obtained. 1.3 For each of the three methods above, calculate MAD and MAPE. Comment on these values. Which forecasting method should you choose? Why? Question 2 Use the data provided in Question 1, the Analyst would like to establish a Linear Regression model for the demand, Using Excel, please run linear regression on this data (1 mark) 2.1 2.2 What is the Coefficient of Determination (R Squared)? What does it mean? Use the model generate to predict the demand for Month # 16 Question 3 A supply chain analyst is analyzing past sales data. He obtained the following numbers: Week 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Demand 3810 3770 3680 3780 4200 3920 4010 3980 3860 3680 3700 3770 3930 4010 4240 He thinks that two methods could be used to forecast future sales: linear regression and weighted moving average. (1 mark) 3.1 Using Excel, please run linear regression on this data. Based on the results you obtain, do you think that linear regression is appropriate in this case? Why? 3.2 For the weighted moving average, the analyst wants to calculate a four-month weighted moving average with weights 0.4, 0.3, 0.2 and 0.1. Please apply the weighted moving average methods to calculate the sales forecast in weeks 5-15. APPENDIX: Suggestions for implementing Question 1-3 in Excel: 1. Create a single excel file for all three questions 2. Create a separate excel worksheet for each question 3. Make a table in Excel (one column for the month, another with the given demand data) Rest of the columns in excel sheet can be used for you working. 4. 5. Label each column with headings and other information so that it is easily understandable 6. Solutions to the discussion aspects of the questions should be submitted in a word file with appropriate reference to the analysis in excels workbook