I need to understand the solution to the Rajat Bhatia HBR business case. Need help on how to calculate value of equity and re.

Regards,

Shilpana

Student Spreadsheet Valuing Rajat Bhatia's Business Plan The following Exhibits are given in the case. Prepare following statements: Exhibit 1 Projected Balance Sheet Year Equity Debt Total Laibilities 0 1000 1000 2000 1 1000 1050 2050 2 1000 1100 2100 3 1000 1150 2150 4 1000 1200 2200 ($'000) 5 1000 1250 2250 Gross Fixed Assets Less Accumulated Depreciation Net Fixed Assets Working Capital Total Assets 1500 0 1500 500 2000 1800 300 1500 550 2050 2100 600 1500 600 2100 2400 900 1500 650 2150 2700 1200 1500 700 2200 3000 1500 1500 750 2250 Exhibit 2 Projected Income Statement Year Revenue Expenses EBITDA Interest Payments Depreciation Profit Before Tax Tax Profit After Tax 1 1100 600 500 70 300 130 52 78 2 1250 650 600 73.5 300 226.5 90.6 135.9 3 1400 700 700 77 300 323 129.2 193.8 4 1550 750 800 80.5 300 419.5 167.8 251.7 ($'000) 5 1700 800 900 84 300 516 206.4 309.6 Exhibit 3 Free Cash Flow to Firm Year EBITDA less depreciation EBIT Tax @40% PAT (unlevered) add Depreciation less increase in Working Capital less Capital Expenditure FCFF 1 500 -300 200 80 120 300 -50 -300 70 2 600 -300 300 120 180 300 -50 -300 130 3 700 -300 400 160 240 300 -50 -300 190 4 800 -300 500 200 300 300 -50 -300 250 ($'000) 5 900 -300 600 240 360 300 -50 -300 310 Exhibit 4 Free Cash Flow to Equity Year Profit After Tax add Depreciation add increase in Debt less increase in WC less increase in Gross Fixed Assets FCFE 1 78 300 50 -50 -300 78 2 135.9 300 50 -50 -300 135.9 3 193.8 300 50 -50 -300 193.8 4 251.7 300 50 -50 -300 251.7 ($'000) 5 309.6 300 50 -50 -300 309.6 1 70 -50 2 73.5 -50 3 77 -50 4 80.5 -50 ($'000) 5 84 -50 20 23.5 27 30.5 34 4 251.7 30.5 282.2 ($'000) 5 309.6 34 343.6 Exhibit 5 Cash Flow to Debt Holders Year Interest Payments less increase in Debt CFD Exhibit 5 Capital Cash Flow Year Free Cash Flow to Equity Cash Flow to Debt Holders CCF 1 78 20 98 2 135.9 23.5 159.4 3 193.8 27 220.8 Market Value of Debt Valuation using Equity Cash Flow Valuation using Capital Cash Flow Valuation using Free Cash Flow to Firm Valuation using Free Cash Flow / WACC(modified) The valuation of the project is based on following parameters mentioned in the case. Interest rate: ractual 7.00% Cost of unlevered equity: ru 13.80% Growth rate: g Cost of debt: rd Tax rate: Tc Market Value of Debt Year Debt Cash Flow rdebt Value of Debt 5.00% 7.00% 40.00% 0 3 27.00 7.00% 1617.29 4 30.50 7.00% 1700.00 5 34.00 7.00% 1785.00 1 2 3 78.00 135.90 193.80 16.76% 16.57% 16.45% 1905.476 2146.794 2366.644 2562.139 1381.45 1458.15 1536.72 1617.29 3286.92 3604.94 3903.36 4179.43 4 251.70 16.38% 2730 1700.00 4430.00 5 309.60 16.34% 2866.5 1785.00 4651.50 7.00% 1381.45 Valuation using Equity Cash Flow Year FCFE re Value of Equity Debt Value of Project 1 20.00 7.00% 1458.15 2 23.50 7.00% 1536.72 0 Market Value of Equity (E) is needed to find Cost of Equity Capital (re) and conversely, Market value of Equity requires Cost of Equity Capital. This creates circularity problem in Microsoft Excel. This circularity problem is resolved by Excel by repeated iterations For references on how to resolve this circular reference, refer Excel Help on "Make a circular reference work by changing the number of times Microsoft Excel iterates formulas". Valuation using Capital Cash Flow Year 0 CCF rccf 1 98.00 12.66% 3604.94 1458.15 2146.79 2 159.40 12.70% 3903.36 1536.72 2366.64 3 220.80 12.73% 4179.43 1617.29 2562.14 4 282.20 12.75% 4430.00 1700.00 2730.00 5 343.60 12.76% 4651.50 1785.00 2866.50 Valuation using Free Cash Flow to Firm Year 0 1 FCFF 70.00 rwacc 11.48% Value of Project 3458.09 3785.08 Debt 1381.45 1458.15 Value of Equity 2076.64 2326.93 2 130.00 11.57% 4092.91 1536.72 2556.19 3 190.00 11.63% 4378.79 1617.29 2761.50 4 250.00 11.66% 4639.53 1700.00 2939.53 5 310.00 11.68% 4871.50 1785.00 3086.50 4 250.00 11.98% 4430.00 1700.00 2730.00 5 310.00 12.00% 4651.50 1785.00 2866.50 Value of Project Debt Value of Equity 3286.92 1381.45 1905.48 Valuation using Free Cash Flow to Firm, discounted at rwacc, modified Year FCFF rwacc, modified Value of Project Debt Value of Equity 0 3286.92 1381.45 1905.48 1 70.00 11.80% 3604.94 1458.15 2146.79 2 130.00 11.88% 3903.36 1536.72 2366.64 3 190.00 11.94% 4179.43 1617.29 2562.14