Question

(USE ONLY EXCEL SHEET FOR DATA,  FORMULAS, and the GRAPHS)

Ø  For the following list of data (number of sold cars in every year ) , apply “exponential smoothing with trend” for all the years below. (i.e find the Forecast including trend)   (20%):

---- case1  use :  α= 0.2     and β = 0.05

---- case2  use:   for  α= 0.05     and β = 0.2

Calculate MAD for both cases. Decide which  case values are better and why?

Assume trend is zero for the 2012 years.

Year	Number of sold cars
2012	150
2013	173
2014	166
2015	167
2016	180
2017	185
2018	190
2019	210
2020	199

Ø  Based on least squared regression method  find the projections for the years 2021, 2022, and 2023. Plot the number of sold cars versus years (points graph) and show the trend line based on your projections. (5%).

(USE ONLY EXCEL SHEET FOR DATA, FORMULAS, and the GRAPHS) ► For the following list of data (number of sold cars in every year), apply "exponential smoothing with trend" for all the years below. (i.e find the Forecast including trend) (20%): ---- casel use : Q=0.2 and B = 0.05 ---- case2 use: for a=0.05 and B = 0.2 Calculate MAD for both cases. Decide which case values are better and why? Assume trend is zero for the 2012 years. Year 2012 2013 2014 2015 2016 2017 2018 2019 2020 Number of sold cars 150 173 166 167 180 185 190 210 199 Based on least squared regression method find the projections for the years 2021, 2022, and 2023. Plot the number of sold cars versus years (points graph) and show the trend line based on your projections. (5%). There will be on a 5 minutes presentation of the project. (5%)

Answer 1

Question 1:

The Excel output is:

V30 X foc A B с D E G H j K 4 5 Alpha 6 Beta 7 Data 0.2 0.05 Forecasts and Error Analysis Smoothe Forecast d Smoothe including Forecast, d Trend, Trend, Ft Tt FITt Error Abs Pct Absolute Squared Err 8 Period Demand 150 0 173 7.5 157.5 166 9 Period 1 10 Period 2 11 Period 3 12 Period 4 13 Period 5 14 Period 6 15 Period 7 16 Period 8 17 Period 9 150 154.6 156.88 158.904 167 180 23 23 529 13.29% 11.41 11.41 129.96 06.87% 10.12 10.12 102.4144 06.06% 21.096 21.096 445.0412 11.72% 21.8768 21.8768 478.5944 11.83% 22.50144 22.50144 506.3148 11.84% 38.00115| 38.00115| 1444.088 18.10% 19.40092 19.40092 376.3958 0.097492 185 190 163.1232 167.4986 171.9988 210 199 179.5991 18 19 Total Average 20 167.3963 167.3963 4011.808 89.46% 20.92454 20.92454 501.476 11.18% Bias MAD MSE MAPE 21 22 SE 25.85797 23

The Excel formulas are:

U31 f с D E H 4 5 Alpha 6 Beta 7 Data 0.2 0.05 Forecasts and Error Analysis Forecast Including Trend, FITT Smoothed Forecast, Ft Smoothed Trend, Tt Error Absolute Squared Abs Pct Err =$B$6*(D10-D9)+(1-$B$6)*E9 =D10+E10 8 Period 9 Period 1 10 Period 2 11 Period 3 12 Period 4 13 Period 5 14 Period 6 15 Period 7 16 Period 8 17 Period 9 Demand 150 173 166 167 180 185 190 210 199 =B9 =+D10+$B$5*(B10-D10) =+D11+$B$5*(B11-D11) =+D12+$B$5*(B12-D12) =+D13+$B$5*(B13-D13) =+D14+$B$5*(B14-D14) =+D15+$B$5*(B15-D15) =+D16+$B$5*(B16-D16) =B10-D10 =B11-011 =B12-D12 =B13-D13 =B14-014 =B15-D15 =B16-D16 =B17-017 =ABS(G10) |=ABS(G11) =ABS(G12) |=ABS(G13) =ABS(G14) |=ABS(G15) =ABS(G16) =ABS(G17) =G1042 =G1112 =G1212 =G1342 =G1412 =G1542 I=G16^2 I=G1742 =H10/B10 =H11/B11 =H12/B12 =H13/B13 =H14/B14 =H15/B15 =H16/B16 =H17/B17 18 19 20 Total Average =SUM(G9:617) =SUM(H9:H17) =SUM(19:117) =SUM(19:17) =AVERAGE(G9:617) =AVERAGE(H9:H17) =AVERAGE(19:117) =AVERAGE(19:J17) Bias MAD MSE MAPE SE =SQRT(119/(COUNT(19:117)-2)) 21 22 23

