1. Model a pro-forma statement using percent of sales approach. When complete, type in (or reference) the External Funds Needed (EFN) at the top of the sheet. 2. Use the data provided to calculate the percent of sales and model a new pro-forma balance sheet and income statement for 2020.'
Please show excel formulas
70% What is the External Funds Needed (EFN)? Utilization Rate Sales at Full Utilization New Projected Sales Capital Intensity Ratio (FA / Sales) Est Add'l Fixed Assets Needed: Retention Ratio = 0.6 60% 10.0% 2019 Income Statement Pct of Sales Sales Costs of Goods Sold SG&A Depreciation Pre-Tax Income Tax (25%) Net income 9,500,000 3,325,000 1,900,000 2,850,000 1,425,000 356,250 1,068,750 Sales Growth = Percent of Sales Method 2020 Pro Forma Income Statement Sales Costs of Goods Sold SG&A Depreciation Pre-Tax Income Tax (25%) Net income Retained Earnings 2019 Balance Sheet 2020 Balance Sheet Cash Inventory Current Assets 1,900,000 1,425,000 3,325,000 Cash Inventory Current Assets Fixed Assets 14,250,000 Fixed Assets Total Assets 17,575,000 Total Assets Current Liabilites 950,000 Current Liabilites LT Debt 12,000,000 LT Debt SH Equity Retained Earnings Total Equity 4,150,000 475,000 4,625,000 17,575,000 SH Equity Retained Earnings Total Equity Total Liabs + Equity Total Liabs + Equity gap / EFN 70% What is the External Funds Needed (EFN)? Utilization Rate Sales at Full Utilization New Projected Sales Capital Intensity Ratio (FA / Sales) Est Add'l Fixed Assets Needed: Retention Ratio = 0.6 60% 10.0% 2019 Income Statement Pct of Sales Sales Costs of Goods Sold SG&A Depreciation Pre-Tax Income Tax (25%) Net income 9,500,000 3,325,000 1,900,000 2,850,000 1,425,000 356,250 1,068,750 Sales Growth = Percent of Sales Method 2020 Pro Forma Income Statement Sales Costs of Goods Sold SG&A Depreciation Pre-Tax Income Tax (25%) Net income Retained Earnings 2019 Balance Sheet 2020 Balance Sheet Cash Inventory Current Assets 1,900,000 1,425,000 3,325,000 Cash Inventory Current Assets Fixed Assets 14,250,000 Fixed Assets Total Assets 17,575,000 Total Assets Current Liabilites 950,000 Current Liabilites LT Debt 12,000,000 LT Debt SH Equity Retained Earnings Total Equity 4,150,000 475,000 4,625,000 17,575,000 SH Equity Retained Earnings Total Equity Total Liabs + Equity Total Liabs + Equity gap / EFN
