Question

You have FCF for the next five years, a long-term growth rate for the cash flows after Year 5, some cash that the firm has in the bank today, their short and long-term debt, the number of shares outstanding, accounts receivable, and net income. Find the per share equity value given this information. Perform your work in the box below. 2) Once you value the firm, make a nicely formatted graph showing how the equity price per share changes with the long-term growth in free cash flows. Use a range of 3% to 5%. 1 Year Estimated Revenues Estimated Net Income Estimated free cash flow 2 3 4 5 $ 1,200,000 $1,300,000 $1,400,000 $1,500,000 $ 1,750,000 $ 356,000 $ 450,000 $ 450,000 $ 600,000 $ 650,000 $ 200,000 $ 250,000 $ 255,000 $ 280,000 $ 295,000 WACC Long-term growth rate of free cash flow Cash and Marketable Securities Short-term debt Long-term debt Accounts Receivable Net Income Accounts Payable Number of shares 14% 4% 170,000 55,000 65,000 15,000 235,000 100,000 120,000

Answer 1

A D E F G H - 1 4 2 250000 =C2/(1+$B$5)^c1 3 255000 =D2/(1+$B$5)^D1 280000 =E2/(1+$B$5)^E1 5 295000 =F2/(1+$B$5)^F1 Terminal =F2*(1+B6)/(B5-B6) =G2/(1+$B$5)^F1 B 1 200000 =B2/(1+$B$5)^B1 =SUM(B3:43) 0.14 0.04 170000 55000 65000 120000 Stock price vs Growth 23 22.47 Growth rate 0.03 0.035 0.04 0.045 0.05 Stock price 19.53 20.16 20.85 22.5 22 21.62 21.62 21.5 20.85 22.47 21 Market cap+ Market cap Debt - =B8+B9 Cash =B7 20.5 20.16 Stock price 20 19.53 19.5 =A13-C13+D13 =B15/B10 19 2 FCFF 3 PV 4 Sum PV 5 WACC 6 Long 8 7 Cash 8 Short Debt 9 Long Debt 10 Number of shares 11 12 EV 13 =B4 14 15 Market Cap 16 Per share price 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 18.5 18 17.5 3% 3.50% 4% 4.50% 5% Growth rate

Everything is given in the excel sheet.

H - J K L M 1 1 Stock price vs Growth 23 22.47 A B с D E F G 2 3 4 5 Terminal 2 FCFF $ 200,000.00 $ 250,000.00 $255,000.00 $280,000.00 $295,000.00 $3,068,000.00 3 PV $ 175,438.60 $ 192,366.88 $ 172,117.74 $ 165,782.48 $ 153,213.76 $ 1,593,423.06 4 Sum PV $2,452,342.51 5 WACC 14% 6 Long 8 4.00% 7 Cash $ 170,000.00 Growth rate Stock price 8 Short Debt 55,000.00 3% 19.53 22.5 9 Long Debt 65,000.00 3.50% 20.16 10 Number of shares 120000 4% 20.85 21.5 11 4.50% 21.62 21 12 EV Market cap+ Debt - Cash 5% 22.47 20.5 13 $ 2,452,342.51 Market cap $ 120,000.00 $ 170,000.00 20 14 19.5 15 Market Cap $ 2,502,342.51 19 16 Per share price $ 20.85 18.5 17 $ $ 22 21.62 20.85 Stock price 19.53 18 18 17.5 19 3% 3.50% 4% 4.50% 5% Growth rate 20 21 22 23