Question

In the past, Peter Kelle's tire dealership in Baton Rouge sold an average of 1,100 radials each year. In the past 2 years, 220 and 250, respectively were sold in fall, 340 and 300 in winter, 150 and 165 in spring, and 320 and 455 in summer. With a major expansion planned, kelle projects sales next year to increase to 1,300 radials. Based on next year's projected sales, the demand for each season is going to be enter your responses as whole numbers): Demand Season Fall Winter Spring Summer

Answer 1

First we take the average value of demand values of 2 years ago and last year

Average of 2 years ago=AVERAGE(B3:B6)=257.5

Average of last year=AVERAGE(C3:C6)=292.5

Seasonal index for Fall of 2 years ago=Demand value/Avg value of 2 years ago=B3/$B$7=0.854

Seasonal index for Winter of 2 years ago=Demand value/Avg value of 2 years ago=B4/$B$7=1.320

Seasonal index for Spring of 2 years ago=Demand value/Avg value of 2 years ago=B5/$B$7=0.583

Seasonal index for Summer of 2 years ago=Demand value/Avg value of 2 years ago=B6/$B$7=1.243

Similarly for last year

Overall seasonal index for Fall =Average of the seasonal indices of 2 years ago and last year=AVERAGE(B11:C11)

=0.855

Overall seasonal index for Winter =Average of the seasonal indices of 2 years ago and last year=AVERAGE(B12:C12)

=1.173

Overall seasonal index for Spring =Average of the seasonal indices of 2 years ago and last year=AVERAGE(B13:C13)

=0.573

Overall seasonal index for Summer =Average of the seasonal indices of 2 years ago and last year=AVERAGE(B14:C14)

=1.399

Given total demand for next year =1300

Average of demand of next year =1300/4=325

Forecast of next year =Seasonal index *Average value

Forecast of next year during Fall=D11*325=278

Forecast of next year during Winter=D12*325=381

Forecast of next year during Spring=D13*325=186

Forecast of next year during Summer=D14*325=455

E11 fx =D11*325 A 1 2 12 years ago Last year 3 Fall 220 250 4 Winter 340 300 5 Spring 150 165 6 Summer 320 455 7 Average 257.5 292.5 8 10 11 Fall 12 Winter 13 Spring 14 Summer Seasonal index Overall Forecast seasonal for Next 2 years ago Last year index year 0.854 0.855 0.855 278 1.320 1.026 1.173 381 0.583 0.564 0.573 186 1.243 1.556 1.399 455 1300 Total demand Next year Average of next year