Question

The manager of a utility company in the Texas panhandle wants to develop quarterly forecasts of power loads for the next year. The power loads are​ seasonal, and the data on the quarterly loads in megawatts​ (MW) for the last four years are as​follows:

                                                                                          

Quarter	Year 1	Year 2	Year 3	Year 4
1	103.8	92.2	117.2	102.4
2	126.4	113.9	140.5	132.6
3	145.9	139.7	165.7	155.6
4	164.7	150.4	180.5	169.0

The manager estimates the total demand for the next year at 663663 MW. Use the multiplicative seasonal method to develop the forecast for each quarter. ​(Round all intermediate calculations to three decimal​places.)

The first quarter forecast is ______ MW. ​(Enter your response rounded to one decimal​ place.)

The second quarter forecast is ______ MW. ​(Enter your response rounded to one decimal​ place.)

The third quarter forecast is _______ MW. ​(Enter your response rounded to one decimal​ place.)

The fourth quarter forecast is _______ MW. ​(Enter your response rounded to one decimal​ place.)

Answer 1

First we take the average value of demand values of year 1 ,

Average of year 1=AVERAGE(B3:B6)=135.2

Average of year 2=AVERAGE(C3:C6)=124.05

Similarly for years 3 and 4.

Seasonal index for Quarter 1 of year 1=Demand value/Avg value of year 1=B3/$B$7=0.7677

Seasonal index for Quarter 2 of year 1=Demand value/Avg value of year 1=B4/$B$7=0.9349

Seasonal index for Quarter 3 of year 1=Demand value/Avg value of year 1=B5/$B$7=1.079

Seasonal index for Quarter 4 of year 1=Demand value/Avg value of year 1=B6/$B$7=1.218

Similarly for years 2,3,4

Overall seasonal index for quarter 1 =Average of the seasonal indices of year 1,2,3,4=AVERAGE(B11:E11)

=0.7548

Overall seasonal index for quarter 2 =Average of the seasonal indices of year 1,2,3,4=AVERAGE(B12:E12)

=0.9329

Overall seasonal index for quarter 3 =Average of the seasonal indices of year 1,2,3,4=AVERAGE(B13:E13)

=1.1038

Overall seasonal index for quarter 4 =Average of the seasonal indices of year 1,2,3,4=AVERAGE(B14:E14)

=1.2085

Given total demand for next year =663

Average of demand of next year =663/4=165.75

Forecast of next year =Seasonal index *Average value

Forecast for first quarter=F11*$C$17=125.1

Forecast for second quarter=F12*$C$17=154.6

Forecast for three quarter=F13*$C$17=182.9

Forecast for four quarter=F14*$C$17=200.3

Calculations are as shown below: