Question

A financial analyst engaged in business valuation obtained financial data on 71 drug companies. Let Y correspond to the​ price-to-book value​ ratio, X1 correspond to the return on​ equity, and X2 correspond to the growth percentage. Use the accompanying data to complete parts a. through e. below.

Price/Book Value Ratio   Return on Equity   Growth%
1.542   12.907   6.508
8.318   11.834   135.645
2.002   12.292   0.105
6.539   25.085   14.264
1.283   8.725   22.836
3.228   38.078   19.078
2.449   25.564   24.663
5.363   19.646   11.692
2.288   22.864   49.979
7.745   69.629   36.793
0.427   3.799   41.008
2.492   9.173   28.801
7.716   29.199   52.003
5.207   17.821   25.109
2.141   29.305   23.926
4.701   31.473   9.484
2.205   14.699   18.464
3.987   11.983   39.184
1.853   14.212   39.512
1.568   14.086   27.082
1.945   14.842   13.154
5.043   20.645   17.266
2.402   14.792   15.935
2.061   5.679   16.697
2.895   11.202   8.386
1.775   16.255   18.286
5.495   24.004   16.761
4.722   14.658   46.442
2.532   6.175   34.053
1.736   19.036   8.595
8.509   39.003   15.028
2.288   15.109   25.053
2.805   19.723   0.299
7.422   18.484   3.213
3.308   20.667   9.601
2.725   34.597   7.055
2.485   15.558   9.485
1.218   10.201   4.647
2.922   23.595   4.057
10.153   91.563   13.267
2.119   1.518   15.803
1.592   9.331   5.703
2.081   19.414   0.071
7.211   5.056   102.681
1.237   42.818   1.588
5.734   90.903   74.072
6.435   19.475   8.923
2.697   27.357   34.435
3.449   13.057   12.105
7.031   24.512   11.599
13.738   81.927   24.506
3.965   1.505   20.266
7.135   3.574   22.226
6.173   31.458   49.851
0.985   5.114   13.289
9.343   47.816   61.191
1.313   13.338   10.761
1.043   35.969   9.143
3.808   28.787   71.102
3.591   17.997   51.744
2.248   13.924   17.045
10.026   132.942   171.276
4.188   21.871   8.614
8.405   11.344   247.699
2.095   17.358   10.863
4.123   19.419   6.425
2.349   8.552   24.613
2.955   18.601   14.207
4.532   21.533   5.816
5.037   49.394   31.464
2.142   19.379   3.863

a. Develop a regression model to predict​ price-to-book-value ratio based on return on equity.

Yi=____ + ____X1i

​(Round to four decimal places as​ needed.)

b. Develop a regression model to predict​ price-to-book-value ratio based on growth.

Yi =____ + ____X2i

​(Round to four decimal places as​ needed.)

c. Develop a regression model to predict​ price-to-book-value ratio based on return on equity and growth.

Yi =____ + ____X1i + ____X2i

​(Round to four decimal places as​ needed.)

d. Compute and interpret the adjusted r2 for each of the three models.

Start with the part​ (a) model.

The adjusted r2 shows that ___% of the variation in ________ is explained by ______ _____ correcting for the number of independent variables in the model.

​(Round to one decimal place as​ needed.)

Compute and interpret the adjusted r2 for the part​ (b) model.

The adjusted r2 shows that ___% of the variation in ____ is explained by ____ ____ correcting for the number of independent variables in the model.

​(Round to one decimal place as​ needed.)

Compute and interpret the adjusted r2 for the part​ (c) model.

The adjusted r2 shows that ____%of the variation in ____ ____ is explained by ____ ____ correcting for the number of independent variables in the model.

​(Round to one decimal place as​ needed.)

e. Which of these three models do you think is the best predictor of​ price-to-book-value ratio?

The model from ___ is the best predictor of​ price-to-book-value ratio because it has the ____ value of ____.

Answer 1

Steps to do Regression in Excel

1. Click on Data analysis tool on the Data Tab and select Regression.
2. Select Y Values and X Values and check labels option.
3. Click on OK. This will give the output for Regression.

a)

Model 1

Price/Book Value Ratio = 2.3388 + 0.0702 * Return on Equity

b)

Model 2

Price/Book Value Ratio = 3.1444 + 0.0306 * Growth

c)

Model 3

Price/Book Value Ratio = 1.9539 + 0.0602 * Return on Equity + 0.0216 * Growth

d)

Start with the part​ (a) model.

The adjusted r2 shows that 33.03% of the variation in Price/Book Value Ratio is explained by Return on Equity correcting for the number of independent variables in the model.

​(Round to one decimal place as​ needed.)

Compute and interpret the adjusted r2 for the part​ (b) model.

The adjusted r2 shows that 18.69% of the variation in Price/Book Value Ratio is explained by Growth correcting for the number of independent variables in the model.

​(Round to one decimal place as​ needed.)

Compute and interpret the adjusted r2 for the part​ (c) model.

The adjusted r2 shows that 41.47% of the variation in Price/Book Value Ratio is explained by Return on Equity and Growth correcting for the number of independent variables in the model.

e)

Even though R Square for Model 3 is highest, still Model 1 is the best Model as Model 3 is being penalized for including an irrelevant independent variable. (Shown by the gap between R Square and Adjusted R Square)