Top

Sales Price Mileage

Cars speed
(in 1,000 units) (in lakh rupees) (Km/ltr) (Km/hr)
Rocinante 1 171.877 6.1 15.8 168.2
Rocinante 2 139.796 6.1 12.1 149.6
Rocinante 3 178.947 9.9 17 173.4
Rocinante 4 140.022 5.8 11.6 170.6
Rocinante 5 186.476 10 17.2 175
Rocinante 6 192.123 6.5 17.6 173.1
Rocinante 7 175.085 5.5 16 184.6
Rocinante 8 146.882 8.4 13 175.7
Rocinante 9 202.847 6.6 19.3 166.7
Rocinante 10 149.933 8.8 13.3 175.4
Rocinante 11 171.579 9.5 15.8 150.9
Rocinante 12 201.512 9.2 19.2 140.5
Rocinante 13 142.604 8.1 11.7 150.2
Rocinante 14 135.158 5.4 11.2 146.2
Rocinante 15 107.322 5.6 7.8 151.2
Rocinante 16 199.699 6.3 19 143.2
Rocinante 17 202.875 5.8 19.7 178.9
Rocinante 18 204.406 7.7 19.5 168.2
Rocinante 19 107.331 7.2 7.5 164.7
Rocinante 20 190.345 8.4 18.1 182.7
Rocinante 21 108.419 8.4 7.9 165.7
Rocinante 22 150.249 5.7 12.6 137

Rocinante 23 155.882 5.5 12.9 173.9

Rocinante 24 100.98 8.4 7.6 165.1

Rocinante 25 182.679 5.6 16.7 160.1
Rocinante 26 166.752 9.9 15.5 155.6
Rocinante 27 121.561 6.2 9.5 155.4
Rocinante 28 174.256 9.9 16.3 161.9
Rocinante 29 119.018 6.5 9.1 146.8
Rocinante 30 169.842 7.1 15.3 149.5
Rocinante 31 198.311 9.6 19.1 146.5
Rocinante 32 204.875 5.8 19.4 178.9
Rocinante 33 119.561 6.2 9.4 175.4
Rocinante 34 203.875 5.9 19.4 175.9
Rocinante 35 118.561 6.1 9.4 165.4

Specification
Rocinante36
s
Price ₹7 lakh
Manufacturin
₹6 lakh
g cost
Mileage 22 km/litre
Top speed 140 km/hr

Q. 6

Q. 7
Dependant Variable (Y) Sales
Independent Variable (X) Price, Mileage & Top Speed

SUMMARY OUTPUT - All variables

Regression Statistics
Multiple R 0.99788065450456
R Square 0.995765800634449
Adjusted R Square 0.995356039405525
Standard Error 2.30779153196702
Observations 35

ANOVA
df SS
Regression 3 38827.6180187373
Residual 31 165.102954405579
Total 34 38992.7209731429

Coefficients Standard Error

Intercept 50.7231271792463 5.29210288470814
Price
(in lakh rupees) -0.795026440875324 0.254333076043226
Mileage
(Km/ltr) 8.30633109237939 0.100181306818659
Top speed (Km/hr) -0.018572563992949 0.030558235449191

Beta Coefficients Significance

50.7231271792463 8.7390238740411E-11
Price -0.795026440875324 0.003833462977011
Mileage 8.30633109237939 5.7835637568169E-38
Top Speed -0.018572563992949 0.547763026123756

Regression equation 50.723+(-0.7950*Price)+(8.3063*mileage)

Calculate Sales Units in Thousands Actual Units

Sales in Units 227.8967 227896.7

Overall Predicted Profit Sales*(Price - Mfg Cost)

 227896.7 Lakhs

Calculate Impact on sales if price increased by Rs. 1 lakh

Regression equation 50.723+(-0.7950*Price)+(8.3063*mileage)
Old Sales 227.8967 227,897
New Sales 227.1017 227,102
Change -0.794999999999988 -795
Change in profit sales*(Price-Mfg cost)
2043.9153 2043915.3

Which Model impacted more?

As per predicted sales, increase in price lead to decrease in sales i.e Rocinante36 sales decreased by 795 Units wherein, sales o
Marengo32 decreased by 186.7 units.
Rerun the regression equation using only significant variables and compare the R square & Adjusted R
Rocinante36
Only Significant
Parameter All variables Variables With
significant variables
R Square 0.995765800634449 0.995715346496514
Adjusted R Square 0.995356039405525 0.995447555652547

