14 Jul 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, whi
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.)
QUESTION: 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.
PLEASE FILL THE SPREADSHEET USING EXCEL FORMULA
Income Statement
| COMPREHENSIVE INCOME STATEMENT | |||||||||
| Assumption per pg.711 (growth rate only for Revenue) – others are Not growth rate | 2016 | Year +1 | Year +2 | Year +3 | Year +4 | Year +5 | |||
| 2.00% | Revenues | 482,130 | 491,773 | 501,608 | 511,640 | 521,873 | 532,310 | ||
| 75% | COGS | (360,984) | (368,829) | (376,206) | (383,730) | (391,405) | (399,233) | ||
| Gross Profit | 121,146 | 122,943 | 125,402 | 127,910 | 130,468 | 133,078 | |||
| 20.00% | SG&A Expense | (97,041) | (98,355) | (100,322) | (102,328) | (104,375) | (106,462) | ||
| Operating Income | 24,105 | 24,589 | 25,080 | 25,582 | 26,094 | 26,616 | |||
| 1.50% | Interest Income | 81 | 168 | 254 | 358 | 474 | 601 | ||
| 5.00% | Interest Expense | (2,502) | – 0 | – 0 | – 0 | – 0 | – 0 | <- didn't finish the balance sheet yet | |
| Income Before Tax | 21,684 | 24,756 | 25,335 | 25,940 | 26,567 | 27,216 | |||
| 32.00% | Income Tax Expense | (6,558) | (7,922) | (8,107) | (8,301) | (8,502) | (8,709) | ||
| Net Income | 15,126 | 16,834 | 17,228 | 17,639 | 18,066 | 18,507 | |||
| 12.60% | NI Attributable to NCI | (386) | (386) | (386) | (386) | (386) | (386) | <- this row was already filled out on the template | |
| NI Attributable to CS | 14,740 | 16,448 | 16,842 | 17,253 | 17,680 | 18,121 | |||
| OCI | (4,429) | <- ???? | |||||||
| Comprehensive Income | 10,311 | ||||||||
Balance Sheet
| BALANCE SHEET | |||||||||
| Assumption per pg.712 (growth rate only for Accounts Receivable & Prepaid Exp) – others are Not growth rate | 2016 | Year +1 | Year +2 | Year +3 | Year +4 | Year +5 | |||
| Cash & CE | 8,705 | 13,675 | 20,225 | 27,539 | 35,614 | 44,459 | <- this row was already filled out on the template | ||
| 2.00% | Accounts Receivables (net) | 5,624 | 5,736 | 5,851 | 5,968 | 6,088 | 6,209 | ||
| 45 | Inventory | 44,469 | 45,472 | 46,382 | 47,309 | 48,255 | 49,220 | ||
| 2.00% | Prepaid Expenses | 1,441 | 1,470 | 1,499 | 1,529 | 1,560 | 1,591 | ||
| Current Assets | 60,239 | 66,353 | 73,957 | 82,346 | 91,517 | 101,480 | |||
| $10,000.00 | PPE (cost) | 188,054 | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | <- ???? | |
| Acc. Depreciation | (71,538) | ||||||||
| Goodwill | 16,695 | ||||||||
| Other Assets | 6,131 | ||||||||
| Total Assets | 199,581 | ||||||||
| Accounts Payable | 38,487 | ||||||||
| Accrued Expenses | 19,607 | ||||||||
| Notes Payable/ST Debt | 2,708 | ||||||||
| LT Debt – Current | 3,296 | ||||||||
| IT Payable | 521 | ||||||||
| Current Liabilities | 64,619 | ||||||||
| LT Debt – Long-Term | 44,030 | ||||||||
| Deferred Taxes – LT | 7,321 | ||||||||
| Redeemable NCI | – 0 | – 0 | – 0 | – 0 | – 0 | – 0 | |||
| Total Liabilities | 115,970 | ||||||||
| Common Stock & APIC | 2,122 | ||||||||
| Retained Earnings | 90,021 | ||||||||
| Acc. Other Comprehensive Income | (11,597) | ||||||||
| Total Shareholder's Equity | 80,546 | ||||||||
| Noncontrolling Interests | 3,065 | 3,065 | 3,065 | 3,065 | 3,065 | 3,065 | <- this row was already filled out on the template | ||
| Total Equity | 83,611 | ||||||||
| Total Liabilities and Equity | 199,581 | ||||||||
CapEx, PP&E and Depreciation
| Forecast Development: Capital Expenditures, Property, Plant and Equipment, and Depreciation | |||||||||
| Property, Plant and Equipment and Depreciation | Property, Plant and Equipment and Depreciation Forecasts: | ||||||||
| PP&E at cost: | 2016 | Year +1 | Year +2 | Year +3 | Year +4 | Year +5 | |||
| Beg. balance at cost: | 188,054 | 198,054 | 208,054 | 218,054 | 228,054 | ||||
| Add: CAPEX forecasts: | 10,000 | 10,000 | 10,000 | 10,000 | 10,000 | ||||
| End balance at cost: | 188,054 | 198,054 | 208,054 | 218,054 | 228,054 | 238,054 | |||
| Accumulated Depreciation: | |||||||||
| Beg. Balance: | (71,538) | (81,441) | (91,843) | (102,746) | (114,149) | ||||
| Subtract: Depreciation expense forecasts from below: | (9,903) | (10,403) | (10,903) | (11,403) | (11,903) | ||||
| End Balance: | (71,538) | (81,441) | (91,843) | (102,746) | (114,149) | (126,051) | |||
| PP&E—net | 116,681 | 116,613 | 116,211 | 115,308 | 113,905 | 112,003 | |||
| Depreciation expense forecast on existing PP&E: | |||||||||
| Existing PP&E at cost: | 188,054 | 9,403 | 9,403 | 9,403 | 9,403 | 9,403 | |||
| Depreciation expense forecasts on new PP&E: | |||||||||
| Capex Year +1 | 500 | 500 | 500 | 500 | 500 | ||||
| Capex Year +2 | 500 | 500 | 500 | 500 | |||||
| Capex Year +3 | 500 | 500 | 500 | ||||||
| Capex Year +4 | 500 | 500 | |||||||
| Capex Year +5 | 500 | ||||||||
| Total Depreciation Expense | 9,903 | 10,403 | 10,903 | 11,403 | 11,903 | ||||