Stores (Wal-Mart) is the largest retailing firm in the world. Building on a base of discount stores,

Question:

Stores (Wal-Mart) is the largest retailing firm in the world. Building on a base of discount stores, Wal-Mart has expanded into warehouse clubs and Supercenters, which sell traditional discount store items and grocery products.
Exhibits 10.11, 10.12, and 10.13 present the of Wal-Mart for Years 2, 3, and 4. Exhibits 4.51 to 4.53 (Case 4.2 in Chapter 4) also present summary for Wal-Mart, and Exhibit 4.54 presents selected financial statement ratios for Years 2, 3, and 4. [Note: The data presented in Chapter 4 for Wal-Mart differ slightly from the data here because the Chapter 4 data have been adjusted slightly to remove the effects of items such as discontinued operations, for purposes of computing financial analysis ratios.]
Required (additional requirements follow on page 794)
a. Design a spreadsheet and prepare a set of financial statement forecasts for Wal-Mart 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

Income Statement
Sales
Sales grew 11.6 percent in Year 3 and 11.6 percent in Year 4, primarily as a result of significant increases in same-store sales and opening new stores. The compound annual growth rate during the last five years was 12.8 percent. In the future, Wal-Mart will continue to grow internationally by opening stores and acquiring other firms, and domestically by converting discount stores to Supercenters. In addition, despite competition, Wal-Mart will likely also continue to enjoy increases in same-store sales of 5 percent to 6 percent, consistent with its experience through Year 4. Thus, assume that sales will grow 10 percent each year between Year +1 and Year +5.
Cost of Goods Sold
The cost of goods sold to sales percentage steadily decreased from 77.7 percent of sales in Year 2 to 77.1 percent in Year 4. Wal-Mart's everyday low-price strategy, its movement into grocery products, and competition will likely prevent Wal-Mart from achieving significant additional decreases in this expense percentage. Assume that the cost of goods sold to sales percentage will average 77.0 percent for Year +1 to Year +5.

Selling and Administrative Expenses
The selling and administrative expense percentage has steadily increased from 17.4 percent of sales in Year 2 to 17.9 percent of sales in Year 4. Identifying and transacting international corporate acquisitions and opening additional Supercenters will put upward pressure on this expense percentage, but the slowdown in sales growth will moderate this upper pressure. Assume that the selling and administrative expense to sales percentage will remain at 18.0 percent for Year +1 to Year +5.
Other Operating Income
Other operating income has been approximately 1 percent of revenues during the last three years. Assume that other operating income will continue at this historical pattern.
Interest Income
Wal-Mart earns a bit of interest income on its cash and cash equivalents accounts. The average interest rate earned on average cash balances was approximately 3.8 percent

