Walmart Inc FIN 373 Assignment #3 Company Valuation: Free Cash Flow Method DUE: February 7 Income Statement ($billions) Sales Cost of goods sold Gross profit Depreciation and amortization Operating expenses Operating income (EBIT) Non-operating income (+) and expense (-) Interest expense Pretax income Income tax expense Net income Oct-2019 521.1 380.9 140.2 10.9 108.0 21.3 1.0 2.6 19.7 5.3 Oct-2019 8.6 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). Balance Sheet($-billions) Cash Short-term investments Accounts receivables Inventories Other current assets Current assets Property, plant, and equipment-net Intangible assets Other assets Total assets 0.0 5.6 51.5 2.1 67.9 125.4 30.7 15.8 239.8 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 Short-term debt and current portion of long-term debt Accounts payable Accurals Other current liabilities Current liabilities Long-term debt Total liabilities Total shareholder equity Total liabilities and shareholder equity 11.2 49.8 0.3 22.5 83.8 77.7 1615 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 Other Data: WACC Tax rate Stock price Shares common stock outstanding (billions) Total dividends ($-billions) Free Cash Flow (current year, in $-billions) 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. Oct-2019 Oct-2020 1.85% Your Answer to Question 2: Constant Growth of FCF: Growth Rate Forecast FCF (2018) Value of Operations Non-operating assets Value of Firm Debt Preferred Stock Value of Common Equity Intrinsic Value 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% Non-constant Growth of FCF: Growth rate Forecast FCF Horizon/Terminal Value Value of Operations Non-operating assets Value of Firm Debt Preferred Stock Value of Common Equity Intrinsic Value Your Answer to
