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 traditional discount store items and grocery products. Exhibits 10.10, 10.11, and 10.12 present the financial statements of Walmart for fiscal years 20182020. In these exhibits we provide modified Walmart financial statements to facilitate forecasting. Specifically, in Exhibit 10.11 we include on the income statement the adjustment for imputed interest expense on operating lease obligations. In addition, in Exhibit 10.12, we provide the implied statements of cash flows for fiscal 2019 and 2020, including implied cash flows for changes in operating lease assets and obligations. For your reference, Exhibits 4.50, 4.51, and 4.52 (Case 4.2 in Chapter 4) present summary financial statements for Walmart based on U.S. GAAP, and Exhibit 4.53 presents selected financial statement ratios for Years 20182020. (Note: A few of the amounts presented in Chapter 4 for Walmart differ slightly from the amounts provided here because, for purposes of computing financial analysis ratios, the Chapter 4 data have been adjusted slightly to remove the effects of nonrecurring items such as discontinued operations.)

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 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.

Income Statement Forecast Assumptions SALES Sales grew by 6.7% in fiscal 2020 due to a surge in demand for Walmart products during the pandemic. However, sales grew by only 2.4% in fiscal 2019 and 3.2% in fiscal 2018. The compound annual sales growth rate during the last five years was only 3.0%. However, the compound rate of sales growth during the prior 4 years (excluding the effects of the pandemic in fiscal 2020) was only 2.1%. Walmart generates sales growth primarily through increasing same-store sales, opening new stores, and acquiring other retailers. In the future, Walmart will likely continue to grow in international markets by opening stores and acquiring other firms and in domestic U.S. markets by converting discount stores to Supercenters. In addition, despite vigorous competition, Walmart will likely continue to generate steady increases in same-store sales, consistent with its performance in prior years. Assume that the elevated demand for Walmart products during the pandemic in fiscal 2020 will return to normal levels, so sales will grow 2.0% each year from Year +1 through Year +5. For a company with the scale of Walmart, achieving even a 2.0% rate of sales growth will require adding over $10 billion per year to top line sales.

COST OF GOODS SOLD The percentage of costs of goods sold relative to sales decreased slightly from 75.3% of sales in 2019 to 75.2% in 2020. Walmarts everyday low-price strategy, its movement into grocery products, and competition will likely prevent Walmart from achieving significant reductions in this expense percentage. Assume that the cost of goods sold to sales percentage will continue to be 75.2% of sales for Year +1 to Year +5.

OPERATING, SELLING, GENERAL AND ADMINISTRATIVE EXPENSES The operating, selling, general and administrative expense percentage has been remarkably steady at 20.8% of sales during 20182020. Walmart has exhibited strong cost control over the years, and is likely to continue to exhibit such control. Assume that operating, selling, general and administrative expenses will continue to average 20.8% of sales for Year +1 to Year +5.

INTEREST INCOME Walmart earns interest income on its cash and cash equivalents accounts. The average interest rate earned on average cash balances was approximately 0.9% during 2020, a bit lower than the rates earned in 2018 and 2019 (3.0% and 2.2%, respectively). Assume that Walmart will earn interest income based on a 1.5% interest rate on average cash balances (that is, the sum of beginning and end-of-year cash balances divided by 2) for Year +1 through Year +5. (Note: Projecting the amount of interest income must await projection of cash on the balance sheet.)

INTEREST EXPENSE ON DEBT Walmart uses long-term mortgages 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 was approximately 5.6% and 7.1% during 2020 and 2019, respectively. Assume a 5.5% interest rate for all outstanding borrowing (short-term and long-term debt, including 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.)

INTEREST AND FINANCING COSTS ON LEASES Walmart also uses long-term finance leases and operating leases to finance new stores and warehouses. In Note 11, Leases, Walmart discloses that during fiscal 2020 the weighted average discount rate was 6.1% on operating leases and 6.8% on finance leases. Assume that the 6.1% interest rate will continue to apply to all outstanding operating lease obligations and the 6.8% interest rate will continue to apply to all outstanding finance lease obligations (current and long-term lease obligations) for Walmart for Year +1 through Year +5. Compute interest expense amounts on the average amount of operating and finance lease obligations outstanding each year. (Note: Projecting the amounts of interest expenses must await projections of the lease obligations on the balance sheet.)

