Demands Of Sea Food Essay Discussion Paper
There are actual seasonal demands of sea food (million cwt.) from 2018 spring to 2020 winter. Please use Seasonalized Regression to calculate the follow questions. (1) What are the seasons’ indexes for spring, summer, fall and winter, respectively? (2) What are forecast data of 2021 four seasons from spring to winter? (Please keep two after the decimal point).
Expert Answer
This solution was written by a subject matter expert. It’s designed to help students like you learn core concepts. Demands Of Sea Food Essay Discussion Paper
ORDER YOUR PAPER NOW
Stepbystep
1st step
All steps
Answer only
Step 1/2
Here, for the given data, I would determine the seasonal index for each quarter, I would also determine the trend regression forecast for each period, this forecast data would be influenced by the seasonal indices.
 Explanation for step 1
The detailed calculations are shown in the next section,
Step 2/2
Here, the seasonal average is the average of demand over each season,
The global average is determined using the excel formula,
The seasonal index=the seasonal average/The global average
For illustration,
The seasonal index for spring season=1016.083=0.622
A  B  C  D  E  F  

1 

Year 2018

Year 2019

Year 2020

Seasonal Average

Seasonal Index

2 
Spring

8

10

12

10

0.622

3 
Summer

13

16

13

14

0.870

4 
Fall

23

23

22

22.67

1.409

5 
Winter

14

18

21

17.67

1.098

6 
Global Average

16.083





Formula:
A  B  C  D  E  F  

1 

Year 2018

Year 2019

Year 2020

Seasonal Average

Seasonal Index

2 
Spring

8

10

12

=AVERAGE(B2:D2)

=E2/$B$6

3 
Summer

13

16

13

=AVERAGE(B3:D3)

=E3/$B$6

4 
Fall

23

23

22

=AVERAGE(B4:D4)

=E4/$B$6

5 
Winter

14

18

21

=AVERAGE(B5:D5)

=E5/$B$6

6 
Global Average

=AVERAGE(B2:D5)





A  B  C  D  

1 
Period

Demand

Regression forecast

Intercept

2 
1

8

12.72

12.11

3 
2

13

13.33

Slope

4 
3

23

13.94

0.61

5 
4

14

14.55


6 
5

10

15.17


7 
6

16

15.78


8 
7

23

16.39


9 
8

18

17.00


10 
9

12

17.61


11 
10

13

18.22


12 
11

22

18.84


13 
12

21

19.45


14 
13


20.06


15 
14


20.67


16 
15


21.28


17 
16


21.90


Formula:
A  B  C  D  

1 
Period

Demand

Regression forecast

Intercept

2 
1

8

=$D$2+($D$4*A2)

=INTERCEPT(B2:B13,A2:A13)

3 
2

13

=$D$2+($D$4*A3)

Slope

4 
3

23

=$D$2+($D$4*A4)

=SLOPE(B2:B13,A2:A13)

5 
4

14

=$D$2+($D$4*A5)


6 
5

10

=$D$2+($D$4*A6)


7 
6

16

=$D$2+($D$4*A7)


8 
7

23

=$D$2+($D$4*A8)


9 
8

18

=$D$2+($D$4*A9)


10 
9

12

=$D$2+($D$4*A10)


11 
10

13

=$D$2+($D$4*A11)


12 
11

22

=$D$2+($D$4*A12)


13 
12

21

=$D$2+($D$4*A13)


14 
13


=$D$2+($D$4*A14)


15 
14


=$D$2+($D$4*A15)


16 
15


=$D$2+($D$4*A16)


17 
16


=$D$2+($D$4*A17)


A  B  C  D  

1 

Forecast data for 2021

Seasonal Index

Final forecast for 2021

2 
Spring

20.06

0.622

12.47

3 
Summer

20.67

0.870

17.99

4 
Fall

21.28

1.409

30.00

5 
Winter

21.90

1.098

24.05

Formula:
A  B  C  D  

1 

Forecast data for 2021

Seasonal Index

Final forecast for 2021

2 
Spring

20.0606060606061

0.621761658031088

=B2*C2

3 
Summer

20.6724941724942

0.870466321243523

=B3*C3

4 
Fall

21.2843822843823

1.40932642487047

=B4*C4

5 
Winter

21.8962703962704

1.09844559585492

=B5*C5

 Explanation for step 2
The final answer is concluded in the next section,
Final answer
1)
Seasonal relatives are shown below
A  B  

1 

Seasonal Index

2 
Spring

0.622

3 
Summer

0.870

4 
Fall

1.409

5 
Winter

1.098

2)
Final forecast data are shown below,

Final forecast for 2021

Spring

12.47

Summer

17.99

Fall

30.00

Winter

24.05

Demands Of Sea Food Essay Discussion Paper