Financial Modeling Case

Technical Details

At the beginning of the process, assuming the value of assets as $120 million with D/A of 30%, and then by using basic user input data to calculate the value of several required terms, such as debt (D), cost of debt (rD), beta of equity (βE), cost of equity (rE), Taxable income, Net income, equity E, Assets (A), WACC.

Since bondholders and stockholders expect high returns, the value of beta E calculated by using formula,

[pic 1]

CAPM.Based on user input data and using the CAPM to calculate the cost of equity (rE) and the cost of debt (rD):

[pic 2]

[pic 3]

Where, rf = risk-free rate,

        βE = beta of equity,

        βD = beta of debt

        MRP = market risk premium.

Taxable income calculated by, Taxable income = Operating income-Financial distress cost-Interest cost.

Net income = Taxable income * (1-Tax rate).

To set a button of “Restore Factory Setting”, which is allowed user to back to the factory setting of inputs, the Macro is to be required to assign with the button.

WACC.User can select two types of financial distress costs, F/A and F/EBIT0. Since the value of F is not given, we projected the value of F/EBIT0 same as the value of F/A. By selecting either F/A or F/EBIT0, they give different value of WACC. More detailed, for choosing financial distress costs specified as F/A, the WACC is calculated by,

[pic 4]

However, if choosing F/EBIT0 to be financial distress costs, the equation for WACC is given by,

[pic 5]

Debt Schedule. The optimal D/A shown in this method is identified from among the discrete choices in the debt schedule that the user has input. The content of this line is exactly the same from the highlighted row in the input area, which contains the maximum asset. Firstly a MAX function is used for determining the maximum asset from the user input. Then a MATCH function is used for locating the row of the maximum asset. Finally an INDEX function is used to refer to the value of each field.

Stepwise. The optimal D/A shown in this method is equal to the D of the lower D/A of the range, with the assumption that D for D/A locates between two consecutive levels of D/A in the debt schedule.

Piecewise Linear. In this approach, the relation between D and D/A between two consecutive levels of D/A is a straight line. Therefore, D calculated by performing a linear interpolation formula as below:

[pic 6]

Where subscripts L and H denote the lower level and higher level of D/A in the debt schedule. When D/A is beyond the range that the user input, the value of D is calculated by linear extrapolate with the nearest (D/A, D) pairs. Similarly, the relation between (F/A, D) is determined exactly the same way as stated above.