during Year 4. Assume Wal-Mart will earn interest income based on a 3.8 percent interest rate on average cash balances (that is, the sum of the beginning and end of the year cash 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
Wal-Mart engages in long-term borrowing to construct new stores domestically, and in both short- and long-term borrowing to finance corporate acquisitions. The average interest rate on all interest-bearing debt was approximately 4.1 percent during Year 4. Assume a 4.1 percent interest rate for all outstanding borrowing (notes payable, long-term debt, and current portion of long-term debt) for Wal-Mart for Year +1 through Year +5. Compute interest expense on the average amount of interest-bearing debt outstanding each year (that is, the sum of the beginning and end of the year divided by 2). NOTE: Projecting the amount of interest expense must await projection of interest-bearing debt on the balance sheet.
Other Expenses
Other expenses have been approximately 0.1 percent of revenues during the last three years. Assume that other expenses will continue at this historical pattern.
Continue over next page

Income Tax Expense
Wal-Mart's average income tax rate as a percentage of income before taxes has varied between 35.2 percent and 36.6 percent during the last three years. Assume an income tax rate of 36.0 percent of income before income taxes for Year +1 through Year +5. NOTE: Projecting the amount of income tax expense must await computation of income before taxes.
Balance Sheet
Cash
Cash will be the flexible financial account we will use to plug the amount necessary 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
Accounts receivable will increase at the growth rate in sales. Inventories Wal-Mart has maintained a steady inventory turnover ratio of 7.8 times during the last two years (based on average inventory balances each year). In recent years, the expanding role of grocery products has increased Wal-Mart's inventory turnover. However, that increase has been offset by the stocking of new stores and the distribution of merchandise to stores worldwide. We will assume that inventory turnover will continue to average 7.8 times per year (every 47 days) in Years +1 to +5. Use this turnover rate to compute the average inventories each year, and then compute the implied ending inventories each year.
Prepayments
Other current assets include prepayments, which relate to ongoing operating costs, such as rent and insurance. Assume that prepayments will grow at the growth rate in sales.
Property, Plant, and Equipment-at cost
Property, plant, and equipment grew 14.4 percent annually during the most recent five years. In the most recent three years, Wal-Mart's growth in property, plant, and equipment has varied from 14.4 percent in Year 2 to 13.9 percent in Year 3 to 16.6 percent in Year 4. The construction of new Supercenters and the acquisition of established retail chains abroad will require additional investments in property, plant, and equipment. Assume that property, plant, and equipment will grow 14.4 percent each year from Year + 1 through Year +5.
Accumulated Depreciation
In Years 3 and 4, the change in Wal-Mart's accumulated depreciation has averaged roughly 4.0 percent of the beginning of year balance in property, plant, and equipment - at cost. During Year +1 through Year +5 assume accumulated depreciation will increase each year by an amount equal to 4.0 percent of the beginning of year balance in property, plant and equipment - at cost.
Goodwill and Other Assets
Goodwill and other assets primarily include goodwill arising from corporate acquisitions abroad. Such acquisitions increase Wal-Mart sales. Assume that goodwill and other assets will grow at the growth rate in sales.
Accounts Payable
Wal-Mart has maintained a steady accounts payable turnover, with payment periods averaging 33 days (an average turnover ratio of roughly 11.1 times per year) during the last five years (based on average balances each year). We will assume that accounts payable turnover will continue to average 33 days in Years +1 to +5. Use this turnover rate to compute the average accounts payable each year, and then compute the implied ending accounts payable each year. 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.
Short-Term Debt, Current Maturities of Long-Term Debt, and Long-Term Debt
Wal-Mart uses short-term debt, current maturities of long-term debt, and long-term debt to augment cash from operations to finance acquisitions of property, plant, and equipment, and acquisition of existing retail chains abroad. Over the past five years, the total amount of Wal-Mart's interest-bearing debt has grown at 7.3 percent compounded annually. Assume that each of three interest-bearing sources of debt capital will increase at 7.3 percent per year over Year +1 through Year +5.
Other Current Liabilities
Other current 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.
Other Noncurrent Liabilities
Other noncurrent liabilities include amounts related to health care benefits and deferred taxes. Assume that other noncurrent liabilities will increase at the growth rate in sales.
Common Stock
Assume that common stock and additional paid-in capital will not change.
Retained Earnings
The increase in retained earnings equals net income minus dividends. Wal-Mart paid dividends amounting to $2,214 million to common shareholders in Year 4. Over the past five years, Wal-Mart's dividends have increased at an average annual rate of 20.0 percent. Assume that dividends will grow 20.0 percent each year between Year +1 and Year +5. In addition, assume that in Year +1 through Year +5, Wal-Mart will not repurchase any treasury stock.
Accumulated Other Comprehensive Income
Assume that accumulated other comprehensive income will not change.
Cash
At this point we can now project the amount of cash on Wal-Mart's balance sheet at each year-end from Year +1 to Year +5. We assume Wal-Mart uses cash as the flexible financial account to balance the balance sheet.
Statement of Cash Flows
Depreciation Add back
Include the change in accumulated depreciation.
Other Add backs
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 balance sheet are investing activities.

Required (continued from page 787)
b. If you have programmed your spreadsheet correctly, the projected amount of cash declines from Year +1 to Year +5, and the projected cash balance at the end of Year +5 is a negative $584 million. Given the profitability and growth projected for Wal-Mart, a negative balance in cash seems unlikely. Identify the likely reason for the negative projected amount of cash.
c. Assume now that long-term debt will grow in Year +1 to Year +5 at a growth rate of 12.0 percent, which is closer to the assumed growth rate of 14.4 percent in property, plant, and equipment - at cost. Leave the forecast assumptions for short-term debt and current maturities of long-term debt unchanged. Assess whether this growth rate in long-term debt provides more reasonable forecast amounts for cash.
d. Calculate the financial statement ratios listed in Exhibit 4.54 for Wal-Mart using the forecast amounts determined in part c for Year +1 to Year +5. Assess the projected changes in the profitability and risk of Wal-Mart for Year +1 to Year +5.

Inventory Turnover Ratio
Inventory Turnover RatioThe inventory turnover ratio is a ratio of cost of goods sold to its average inventory. It is measured in times with respect to the cost of goods sold in a year normally. Inventory Turnover Ratio FormulaWhere,... 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... 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... Distribution
The word "distribution" has several meanings in the financial world, most of them pertaining to the payment of assets from a fund, account, or individual security to an investor or beneficiary. Retirement account distributions are among the most...

Transcribed Image Text: