Financial Planning Spreadsheet - Create the Excel-based Financial Planning Model.

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 Sales Growth Rate Tax Rate Int Rate on Short-Term Debt Int Rate on Long-Term Debt Dividend Payout Rate Price / Earnings 0.1% 4.6% 0.1% 4.9% 0.5% 4.8% 5.0% 35.0% 1.0% 5.0% 32.0% 15.0 5.0% 35.0% 1.0% 5.0% 32.0% 15.0 5.0% 35.0% 1.0% 5.0% 32.0% 15.0 Income Statement (Mil.$) [As originally stated in FYE Annual Report.] Sales (Total Revenues) Cost of Goods Sold Gross Margin Selling, Gen & Adm Expenses Depreciation EBIT (exclude special items) Interest Expense (net) Taxes Special items & Minority int. Net Income (check actual) Shares Outstanding (Millions) Earnings Per Share (check actual) Allocation of Net Income: Dividends (total) Income to Equity Balance Sheet (Mil.$) $469,162 $476,294 $485,651 $352,488 $358,069 $365,086 $80,372 $8,501 $82,553 $8,800 $84,245 $9,173 $2,064 $7,981 -$757 $2,216 $8,105 -$529 $2,348 $7,985 -$451 -$500 -$500 -$500 3,374 $0.00 3,269 $0.00 3,230 $0.00 #DIV/0! #DIV/0! #DIV/0! $5,361 $6,139 $6,185 [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 Other Current Assets $1,774 $1,588 $2,369 $2,224 Total Current Assets (check actual) 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 Property, Plant & Equip. (PPE) $160,938 $171,724 $178,678 $182,634 Accumulated Deprec. & Amort $48,614 $55,043 $60,771 $65,979 Net PPE Other Non-Current Assets $26,107 $26,484 $25,659 $23,773 Total Assets (check actual) Liabilities and Shareholders' Equity Current Liabilities Accounts Payable $40,655 Short-term Debt $2,301 Other Current Liabilities $19,344 Total Current Liabilities (check actual) $44,885 $5,914 $21,019 $45,085 $4,412 $19,848 $40,002 $5,097 $20,173 $3,952 $72,391 $2,685 $73,570 $2,785 $78,609 $69.95 $74.68 $84.98 Long-term Debt & Liabilities (includes minority interest) Total Liabilities (check actual) Shareholders' Equity Paid-in Capital $4,034 Retained Earnings (& Comp. Income) $67,281 Total Shareholders' Equity (check actual) Total Liab. & Share. Equity Debt / (Debt + Equity) Market Price per Share External Funds Needed $61.36 Income Statement (% of Sales) Sales Cost of Goods Sold Gross Margin Selling, Gen & Adm Expenses Depreciation EBIT $2,785 $2,785 $2,785 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 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 Other Non-Current Assets Total Assets Liabilities and Shareholders' Equity Current Liabilities Accounts Payable Short-term Debt Other Current Liabilities Total Current Liabilities Long-term Debt FINANCIAL PLANNING SPREADSHEET Wal-Mart (FYE 1/31) Financial Plan Total Liabilities Shareholders' Equity Paid-in Capital Retained Earnings Total Shareholders' Equity Total Liabilities and Equity 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 2012 2013 2014 2015 2016 2017 2018 Ave Hist. Actual Actual Actual Actual Forecast Forecast Forecast % of Sales 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.) BONUS : The 1/31/16 finacial statements are out. Explain the three most important reasons that actual results were different than those we projected in the "Basic Plan. 1) 2) 3) ention to how "external funds" is asons that actual results were Justify your assumptions here. Type in the shaded areas. Items 1 2 3 4 5 6 7