SOLVE IT ON MS EXCEL or GOOGLE SPREADSHEET (please send the file) Make a Proforma Income Statement and ProForma Balance Sheet using the Financial Planning attached. Use 15% as the Sales Growth Rate of the 2013 base year, all other data remain the same. Ziegen, Inc. Income Statement for 2013 Sales Net Income Ziegen, Inc. Balance Sheet for 2013 Current assets Net fixed Total Accounts payable Accrued expenses Notes payable Current liabilities Long-term debt Total liabilities Common stock (par) Paid-in capital Retained earnings Common equity Total $10,000,000 $ 500.000 $ 2,000,000 Calculation % of 2013 Sales $.5m/$10m = Calculation $2m/$10m = $4m/$10 4,000,000 $ 6,000,000 $ 1,000,000 $1m/$10m= $1m/$10m= 1,000,000 500,000 $ 2,500,000 2,000,000 $ 4.500,000 100,000 200,000 1,200,000 $ 1,500,000 $ 6,000,000 5.0% % of 2013 Sales 20.0% 40.0% 10.0% 10.0% NA NA" NA NA" Ziegen, Inc. Pro Forma Income Statement for 2014 Sales growth rate = Sales Net Income Ziegen, Inc. Pro Forma Balance Sheet for 2014 Current assets Net fixed Calculation 20% $10m X (1+.20) = $12m x (.05) = Calculation .20 x 12m = 40 x 12m = Total Accounts payable Accrued expenses Notes payable Current liabilities Long-term debt Total liabilities Common stock (par) Paid-in capital Retained earnings Common equity Projected sources of financing Discretionary financing needs (Plug figure) Total financing needs = Total assets .10 x 12m = .10 x 12m = No change No change No change No change Calculation $12,000,000 $ 600,000 $ 2,400,000 $ 4,800,000 $ 7,200,000 $ 1,200,000 1,200,000 500,000 $ 2,900,000 $ 2,000,000 $ 4,900,000 100,000 200,000 1,500,000 1,800,000 $ 6,700,000 $ 500,000 $ 7,200,000 "Not applicable. These account balances do not vary with sales. Projected retained earnings for 2014 equals $1,500,000, which is equal to the 2013 level of retained earnings of $1,200,000 plus net income of $600,000 less common dividends equal to 50% of projected net income, or $300,000. Discretionary financing needs (DFN) for 2014 is a "plug figure that equals the difference in the firm's projected total financing requirements or total assets equal to $7.200.000 and projected sources of financing, which is $6,700,000. In this scenario DFN is $500,000.