CHAPTER 12 Valuation Cash-Flow-s peeches me i n Exhibits 12.17 12 10 12 14 inchide the actual amounts for fiscal 2015 and the projected amounts for Year 1 to Years for the income statements, balance sheets, and statements of ash flows for Walmart in millions. These forecast amounts assume Walmart will use implied dividends as the financial flexible account to balance the balance Sheet. The market equity beta for Walmart at the end of 2015 was 1.00. Assume that the risk free interest rate was 3.0% and the market risk premium was 604 Walmart had 3,162 million shares outstanding at the end of fiscal 2015, and a share price of $67.50. REQUIRED Part Computing Walmart's Share Value Using Free Cash Flows to Common Equity Shareholders a. Use the CAPM to compute the required rate of return on common equity capital for Walmart. b. Beginning with proiected net cash flows from operations, derive the projected free cash flows for common equity shareholders for Walmart for Years +1 through +5 based on the projected financial statements. Assume that Walmart uses cash for operating liquidity purposes. Exhibit 12.17 Walmart Stores, Inc. Income Statements for 2015 (Actual) and Year + 1 through Year 15 (Projected) (amounts in millions; allow for rounding) Actual 2015 Year +1 Year +2 Year +3 Year 4 Year +5 $ 482,130 (360,984) $121,146 $ 491,773 (368,829) $ 122,943 $501,608 (376,206) $125,402 $ 511,640 (383,730) $ 127,910 $ 521,873 (391.405) $ 130,468 $532,310 (399,233) $ 133,078 (97,041) (98,355) (100,322) (102,328) (104,375) Revenues Cost of goods sold Gross Profit Selling, general, and administrative expenses Interest income Interest expense Income before Tax Income tax expense Net Income Net income attributable to noncontrolling interests Net Income attributable to common shareholders Other comprehensive income Items Comprehensive Income (2.548) $ 21,638 (6,558) $ 15,080 (2,539) $ 22,180 (7,098) $ 15,082 (2,615) $ 22,596 (7,231) $ 15,365 (2.694) $ 23,019 (7,366) $ 15,653 (2.775) $ 23,450 (7,504) $ 15,946 (106,462) 131 (2,858) $23.888 (7,644) $ 16.244 (386) $ 14,694 $ 14,696 $ 14,979 $ 15,267 $ 15,560 S $ 10,651 $ 14,696 $ 14,979 $ 15,267 $ 15,560 Source for Actual 2015 Walmart Stores, Inc, Form 10-K for the Fiscal Year Ended January 31, 2015. ceivable yable at liabilities erations 394 (557) 274 300 $ XXXX 23) 301 330 $ XXXX 305 422) (1,322) 305 436 $ XXXX 11,555) (1,165) 269 569 $ XXXX 5,58 (1,566) (622) 452 741 $ XXXX ets $(9,130) $(10,045) $6,022) $(24,796) $(18,632) $ ebt ock 3352 4,094 2,233 $ 320 3,721 2,029 (750) $ XXXX $ XXXX $ 343 2,455 306 (891) $ XXXX $ XXXX $ 1,272 10,107 4,895 (1,016) $ XXXX $ XXXX $ 1,515 7,595 1,885 (1,178) $ XXXX $ XXXX ancing $ XXXX $ XXXX INTEGRATIVE CASE 10.1 Walmart Walmart Stores, Inc. (Walmart) is the largest retailing firm in the world. Building on a base of discount stores, Walmart has expanded into warehouse clubs and Supercenters, which sell tradi- tional discount store items and grocery products. Exhibits 10.10, 10.11, and 10.12 present the fi- nancial statements of Walmart for 2013-2015. Exhibits 4.50-4.52 (Case 4.2 in Chapter 4) also present summary financial statements for Walmart, and Exhibit 4.53 presents selected financial statement ratios for Years 2013-2015. (Note: A few of the amounts presented in Chapter 4 for Walmart differ slightly from the amounts provided here because, for purposes of computing fi nancial analysis ratios, the Chapter 4 data have been adjusted slightly to remove the effects of nonrecurring items such as discontinued operations.) REQUIRED (additional requirements follow on page 714) a. Design a spreadsheet and prepare a set of financial statement forecasts for Walmart for Year +1 to Year +5 using the assumptions that follow. Project the amounts in the order Walmart presented (unless indicated otherwise) beginning with the income statement, then the balance sheet, and then the statement of cash flows. For this portion of the case, assume that Walmart will exercise its financial flexibility with the cash and cash equivalents account to balance the balance sheet Exhibit 10.10 2016 $ 8,705 5,624 44,469 1,441 $ 60,239 188,054 (71,538) 16,695 6,131 $199,581 Walmart Stores, Inc. Balance Sheets (amounts in millions; allow for rounding) (Integrative Case 10.1) 2014 2015 Assets Cash and cash equivalents $ 7,281 Accounts and notes receivable-net $ 9,135 6,677 6,778 Inventories 44,858 45,141 Prepaid expenses and other current assets 1,909 2,224 Current assets of discontinued segments 460 Current Assets $ 61,185 $ 63,278 Property, plant, and equipment-at cost 178,678 182,634 Accumulated depreciation (60,771) (65,979) Goodwill 19,510 18,102 Other assets 6,149 5,455 Total Assets $204,751 $203,490 Liabilities and Equities Accounts payable $ 37,415 $ 38,410 Current accrued expenses 18,793 19,152 Notes payable and short-term debt 7,670 1,592 Current maturities of long-term debt 4412 5,078 Income taxes payable 966 1,021 Current liabilities of discontinued operations $ 69,345 $ 65,253 Current Liabilities 44,559 43,495 Long-term debt obligations 8,017 8,805 Deferred tax liabilities-noncurrent 1.491 Redeemable noncontrolling interest $123,412 $117,553 Total Liabilities 2,685 2,785 Common stock + Additional paid-in capital 76,566 85,777 Retained earnings (2,996) (7,168) $ 76,255 $ 81,394 5,084 4,543 $ 81,339 $ 85,937 $204,751 $203,490 $ 38,487 19,607 2,708 3,296 521 $ 64,619 44,030 7,321 $115,970 2,122 90,021 (11,597) $ 80,546 3,065 $ 83,611 $199,581 Accum. other comprehensive income (loss) Total Common Shareholders' Equity Noncontrolling interests Total Equity Total Liabilities and Equities LAPTER 10 Forecasting Financial Statements Exhibit 10.11 Walmart Stores, Inc. Income Statements (amounts in millions; allow for rounding) (Integrative Case 10.1) 2014 2015 2016 $ 476,294 (358,069) $ 118,225 (91,353) $ 26,872 119 (2,335) $ 24,656 (8,105) $ 485,651 (365,086) $ 120,565 193,418) $ 27,147 Revenues Cost of goods sold Gross Profit Selling, general, and administrative expenses Operating Profit Interest income Interest expense Income before Tax Income tax expense Income (Loss) from discontinued operations Net Income Net income attributable to noncontrolling interests Net Income Attributable to Common Shareholders Other comprehensive income items Comprehensive Income $ 482,130 (360,984) $121,146 (97,041) $ 24,105 81 (2,548) $ 21,638 (6,558) 113 (2,461) $ 24,799 (7,985) 144 285 $ 16,695 (673) $ 16,022 (2,409) $ 13,613 $ 17,099 (736) $ 16,363 (4,172) $ 12,191 $ 15,080 (386) $ 14,694 (4,429) $ 10,265 Exhibit 10.12 Walmart Stores, Inc. Statements of Cash Flows (amounts in millions, allow for rounding) (Integrative Case 10.1) 2014 2015 2016 Net Income Add back depreciation and amortization expenses Deferred income taxes (Increase) Decrease in accounts receivable (Increase) Decrease in inventories Increase (Decrease) in accounts payable Increase (Decrease) in income taxes payable Increase (Decrease) in other current liabilities (Income) Loss from discontinued segments Other operating cash flows Net Cash Flow from Operating Activities $ 16,695 8,870 (279) (566) (1,667) 531 (1,224) 103 (144) 938 $ 23,257 $ 17,099 9,173 (503) (569) (1,229) 2,678 166 1,249 (285) 785 $ 28,564 $ 15,080 9.454 (672) (19) (703) 2,008 (472) 1,303 1.410 $ 27,389 (Continued) Interest Expense Walmart uses long-term mortgages and capital leases to finance new stores and warehouses and short and long-term debt to finance corporate acquisitions. The average interest rate on all interest-bearing debt and capital leases was approximately 46% and 5.1% during 2015 and 2016, respectively. Assume a 5.0% interest rate for all outstanding borrowing (short-term and long-term debt, including capital leases, and the current portion of long-term debt) for Walmart for Year +1 through Year +5. Compute interest expense on the average amount of interest-bearing debt outstanding each year. (Note: Projecting the amount of interest expense must await projection of the interest-bearing debt accounts on the balance sheet.) Income Tax Expense Walmart's average income tax rate as a percentage of income before taxes has been roughly 31.8% during the last three years. Assume that Walmart's effective income tax rate will be 32.0% of income before taxes for Year +1 through Year +5. (Note: Pro jecting the amount of income tax expense must await computation of income before taxes.) Net Income Attributable to Noncontrolling Interests Noncontrolling interest shareholders in Walmart subsidiaries were entitled to a $386 million share in Walmart's 2016 net income, which amounted to roughly a 12.6% rate of return on investment. Assume that the portion of net income attributable to non controlling interests in the future will continue to amount to a 12.6% rate of return in Year +1 through Year +5. Balance Sheet Forecast Assumptions Cash We will adjust cash as the flexible financial account to equate total assets with total liabil ities plus shareholders' equity. Projecting the amount of cash must await projections of all other balance sheet amounts. Accounts Receivable As a retailer, a large portion of Walmart's sales are in cash or for third-party credit card charges, which Walmart can convert into cash within a day or two. Walmart has its own credit card that customers can use for purchases at its Sam's Club warehouse stores, but the total amount of receivables outstanding on these credit cards is relatively minor compared to Walmart's total sales. As a consequence, Walmart's receivables turnover is very steady and fast, averaging roughly five days during each of the past two years. Assume that accounts receivable will continue to turn over at the same rate and increase at the growth rate in sales. Inventories Walmart's overall inventory efficiency has remained steady over the past three years. Inventory turnover has averaged 45 days in 2014, 2015, and 2016. Assume that ending inventory will continue to be equal to 45 days of cost of goods sold, in Year +1 to Year +5. Prepaid Expenses Current assets include prepayments for ongoing operating costs such as rent and insurance. Assume that prepayments will grow at the growth rate in sales. Current Assets and Liabilities of Discontinued Segments Walmart's 2014 balance she recognized amounts as current assets and current liabilities that are associated with disconti ued segments (subsidiaries that Walmart is divesting). Walmart divested these operations in fi cal 2015, so assume that these amounts will be zero in Year +1 through Year +5. Property. Plant, and Equipment-Ar Cost With regard to property, plant, and equip (including assets held under capital leases), Walmart's net capital expenditures (capital expend tures net of proceeds from selling property, plant, and equipment) have declined from roughly Interest Expense Walmart uses long-term mortgages and capital leases to finance new stores and warehouses and short and long-term debt to finance corporate acquisitions. The average interest rate on all interest-bearing debt and capital leases was approximately 46% and 5.1% during 2015 and 2016, respectively. Assume a 5.0% interest rate for all outstanding borrowing (short-term and long-term debt, including capital leases, and the current portion of long-term debt) for Walmart for Year +1 through Year +5. Compute interest expense on the average amount of interest-bearing debt outstanding each year. (Note: Projecting the amount of interest expense must await projection of the interest-bearing debt accounts on the balance sheet.) Income Tax Expense Walmart's average income tax rate as a percentage of income before taxes has been roughly 31.8% during the last three years. Assume that Walmart's effective income tax rate will be 32.0% of income before taxes for Year +1 through Year +5. (Note: Pro jecting the amount of income tax expense must await computation of income before taxes.) Net Income Attributable to Noncontrolling Interests Noncontrolling interest shareholders in Walmart subsidiaries were entitled to a $386 million share in Walmart's 2016 net income, which amounted to roughly a 12.6% rate of return on investment. Assume that the portion of net income attributable to non controlling interests in the future will continue to amount to a 12.6% rate of return in Year +1 through Year +5. Balance Sheet Forecast Assumptions Cash We will adjust cash as the flexible financial account to equate total assets with total liabil ities plus shareholders' equity. Projecting the amount of cash must await projections of all other balance sheet amounts. Accounts Receivable As a retailer, a large portion of Walmart's sales are in cash or for third-party credit card charges, which Walmart can convert into cash within a day or two. Walmart has its own credit card that customers can use for purchases at its Sam's Club warehouse stores, but the total amount of receivables outstanding on these credit cards is relatively minor compared to Walmart's total sales. As a consequence, Walmart's receivables turnover is very steady and fast, averaging roughly five days during each of the past two years. Assume that accounts receivable will continue to turn over at the same rate and increase at the growth rate in sales. Inventories Walmart's overall inventory efficiency has remained steady over the past three years. Inventory turnover has averaged 45 days in 2014, 2015, and 2016. Assume that ending inventory will continue to be equal to 45 days of cost of goods sold, in Year +1 to Year +5. Prepaid Expenses Current assets include prepayments for ongoing operating costs such as rent and insurance. Assume that prepayments will grow at the growth rate in sales. Current Assets and Liabilities of Discontinued Segments Walmart's 2014 balance she recognized amounts as current assets and current liabilities that are associated with disconti ued segments (subsidiaries that Walmart is divesting). Walmart divested these operations in fi cal 2015, so assume that these amounts will be zero in Year +1 through Year +5. Property. Plant, and Equipment-Ar Cost With regard to property, plant, and equip (including assets held under capital leases), Walmart's net capital expenditures (capital expend tures net of proceeds from selling property, plant, and equipment) have declined from roughly Walmart $12.4 billion in 2014, to $11.6 billion in 2015, to $10.8 billion in 2016. Assume that capital spending on new property, plant, and equipment will be $10.0 billion each year from Year +1 through Year +5. Accumulated Depreciation in 2015 and 2016, Walmart depreciated property, plant, and equipment using an average useful life of approximately 19.7 years. For Year +1 through Year +5, assume that accumulated depreciation will increase each year by depreciation expense. For Simplicity, compute straight-line depreciation expense based on an average 20-year useful life and zero salvage value. In computing depreciation expense each year, make sure you depreci- ate the beginning balance in the existing property, plant and equipment--at cost. Also add a new layer of depreciation expense for the new property, plant, and equipment acquired through capital expenditures. Assume that Walmart recognizes a full year of depreciation on new property, plant, and equipment in the first year of service. Goodwill and Other Assets Goodwill and other assets include primarily goodwill arising from corporate acquisitions outside the United States. Such acquisitions increase Walmart sales. Assume that goodwill and other assets will grow at the same rate as revenues. Also assume that goodwill and other assets are not amortizable, and that no impairment charges will be needed. Accounts Payable Walmart has maintained a steady accounts payable turnover, with payment periods averaging 9.5 times per year (an average turnover of roughly 38 days) during the last three years. Assume that ending accounts payable will continue to approximate 38 days of in- ventory purchases in Years +1 to +5. To compute the ending accounts payable balance using a 38-day turnover period, remember to add the change in inventory to the cost of goods sold to obtain the total amount of credit purchases of inventory during the year. Accrued Liabilities Accrued liabilities relate to accrued expenses for ongoing operating activ- ities and are expected to grow at the growth rate in selling and administrative expenses, which are expected to grow with sales. Income Taxes Payable and Deferred Tax Liabilities-Noncurrent For simplicity, assume that income taxes payable and deferred tax liabilities--noncurrent grow at 2.0% per year in Year +1 through Year +5. Short-Term Debt, Current Maturities of Long-Term Debt, and Long-Term Debt Walmart uses short-term debt, current maturities of long-term debt, and long-term debt to augment cash from operations to finance capital expenditures on property, plant, and equipment and acauisitions of existing retail chains outside the United States. Over the past two years, individ ual amounts of debt financing (short-term debt, current maturities of long-term debt, and long- term debt) have fluctuated from year to year, whereas the aggregate amount of debt financing has remained steady, averaging roughly 25.0% of total assets. For simplicity, assume that the total amount of debt financing will continue to remain a steady percentage of total assets for Year +1 through Year +5. Assume that Walmart's short-term debt, current maturities of long- term debt, and long-term debt will grow at 3.0% per year in Year +1 through Year +5, roughly consistent with the projected growth in total assets. mon Stock and Additional Paid-in Capital Over the past three years, Walmart's com- stock and additional paid in capital have averaged approximately 1.2% of total assets. wwsmart repurchases company shares on the open market and then reissues these shares to Ces and executives to satisfy stock option exercises.) Assume that equity financing will continue to be 1.2% of total assets for Year + 1 through Year +5. Assume that Walmart's com- mon stock and additional paid-in capital will grow at 3.0% per year in Year +1 through Year +5, roughly consistent with the projected growth in total assets. Retained Earnings The increase in retained earnings equals net income minus dividends and share repurchases. In 2016, Walmart paid total dividends of $6.294 million to common share- holders, which amounted to roughly 42% of net income attributable to Walmart shareholders. Assume that Walmart will maintain a policy to pay dividends equivalent to 42% of net income attributable to Walmart shareholders in Year +1 through Year +5. In addition, Walmart has used varying amounts of cash to repurchase common shares: $6,683 million in 2014, 51,015 mil- lion in 2015, and $4,112 million in 2016. Assume that Walmart will use $4,000 million per year to repurchase common shares in Year +1 through Year +5. Accumulated Other Comprehensive Income Assume that accumulated other comprehen- sive income will not change. Equivalently, assume that future other comprehensive income items will be zero, on average, in Year + 1 through Year +5. Noncontrolling Interests Noncontrolling interests amount to equity investments made by third-party investors in subsidiaries that Walmart controls and consolidates. Noncontrolling Interests grow each year by their proportionate share of the subsidiary's income, and these interests decrease by any dividends paid to them. We assumed, for purposes of projecting the income statement, that net income attributable to noncontrolling interests would generate a 12.6% rate of return for those investors. For simplicity, assume that the dividends Walmart will pay to the noncontrolling interest shareholders will equal the amount of net income attributa- ble to these noncontrolling interests in Year +1 to Year +5. Therefore, the amount of noncon- trolling interests in equity will remain constant. Cash At this point, you can project the amount of cash on Walmart's balance sheet at each year-end from Year +1 to Year +5. Assume that Walmart uses cash as the flexible financial account to balance the balance sheet. The resulting cash balance each year should be the total amount of liabilities and shareholders' equity minus the projected ending balances in all non- cash asset accounts Statement of Cash Flows Forecast Assumptions Depreciation Addback Include depreciation expense, which should equal the change in accu- mulated depreciation. Other Addbacks Assume that changes in other noncurrent liabilities on the balance sheet are operating activities. Other Investing Transactions Assume that changes in other noncurrent assets on the bal- ance sheet are investing activities. REQUIRED (continued from page 708) b. If you have programmed your spreadsheet correctly, the projected amount of cash grows steadily from $13,675 million at the end of Year +1 to a whopping $44 450 ml lion at the end of Year +5 (allow for rounding), which is 18.6% of total assets de one problem that so much cash could create for the financial management of ement of Walmart. CHAPTER 12 Valuation Cash-Flow-s peeches me i n Exhibits 12.17 12 10 12 14 inchide the actual amounts for fiscal 2015 and the projected amounts for Year 1 to Years for the income statements, balance sheets, and statements of ash flows for Walmart in millions. These forecast amounts assume Walmart will use implied dividends as the financial flexible account to balance the balance Sheet. The market equity beta for Walmart at the end of 2015 was 1.00. Assume that the risk free interest rate was 3.0% and the market risk premium was 604 Walmart had 3,162 million shares outstanding at the end of fiscal 2015, and a share price of $67.50. REQUIRED Part Computing Walmart's Share Value Using Free Cash Flows to Common Equity Shareholders a. Use the CAPM to compute the required rate of return on common equity capital for Walmart. b. Beginning with proiected net cash flows from operations, derive the projected free cash flows for common equity shareholders for Walmart for Years +1 through +5 based on the projected financial statements. Assume that Walmart uses cash for operating liquidity purposes. Exhibit 12.17 Walmart Stores, Inc. Income Statements for 2015 (Actual) and Year + 1 through Year 15 (Projected) (amounts in millions; allow for rounding) Actual 2015 Year +1 Year +2 Year +3 Year 4 Year +5 $ 482,130 (360,984) $121,146 $ 491,773 (368,829) $ 122,943 $501,608 (376,206) $125,402 $ 511,640 (383,730) $ 127,910 $ 521,873 (391.405) $ 130,468 $532,310 (399,233) $ 133,078 (97,041) (98,355) (100,322) (102,328) (104,375) Revenues Cost of goods sold Gross Profit Selling, general, and administrative expenses Interest income Interest expense Income before Tax Income tax expense Net Income Net income attributable to noncontrolling interests Net Income attributable to common shareholders Other comprehensive income Items Comprehensive Income (2.548) $ 21,638 (6,558) $ 15,080 (2,539) $ 22,180 (7,098) $ 15,082 (2,615) $ 22,596 (7,231) $ 15,365 (2.694) $ 23,019 (7,366) $ 15,653 (2.775) $ 23,450 (7,504) $ 15,946 (106,462) 131 (2,858) $23.888 (7,644) $ 16.244 (386) $ 14,694 $ 14,696 $ 14,979 $ 15,267 $ 15,560 S $ 10,651 $ 14,696 $ 14,979 $ 15,267 $ 15,560 Source for Actual 2015 Walmart Stores, Inc, Form 10-K for the Fiscal Year Ended January 31, 2015. ceivable yable at liabilities erations 394 (557) 274 300 $ XXXX 23) 301 330 $ XXXX 305 422) (1,322) 305 436 $ XXXX 11,555) (1,165) 269 569 $ XXXX 5,58 (1,566) (622) 452 741 $ XXXX ets $(9,130) $(10,045) $6,022) $(24,796) $(18,632) $ ebt ock 3352 4,094 2,233 $ 320 3,721 2,029 (750) $ XXXX $ XXXX $ 343 2,455 306 (891) $ XXXX $ XXXX $ 1,272 10,107 4,895 (1,016) $ XXXX $ XXXX $ 1,515 7,595 1,885 (1,178) $ XXXX $ XXXX ancing $ XXXX $ XXXX INTEGRATIVE CASE 10.1 Walmart Walmart Stores, Inc. (Walmart) is the largest retailing firm in the world. Building on a base of discount stores, Walmart has expanded into warehouse clubs and Supercenters, which sell tradi- tional discount store items and grocery products. Exhibits 10.10, 10.11, and 10.12 present the fi- nancial statements of Walmart for 2013-2015. Exhibits 4.50-4.52 (Case 4.2 in Chapter 4) also present summary financial statements for Walmart, and Exhibit 4.53 presents selected financial statement ratios for Years 2013-2015. (Note: A few of the amounts presented in Chapter 4 for Walmart differ slightly from the amounts provided here because, for purposes of computing fi nancial analysis ratios, the Chapter 4 data have been adjusted slightly to remove the effects of nonrecurring items such as discontinued operations.) REQUIRED (additional requirements follow on page 714) a. Design a spreadsheet and prepare a set of financial statement forecasts for Walmart for Year +1 to Year +5 using the assumptions that follow. Project the amounts in the order Walmart presented (unless indicated otherwise) beginning with the income statement, then the balance sheet, and then the statement of cash flows. For this portion of the case, assume that Walmart will exercise its financial flexibility with the cash and cash equivalents account to balance the balance sheet Exhibit 10.10 2016 $ 8,705 5,624 44,469 1,441 $ 60,239 188,054 (71,538) 16,695 6,131 $199,581 Walmart Stores, Inc. Balance Sheets (amounts in millions; allow for rounding) (Integrative Case 10.1) 2014 2015 Assets Cash and cash equivalents $ 7,281 Accounts and notes receivable-net $ 9,135 6,677 6,778 Inventories 44,858 45,141 Prepaid expenses and other current assets 1,909 2,224 Current assets of discontinued segments 460 Current Assets $ 61,185 $ 63,278 Property, plant, and equipment-at cost 178,678 182,634 Accumulated depreciation (60,771) (65,979) Goodwill 19,510 18,102 Other assets 6,149 5,455 Total Assets $204,751 $203,490 Liabilities and Equities Accounts payable $ 37,415 $ 38,410 Current accrued expenses 18,793 19,152 Notes payable and short-term debt 7,670 1,592 Current maturities of long-term debt 4412 5,078 Income taxes payable 966 1,021 Current liabilities of discontinued operations $ 69,345 $ 65,253 Current Liabilities 44,559 43,495 Long-term debt obligations 8,017 8,805 Deferred tax liabilities-noncurrent 1.491 Redeemable noncontrolling interest $123,412 $117,553 Total Liabilities 2,685 2,785 Common stock + Additional paid-in capital 76,566 85,777 Retained earnings (2,996) (7,168) $ 76,255 $ 81,394 5,084 4,543 $ 81,339 $ 85,937 $204,751 $203,490 $ 38,487 19,607 2,708 3,296 521 $ 64,619 44,030 7,321 $115,970 2,122 90,021 (11,597) $ 80,546 3,065 $ 83,611 $199,581 Accum. other comprehensive income (loss) Total Common Shareholders' Equity Noncontrolling interests Total Equity Total Liabilities and Equities LAPTER 10 Forecasting Financial Statements Exhibit 10.11 Walmart Stores, Inc. Income Statements (amounts in millions; allow for rounding) (Integrative Case 10.1) 2014 2015 2016 $ 476,294 (358,069) $ 118,225 (91,353) $ 26,872 119 (2,335) $ 24,656 (8,105) $ 485,651 (365,086) $ 120,565 193,418) $ 27,147 Revenues Cost of goods sold Gross Profit Selling, general, and administrative expenses Operating Profit Interest income Interest expense Income before Tax Income tax expense Income (Loss) from discontinued operations Net Income Net income attributable to noncontrolling interests Net Income Attributable to Common Shareholders Other comprehensive income items Comprehensive Income $ 482,130 (360,984) $121,146 (97,041) $ 24,105 81 (2,548) $ 21,638 (6,558) 113 (2,461) $ 24,799 (7,985) 144 285 $ 16,695 (673) $ 16,022 (2,409) $ 13,613 $ 17,099 (736) $ 16,363 (4,172) $ 12,191 $ 15,080 (386) $ 14,694 (4,429) $ 10,265 Exhibit 10.12 Walmart Stores, Inc. Statements of Cash Flows (amounts in millions, allow for rounding) (Integrative Case 10.1) 2014 2015 2016 Net Income Add back depreciation and amortization expenses Deferred income taxes (Increase) Decrease in accounts receivable (Increase) Decrease in inventories Increase (Decrease) in accounts payable Increase (Decrease) in income taxes payable Increase (Decrease) in other current liabilities (Income) Loss from discontinued segments Other operating cash flows Net Cash Flow from Operating Activities $ 16,695 8,870 (279) (566) (1,667) 531 (1,224) 103 (144) 938 $ 23,257 $ 17,099 9,173 (503) (569) (1,229) 2,678 166 1,249 (285) 785 $ 28,564 $ 15,080 9.454 (672) (19) (703) 2,008 (472) 1,303 1.410 $ 27,389 (Continued) Interest Expense Walmart uses long-term mortgages and capital leases to finance new stores and warehouses and short and long-term debt to finance corporate acquisitions. The average interest rate on all interest-bearing debt and capital leases was approximately 46% and 5.1% during 2015 and 2016, respectively. Assume a 5.0% interest rate for all outstanding borrowing (short-term and long-term debt, including capital leases, and the current portion of long-term debt) for Walmart for Year +1 through Year +5. Compute interest expense on the average amount of interest-bearing debt outstanding each year. (Note: Projecting the amount of interest expense must await projection of the interest-bearing debt accounts on the balance sheet.) Income Tax Expense Walmart's average income tax rate as a percentage of income before taxes has been roughly 31.8% during the last three years. Assume that Walmart's effective income tax rate will be 32.0% of income before taxes for Year +1 through Year +5. (Note: Pro jecting the amount of income tax expense must await computation of income before taxes.) Net Income Attributable to Noncontrolling Interests Noncontrolling interest shareholders in Walmart subsidiaries were entitled to a $386 million share in Walmart's 2016 net income, which amounted to roughly a 12.6% rate of return on investment. Assume that the portion of net income attributable to non controlling interests in the future will continue to amount to a 12.6% rate of return in Year +1 through Year +5. Balance Sheet Forecast Assumptions Cash We will adjust cash as the flexible financial account to equate total assets with total liabil ities plus shareholders' equity. Projecting the amount of cash must await projections of all other balance sheet amounts. Accounts Receivable As a retailer, a large portion of Walmart's sales are in cash or for third-party credit card charges, which Walmart can convert into cash within a day or two. Walmart has its own credit card that customers can use for purchases at its Sam's Club warehouse stores, but the total amount of receivables outstanding on these credit cards is relatively minor compared to Walmart's total sales. As a consequence, Walmart's receivables turnover is very steady and fast, averaging roughly five days during each of the past two years. Assume that accounts receivable will continue to turn over at the same rate and increase at the growth rate in sales. Inventories Walmart's overall inventory efficiency has remained steady over the past three years. Inventory turnover has averaged 45 days in 2014, 2015, and 2016. Assume that ending inventory will continue to be equal to 45 days of cost of goods sold, in Year +1 to Year +5. Prepaid Expenses Current assets include prepayments for ongoing operating costs such as rent and insurance. Assume that prepayments will grow at the growth rate in sales. Current Assets and Liabilities of Discontinued Segments Walmart's 2014 balance she recognized amounts as current assets and current liabilities that are associated with disconti ued segments (subsidiaries that Walmart is divesting). Walmart divested these operations in fi cal 2015, so assume that these amounts will be zero in Year +1 through Year +5. Property. Plant, and Equipment-Ar Cost With regard to property, plant, and equip (including assets held under capital leases), Walmart's net capital expenditures (capital expend tures net of proceeds from selling property, plant, and equipment) have declined from roughly Interest Expense Walmart uses long-term mortgages and capital leases to finance new stores and warehouses and short and long-term debt to finance corporate acquisitions. The average interest rate on all interest-bearing debt and capital leases was approximately 46% and 5.1% during 2015 and 2016, respectively. Assume a 5.0% interest rate for all outstanding borrowing (short-term and long-term debt, including capital leases, and the current portion of long-term debt) for Walmart for Year +1 through Year +5. Compute interest expense on the average amount of interest-bearing debt outstanding each year. (Note: Projecting the amount of interest expense must await projection of the interest-bearing debt accounts on the balance sheet.) Income Tax Expense Walmart's average income tax rate as a percentage of income before taxes has been roughly 31.8% during the last three years. Assume that Walmart's effective income tax rate will be 32.0% of income before taxes for Year +1 through Year +5. (Note: Pro jecting the amount of income tax expense must await computation of income before taxes.) Net Income Attributable to Noncontrolling Interests Noncontrolling interest shareholders in Walmart subsidiaries were entitled to a $386 million share in Walmart's 2016 net income, which amounted to roughly a 12.6% rate of return on investment. Assume that the portion of net income attributable to non controlling interests in the future will continue to amount to a 12.6% rate of return in Year +1 through Year +5. Balance Sheet Forecast Assumptions Cash We will adjust cash as the flexible financial account to equate total assets with total liabil ities plus shareholders' equity. Projecting the amount of cash must await projections of all other balance sheet amounts. Accounts Receivable As a retailer, a large portion of Walmart's sales are in cash or for third-party credit card charges, which Walmart can convert into cash within a day or two. Walmart has its own credit card that customers can use for purchases at its Sam's Club warehouse stores, but the total amount of receivables outstanding on these credit cards is relatively minor compared to Walmart's total sales. As a consequence, Walmart's receivables turnover is very steady and fast, averaging roughly five days during each of the past two years. Assume that accounts receivable will continue to turn over at the same rate and increase at the growth rate in sales. Inventories Walmart's overall inventory efficiency has remained steady over the past three years. Inventory turnover has averaged 45 days in 2014, 2015, and 2016. Assume that ending inventory will continue to be equal to 45 days of cost of goods sold, in Year +1 to Year +5. Prepaid Expenses Current assets include prepayments for ongoing operating costs such as rent and insurance. Assume that prepayments will grow at the growth rate in sales. Current Assets and Liabilities of Discontinued Segments Walmart's 2014 balance she recognized amounts as current assets and current liabilities that are associated with disconti ued segments (subsidiaries that Walmart is divesting). Walmart divested these operations in fi cal 2015, so assume that these amounts will be zero in Year +1 through Year +5. Property. Plant, and Equipment-Ar Cost With regard to property, plant, and equip (including assets held under capital leases), Walmart's net capital expenditures (capital expend tures net of proceeds from selling property, plant, and equipment) have declined from roughly Walmart $12.4 billion in 2014, to $11.6 billion in 2015, to $10.8 billion in 2016. Assume that capital spending on new property, plant, and equipment will be $10.0 billion each year from Year +1 through Year +5. Accumulated Depreciation in 2015 and 2016, Walmart depreciated property, plant, and equipment using an average useful life of approximately 19.7 years. For Year +1 through Year +5, assume that accumulated depreciation will increase each year by depreciation expense. For Simplicity, compute straight-line depreciation expense based on an average 20-year useful life and zero salvage value. In computing depreciation expense each year, make sure you depreci- ate the beginning balance in the existing property, plant and equipment--at cost. Also add a new layer of depreciation expense for the new property, plant, and equipment acquired through capital expenditures. Assume that Walmart recognizes a full year of depreciation on new property, plant, and equipment in the first year of service. Goodwill and Other Assets Goodwill and other assets include primarily goodwill arising from corporate acquisitions outside the United States. Such acquisitions increase Walmart sales. Assume that goodwill and other assets will grow at the same rate as revenues. Also assume that goodwill and other assets are not amortizable, and that no impairment charges will be needed. Accounts Payable Walmart has maintained a steady accounts payable turnover, with payment periods averaging 9.5 times per year (an average turnover of roughly 38 days) during the last three years. Assume that ending accounts payable will continue to approximate 38 days of in- ventory purchases in Years +1 to +5. To compute the ending accounts payable balance using a 38-day turnover period, remember to add the change in inventory to the cost of goods sold to obtain the total amount of credit purchases of inventory during the year. Accrued Liabilities Accrued liabilities relate to accrued expenses for ongoing operating activ- ities and are expected to grow at the growth rate in selling and administrative expenses, which are expected to grow with sales. Income Taxes Payable and Deferred Tax Liabilities-Noncurrent For simplicity, assume that income taxes payable and deferred tax liabilities--noncurrent grow at 2.0% per year in Year +1 through Year +5. Short-Term Debt, Current Maturities of Long-Term Debt, and Long-Term Debt Walmart uses short-term debt, current maturities of long-term debt, and long-term debt to augment cash from operations to finance capital expenditures on property, plant, and equipment and acauisitions of existing retail chains outside the United States. Over the past two years, individ ual amounts of debt financing (short-term debt, current maturities of long-term debt, and long- term debt) have fluctuated from year to year, whereas the aggregate amount of debt financing has remained steady, averaging roughly 25.0% of total assets. For simplicity, assume that the total amount of debt financing will continue to remain a steady percentage of total assets for Year +1 through Year +5. Assume that Walmart's short-term debt, current maturities of long- term debt, and long-term debt will grow at 3.0% per year in Year +1 through Year +5, roughly consistent with the projected growth in total assets. mon Stock and Additional Paid-in Capital Over the past three years, Walmart's com- stock and additional paid in capital have averaged approximately 1.2% of total assets. wwsmart repurchases company shares on the open market and then reissues these shares to Ces and executives to satisfy stock option exercises.) Assume that equity financing will continue to be 1.2% of total assets for Year + 1 through Year +5. Assume that Walmart's com- mon stock and additional paid-in capital will grow at 3.0% per year in Year +1 through Year +5, roughly consistent with the projected growth in total assets. Retained Earnings The increase in retained earnings equals net income minus dividends and share repurchases. In 2016, Walmart paid total dividends of $6.294 million to common share- holders, which amounted to roughly 42% of net income attributable to Walmart shareholders. Assume that Walmart will maintain a policy to pay dividends equivalent to 42% of net income attributable to Walmart shareholders in Year +1 through Year +5. In addition, Walmart has used varying amounts of cash to repurchase common shares: $6,683 million in 2014, 51,015 mil- lion in 2015, and $4,112 million in 2016. Assume that Walmart will use $4,000 million per year to repurchase common shares in Year +1 through Year +5. Accumulated Other Comprehensive Income Assume that accumulated other comprehen- sive income will not change. Equivalently, assume that future other comprehensive income items will be zero, on average, in Year + 1 through Year +5. Noncontrolling Interests Noncontrolling interests amount to equity investments made by third-party investors in subsidiaries that Walmart controls and consolidates. Noncontrolling Interests grow each year by their proportionate share of the subsidiary's income, and these interests decrease by any dividends paid to them. We assumed, for purposes of projecting the income statement, that net income attributable to noncontrolling interests would generate a 12.6% rate of return for those investors. For simplicity, assume that the dividends Walmart will pay to the noncontrolling interest shareholders will equal the amount of net income attributa- ble to these noncontrolling interests in Year +1 to Year +5. Therefore, the amount of noncon- trolling interests in equity will remain constant. Cash At this point, you can project the amount of cash on Walmart's balance sheet at each year-end from Year +1 to Year +5. Assume that Walmart uses cash as the flexible financial account to balance the balance sheet. The resulting cash balance each year should be the total amount of liabilities and shareholders' equity minus the projected ending balances in all non- cash asset accounts Statement of Cash Flows Forecast Assumptions Depreciation Addback Include depreciation expense, which should equal the change in accu- mulated depreciation. Other Addbacks Assume that changes in other noncurrent liabilities on the balance sheet are operating activities. Other Investing Transactions Assume that changes in other noncurrent assets on the bal- ance sheet are investing activities. REQUIRED (continued from page 708) b. If you have programmed your spreadsheet correctly, the projected amount of cash grows steadily from $13,675 million at the end of Year +1 to a whopping $44 450 ml lion at the end of Year +5 (allow for rounding), which is 18.6% of total assets de one problem that so much cash could create for the financial management of ement of Walmart