R Square value alaways goes up if variable is added

Adjusted R sqaure goes up when added variable is significant
Adjusted R Sqaure may decrease if added variable is not significant
 MS F Significance F
12942.53934 2430.112296 7.4472712E-37 >0.05 Regression is not possible
5.325901755

t Stat P-value Lower 95% Upper 95%Lower 95.0%Upper 95.0%

9.5846827404 8.73902E-11 39.9298121862 61.51644 39.9298122 61.51644

-3.1259262588 0.003833463 -1.3137421693 -0.276311 -1.3137422 -0.276311

82.91298403 5.78356E-38 8.10200997005 8.510652 8.10200997 8.510652

-0.6077760617 0.547763026 -0.0808964961 0.043751 -0.0808965 0.043751

If P value <0.05 then significant

Significant
Significant
Non Significant
 % proportion change with existing sales
-0.35%

in Lakhs

ed by 795 Units wherein, sales of

SUMMARY OUTPUT - Using only significant v

Diff
Regression Statistics
-5.045414E-05 Multiple R
9.1516247E-05 R Square
Adjusted R Square
Standard Error
Observations

ANOVA

Regression

Residual

Total

Intercept
Price
(in lakh rupees)
Mileage
(Km/ltr)
Y OUTPUT - Using only significant variables

egression Statistics
0.9978553735
0.9957153465
0.9954475557
2.2849391303
35

df SS MS F Significance F
2 38825.6506746148 19412.825337 3718.257621295 1E-38

32 167.070298528013 5.220946829

34 38992.7209731429

Coefficients Standard Error t Stat P-value Lower 95%

Upper 95%Lower 95.0%
47.779964019 2.11344142050695 22.607659505 3.03812605E-21 43.475 52.0849 43.4750247

-0.782925328 0.251041783157933 -3.118705252 0.003827345185 -1.294 -0.27157 -1.29428071

8.2944664974 0.097287931072783 85.256890613 2.60256971E-39 8.0963 8.492636 8.09629747

Upper 95.0%
52.08490332

-0.27156995

8.492635528
 Top
Sales Price Mileage
Cars speed
(in 1,000 units) (in lakh rupees) (Km/hr) (Km/ltr)

Marengo 1 20.896 42.5 199.4 9.3

Marengo 2 31.048 36 235.2 9.7
Marengo 3 29.904 54.7 240.8 16.6
Marengo 4 28.792 42.7 232.5 11.7
Marengo 5 16.776 44.9 188.8 13.7
Marengo 6 18.928 35.5 184.2 9.6
Marengo 7 22.776 51.3 207.7 13.7
Marengo 8 36.824 30.4 249.5 12.6
Marengo 9 22.216 38.4 175.8 16.2
Marengo 10 35.456 32.2 245.6 9.6
Marengo 11 28.576 59.4 223.3 11.3
Marengo 12 33.648 50.8 240 13.6
Marengo 13 33.44 57.5 250 13.5
Marengo 14 34.912 44.6 247.4 11.1
Marengo 15 24.016 34.6 192 14.1
Marengo 16 20.992 56.4 201.2 13.4
Marengo 17 21.696 41.4 193.3 12.8
Marengo 18 17.832 53.7 202 16
Marengo 19 25.624 44.8 208.9 12.7
Marengo 20 21.92 32.4 176.9 13.6
Marengo 21 24.792 40.9 218.2 11.6

Marengo 22 22.256 44.1 190 11.4

Marengo 23 15.488 46.6 176.8 16.3

Marengo 24 21.64 31.4 178.9 14.5
Marengo 25 23.264 54.7 192.3 14.3
Marengo 26 19.328 37.9 184.1 9.4
Marengo 27 27.216 52.9 240.3 15.3
Marengo 28 23.096 42.7 192.1 15
Marengo 29 17.824 54.9 199 13.5
Marengo 30 19.144 44 181.1 11.8
Marengo 31 21.6 42.2 181.2 9.3

Specificatio
Marengo32
ns
Price ₹41 lakh
Manufacturi
₹33 lakh
ng cost
Mileage 15 km/litre
Top speed 210 km/hr

Q. 6

