SmartArt Formulas Review Edit Font Data Alignment abc FRI Number Cambria 12 Wrap Text General Paste Clear BI Merge 00 ES Conditional Formatting A106 Proforma Balance Sheet D G 4 5 2 3 Suppose, you were hired by Fleury, Inc., to assist the company with its financial planning and to evaluate the company's performance. Your supervisor provides yo with the most recent income statement and balance sheet of GEM, Inc (provided in the template). The capacity utilization rate in 2014 was 97%. 6 Sales for 2015 are projected to grow by 20 percent. The dividend payout rate and interest rate will remain constant in the new year. Costs, other expenses, current 7 assets, fixed assets, and accounts payable increase spontaneously with sales. Regardless of the external financing need, the long-term debt will increase by 2% and 8 common equity will decrease by 2% in the forecasted year. If the firm is going to operate at full capacity (100%) what is the external financing needed (EFN) to 9 support the 20 percent sales growth? Prepare Pro-forma balance sheet for the company for 2 scenarios (see details below). 10 11 12 Input 13 FLEURY, INC. 14 2014 Income Statement Other Inputs 15 Sales 891,600 Sales Growth 20.00% 16 Costs 693,600 Tax rate 35.00% 17 Other expenses 18,240 Changes in Common stock -2% 18 EBIT 179,760 Changes in Long Term Debt 2% 19 Interest expense 13,400 Capacity Utilization in 2014 97% 20 Taxable income 166.360 21 Taxes 58,226 22 Net income 108,134 23 24 Dividends 35,684 25 Add to retained earnings 72,450 26 27 FLEURY, Inc. 28 Balance Sheet as of December 2014 29 Assets Liabilities & Equity 30 Current Assets Current Liabilities 31 Cash 24,280 Accounts Payable 65,200 32 Accounts rec 37,070 Notes Payable 16,320 33 Inventory 83,400 TotalCL 81.520 24 TotalCA 144750 Instructions Question 1 Question 2 Question Question 4 + Normal View Ready Fons Number Full Cambria Alignment abc 12 A. A- Wrap Text General Clear Merge % 00 Paste A106 Conditional Formatting Proforma Balance Sheet B D E Assets 26 27 28 29 30 Current Assets 31 Cash 32 Accounts rec 33 Inventory 34 Total CA 35 36 37 38 Fixed assets 39 Net PP&E 40 FLEURY, Inc. Balance Sheet as of December 2014 Liabilities & Equity Current Liabilities 24,280 Accounts Payable 37,070 Notes Payable 83,400 TotalCL 144,750 Long-term debt 65,200 16,320 81,520 155,000 396,500 Shareholder Equity Common stock Retained earnings Total Equity 130,000 174,730 304,730 42 Total Assets 43 541,250 Total LAE 541.250 45 Calculation & Output 46 FLEURY, INC. 47 Proforma Income Statement 48 Sales 49 Costs 50 Other expenses 51 EBIT 52 Interest expense 53 Taxable income 54 Taxes 55 Net income 56 Dividends 57 Add. To RE 58 Dividend payout ratio Interest Rate Fixed Asset Calculation Full capacity sales Fixed assets/sales at full capacity KLEIDVIN na rin urinn aslan U Wrap Text General waste Clear U Merge % 00 A106 Condi Forma fx Proforma Balance Sheet 8 D F 3 9 5 FLEURY, Inc. Proforma Balance Sheet (External Financing Calculation) Assets Liabilities & Equity Current Assets Current Liabilities Cash Accounts Payable Accounts rec. Notes Payable Inventory Total CL 6 Total CA 7 Long-term debt 8 Fixed assets 9 Net PP&E Shareholder Equity "0 Common stock "1 Retained earnings 2 Total Equity 3 24 Total Assets Total L&E 75 76 77 Debt and Interest Expense 78 EFN 79 Increase (Decrease) in Long-Term Debt 80 Increase (Decrease) in Notes Payable SCENARIO 1: In this scenario the remaining balance between actual borrowed debt and EFN will be 81 82 FLEURY, Inc. 83 Proforma Balance Sheet 84 Assets Liabilities & Equity 85 Current Assets Current Liabilities 86 Cash Accounts Payable 87 Accounts rec. Notes Payable 88 Inventory TotalCL 89 Total CA 90 Long-term debt 91 Fixed assets Instructions Question 1 Question 2 Question 3 Question 4 Home Tables Charts SmartArt Formulas Data Review Edit Font Alignment Number Fill Cambria 12 abc Wrap Text General A A A Paste Clear B I U 00 Merge % ) 0.0 .00 fx Proforma Balance Sheet B Proforma Balance Sheet D F Assets Liabilities & Equity A106 33 34 B5 Current Assets 86 Cash 87 Accounts rec. 88 Inventory 89 Total CA Current Liabilities Accounts Payable Notes Payable Total CL 90 Long-term debt 91 Fixed assets 92 Net PP&E 93 94 95 96 97 Total Assets 98 99 100 101 Shareholder Equity Common stock Retained earnings Total Equity Total L&E Proforma Balance Sheet 102 SCENARIO 2: How the pro-forma balance sheet will look like if the company decides to increase or decrease the CASH instead of Notes Payable? 103 HINT: assume everything else is the same, you only need to change CASH item instead of NOTES PAYABLE and make sure TOTAL ASSETS = TOTAL LIAI FLEURY, Inc. Assets Liabilities & Equity Current Liabilities Accounts Payable Notes Payable TotalCL Long-term debt Shareholder Equity Common stock Instructions Question 1 Question 2 Question 3 Question 4 + Ready 104 105 106 107 108 Current Assets 109 Cash 110 Accounts rec 111 Inventory 112 Total CA 113 114 Fixed assets 115 Net PP&E 116 Normal View Sumo RO A