The quarterly demand for costumes at a party supplies store is shown in the table below.

Time	Period	Demand
First-quarter, year 1	1	210
Second-quarter, year 1	2	1700
Third-quarter, year 1	3	3400
Fourth-quarter, year 1	4	1400
First-quarter, year 2	5	300
Second-quarter, year 2	6	2000
Third-quarter, year 2	7	3500
Fourth-quarter, year 2	8	1800
First-quarter, year 3	9	450
Second quarter, year 3	10	2100
Third quarter, year 3	11	3600
Fourth quarter, year 3	12	2000

Conduct time-series demand forecasting in Excel and answer the following questions. 

a) How can you calculate the deseasonalized demand for period 3? Write down the formula and show your calculations.

(For questions b-d, simply put down "see Excel for solutions" in the answer box below. Make sure that you clearly mark where these numbers are calculated in the Excel workbook.)

(b) Calculate the deseasonalized demand for periods 3-10 in Excel.

(c) Use the regression function in Excel to estimate the level and the trend factors.

(d) Estimate the seasonal factors in Excel.

(e) Based on your calculations, write down the formula of the systematic component of the demand forecast.
 

Transcribed Image Text:

AutoSave OFF OFF B HES J. Home Insert. Draw Page Layout Formulas Data Review P Calibri (Body) ✓ A A 11 LA Pastel PRV BI U RE 4 Jo Merge & Center v Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. F25 + x ✓ fx B C D E F 1 Week 4 Quiz Exercise - Demand Forecasting Part 2 and 3 Time Period first quarter, year 1 second quarter, year 1 third quarter, year 1 fourth quarter, year 1 first quarter, year 2 second quarter, year 2 third quarter, year 2 fourth quarter, year 2 first quarter, year 3 second quarter, year 3. third quarter, year 3 fourth quarter, year 3 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 Sheet1 Demand deseasonalized observation demand 1 210 2 1700 3 3400 4 1400 5 300 6 2000 7 3500 8 1800 450 2100 3600 2000 9 10 11 12 View forecast class exercise_template-Saved to my Mac 22 Wrap Text Number H D $ Y 588 * Cell Conditional Format Formatting as Table Styles T J K G % 9 H L Insert v Delete Format v M WEX Sort & X Filler M N E E 0 Find & Select Share P a ☺ Comments Sensitivity Check for Updates Q -+ Ideas 125% R V AutoSave OFF OFF B HES J. Home Insert. Draw Page Layout Formulas Data Review P Calibri (Body) ✓ A A 11 LA Pastel PRV BI U RE 4 Jo Merge & Center v Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. F25 + x ✓ fx B C D E F 1 Week 4 Quiz Exercise - Demand Forecasting Part 2 and 3 Time Period first quarter, year 1 second quarter, year 1 third quarter, year 1 fourth quarter, year 1 first quarter, year 2 second quarter, year 2 third quarter, year 2 fourth quarter, year 2 first quarter, year 3 second quarter, year 3. third quarter, year 3 fourth quarter, year 3 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 Sheet1 Demand deseasonalized observation demand 1 210 2 1700 3 3400 4 1400 5 300 6 2000 7 3500 8 1800 450 2100 3600 2000 9 10 11 12 View forecast class exercise_template-Saved to my Mac 22 Wrap Text Number H D $ Y 588 * Cell Conditional Format Formatting as Table Styles T J K G % 9 H L Insert v Delete Format v M WEX Sort & X Filler M N E E 0 Find & Select Share P a ☺ Comments Sensitivity Check for Updates Q -+ Ideas 125% R V

Answer rating: 100% (QA)

Time Period Demand Firstquarter year 1 1 210 Secondquarter year 1 2 1700 Thirdquarter year 1 3 3400 Fourthquarter year 1 4 1400 Firstquarter year 2 5 300 Secondquarter year 2 6 2000 Thirdquarter year ... View the full answer