A |
B |
C |
D |
E |
F |
|
A4 |
|
|
|
|
|
|
A5 |
Total assets |
$1.000.000 |
||||
A6 |
Amount of debt |
$650.000 |
||||
A7 |
Amount of equity |
$450.000 |
||||
A8 |
Interest expense |
|
|
|
10% |
|
A9 |
||||||
A10 |
Estimate for EBIT level in a very bad economy (Scenario A) |
$85.000 |
||||
A11 |
|
|||||
A12 |
CALCULATION TABLE |
|
|
|
||
A13 |
State of Economy |
Very Bad |
Fair |
Good |
Very Good |
|
A14 |
Probability |
0,25 |
0,25 |
0,25 |
0,25 |
|
A15 |
EBIT growth factor |
1,0000 |
1,2857 |
1,5714 |
1,8571 |
|
A16 |
EBIT |
$85.000 |
$109.285 |
$133.569 |
$157.854 |
=$E$10*F15 |
A17 |
Interest |
$65.000 |
$65.000 |
$65.000 |
$65.000 |
=$E$6*$E$8 |
A18 |
Earnings |
$20.000 |
$44.285 |
$68.569 |
$92.854 |
=F16-F17 |
A19 |
Expected EBIT |
|
|
|
$121.427 |
=(C16*C14)+(D16*D14)+(E16*E14)+(F16*F14) |
A20 |
Expected earnings |
|
|
|
$56.427 |
=(C18*C14)+(D18*D14)+(E18*E14)+(F18*F14) |
A21 |
Expected return on assets |
12% |
=F19/F5 |
|||
A22 |
Expected return on equity |
13% |
=F20/F7 |
|||
A23 |
Standard deviation on return on equity |
6,03% |
=(C14*(C18/F7-F22)^2+D14*(D18/F7-F22)^2+E14*(E18/F7-F22)^2+F14*(F18/F7-F22)^2)^0,5 |
|||
A24 |
Coefficient of variation |
|
|
|
0,481 |
=F23/F22 |