INCOME TAX EXPENSE Walmarts average income tax rate as a percentage of income before taxes has been roughly 31.7% during the last three years. Assume that Walmarts effective income tax rate will be 31.0% of income before taxes for Year +1 through Year +5. (Note: Projecting 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 $196 million share in Walmarts 2020 net income, which amounted to roughly a 2.9% rate of return on investment. This rate of return is unusually low compared to average returns of 4.6% and 10.1% in 2019 and 2018, respectively. Assume that the portion of net income attributable to noncontrolling interests in the future will continue to yield a 5.0% rate of return in Year +1 through Year +5.

CASH We will adjust cash as the flexible financial account to equate total assets with total liabilities 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 Walmarts sales are in cash or for third-party credit card charges, which Walmart can convert into cash within a day or two. Walmart also has its own credit card that customers can use for purchases at its stores, but the total amount of receivables outstanding is relatively minor compared to Walmarts total sales. As a consequence, Walmarts receivables turnover is very steady and fast, averaging roughly four days during each of the past five years. Assume that accounts receivable will continue to turn over at the same rate and the ending accounts receivable balance will grow with sales growth.

INVENTORIES Walmarts overall inventory efficiency has increasing slightly over the past five years. Inventory turnover has improved from an average of 44 days in fiscal 2016 to an average of only 39 days in fiscal 2020. Assume that ending inventory will be equal to 40 days of cost of goods sold, in Year +1 to Year +5.

PREPAID EXPENSES AND OTHER CURRENT ASSETS These assets include prepayments for ongoing operating costs such as rent and insurance. Assume that prepayments will grow at the growth rate in sales in Year +2 through Year +5. However, at fiscal year end 2020, Walmart includes $19,200 million in this account for assets associated with a business being held for sale. We will assume that the sale of the business will be completed in Year +1. Therefore, subtract $19,200 from the ending balance in 2020 to project the ending balance for Year +1.

PROPERTY, PLANT, AND EQUIPMENTAT COST With regard to property, plant, and equipment, Walmarts net capital expenditures (capital expenditures net of proceeds from selling property, plant, and equipment) have been roughly $10.0 billion per year in fiscal years 20182020. Assume that capital spending on new property, plant, and equipment will continue to be $10.0 billion each year from Year +1 through Year +5.

ACCUMULATED DEPRECIATION In 2019 and 2020, Walmart depreciated property, plant, and equipment using an average useful life of approximately 17.0 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 17-year useful life and zero salvage value. In computing depreciation expense each year, make sure you depreciate the beginning balance in the existing property, plant, and equipmentat cost. Also add a new layer of depreciation expense for the new property, plant, and equipment acquired through $10.0 billion each year in capital expenditures. Assume that Walmart recognizes a full year of depreciation on new property, plant, and equipment in the first year of service.

LEASE RIGHT-OF-USE ASSETS Walmart recognizes long-term assets for lease rights in the Operating Lease Right-of-Use Assets and the Finance Lease Right-of-Use Assets accounts. Walmart leases assets that are similar to the types of assets purchased with capital expenditures and included in Property, Plant, and Equipment. Assume that the Operating and Finance lease asset accounts will grow at the same rate of growth in Property, Plant, and Equipment during Year +1 through Year +5.

GOODWILL AND OTHER LONG-TERM ASSETS Goodwill and other long-term assets include primarily goodwill arising from corporate acquisitions outside the United States. Such acquisitions increase Walmart sales. Other long-term assets include various types of noncurrent assets including indefinite-lived intangibles Walmart has acquired, including trade names. Assume that goodwill and other long-term assets will grow at the same rate as sales. 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 roughly 42 days during the last 5 years. Assume that ending accounts payable will continue to approximate 42 days of inventory purchases in Years +1 to +5. To compute the ending accounts payable balance using a 42-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 activities and are expected to grow at the growth rate in operating, selling, general and administrative expenses, which are expected to grow with sales.

INCOME TAXES PAYABLE AND DEFERRED TAX LIABILITIESNONCURRENT For simplicity, assume that income taxes payable and deferred tax liabilitiesnoncurrent 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 acquisitions of existing retail chains outside the United States. Over the past two years, individual amounts of debt financing (short-term debt, current maturities of long-term debt, and long-term debt) have fluctuated from year to year. For simplicity, assume that Walmarts 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.

OPERATING AND FINANCE LEASE OBLIGATIONS DUE WITHIN ONE YEAR AND LONG-TERM When Walmart executes new operating or finance leases, they recognize the right-of-use assets and the associated short-term and long-term lease obligations. We will assume that the short-term and long-term obligations under operating leases and finance leases will grow at the same rate as the lease right-of-use assets will grow during Year +1 through Year +5, which will be equal to the rate of growth in property, plant, and equipment.

COMMON STOCK AND ADDITIONAL PAID-IN CAPITAL Over the past five years, Walmarts common stock and additional paid-in capital have grown at a compound annual rate of 13% per year. Assume that Walmarts common stock and additional paid-in capital will continue to grow at 13.0% per year in Year +1 through Year +5.

RETAINED EARNINGS The increase in retained earnings equals net income minus dividends and share repurchases. In fiscal 2020, Walmart paid total dividends of $6,116 million to common shareholders, which amounted to roughly 45% of net income attributable to Walmart shareholders. Assume that Walmart will maintain a policy to pay dividends equivalent to 45% of net income attributable to Walmart shareholders in Year +1 through Year +5. In addition, Walmart has used on average roughly $6,000 of cash to repurchase common shares in each of the past five fiscal years. Assume that Walmart will continue to use $6,000 million per year to repurchase common shares in Year +1 through Year +5.

ACCUMULATED OTHER COMPREHENSIVE INCOME Assume that accumulated other comprehensive income will not change. Equivalently, assume that other comprehensive income items will be zero (comprehensive income will equal net income), 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 subsidiarys income, and these interests decrease by any dividends paid to the noncontrolling shareholders. We assumed, for purposes of projecting the income statement, that net income attributable to noncontrolling interests would generate a 5.0% 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 attributable to these noncontrolling interests in Year +1 to Year +5. Therefore, the amount of noncontrolling interests in equity will remain constant.

CASH At this point, you can project the amount of cash on Walmarts 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 noncash asset accounts.

Transcribed image text: Exhibit 10.10. Walmart Stores, Inc. Balance Sheets (Amounts in Millions; Allow for Rounding) (Integrative Case 10.1) Exhibit 10.12. Walmart Stores, Inc. Statements of Cash Flows (amounts in millions, allow for rounding) (Integrative Case 10.1) Exnibit 10.11. Waimart stores, inc. income statements (amounts in millions; allow for rounding) (Integrative Case 10.1)Transcribed image text: Exhibit 10.10. Walmart Stores, Inc. Balance Sheets (Amounts in Millions; Allow for Rounding) (Integrative Case 10.1) Exhibit 10.12. Walmart Stores, Inc. Statements of Cash Flows (amounts in millions, allow for rounding) (Integrative Case 10.1) Exnibit 10.11. Waimart stores, inc. income statements (amounts in millions; allow for rounding) (Integrative Case 10.1)