Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

10.16 PREPARING AND INTERPRETING FINANCIAL STATEMENT FORECASTS. Wal-Mart Stores, Inc. (Walmart)... 10.16 PREPARING AND INTERPRETING FINANCIAL STATEMENT FORECASTS. Wal-Mart Stores, Inc. (Walmart) is the largest

10.16 PREPARING AND INTERPRETING FINANCIAL STATEMENT FORECASTS. Wal-Mart Stores, Inc. (Walmart)...

10.16 PREPARING AND INTERPRETING FINANCIAL STATEMENT FORECASTS. Wal-Mart 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.11, 10.12, and 10.13 present the financial statements of Walmart for 2010-2012. Exhibits 4.504.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 20102012. (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 non- recurring items such as discontinued operations.)

Required (additional requirements follow on page 843)

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 problem, assume that Walmart will exercise its financial flexibility with the cash and cash equivalents account to balance the balance sheet.

2010

2011

2012

Assets:

Cash

$7,395

$6,550

$7,781

Receivables

5,089

5,937

6,768

Inventories

36,318

40,714

43,803

Prepaid expenses and other current assets

2,960

1,774

1,588

Current assets of discontinued segments

131

Current Assets

$51,893

$54,975

$59,940

Property, plant & equipmentAt cost

154,489

160,938

171,724

Accumulated depreciation

(46,611)

(48,614)

(55,043)

Goodwill

16,763

20,651

20,497

Other assets

Total Assets

4,129

$180,663

5,456

$193,406

5,987

$203,105

Liabilities and Equities

Accounts payable

$33,557

$36,608

$38,080

Current accrued expenses

18,701

18,180

18,808

Notes payable and short term debt

1,031

4,047

6,805

Current maturities of long term debt

4,991

2,301

5,914

Income taxes payable

Current liabilities od discount operations

157

47

1,164

2,211

Current Liabilities

$ 58,484

$ 62,300

$ 71,818

Long term debt obligations

43,842

47,079

41,417

Deferred tax liabilities-noncurrent

6,682

7.862

7,613

Redeemable noncontrolling interest

Total Liabilities

408

$109, 416

404

$117,645

519

$121,367

Common stock Additional paid in capital

3,929

4,034

3,952

Retained earnings

63,967

68,691

72,978

Accumulated other comprehensive income (Loss)

646

(1,410)

(587)

Total Common Shareholders Equity

$68,542

$71,315

$76,343

Noncontrolling interest

2,705

4,446

5,395

Total Equities

71,247

75,767

81,738

Total Liabilities and Equities

$180,663

$193,406

$203,105

2010

2011

2012

Revenues

$421,849

$446,950

$469,162

Cost of goods sold

(314,946)

(335,127)

(352,488)

Gross Profit

$106,903

$111,823

$116,674

Selling, general, and administrative expense

(81,361)

(85,265)

(88,873)

Operating Profit

$25,542

$26,558

$27,801

Interest income

201

162

187

Interest expense

(2,205)

(2,322)

(2,251)

Income before Tax

$23,538

$24,398

$25,737

Income tax expense

(7,579)

(7,944)

(7,981)

Income (Loss) from discontinued operations

1,034

(67)

Net Income

Net income attributable to noncontrolling interests

$16,993

(604)

$16,387

(688)

$17,756

(757)

Net income attributable to common shareholders

$16, 389

$15,699

$16,999

Other comprehensive income items

716

(2,056)

823

Comprehensive Income

$17,105

$13,643

$17,822

2010 2011 2012

Net Income

$16,993

$16,387

$17,756

Add back depreciation and amortization expenses

7,647

8,130

8,501

Deferred taxes

651

1,050

(133)

(Increase) Decrease in account receivables

(733)

(796)

(614)

(Increase) Decrease in inventories

(3,205)

(3,727)

(2,759)

Increase (Decrease) in accounts payable

2,676

2,687

1,061

Increase (Decrease) in income taxes payable

Increase (Decrease) in other current liabilities

(Income) Loss from discontinued segments

(153)

(280)

(1,034)

994

(935)

67

981

(271)

Other operating cash flows

1,087

398

527

Net Cash Flow From Operating activities

$23,643

$24,255

$25,591

Proceeds from sales of property, plant, and equipment

489

580

532

Property, plant, and equipment acquired

(12,699)

(13,510)

(12,898)

Investments acquired

(202)

(3,548)

(316)

Other investment transactions

219

(131)

71

Net Cash Flow from Investing Activities

$(12,193)

$(16,609)

$(12,611)

Increase (Decrease) in short-term borrowing

503

3,019

2,754

Increase (Decrease) in long-term borrowing

7,316

466

(1,267)

Share repurchases-treasury stock

(14,776)

(6,298)

(7,600)

Dividend payments

(4,437)

(5,048)

(5,361)

Other financing activities

(634)

(597)

(498)

Net Cash Flow from Financing Activities

$(12,028)

$(8,458)

$(11,972)

Effects of exchange rate changes on cash

66

(33)

223

Net Change in Cash

$(512)

$(845)

$1,231

Cash and cash equivalents, beginning of year

$7,907

$7,395

$6,550

Cash and cash equivalents, end of year

$7,395

$6,550

$7,781

Income Statement Forecast Assumptions

Sales

