Question

Vandelay Industries manufactures sailboats. Actual demand for their sailboats for each season over the past four years was as follows: YEAR SEASON 1 2 3 4 Winter 1,400 1,200 1,000 900 Spring 1,500 1,400 1,600 1,500 Summer 1,000 2,100 2,000 1,900 Fall 600 750 650 500 Complete all parts of this problem in Excel. Clearly identify the responses to each part of the question. a. (20 pts) Download ForecastSolver.xlsx from BbLearn and save it on your computer. Open the file and modify it as needed so that you can enter the necessary data and formulas for this problem into the spreadsheet. b. (20 pts) Use Excel Solver to find the best estimates for the linear trend line and the seasonal indices for this data. Provide a screenshot (or Snipping Tool capture) of the Solver Dialog Box. c. (10 pts) Use your estimates of the linear trend line and the seasonal factors to determine a forecast for sailboat demand for every season of the next year (Year 5).

Answer 1

b.

Using excel for performing regression with hours as the dependent variable and week as independent variables.

In excel ,go to data tab-->data analysis and select regression

In the regression dialog select the demand values as Y range and period values as X range and click OK.

We get the regression output with coefficients of week and intercept .

The trend equation is hours =Intercept+Coefficient of period*period

Linear trend line is , trend forecast Y=1277.5-3.235*period

Forecast for Winter of year 5 =1277.5-3.235*17 =$I$5+C19*$I$6=1222.5

Similarly for others seasons of year 5.

Next we calculate the seasonal indices for different seasons:

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

Average of demand of year 1=AVERAGE(D3:D6)=1125

Average of demand of year 2=AVERAGE(D7:D10)=1362.5

Average of demand of year 3=AVERAGE(D11:D14)=1312.5

Average of demand of year 4=AVERAGE(D15:D18)=1200

Seasonal index for Winter of year 1=Demand value/Avg demand value of year 1=D3/$B$23=1.244

Similarly for other seasons of Year 1.

Repeat the process for all the four years .

Overall seasonal index for Winter =Average of the seasonal indices of year 1 ,2,3,4

=AVERAGE(F3,F7,F11,F15)

=0.91

Similarly for other other seasons as well.

c.Trend adjusted forecast =Trend forecast *Seasonal index

Forecast for Winter of year 5=E19*F19=1112

Simiarly for other seasons of year 5.

Calculations are as shown below: