Write an IF statement that balances the Balance Sheet using either the Cash cells 118,118, and K18 or the Debt Cells 127, 127, and K27 (shaded in yellow). No hard coded answers will be allowed. DO NOT CHANGE any other formulas. (10 points) Income Statement Balance Sheet Factor Assumption Range Name Notes Factor Assumption Range Name Notes Sales growth S.0% sales growth prior year *(1+salesgrowth) Gross Fixed Assets/Sales 95.0% GFA current year sales GFA COGS/Sales 60.0% % current year salescogs Current Assets/Sales 25.0% assets current year sales assets Operating expense/Gross Margin 35.0% expense current year gross margin expense Current Liabilities/Sales 15.0% liabilities current year sales liabilities Depreciation/Gross Fixed Assets 15.0% depreciation current year gross foxed assets depreciation Debt I for when Liabilities debt % of debt reduced each year Interest rate on debt variable IRD current year debt.IRD Retained earnings add current year net income to get ending retained earnings Tax rate 30.0% taxes income before taxes taxes Cash write if statement for when Liabilities & Equity > Assets cogs Income Statement Balance Sheet Assets Year 1 Year 2 Year 3 Cash There should be no negative numbers in Cash or Debt Year o $500,000 $300,000 $200,000 Sales Cost of Goods Sold (COGS) Gross Margin Operating expenses Depreciation Operating income Interest expense Income before taxes Taxes Net income Year 1 $525.000 $315,000 $210,000 $73,500 $74,813 $61,688 $30,000 $31,688 $9,506 $22,181 Year 2 $ $551,250 $330,750 $220,500 $77,175 $78,553 $64,772 $40,000 $24,772 $7,432 $17,340 Year 3 $578,813 $347,288 $231,525 $81,034 $82,481 $ $68,010 $50,000 $18,010 $5,403 $12,607 Current Assets Gross Foved Assets less: Accumulated Depreciation Net Foxed Assets Total Assets Year 0 $760,000 $125,000 $475,000 $125.000 $350,000 $1.235,000 $131,250 $498,750 $199.813 $298.938 $430,185 $137,813 $523,688 $278.366 $245322 $383,134 $144,703 $549,872 $360846 $189,025 $333,729 Year 1 $78,750 Year 2 $82,688 Year 3 $86,822 Liabilities & Equity Current liabilities Debt Total liabilities Year O $100,000 $600,000 $700,000 There should be no negative numbers in Cash or Debt. $78,750 $82,688 $86,822 Stock (remains unchanged) Retained earnings Total equity Total Liabilities & Equity $425,000 $110,000 $535,000 $1,235,000 $325,000 $119,506 $444,506 $523,256 $200,000 $126,938 $326,938 $409,625 $100,000 $132,341 $232 341 $319,163 out of balance? SO -$93,069 -$26.491 $14,566 Write an IF statement that balances the Balance Sheet using either the Cash cells 118,118, and K18 or the Debt Cells 127, 127, and K27 (shaded in yellow). No hard coded answers will be allowed. DO NOT CHANGE any other formulas. (10 points) Income Statement Balance Sheet Factor Assumption Range Name Notes Factor Assumption Range Name Notes Sales growth S.0% sales growth prior year *(1+salesgrowth) Gross Fixed Assets/Sales 95.0% GFA current year sales GFA COGS/Sales 60.0% % current year salescogs Current Assets/Sales 25.0% assets current year sales assets Operating expense/Gross Margin 35.0% expense current year gross margin expense Current Liabilities/Sales 15.0% liabilities current year sales liabilities Depreciation/Gross Fixed Assets 15.0% depreciation current year gross foxed assets depreciation Debt I for when Liabilities debt % of debt reduced each year Interest rate on debt variable IRD current year debt.IRD Retained earnings add current year net income to get ending retained earnings Tax rate 30.0% taxes income before taxes taxes Cash write if statement for when Liabilities & Equity > Assets cogs Income Statement Balance Sheet Assets Year 1 Year 2 Year 3 Cash There should be no negative numbers in Cash or Debt Year o $500,000 $300,000 $200,000 Sales Cost of Goods Sold (COGS) Gross Margin Operating expenses Depreciation Operating income Interest expense Income before taxes Taxes Net income Year 1 $525.000 $315,000 $210,000 $73,500 $74,813 $61,688 $30,000 $31,688 $9,506 $22,181 Year 2 $ $551,250 $330,750 $220,500 $77,175 $78,553 $64,772 $40,000 $24,772 $7,432 $17,340 Year 3 $578,813 $347,288 $231,525 $81,034 $82,481 $ $68,010 $50,000 $18,010 $5,403 $12,607 Current Assets Gross Foved Assets less: Accumulated Depreciation Net Foxed Assets Total Assets Year 0 $760,000 $125,000 $475,000 $125.000 $350,000 $1.235,000 $131,250 $498,750 $199.813 $298.938 $430,185 $137,813 $523,688 $278.366 $245322 $383,134 $144,703 $549,872 $360846 $189,025 $333,729 Year 1 $78,750 Year 2 $82,688 Year 3 $86,822 Liabilities & Equity Current liabilities Debt Total liabilities Year O $100,000 $600,000 $700,000 There should be no negative numbers in Cash or Debt. $78,750 $82,688 $86,822 Stock (remains unchanged) Retained earnings Total equity Total Liabilities & Equity $425,000 $110,000 $535,000 $1,235,000 $325,000 $119,506 $444,506 $523,256 $200,000 $126,938 $326,938 $409,625 $100,000 $132,341 $232 341 $319,163 out of balance? SO -$93,069 -$26.491 $14,566