Need help with the problem being done in Excel

3. In the preceding problem, we divided the value of all the assets between two classes of investors creditors and shareholders. This process tells us where the change in value is going, but it sheds little light on where the change is coming from. Let's divide the free cash flows of the firm into pure business flows and cash flows resulting from financing effects. Now, an axiom in finance is that you should discount cash flows at a rate consistent with the risk of those cash flows. Pure business flows should be discounted at the unlevered cost of equity (i.e., the cost of capital for the unlevered firm). Financing flows should be discounted at the rate of return required by the providers of debt. 0% Debt/ 100% Equity 25% Debt/ 75% Equity 50% Debt/ 50% Equity Pure Business Cash Flows: EBIT Taxes (@ 34%) EBIAT +Depreciation Capital Exp. Cash Flow 1,485.00 504.90 980.10 500.00 (500.00) 980.10 1,485.00 504.90 980.10 500.00 (500.00) 980.10 1,485.00 504.90 980.10 500.00 (500.00) 980.10 Unlevered Beta Risk-Free Rate Market Premium Unlevered WACC 0.80 5.00% 6.00% 0.80 5.00% 6.00% 0.80 5.00% 6.00% Value of Pure Business Flows: (CF/Unlevered WACC) Financing Cash Flows Interest (from Prob. 2) Tax Reduction Pretax Cost of Debt 5.00% 5.00% 5.00% Value of Financing Effect: (Tax Reduction/Pretax Cost of Debt) Total Value (Sum of Values of Pure Business Flows and Financing Effects) The first three problems illustrate one of the most important theories in finance. This theory, developed by two professors, Franco Modigliani and Merton Miller, revolutionized the way we think about capital-structure policies. The M&M theory says, Value of Assets = Value of Value of Value of Debt + Equity = Unlevered + Firm Shields Value of Debt Tax A Problem 1 Problem 2 Problem 3