5 FIN 373 Assignment #3 Company Valuation: Free Cash Flow Method Oct-2019 521.1 380.9 140.2 10.9 108.0 21.3 1.0 2.6 19.7 5.3 14.4 6 Walmart Inc. 7 8 9 Income Statement (Sbillions) 10 Sales 11 Cost of goods sold 12 Gross profit 13 Depreciation and amortization 14 Operating expenses 15 Operating income (EBIT) 16 Non-operating income (+) and expense (-) 17 Interest expense 18 Pretax income 19 Income tax expense 20 Net income 21 22 Balance Sheet (S-billions) 23 Cash 24 Short-term investments 25 Accounts receivables 26 Inventories 27 Other current assets 28 Current assets 29 Property, plant, and equipment - net 30 Intangible assets 31 Other assets 32 Total assets 33 34 Short-term debt and current portion of long-term debt 35 Accounts payable 36 Accurals 37 Other current liabilities 38 Current liabilities 39 Long-term debt 40 Total liabilities 41 Total shareholder equity 42 Total liabilities and shareholder equity 43 44 Other Data: 45 WACC 46 Tax rate 47 Stock price 48 Shares common stock outstanding (billions) 49 Total dividends (S-billions) 50 Free Cash Flow (current year, in S-billions) 51 Oct-2019 8.6 0.0 5.6 51.5 2.1 67.9 125.4 30.7 15.8 239.8 DUE: September 20 1. Using the data provided, complete the template on the spreadsheet (Fill in all blue- shaded areas). Specifically, you need to: a. Forecast FCF for 2020 assuming that October 2019 free cash flow grows at a constant annual rate of 1.85%. b. Use the corporate valuation model to determine the intrinsic value of the company's stock. 2. Given Walmart's stock price at the end of October 2019 and your estimate of the value of Walmart's stock (determined in question 1b), determine if the company's stock is overvalued, fairly valued, or undervalued. Because of the uncertainties involved in forecasting, you decide that any estimated stock price or intrinsic value within 5% +/- of the current stock price is fairly valued. The WACC for Walmart is 5.31% 3. Now re-estimate the value of Walmart assuming non-constant growth of FCF. The consensus estimate by financial analysis is that Walmart will grow at a 5.2% annual rate for the next 5 years (2020 through 2024), and then at a constant rate of 1.85% from 2025 onward. Again, fill-in all of the blue-shaded areas in the spreadsheet. Assume a WACC of 5.31%. 4. Determine if Walmart is overvalued, fairly valued, or undervalued in the non-constant growth case (assume that an intrinsic value within +/- 5% of the current market price is fairly valued). NOTES: The FCF for the current year in the assignment (October 2019) is $13.9 billion. If you come up with a negative intrinsic value, then you have an error(s) in your work 11.2 49.8 0.3 22.5 83.8 77.7 161.5 Please include all work in the spreadsheet, including your answers to questions 2 and 4. Then submit the spreadsheet to the D2L dropbox. As with the first homework, all of the blue shaded cells should have formulas which compute the answer. Please do not type in numerical values into these cells. Provide your answers to questions 2 and 4 in the appropriate textboxes in the spreadsheet. 78.3 239.8 Oct-2019 5.31% 26.8% 117.26 2.839 6.05 13.90 Use formulas in each of the Blue shaded cells. Please do not type in numerical values in those cell. 52 Constant Growth of FCF: Oct-2019 Oct-2019 Oct-2020 1.85% 53 Growth Rate Your Answer to Question 2: 54 Forecast FCF (2018) 55 Value of Operations 56 Non-operating assets 57 Value of Firm 58 Debt 59 Preferred Stock 60 Value of Common Equity 61 Intrinsic Value 62 63 Non-constant Growth of FCF: 64 Growth rate 65 Forecast FCF 66 Horizon/Terminal Value / 67 Value of Operations 68 Non-operating assets 69 Value of Firm 70 Debt 71 Preferred Stock Oct-2019 Oct-2020 5.20% Oct-2021 5.20% Oct-2022 5.20% Oct-2023 5.20% Oct-2024 5.20% Oct-2025 1.85% Your Answer to Question 4: 72 Value of Common Equity 73 Intrinsic Value 74 75