Please answer questions on page 2 and justify page with numbers from page 1 , also provide formulas used for all calculations

FINANCIAL PLANNING SPREADSHEET Wal-Mart (FYE 1/31) Financial Plan Key Assumptions 2012 2013 2014 2015 2016 2017 2018 Ave Hist. Actual Actual Actual Actual Forecast Forecast Forecast % of Sales Tax Rate Int Rate on Short-Term Debt Int Rate on Long-Term Debt 31.01% 1.52% 32.87% 1.96% 32.20% 0.1% 4.6% 0.1% 4.9% 0.5% 4.8% Dividend Payout Rate 31.54% 13.88 38.32% 15.24 Sales Growth Rate Price / Earnings 5.0% 5.0% 5.0% 35.0% 1.0% 5.0% 35.0% 1.0% 5.0% 35.0% 1.0% 5.0% 37.80% 16.77 32.0% 32.0% 32.0% 15.0 15.0 15.0 $535,430 $402,437 $562,202 $422,559 Income Statement (Mil.$) [As originally stated in FYE Annual Report.] Sales (Total Revenues) $469,162 $476,294 $485,651 Cost of Goods Sold $352,488 $358,069 $365,086 $509,934 $383,273 Gross Margin $116,674 $118,225 $120,565 $126,661 $132,994 $139,643 Selling, Gen & Adm Expenses $80,372 $82,553 $84,245 Depreciation EBIT (exclude special items) $8,501 $27,801 $8,800 $26,872 $9,173 $27,147 $88,066 $9,431 $92,469 $9,903 $97,093 $10,398 $29,164 $30,622 $32,153 $2,064 $2,216 $2,348 $7,981 -$757 $16,999 $8,105 -$529 $16,022 $7,985 -$451 $16,363 $2,903 $9,191 $2,639 $9,794 $2,401 $10,413 -$500 $16,570 -$500 $17,689 -$500 $18,839 3,374 $5.04 3,269 $4.90 3,230 $5.07 3230 3230 3230 $5.13 $5.48 $5.83 Dividends (total) $5,361 $6,139 $6,185 Income to Equity $11,638 $9,883 $10,178.0 $5,302 $11,267 $5,660 $12,028 $6,028 $12,810 $8,615 $7,207 $47,678 $2,199 $65,699 $9,045 $7,568 $50,062 $2,309 $68,984 $9,498 $7,946 $52,565 $2,425 $72,433 216,753 85,313 131,440 28,426 233,722 95,710 138,012 29,848 Interest Expense (net) Taxes Special items & Minority int. Net Income (check actual) Shares Outstanding (Millions) Earnings Per Share (check actual) 75% 17.27% 1.85% Allocation of Net Income: Balance Sheet (Mil.$) [As originally stated in FYE Annual Report.] Assets Current Assets Cash & Equivalents $6,550 $7,781 $7,281 $9,135 Receivables $5,937 $6,768 $6,677 $6,778 Inventories $40,714 $43,803 $44,858 $45,141 $1,774 $1,588 $2,369 $2,224 $54,975 $59,940 $61,185 $63,278 $160,938 $171,724 $178,678 $182,634 $48,614 $55,043 $60,771 $65,979 $112,324 $116,681 $117,907 $116,655 $26,107 $26,484 $25,659 $23,773 200,591 75,410 125,181 27,073 $193,406 $203,105 $204,751 $203,706 $217,953 $228,851 $240,293 $46,352 $5,501 $21,759 $48,670 $5,776 $22,847 $51,103 $6,065 $23,989 $73,612 $77,293 1.69% 1.41% 9.35% 0.43% $81,158 Other Current Assets Total Current Assets (check actu Property, Plant & Equip. (PPE) Accumulated Deprec. & Amort Net PPE Other Non-Current Assets Total Assets (check actual) 24.55% 5.31% Liabilities and Shareholders' Equity Current Liabilities Accounts Payable $40,655 $44,885 $45,085 $40,002 $2,301 $5,914 $4,412 $5,097 $19,344 $62,300 $21,019 $71,818 $19,848 $69,345 $20,173 $65,272 $59,791 $54,944 $59,151 $122,091 $126,762 $128,496 $57,040 $122,312 $51,679 $125,292 $46,868 $124,161 $41,635 $122,793 Short-term Debt Other Current Liabilities Total Current Liabilities (check a Long-term Debt & Liabilities (inc Total Liabilities (check actual) Shareholders' Equity Paid-in Capital $4,034 $3,952 $2,685 $2,785 $2,785 $2,785 $2,785 $67,281 $72,391 $73,570 $78,609 $71,315 $76,343 $76,255 $81,394 $89,876 $92,661 $101,905 $104,690 $114,715 $117,500 $193,406 $203,105 $204,751 $203,706 $217,953 $228,851 $240,293 Retained Earnings (& Comp. Income) Total Shareholders' Equity (chec Total Liab. & Share. Equity Debt / (Debt + Equity) 9.09% 1.08% 4.27% 0.4654328 44% 45% 43% $61.36 $69.95 $74.68 $84.98 $359 $267 100.00% 100.00% 75.13% 75.18% 24.87% 24.82% Market Price per Share External Funds Needed -$1,001 38% 76.95 $ -$3,371 33% 82.15 $ -$3,448 100.00% 75.17% 24.83% 100% 75% 25% 100% 75% 25% 17.35% 1.89% 5.59% 17% 2% 6% 17% 2% 6% $ 29% 87.49 -$3,801 Income Statement (% of Sales) Sales Cost of Goods Sold Gross Margin Selling, Gen & Adm Expenses Depreciation EBIT 17.13% 1.81% 5.93% 17.33% 1.85% 5.64% 100% 100.00% 75% 75.16% 25% 24.84% 17% 2% 6% 17.27% 1.85% 5.72% FINANCIAL PLANNING SPREADSHEET Wal-Mart (FYE 1/31) Financial Plan 2012 2013 2014 2015 2016 2017 2018 Ave Hist. Actual Actual Actual Actual Forecast Forecast Forecast % of Sales 0.44% 1.70% -0.16% 3.62% 0.72% 0.00% 0.47% 1.70% -0.11% 3.36% 0.69% 0.00% 0.48% 1.64% -0.09% 3.37% 0.67% 0.00% 0.57% 1.80% -0.10% 3.25% 0.63% 0.00% 0.49% 1.83% -0.09% 3.30% 0.60% 0.00% 0.43% 1.85% -0.09% 3.35% 0.57% 0.00% 0.46% 1.68% -0.12% 3.45% 0.69% 0.00% 1.14% 2.48% 1.29% 2.07% 1.27% 2.10% 1.04% 2.21% 1.06% 2.25% 1.07% 2.28% 1.24% 2.22% 1.66% 1.44% 9.34% 0.34% 12.78% 1.53% 1.40% 9.42% 0.50% 12.85% 1.88% 1.40% 9.29% 0.46% 13.03% 1.69% 1.41% 9.35% 0.43% 12.88% 1.69% 1.41% 9.35% 0.43% 12.88% 1.69% 1.41% 9.35% 0.43% 12.88% 1.69% 1.41% 9.35% 0.43% 12.88% Other Non-Current Assets 36.60% 11.73% 24.87% 5.64% 37.51% 12.76% 24.76% 5.39% 37.61% 13.59% 24.02% 4.90% 39.34% 14.79% 24.55% 5.31% 40.48% 15.93% 24.55% 5.31% 41.57% 17.02% 24.55% 5.31% 37.24% 12.69% 24.55% 5.31% Total Assets 43.29% 42.99% 41.94% 42.74% 42.74% 42.74% 42.74% 9.57% 1.26% 4.48% 15.31% 9.47% 0.93% 4.17% 14.56% 8.24% 1.05% 4.15% 13.44% 9.09% 1.08% 4.27% 14.44% 9.09% 1.08% 4.27% 14.44% 9.09% 1.08% 4.27% 14.44% 9.09% 1.08% 4.27% 14.44% 11.71% 27.02% 12.42% 26.98% 11.75% 25.19% 10.13% 24.57% 8.75% 23.19% 7.41% 21.84% 11.96% 26.39% Total Shareholders' Equity 0.84% 15.43% 16.27% 0.56% 15.45% 16.01% 0.57% 16.19% 16.76% 0.55% 17.63% 18.17% 0.52% 19.03% 19.55% 0.50% 20.40% 20.90% 0.66% 15.69% 16.35% Total Liabilities and Equity 43.29% 42.99% 41.94% 42.74% 42.74% 42.74% 42.74% 5.93% 14.02% 23.02% 5.64% 13.18% 21.00% 5.59% 13.29% 20.76% 5.72% 13.83% 19.04% 5.72% 13.71% 17.93% 5.72% 13.71% 16.96% 73.85 8.34 2.37 70.85 8.08 2.34 72.19 8.11 2.38 72.92 8.26 2.42 72.48 8.23 2.40 72.48 8.23 2.40 27.05% 13.47 28.89% 12.13 28.00% 11.56 23.71% 10.05 20.48% 11.60 17.33% 13.39 0.83 0.20 0.88 0.20 0.97 0.24 0.89 0.21 0.89 0.21 0.89 0.21 13.88 3.09 15.24 3.20 16.77 3.37 15.00 2.68 15.00 2.53 15.00 2.40 Interest Expense Taxes Special items Net Income Shares Outstanding (Millions) Earnings Per Share Allocation of Net Income: Dividends Change in Equity Balance Sheet (% of Sales) Assets Current Assets Cash & Equivalents Receivables Inventories Other Current Assets Total Current Assets Property, Plant & Equip. (PPE) Accum Depreciation Net PPE Liabilities and Shareholders' Equity Current Liabilities Accounts Payable Short-term Debt Other Current Liabilities Total Current Liabilities Long-term Debt Total Liabilities Shareholders' Equity Paid-in Capital Retained Earnings Financial Ratios Profitability Return On Sales (ROS) Return On Assets (ROA) Return On Equity (ROE) Asset Turnover Receivables Turnover Inventory Turnover Asset Turnover Financial Leverage Long-Term Debt Times Interest Earned Liquidity Current Quick Market Value Price to Earnings Market to Book Use the information in the Financial Plan worksheet to compute the firm's SGR and IGR, as defined in this lesson. Be sure to use ROE as defined for SGR and IGR. How do SGR and IGR relate to row 67? Insert rows if needed. SGR = ROE = Payout = IGR = Equity/Assets = (SGR) Add. external financing = (IGR) Tot. external financing = How do SGR and IGR relate to "External Funds Needed" in row 67? (Hint: pay close attention to how "external funds" is defined.) Justify your assumptions here. Type in the shaded areas. Items 1 2 3 4 5 6 7