Giapetto's Woodcarving, Inc. manufactures several types of wooden products. Geoff McQuilkin is the production manager for...

 

 
 

Transcribed Image Text:

Giapetto's Woodcarving, Inc. manufactures several types of wooden products. Geoff McQuilkin is the production manager for wooden chests and toy soldiers. It is now December 31st 2022. Geoff wishes to forecast next month's (January 2023) demand for both chests and soldiers. Historical demand for the last two years (2021 and 2022) (in thousands of units) for Wooden Chests and for the last three years (2020 to 2022) for Toy Soldiers (again in thousands of units) is given in Tables 1 and 2 below: Table 1: Historical Sales Figures for Wooden Chests (thousands of units) Month Sales Month Sales Month Sales Month January 2021 February 2021 March 2021 April 2021 May 2021 June 2021 275 278 279 287 289 290 January 2020 February 2020 March 2020 July 2021 August 2021 April 2020 May 2020 June 2020 July 2020 August 2020 September 2020 October 2020 November 2020 December 2020 September 2021 October 2021 November 2021 December 2021 291 66 83 98 293 299 300 301 January 2022 February 2022 March January 2021 February 2021 March 2021 2022 April 2022 April 2021 May 2021 June 2021 July 2021 August 2021 September 2021 October 2021 November 2021 December 2021 304 306 May 2022 303 June 2022 311 Sales 115 84 68 71 18 12 16 35 67 61 78 93 307 Table 2: Historical Sales Figures for Toy Soldiers (thousands of units) Month Sales Month Month 120 89 73 76 23 17 21 40 72 308 310 July 2022 August 2022 September 2022 October 2022 November 2022 December 2022 January 2022 February 2022 March 2022 April 2022 May 2022 June 2022 July 2022 August 2022 September 2022 October 2022 November 2022 December 2022 Sales 312 314 315 317 318 319 Sales 110 79 63 66 13 7 11 30 62 56 73 88 Build a spreadsheet model to forecast next month's demand (January 2023) for each product. Use exponential smoothing as the forecasting technique. Also set up your spreadsheet to compute the MAD, BIAS, and MSE. Assume any reasonable value for the alpha smoothing parameter. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 A B с WOODEN CHESTS Smoothing Parameter for Forecast Alpha (a) 3 Month D E Demand Average Forecast (At) (Dt) FL (Ft) Error (Et) G MAD H BIAS MSE 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 A B TOY SOLDIERS Smoothing Parameter for Forecast Alpha (a) Month Demand (Dt) D Average (At) E Forecast (Ft) Error (Et) G MAD H BIAS MSE Giapetto's Woodcarving, Inc. manufactures several types of wooden products. Geoff McQuilkin is the production manager for wooden chests and toy soldiers. It is now December 31st 2022. Geoff wishes to forecast next month's (January 2023) demand for both chests and soldiers. Historical demand for the last two years (2021 and 2022) (in thousands of units) for Wooden Chests and for the last three years (2020 to 2022) for Toy Soldiers (again in thousands of units) is given in Tables 1 and 2 below: Table 1: Historical Sales Figures for Wooden Chests (thousands of units) Month Sales Month Sales Month Sales Month January 2021 February 2021 March 2021 April 2021 May 2021 June 2021 275 278 279 287 289 290 January 2020 February 2020 March 2020 July 2021 August 2021 April 2020 May 2020 June 2020 July 2020 August 2020 September 2020 October 2020 November 2020 December 2020 September 2021 October 2021 November 2021 December 2021 291 66 83 98 293 299 300 301 January 2022 February 2022 March January 2021 February 2021 March 2021 2022 April 2022 April 2021 May 2021 June 2021 July 2021 August 2021 September 2021 October 2021 November 2021 December 2021 304 306 May 2022 303 June 2022 311 Sales 115 84 68 71 18 12 16 35 67 61 78 93 307 Table 2: Historical Sales Figures for Toy Soldiers (thousands of units) Month Sales Month Month 120 89 73 76 23 17 21 40 72 308 310 July 2022 August 2022 September 2022 October 2022 November 2022 December 2022 January 2022 February 2022 March 2022 April 2022 May 2022 June 2022 July 2022 August 2022 September 2022 October 2022 November 2022 December 2022 Sales 312 314 315 317 318 319 Sales 110 79 63 66 13 7 11 30 62 56 73 88 Build a spreadsheet model to forecast next month's demand (January 2023) for each product. Use exponential smoothing as the forecasting technique. Also set up your spreadsheet to compute the MAD, BIAS, and MSE. Assume any reasonable value for the alpha smoothing parameter. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 A B с WOODEN CHESTS Smoothing Parameter for Forecast Alpha (a) 3 Month D E Demand Average Forecast (At) (Dt) FL (Ft) Error (Et) G MAD H BIAS MSE 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 A B TOY SOLDIERS Smoothing Parameter for Forecast Alpha (a) Month Demand (Dt) D Average (At) E Forecast (Ft) Error (Et) G MAD H BIAS MSE

Answer rating: 100% (QA)

1 a 1 lev... View the full answer