Question

Can you do in excel please and show the formulas

Lunar Motivation Corporation

Income Statement ($ in millions)

                                                                                    2019               

                                                Sales                          8000                           

                                                 Cost of Goods           -5600

                                                 SGA                           -1500

                                                 Interest                        -100

                                                 EBT                              800

                                                 Taxes (40%)               -320

                                                 Net income                   480

                                                 Dividends                    -100

                                                 Retained earnings        380

                                                                                    ====

                                                                                                                                                            

Lunar Motivation Corporation

Balance Sheet

                                               2019                                                                          2019               

  Cash                                         10                                     Accounts payable        300

  Accounts receivables             400                                     Notes payable              300

  Inventory                                560                                     Current liabilities          600

  Short term Investments            30                                     Long-term debt          1000

  Current assets                     1000                                     Common stock             400

  Net plant & equip.                3000                                     Retained earnings      2000

  Total assets                         4000                                     Total liab. & equity     4000

                                              =====                                                                      =====                             

You have forecasted Lunar Motivation Corporation (LMC) free cash flow in 2020 to be $50.51 million, $450 million in 2021, 500 million in 2022, and 550 million in 2023.  After 2023, free cash flow will grow at a constant rate of 7 percent per year forever.  The company has a 14% WACC and 100 million shares of common stock. Please provide an estimate of the items below, including the stock price, using the free cash flow model.   Show your calculations to the right.  

Value of Operations             ______

Total Corporate Value          ______

Value of Equity                      ______

Estimated Stock Price          ______

Can you do in excel please and show the formulas

Answer 1

The below table (Excel sheet can not be put here, you can replicate it with the formulas given)

WACC	14.0%
Perpetual Growth Rate	7%
Lunar Motivation Corporation
Time --->	1	2	3	4
Year ---->	2020	2021	2022	2023
Free Cash Flows	51	450	500	550
Terminal Value				8407
PV of FCF	44	346	337	326
PV of Terminal Value				4978
Value of Operations (A)	6,031.4
Cash & Cash Equivalents (B)	10.0
Corporate Value (C = A + B)	6,041.4
Value of Debt: LTD + Notes Payable (D)	1,300.0
Value of Equity (E = C - D)	4,741.4
Number of common stock (F)	100.0
Value per share (G = E / F)	47.4

*Values in Million, except Value per share

Terminal value of cash flow in the year t = FCFt (1+g)/ (k - g)

where FCFt = FCF in t year, where we need to find the terminal value

g = Perpetual growth rate

k = WACC

Terminal Value of Cash Flow = 550 X (1+ 7%)/ (14% - 7%) = 8407

PV of cash flows = Cash Flow / (1+k)^ t

K = Discount Rate = WACC

t = Year of discounting

For example :

In the 4th year, PV of cashflow = 550/ (1+14%)^4 = 326

Starting A1 cell (WACC) , the worksheet with formulas look like this

WACC	0.14
Perpetual Growth Rate	0.07
Lunar Motivation Corporation
Time --->	1	2	3	4
Year ---->	2020	2021	2022	2023
Free Cash Flows	50.51	450	500	550
Terminal Value				=E6*(1+B2)/(B1-B2)
PV of FCF	=B6/(1+$B1)^B4	=C6/(1+$B1)^C4	=D6/(1+$B1)^D4	=E6/(1+$B1)^E4
PV of Terminal Value				=E7/(1+B1)^E4
Value of Operations (A)	=SUM(B8,C8,D8,E8,E9)
Cash & Cash Equivalents (B)	10
Corporate Value (C = A + B)	=B10+B11
Value of Debt: LTD + Notes Payable (D)	=1000+300
Value of Equity (E = C - D)	=B12-B13
Number of common stock (F)	100
Value per share (G = E / F)	=B14/B15