The Excel output is:

T30 X for A B с D E F H J K 4 5 Alpha 6 Beta 7 Data 0.05 0.2 Forecasts and Error Analysis Forecast Including Trend, FITt Error Abs Pct Smoothed Smoothed Forecast, Ft Trend, Tt Demand Absolute Squared Err 150 0 173 150 30 180 529 13.29% 08.95% 166 220.5225 167 151.15 151.8925 152.647875 228.2365563 8 Period 9 Period 1 10 Period 2 11 Period 3 12 Period 4 13 Period 5 14 Period 6 15 Period 7 16 Period 8 17 Period 9 09.05% 23 23 14.85 14.85 15.1075 15.1075 27.35213 27.35213 30.98452| 30.98452 34.43529) 34.43529 180 185 190 154.0154813 155.5647072 157.2864718 159.9221482 748.138742 15.20% 960.0404022 16.75% 1185.789391 18.12% 2778.716052 25.10% 1527.078498 0.196371 210 52.71353] 52.71353 39.07785| 39.07785 199 18 19 Total 20 Average 237.5208 237.5208 29.6901 29.6901 Bias MAD 8177.522142 126.09% 1022.190268 15.76% MSE MAPE 36.91775125 21 22 SE 23

The Excel formulas are:

α= 0.2     and β = 0.05 is better since it has a lower MAD value.

T30 X fx A B D E F G H - 0.05 4 5 Alpha 6 Beta 7 Data 0.2 Forecasts and Error Analysis 8 Period Demand Smoothed Forecast, Ft Error Absolute Squared Abs Pct Err Forecast Including Smoothed Trend, Tt Trend, FITT 0 =$B$6*(D10-D9)+(1-$B$6) *E9 =D10+E10 150 173 =B10-D10 =G10^2 166 167 I=B11-D11 =B12-D12 9 Period 1 10 Period 2 11 Period 3 12 Period 4 13 Period 5 14 Period 6 15 Period 7 16 Period 8 17 Period 9 =G11^2 =G12^2 =G1342 180 =B9 =+D10+$B$5*(B10-D10) =+D11+$B$5*(B11-D11) =+D12+$B$5*(B12-D12) =+D13+$B$5*(B13-D13) =+D14+$B$5*(B14-D14) =+D15+$B$5*(B15-D15) =+D16+$B$5*(B16-D16) I=B13-D13 =B14-D14 =ABS(G10) =ABS(G11) =ABS(G12) |=ABS(G13) =ABS(G14) =ABS(G15) =ABS(G16) =ABS(G17) =H10/B10 =H11/B11 =H12/B12 =H13/B13 =H14/B14 =H15/B15 =H16/B16 =H17/B17 185 190 =B15-D15 =G1412 =G1542 =G16^2 =G1742 210 =B16-D16 199 I=B17-D17 18 19 Total Average 20 =SUM(G9:G17) =SUM(H9:H17) =SUM(19:117) =SUM(19:J17) =AVERAGE(G9:G17) =AVERAGE(H9:H17) =AVERAGE(19:117) =AVERAGE(19:J17) Bias MAD MSE MAPE SE =SQRT(119/(COUNT(19:117)-2)) 21 22 23