Sales grew by 3.4 % in 210, 6.0% in 2011, and 5.0% in 2012. The compound annual sales growth rate during the last five years was 5.5%. Walmart generates sales growth primarily through increasing same-store sales, opening new stores, and acquiring other retailers. In the future, Walmart will 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 2012. Assume that sales will grow 4.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 74.7% of sales in 2010 to 75.0 % in 2011 to 75.1%t in 2012. 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 remain steady at 75.0% for Year +1 to Year +5.

Selling and Administrative Expenses

The selling and administrative expense percentage has declined slightly from 19.3% of sales in 2010 to 19.1% in 2011 to 18.9% of sales in 2012. 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 will continue to average 19.0% of sales for Year +1 to Year +5.

Interest Income

Walmart earns some interest income on its cash and cash equivalents accounts. The average interest rate earned on average cash balances was approximately 2.6% during 2012, similar to rates earned in 2010 and 2011. Assume that Walmart will earn interest income based on a 2.6% 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 borrowing to finance corporate acquisitions. The average interest rate on all interest-bearing debt and capital leases was approximately 4.2% during 2011 and 2012. Assume a 4.2% 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

Walmarts average income tax rate as a percentage of income before taxes has been roughly

32% during the last two years. Assume that Walmarts effective income tax rate remains a constant 32% 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 $757 million share in Walmarts 2012 net income which amounted to roughly a 15% 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 15% 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 three days during each of the past three years. Assume that accounts receivable will increase at the growth rate in sales.

Inventories

Walmarts overall inventory efficiency has declined slightly in the past two years, in part of the distribution of merchandise to stores worldwide. Inventory turnovers have increased from an average of 42 days in 2011 to 44 days in 2012. Assume that ending inventory will continue to be equal to 44 days of cost of goods sold, Year +1 to +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 balance sheets in 20120 and prior recognize amounts as current assets and current liabilities that are associated with discontinued segments (subsidiaries that Walmart is divesting). These operations were divested in 2011, so assume that these amounts will be zero in Year +1 through Year +5.

Property, Plant, and EquipmentAt Cost

Property, plant, and equipment (including assets held under capital leases) grew by roughly $12.5 billion per year in 2010 through 2012 (capital expenditures net of proceeds from selling property, plant and equipment). 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 continue to grow by $12.5 billion each year from Year +1 through Year +5.

Accumulated Depreciation

In 2011 and 2012, Walmart depreciated property, plant, and equipment using an average useful life of approximately 19.8 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 property, plant, and equipmentat 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.

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 in 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 Liabilities Noncurrent

For simplicity, assume the income taxes payable and deferred tax liabilities noncurrent grow at 3.0% per year in Year +1 through Year +5.

Redeemable Noncontrolling Interest

Redeemable noncontrolling interest amount to investment made by third-party investor in subsidiaries that Walmart controls and consolidates. Because these noncontrolling interest are redeemable, Walmart can redeem (pay off and retire) these interests. For simplicity, assume Walmart redeems and retires these redeemable noncontrolling interest in Year +1.

Short-Term Debt, Current Maturities of Long-Term Debt, Capital Leases, and Long-Term Debt

Walmart uses short-term debt, current maturities of long-term debt, capital leases, 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 three years, each individual amount of debt financing (short-term debt and current maturities of long-term debt, and long-term debt) have fluctuated considerably from year to year, whereas the aggregate amount of debt financing has remained fairly steady, averaging roughly 27.0% of total assets. For simplicity, assume that the total amount of short-term debt, current maturities of long-term debt, and long-term debt will continue to remain a fairly 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, Walmart has increased common stock and additional paid-in capital have remained a fairly steady 2.0% of total assets (Walmart repurchases company shares on the open market and then these shares to employees and executives to satisfy stock option exercises). Assume that common stock and additional paid-in capital will continue to be 2.0% of total assets for Year +1 through Year +5.

Retained Earnings

The increase in retained earnings equals net income minus dividends. In 2012, Walmart paid total dividends amounting of $5,361 million to common shareholders in 2008, which amounted to roughly 30% of net income attributable to Walmart shareholders. Assume that Walmart will maintain a policy to pay dividends equivalent to 30% of net income attributable to Walmart shareholders 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.

Noncontrolling Interest

Noncontrolling interest amount to equity investment made by third-party investor in subsidiaries that Walmart controls and consolidates. Noncontrolling interest grow each year by their proportionate share of the subsidiarys income, and these interests decrease by any dividends paid them. We assumed for purposes of the income statement, that ne income attributable to noncontrolling interest would generate a 15% rate of return for those investors. For simplicity, assume Walmarts controlling interest will grow by the amount of net income attributable to these noncontrolling interests each year, and dividends paid to them will be the same amount in Year +1 to Year +5. Therefore, the amount of noncontrolling interest 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 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 balance sheet are investing activities.

Required (continued from page 839)

b. If you have programmed your spreadsheet correctly, the projected amount of cash grows steadily from Year +1 to Year +5 and the projected cash balance at the end of Year +5 is a whopping $33,511 million (allow for rounding), which is more than 12.5%t of total assets. Identify one problem that so much cash could create for the financial management of Walmart.

c. Assume that Walmart will augment its dividend policy by paying out 30% of lagged 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 $7,781 million (the ending cash balance in 2012). 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?

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Using Financial Accounting Information The Alternative to Debits and Credits

Authors: Gary A. Porter, Curtis L. Norton

7th Edition

978-0-538-4527, 0-538-45274-9, 978-1133161646

More Books

Students also viewed these Accounting questions

Question

What are negative messages? (Objective 1)

Answered: 1 week ago