Q. 7
Dependant Variable (Y) Sales
Independent Variable (X) Price, Mileage & Top Speed

SUMMARY OUTPUT

Regression Statistics
Multiple R 0.929025133175836
R Square 0.863087698072379
Adjusted R Square 0.847875220080421
Standard Error 2.30673039512172
Observations 31

ANOVA
df SS MS F
Regression 3 905.669817744951 301.8899392483 56.73551
Residual 27 143.667138126017 5.321005115778
Total 30 1049.33695587097

Coefficients Standard Error t Stat P-value

Intercept -13.4476488883451 4.45379402893311 -3.01936928403 0.005479
Price
(in lakh rupees) -0.186728172116573 0.055438363262194 -3.36821221134 0.002288
Mileage
(Km/ltr) 0.041301186988648 0.206640301103274 0.199869951641 0.84308
Top speed (Km/hr) 0.22080168203862 0.017223150084434 12.82005213658 5.388E-13

Beta Coefficients Significance If P value <0.05 then significant

-13.4476488883451 0.00547896316725
Price -0.186728172116573 0.002288376733801 Significant
Mileage 0.041301186988648 0.843080033902266 Non significant
Top Speed 0.22080168203862 5.3882749436174E-13 Significant

Regression equation (-13.4476)+(-0.1867*Price)+(0.2208*top speed)

Calculate Sales Units in Thousands Actual Units

Sales in Units 25.2657 25265.7

Overall Predicted Profit Sales*(Price - Mfg Cost)
202125.6000 in Lakhs

Calculate Impact on sales if price increased by Rs. 1 lakh

Regression equation (-13.4476)+(-0.1867*Price)+(0.2208*top speed) % proportion change with existi

Old Sales 25.2657 25265.7 -0.74%
New Sales 25 25079

Change -0.1867 -186.7000

Change in profit sales*(Revised Price-Mfg cost)
50.16 50,158.00 in lakhs

Which Model impacted more?

As per predicted sales, increase in price lead to decrease in sales i.e Rocinante36 sales decreased by
795 Units wherein, sales of Marengo32 decreased by 186.7 units.
Regression coefficient of price of Rocinante36 is higher than regression coefficient of price of
Marengo32, however % change in sales unit of Rocinante36 after price increase is negligible compared
to old sales units (227896.7 units) i.e 0.35% whereas Marengo32 sales decreased by 186.7 units
compared to units before price change (25265.7 units) which is almost 0.74% .
This
Rerundipthe
in regression
sales will impact profitability
equation using onlyof significant
model withvariables and compare the R square & Adjusted R
Marengo32
That’s why impact of increase in price is higher on Marengo32 than Rocinante36 .
Parameter All variables Only Significant Variabl Diff
R Square 0.863087698072379 0.862885128727765 -0.00020256934
Adjusted R Square 0.847875220080421 0.853091209351177 0.005215989271

R Square value alaways goes up if variable is added

Adjusted R sqaure goes up when added variable is significant
Adjusted R Sqaure may decrease if added variable is not significant
 Significance F
8.74E-12

Lower 95% Upper 95%Lower 95.0%Upper 95.0%

-22.586079391 -4.309218 -22.58608 -4.309218

-0.3004782976 -0.072978 -0.300478 -0.072978

-0.3826896887 0.465292 -0.38269 0.465292

0.18546269711 0.256141 0.185463 0.256141

hen significant
% proportion change with existing sales

SUMMARY OUTPUT With significant variables

Regression Statistics
Multiple R 0.928916
R Square 0.862885
Adjusted R 0.853091
Standard E 2.266839
Observatio 31

ANOVA
df SS MS F Significance F
Regression 2 905.4573 452.7286 88.10417 8.302E-13
Residual 28 143.8797 5.138561
Total 30 1049.337

Coefficients
Standard Error t Stat P-value Lower 95%Upper 95%Lower 95.0%
Upper 95.0%
Intercept -12.97013 3.69376 -3.511363 0.001531 -20.53646 -5.403807 -20.53646 -5.403807

Price
(in lakh
rupees) -0.182474 0.050304 -3.627423 0.00113 -0.285517 -0.079431 -0.285517 -0.079431
Top speed 0.22014 0.01661 13.25378 1.38E-13 0.186117 0.254163 0.186117 0.254163
Upper 95.0%