Walmart Stores, Inc. (Walmart) is the largest retailing firm in the world. Building on a base of
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 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.
data-fr-image-pasted="true" src="images/question_images/1540/8/9/2/7515bd8284f055101540824753529.jpg" class="fr-fic fr-dib">
Income Statement Forecast Assumptions
Sales Sales grew by 1.6% in 2014 and 2.0% in 2015, but fell by 0.7% in 2016. The compound annual sales growth rate during the last three years was only 0.6%. 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 experience through 2016. Assume that sales will grow 2.0% each year from Year +1 through Year +5.
Cost of Goods Sold The percentage of costs of goods sold relative to sales decreased slightly from 75.2% of sales in 2014 and 2015 to 74.9% in 2016. Walmart's 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 be 75.0% for Year +1 to Year +5.
Selling and Administrative Expenses The selling and administrative expense percentage has increased slightly from 19.2% of sales in 2014 and 2015 to 20.1% in 2016. Walmart has exhibited strong cost control over the years, and is likely to continue to exhibit such control. Assume that the selling and administrative expenses average 20.0% 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 2016, a bit lower than the rates earned in 2014 and 2015 (1.6% and 1.4%, 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 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 4.6% 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.
Income Tax Expense Walmarts average income tax rate as a percentage of income before taxes has been roughly 31.8% during the last three years. Assume that Walmarts effective income tax rate will be 32.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 Non controlling Interests Non controlling interest shareholders in Walmart subsidiaries were entitled to a $386 million share in Walmarts 2016 net income, which amounted to roughly a 12.6% rate of return on investment. Assume that the portion of net income attributable to noncontrolling 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 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 has its own credit card that customers can use for purchases at its Sams Club warehouse stores, but the total amount of receivables outstanding on these credit cards is relatively minor compared to Walmarts total sales. As a consequence, Walmarts 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 Walmarts 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 Walmarts 2014 balance sheet recognized amounts as current assets and current liabilities that are associated with discontinued segments (subsidiaries that Walmart is divesting). Walmart divested these operations in fiscal 2015, so assume that these amounts will be zero in Year +1 through Year +5.
Property, Plant, and EquipmentAt Cost With regard to property, plant, and equipment (including assets held under capital leases), Walmart's net capital expenditures (capital expenditures net of proceeds from selling property, plant, and equipment) have declined from roughly $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 depreciate 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 inventory 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 activities 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 LiabilitiesNon current For simplicity, assume that income taxes payable and deferred tax liabilitiesnon current 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, 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 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, roughly consistent with the projected growth in total assets.
Common Stock and Additional Paid-in Capital Over the past three years, Walmarts common stock and additional paid-in capital have averaged approximately 1.2% of total assets. (Walmart repurchases company shares on the open market and then reissues these shares to employees 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 Walmarts common 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 shareholders, 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, $1,015 million 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 comprehensive income will not change. Equivalently, assume that future other comprehensive income items will be zero, on average, in Year +1 through Year +5.
Non controlling Interests Non controlling interests amount to equity investments made by third-party investors in subsidiaries that Walmart controls and consolidates. Non controlling interests grow each year by their proportionate share of the subsidiarys 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 non controlling interests would generate a 12.6% rate of return for those investors. For simplicity, assume that the dividends Walmart will pay to the non controlling interest shareholders will equal the amount of net income attributable to these non controlling interests in Year +1 to Year +5. Therefore, the amount of non controlling 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 non cash asset accounts.
Statement of Cash Flows Forecast Assumptions
Depreciation Addback Include depreciation expense, which should equal the change in accumulated depreciation.
Other Add backs Assume that changes in other non current liabilities on the balance sheet are operating activities.
Other Investing Transactions Assume that changes in other non current assets on the balance sheet are investing activities.
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,459 million at the end of Year +5 (allow for rounding), which is 18.6% of total assets. Identify one problem that so much cash could create for the financial management of Walmart.
c. Suppose that Walmart announces that it will augment its dividend policy by paying out 42% of net income plus the amount of excess cash each year (if any). Assume that during Year +1 to Year +5, Walmart will maintain a constant cash balance of $8,705 million (the ending cash balance in 2016). Revise your forecast model spreadsheets to change the financial flexibility account from cash to dividends. Determine the total amount of dividends that Walmart could pay each year under this scenario. Identify one potential benefit that increased dividends could create for the financial management of Walmart.
d. Calculate and compare the return on common equity for Walmart using the forecast amounts determined in Requirements a and c for Year +1 to Year +5. Why are the two sets of returns different? Which results will Walmarts common shareholders prefer? Why?Goodwill
Goodwill is an important concept and terminology in accounting which means good reputation. The word goodwill is used at various places in accounting but it is recognized only at the time of a business combination. There are generally two types of... Ending Inventory
The ending inventory is the amount of inventory that a business is required to present on its balance sheet. It can be calculated using the ending inventory formula Ending Inventory Formula =... Financial Statements
Financial statements are the standardized formats to present the financial information related to a business or an organization for its users. Financial statements contain the historical information as well as current period’s financial... Common Stock
Common stock is an equity component that represents the worth of stock owned by the shareholders of the company. The common stock represents the par value of the shares outstanding at a balance sheet date. Public companies can trade their stocks on... Salvage Value
Salvage value is the estimated book value of an asset after depreciation is complete, based on what a company expects to receive in exchange for the asset at the end of its useful life. As such, an asset’s estimated salvage value is an important... Accounts Payable
Accounts payable (AP) are bills to be paid as part of the normal course of business.This is a standard accounting term, one of the most common liabilities, which normally appears in the balance sheet listing of liabilities. Businesses receive... Accounts Receivable
Accounts receivables are debts owed to your company, usually from sales on credit. Accounts receivable is business asset, the sum of the money owed to you by customers who haven’t paid.The standard procedure in business-to-business sales is that... Balance Sheet
Balance sheet is a statement of the financial position of a business that list all the assets, liabilities, and owner’s equity and shareholder’s equity at a particular point of time. A balance sheet is also called as a “statement of financial... Dividend
A dividend is a distribution of a portion of company’s earnings, decided and managed by the company’s board of directors, and paid to the shareholders. Dividends are given on the shares. It is a token reward paid to the shareholders for their...
Step by Step Answer: