1. **Problem Statement:** Create linked Income Statement and Balance Sheet models in Excel using given inputs and formulas, then calculate financial ratios on a separate sheet. 2. **Income Statement Setup:** - Revenue = 100000 (input) - COGS = 50% of Revenue = $100000 \times 0.5 = 50000$ - SG&A Expense = 10000 (input) - Interest Expense = 12% of Long-Term Debt (from Balance Sheet) - Depreciation = 20% of Net Fixed Assets (from Balance Sheet) - Taxes = 35% of Taxable Income - Dividends = 50% of Net Income - Number of Shares = 100000 (input) - EPS = Net Income / Number of Shares 3. **Balance Sheet Setup:** - Current Assets: - Cash = 20000 (input) - Accounts Receivable = 20% of Sales = $100000 \times 0.2 = 20000$ - Inventory = 20000 (input) - Total Current Assets = Cash + A/R + Inventory = 20000 + 20000 + 20000 = 60000 - Fixed Assets: - Net Fixed Assets = 75000 (input) - Total Assets = Total Current Assets + Net Fixed Assets = 60000 + 75000 = 135000 - Current Liabilities: - Accounts Payable = 40% of COGS = $50000 \times 0.4 = 20000$ - Notes Payable = 10000 (input) - Total Current Liabilities = 20000 + 10000 = 30000 - Long Term Debt = 20000 (input) - Owners Equity: - Retained Earnings = Net Income - Dividends - Total Owners Equity = Retained Earnings + Other Equity (if any, here assumed 0) - Total Liabilities and Owners Equity = Total Current Liabilities + Long Term Debt + Owners Equity 4. **Calculations for Income Statement:** - Interest Expense = 12% of Long Term Debt = $20000 \times 0.12 = 2400$ - Depreciation = 20% of Net Fixed Assets = $75000 \times 0.2 = 15000$ - Earnings Before Tax (EBT) = Revenue - COGS - SG&A - Interest Expense - Depreciation $$ EBT = 100000 - 50000 - 10000 - 2400 - 15000 = 26300 $$ - Taxes = 35% of EBT = $26300 \times 0.35 = 9205$ - Net Income = EBT - Taxes = $26300 - 9205 = 17095$ - Dividends = 50% of Net Income = $17095 \times 0.5 = 8547.5$ - Retained Earnings = Net Income - Dividends = $17095 - 8547.5 = 8547.5$ - EPS = Net Income / Number of Shares = $17095 / 100000 = 0.17095$ 5. **Owners Equity Calculation:** - Assuming no other equity, Owners Equity = Retained Earnings = 8547.5$ 6. **Total Liabilities and Owners Equity:** - Total = Current Liabilities + Long Term Debt + Owners Equity $$ 30000 + 20000 + 8547.5 = 58547.5 $$ 7. **Check Balance Sheet:** - Total Assets = 135000 - Total Liabilities and Owners Equity = 58547.5 - Difference indicates missing equity or adjustments needed; typically Owners Equity includes contributed capital. 8. **Ratios (formulas and explanations):** - Current Ratio = Total Current Assets / Total Current Liabilities = $60000 / 30000 = 2$ - Quick Ratio = (Cash + A/R) / Current Liabilities = $(20000 + 20000) / 30000 = 1.33$ - Inventory Turnover = COGS / Inventory = $50000 / 20000 = 2.5$ - Average Collection Period = 365 / Accounts Receivable Turnover = $365 / 11.5 \approx 31.74$ days - Fixed Asset Turnover = Revenue / Net Fixed Assets = $100000 / 75000 = 1.33$ - Total Asset Turnover = Revenue / Total Assets = $100000 / 135000 \approx 0.74$ - Debt Ratio = Total Debt / Total Assets = $(30000 + 20000) / 135000 = 0.37$ - Debt to Equity = Total Debt / Owners Equity = $50000 / 8547.5 \approx 5.85$ - Time Interest Earned = EBIT / Interest Expense, where EBIT = EBT + Interest Expense = $26300 + 2400 = 28700$ $$ \text{TIE} = 28700 / 2400 \approx 11.96 $$ - Gross Profit Margin = (Revenue - COGS) / Revenue = $(100000 - 50000) / 100000 = 0.5$ - Net Profit Margin = Net Income / Revenue = $17095 / 100000 = 0.17095$ - Return on Assets = Net Income / Total Assets = $17095 / 135000 \approx 0.1266$ - Return on Equity = Net Income / Owners Equity = $17095 / 8547.5 = 2 **Final Notes:** - Set up Excel with these formulas linking cells as described. - Ensure all inputs are entered as given. - Calculate all intermediate values and link sheets properly.