A B 2 3 Use the Data on the Inputs Page To Construct 1. Income Statement 2. Balance Sheet 3. Cash Flow Statement 5 6 Once you have those statements done, compute the "One Sizing" for each statement You will be asked to compute a number of ratios. Do so where indicated, which is below the financial statements Round all Figures in the Statements to the nearest Dollar Round Percentages to 1 decimal place Round Ratios to 2 decimal places Show subtractions as a negative number e.g. Interest expense => -1,000 or (1,000) Consider Debt to be ALL Liabilities (i.e. Debt/Equity Ratio = Liability/Equity) 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Income Statement 33 Balance Sheet 34 Cash Flow Statement 35 Raw Score 36 Adjusted 37 Do not use column shaded in yellow. Format Imputs to you model should be colored in blue Calulations in your model should be colored in black ooooo 20 Instructions Inputs IS BS CFS + D E 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 MWG Enterprises 2017 Revenues Cost of goods sold Interest Rate Dividends Payout Ratio Depreciation Administrative expenses Short-Term Debt Inventory Accounts payable Long-Term debt Due in 10 Years Accounts Receivable Common stock Other Long-Term Liabilities Share Repurchases Tax Rate Gross Fixed Assets Purchase of New Equipment Original Cost of Old Equipment Sold Accumulated Depreciation on Equipment Sold Amortization Intangibles Net Fixed Assets New Short Term Debt Issued Asset Sales Proceeds Retained Earnings 0 Accumulated Depreciation 16 2018 $95,000.00 $62,175.00 4.0% 60.0% $5,250.00 $9,500.00 $8,750.00 $6,200.00 $5,275.00 $42,500.00 $6,350.00 $45,000.00 $7,750.00 $0.00 25.0% $72,500.00 N/A N/A N/A $3,200.00 $18,000.00 $55,000.00 N/A N/A Calculate Calculate 2019 $102,500.00 $69,675.00 5.0% 60.0% $5,375.00 $10,250.00 Calculate $5,900.00 $5,575.00 Calculate $6,900.00 calculate $7,250.00 $10,000.00 25.0% Calculate $8,250.00 $2,500.00 $2,000.00 $3,200.00 Calculate Calculate $2,500.00 $0.00 Calculate Calculate 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Instructions Inputs IS BS CFS + A C D E F 1 2 3 Revenue G One Sizing Formatting 2,018 2,019 2,018 95,000 2,019 102,500 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Dividend 24 Retained Earnings 25 26 EBITDA 27 28 Gross Margin 29 EBITDA Margin 30 Profit Margin 31 32 33 Solvency Analysis 34 Times Interest Earned 35 Cash (flow) Coverage Ratio 36 Debt to Cash Flow Ratio 37 38 Profitability Analysis 39 ROA (Pretax) 40 ROA (Aftertax) 41 ROC (Pretax) 42 ROE (After Tax) 43 Instructions Inputs IS BS CFS + Ready A B E - J 1 H One Sizing Formatting 2,018 2,019 2,018 2,019 2 3 4 Cash 5 6 7 8 9 10 Gross Fixed Assets 11 12 13 14 15 16 17 Total Assets 18 19 20 21 Current Liabilities 22 23 24 25 26 Total Long-Term Liabilities 27 28 Total Liabilities 29 30 31 32 33 Total Equity 34 35 Liab & Owners Equity 36 37 Working Capital 38 Change in Workig Capital 39 40 Capital Spending 41 Asset Sold 42 Net Capital Spending 43 44 Liquidity Analysis 45 Current Ratio 46 Quick Ratio 47 Cash Ratio 48 49 Activity Analysis 50 Days Receivable 51 Days Inventory 52 Days Payable 53 Asset Turnover 54 Capital Intensity 55 56 Solvency Analysis 57 Debt/Assets 58 Debt/Equity 59 Equity Multiplier 60 61 Capital Structure 62 Debt/Equity 63 Debt/Assets 64 65 Instructions Inputs IS BS CFS + B D E F G HT 1 One Sizing Formatting 2,019 2,019 2 3 4 Operating Activity 5 Net Income 6 7 8 9 10 11 12 13 14 15 16 CapEx 17 18 19 20 21 Financing Activity 22 23 24 25 26 27 Net Change in Cash 28 Beginning Cash 29 Ending Cash 30 31 32 33 34 35 36 37 Instructions Inputs IS BS CFS + Ready A B 2 3 Use the Data on the Inputs Page To Construct 1. Income Statement 2. Balance Sheet 3. Cash Flow Statement 5 6 Once you have those statements done, compute the "One Sizing" for each statement You will be asked to compute a number of ratios. Do so where indicated, which is below the financial statements Round all Figures in the Statements to the nearest Dollar Round Percentages to 1 decimal place Round Ratios to 2 decimal places Show subtractions as a negative number e.g. Interest expense => -1,000 or (1,000) Consider Debt to be ALL Liabilities (i.e. Debt/Equity Ratio = Liability/Equity) 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Income Statement 33 Balance Sheet 34 Cash Flow Statement 35 Raw Score 36 Adjusted 37 Do not use column shaded in yellow. Format Imputs to you model should be colored in blue Calulations in your model should be colored in black ooooo 20 Instructions Inputs IS BS CFS + D E 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 MWG Enterprises 2017 Revenues Cost of goods sold Interest Rate Dividends Payout Ratio Depreciation Administrative expenses Short-Term Debt Inventory Accounts payable Long-Term debt Due in 10 Years Accounts Receivable Common stock Other Long-Term Liabilities Share Repurchases Tax Rate Gross Fixed Assets Purchase of New Equipment Original Cost of Old Equipment Sold Accumulated Depreciation on Equipment Sold Amortization Intangibles Net Fixed Assets New Short Term Debt Issued Asset Sales Proceeds Retained Earnings 0 Accumulated Depreciation 16 2018 $95,000.00 $62,175.00 4.0% 60.0% $5,250.00 $9,500.00 $8,750.00 $6,200.00 $5,275.00 $42,500.00 $6,350.00 $45,000.00 $7,750.00 $0.00 25.0% $72,500.00 N/A N/A N/A $3,200.00 $18,000.00 $55,000.00 N/A N/A Calculate Calculate 2019 $102,500.00 $69,675.00 5.0% 60.0% $5,375.00 $10,250.00 Calculate $5,900.00 $5,575.00 Calculate $6,900.00 calculate $7,250.00 $10,000.00 25.0% Calculate $8,250.00 $2,500.00 $2,000.00 $3,200.00 Calculate Calculate $2,500.00 $0.00 Calculate Calculate 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Instructions Inputs IS BS CFS + A C D E F 1 2 3 Revenue G One Sizing Formatting 2,018 2,019 2,018 95,000 2,019 102,500 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Dividend 24 Retained Earnings 25 26 EBITDA 27 28 Gross Margin 29 EBITDA Margin 30 Profit Margin 31 32 33 Solvency Analysis 34 Times Interest Earned 35 Cash (flow) Coverage Ratio 36 Debt to Cash Flow Ratio 37 38 Profitability Analysis 39 ROA (Pretax) 40 ROA (Aftertax) 41 ROC (Pretax) 42 ROE (After Tax) 43 Instructions Inputs IS BS CFS + Ready A B E - J 1 H One Sizing Formatting 2,018 2,019 2,018 2,019 2 3 4 Cash 5 6 7 8 9 10 Gross Fixed Assets 11 12 13 14 15 16 17 Total Assets 18 19 20 21 Current Liabilities 22 23 24 25 26 Total Long-Term Liabilities 27 28 Total Liabilities 29 30 31 32 33 Total Equity 34 35 Liab & Owners Equity 36 37 Working Capital 38 Change in Workig Capital 39 40 Capital Spending 41 Asset Sold 42 Net Capital Spending 43 44 Liquidity Analysis 45 Current Ratio 46 Quick Ratio 47 Cash Ratio 48 49 Activity Analysis 50 Days Receivable 51 Days Inventory 52 Days Payable 53 Asset Turnover 54 Capital Intensity 55 56 Solvency Analysis 57 Debt/Assets 58 Debt/Equity 59 Equity Multiplier 60 61 Capital Structure 62 Debt/Equity 63 Debt/Assets 64 65 Instructions Inputs IS BS CFS + B D E F G HT 1 One Sizing Formatting 2,019 2,019 2 3 4 Operating Activity 5 Net Income 6 7 8 9 10 11 12 13 14 15 16 CapEx 17 18 19 20 21 Financing Activity 22 23 24 25 26 27 Net Change in Cash 28 Beginning Cash 29 Ending Cash 30 31 32 33 34 35 36 37 Instructions Inputs IS BS CFS + Ready