CAPITAL BUDGET AND FINANCIAL MODELLING

Capital Budget And Financial Modeling



Capital Budget And Financial Modeling

Question 1

Year

XYZ Returns

SP 500 Returns

1992

9%

7%

1993

10%

9%

1994

10%

10%

1995

10%

12%

1996

10%

11%

1997

11%

10%

1998

11%

10%

1999

10%

9%

2000

9%

8%

2001

7%

7%

See Table 1 for the results. Results show that we have determined the same results as that of provided on the assignment sheet.

ii. The estimated beta is determined to be 0.493.

iii.

 

XYZ Returns

SP 500 Returns

Mean

10%

9%

Standard Deviation

0.011595018

0.016363917

Variance

0.000134444

0.000267778

Co-Variance

0.000119

Correlation

0.69686035

iv.

Linar Equation Based on CAPM

Risk Free Rate

Rf

Beta

0.493775934

Stock Returns

10%

Markets Returns

9%

 

Return on XYZ = Rf + Beta ( Rm - Rxyz)

Return on XYZ = Rf + 0.494 x (0.09 - .10)

Return on XYZ = Rf - 0.00494

v.

 

Goal Seek

at Rf = 10.1%

Risk Free Rate

10.20%

10.10%

Return on XYZ

10%

10.30%

Based on the data analysis tool in excel, goal seek, the risk free rate is determined to be 10.2%. We have entered the capm formula in a cell and set the result equal to the XYZ average return. Using goal seek, we change the risk free rate in a way that yields the same result for return as its average return i.e., 10%.

vi.

Year

XYZ Returns

SP 500 Returns

X Variable 1

X Variable 2

1992

9%

7%

1

1

1993

10%

9%

2

4

1994

10%

10%

3

9

1995

10%

12%

4

16

1996

10%

11%

5

25

1997

11%

10%

6

36

1998

11%

10%

7

49

1999

10%

9%

8

64

2000

9%

8%

9

81

2001

7%

7%

10

100

First requirement

According to the statistical analysis of the data via the application of regression analysis, it can be interpreted that the co-efficient representing each of the study variable is significant. The alpha values representing X variable 1 and X variable 2 are less than the threshold selected (0.05). Moreover, the co-efficient for X variable 1 is 0.020098 which asserts that one unit change in the independent variable will affect the dependent variable by 0.020098 units. In addition, the coefficient representing X variable 2 is equal to -0.00193 which asserts that one unit change in the independent variable will negatively influence the dependent variable by 0.00193 unit change. The coefficients representing both variables are less than 0.05; hence, the effect depicted by the independent variable is significant not hypothetical.

Third Requirement

According to the analysis of the data through regression analysis, it can be interpreted that the alpha value of the model prescribed in the table of ANOVA is statistically significant as the sig value is less than 0.05 (0.000952). Since the model is statistically significant and the independent variables tend to have a significant impact on the dependent variable; hence, it can be interpreted that the model can be successfully used in forecasting the future returns. The independent variable used in this model is momentously influenced by the dependent variables. Therefore, the future analysis carried out through the prescribed statistical model will also reveal suitable outcomes that can be of essential importance in forecasting future returns.

Four Requirements

After determining the statistical outcomes using regression analysis on model, we have found the same results. See Table 2. Table 1

Regression Statistics

Multiple R

0.69686035

R Square

0.485614348

Adjusted R Square

0.421316141

Standard Error

0.008820478

Observations

10

ANOVA

 

df

SS

MS

F

Significance F

Regression

1

0.000587593

0.000587593

7.552533333

0.025129216

Residual

8

0.000622407

7.78008E-05

Total

9

0.00121

 

 

 

 

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Lower 95.0%

Upper 95.0%

Intercept

0.051078838

0.016940822

3.015133433

0.016682621

0.012013233

0.090144443

0.012013233

0.090144443

SP 500 Returns

0.493775934

0.17967332

2.748187281

0.025129216

0.079448514

0.908103353

0.079448514

0.908103353

Table 2

SUMMARY OUTPUT

Regression Statistics

Multiple R

0.928982061

R Square

0.86300767

Adjusted R Square

0.823867004

Standard Error

0.006867642

Observations

10

ANOVA

 

df

SS

MS

F

Significance F

Regression

2

0.002079848

0.00103992

22.0488756

0.000951562

Residual

7

0.000330152

4.7165E-05

Total

9

0.00241

 

 

 

 

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%

Lower 95.0%

Upper 95.0%

Intercept

0.056833333

0.00807739

7.03610125

0.00020489

0.037733341

0.075933325

0.037733341

0.075933325

X Variable 1

0.020098485

0.003373459

5.95782769

0.00056561

0.012121523

0.028075447

0.012121523

0.028075447

X Variable 2

-0.001931818

0.000298876

-6.46361653

0.00034579

-0.002638547

-0.001225089

-0.002638547

-0.001225089

Question 2:10 year lease on a piece of of specialized mining equipment

Interest Rate 4% converted monthly. Be for an amount financed to $1000000. Have a residual payment of 10% of the ...