Quiz: Quiz 3 This Question: 14 pts 9 of 15 (12 complete) Sales of Volkswagen\'s
ID: 397723 • Letter: Q
Question
Quiz: Quiz 3 This Question: 14 pts 9 of 15 (12 complete) Sales of Volkswagen's popular Beetle have grown steadily at auto dealerships in Nevada during the past 5 years (see table below). YearSales 455 495 520 575 580 2 a) Forecasted sales for year 6 using the trend projection (linear regression) method are sales (round your response to one decimal place). b) The MAD for a linear regression forecast is sales (round your response to one decimal place). o) The MSE for the linear regression forecast is sales (round your response to one decimal place). Enter your answer in each of the answer boxes.Explanation / Answer
Let the linear regression equation is :
Y = a + b.X
Where,
Y ( dependent variable ) = Sales
X ( Independent variable ) = Year
A, b = constants
We now place all values of year as well as sales ( as provided ) in two adjacent columns in excel and apply the formula LINEST ( ) to obtain the values of a and b
Accordingly values of a and b are as follows :
A= 426
B = 33
Therefore, Linear regression equation is :
Y = 426 + 33.X
Hence forecasted sales for year 6 = 426 + 33 x 6 = 426 + 198 = 624
Refer below mentioned table. The forecasted sales for all periods ( 1 through 5 ) are filled up using the above linear regression equation .
Also to be noted :
Absolut deviation = Absolute difference between Forecasted sales and Actual sales
Squared error = ( Absolute deviation ) ^2
Mean absolute Deviation ( MAD ) = Sum of absolute deviation / 5 ( i.e. number of observations)
Mean Squared Error ( MSE ) = Sum of squared error / 5 ( i.e. number of observations)
Year
Sales
Forecast
Absolute deviation
Square error
1
455
459
4
16
2
495
492
3
9
3
520
525
5
25
4
575
558
17
289
5
580
591
11
121
SUM =
40
460
Thus,
Mean Absolute deviation ( MAD ) = 40/5 = 8
Mean squared error ( MSE ) = 460 / 5 = 92
- FORECASTED SALES FOR YEAR 6 = 624
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.