Question 2:

The Excel output is:

W36 A B D 1 J K L M N 0 P Q R S T Regression Demand (y) Period(x) 150 1 173 2 250 166 3 167 y = 6.2167x + 148.92 4 200 6 Data 7 Period 8 Period 1 9 Period 2 10 Period 3 11 Period 4 12 Period 5 13 Period 6 14 Period 7 15 Period 8 16 Period 9 17 5 F F G H Forecasts and Error Analysis Forecast Error Absolute Squared Abs Pct Err 155.1333 -5.13333 5.133333 26.35111 03.42% 161.35 11.65 11.65 135.7225 06.73% 167.5667 -1.56667 1.566667 2.454444 00.94% 173.7833 -6.78333 6.783333 46.01361 04.06% 180 0 0 0 00.00% 186.2167 -1.21667| 1.216667| 1.480278 00.66% 192.43331 -2.43333 2.433333 5.921111 01.28% 198.65 11.35 11.35| 128.8225 05.40% 204.8667 -5.86667 5.866667 34.41778 02.95% Total 5.68E-14 46 381.1833 25.45% Average 6.32E-15| 5.111111 42.3537 02.83% Bias IMAD MSE MAPE SE 7.379347 1801 185 190 210) 6 150 7 8 9 199 100 148.916667 6.21666667 18 Intercept 19 Slope 20 21 Forecast 22 50 10 211.083333 217.3 223.516667 11 Correlatio 0.926726 Coefficient of determination 0.858821 23 12 0 24 0 1 2 3 4 5 6 7 8 9 10 25 26 Series1 -Linear (Series 1) 27 28

The Excel formulas are:

W35 X A B = D C 6 Data 7 Period Period() Demand (y) 150 1 173 2. 166 3 167 4 180 5 E F G H Forecasts and Error Forecast Error Absolute Squared =$B$18+$B$19*C8 =B8-E8 =ABS(F8) |=F8^2 =$B$18+$B$19*C9 =B9-E9 =ABS(F9) =F9^2 =$B$18+$B$19*C10 =B10-E10 =ABS(F10) =F10^2 =$B$18+$B$19*011 =B11-E11 =ABS(F11) =F11^2 =$B$18+$B$19*C12 =B12-E12 =ABS(F12) =F1212 =$B$18+$B$19*013 =B13-E13 |=ABS(F13) =F1342 =$B$18+$B$19*C14 =B14-E14 =ABS(F14) =F14^2 =$B$18+$B$19*C15 =B15-E15 =ABS(F15) =F1542 =$B$18+$B$19*C16 =B16-E16 =ABS(F16) =F16^2 Total =SUM(F8:F16) =SUM(G8:16) =SUM(H8:H16) Average =AVERAGE(F8:F16) =AVERAGE(G8:G16) =AVERAGE(H8:H16) Bias MAD MSE SE =STEYX(B8:B16,C8:C16) 185 6 Abs Pct Err =G8/B8 I=G9/B9 =G10/B10 =G11/B11 =G12/B12 =G13/B13 =G14/B14 =G15/B15 =G16/B16 =SUM(18:116) =AVERAGE(18:116) MAPE 8 Period 1 9 Period 2 10 Period 3 11 Period 4 12 Period 5 13 Period 6 14 Period 7 15 Period 8 16 Period 9 17 18 Intercept 19 slope 20 21 Forecast 190 7 8 210 199 9 =INTERCEPT(B8:B16,C8:C16) =SLOPE(B8:B16,C8:C16) 10 22 =$B$18+$B$19*C21 =$B$18+$B$19*c22 =$B$18+$B$19*C23 11 12 Correlation =CORREL(B8:B16,C8:C16) ent of determination =H22*H22 23 24 25

Regression 250 200 y = 6.2167x + 148.92 150 100 50 0 0 1 2 3 4 5 6 7 8 9 10 Series1 Linear (Series 1)