Can I get a step-by-step description of how to solve this. I am a little confused in certain areas.

ctions Data Types Els Sort & Filter C D 18 H The Loftis Company is preparing its pro forma financial statements for the next year using this model. The abbreviated fina below. 19 20 Sales growth Tax rate 20% 34% 21 22 23 24 25 26 27 28 29 Income Statement Sales $ 780,000.00 Costs 415,000.00 Depreciation 135,000.00 Interest 68,000.00 Taxable income $ 162,000.00 Taxes 55,080.00 Net income $ 106,920.00 30 31 32 33 $ 30,000.00 34 Dividends Additions to retained earnings 35 $ 76,920.00 36 37 Master It! Solution + Type here to search ta From Table/Range Refresh All Stocks Edit Links Geography aj Sort Filter T Reapp Advar Get & Transform Data Queries & Connections Data Types Sort & Filter 15 A B C E F G H $ 30,000.00 Dividends Additions to retained earnings $ 76,920.00 Assets 0 Current assets Net fixed assets Total assets 1 Balance Sheet Liabilities and Equity Total debt $ 880,000.00 Owners' equity 710,000.00 Total debt and equity $1,590,000.00 $ 240,000.00 1,350,000.00 $ 1,590,000.00 2 -3 14 15 46 a. Calculate each of the parameters necessary to construct the pro forma balance sheet. 47 48 b. Construct the pro forma balance sheet. What is the total debt necessary to balance the pro forma balance sheet? 49 50 c. In this financial planning model, show that it is possible to solve algebraically for the amount of new borrowing. 51 52 53 RWJ Excel Tip Master It! Solution Type here to search E F G H B 3 4 a. Calculate the following ratios for financial planning: 5 6 7 8 Cost percentage Depreciation percentage Interest rate Tax rate Payout ratio Fixed assets/Total assets Capital intensity ratio 0.53205128 0.09 0.08 0.84 0.28 9 1 1 1 1.73 10 11 12 13 14 b. 15 Construct the pro forma financial statements using the parameters you calculated. Your pro forma balance 16 17 18 19 Loftis Company Pro forma Income Statement Sales $ 936,000.00 Costs 498,045.60 Depreciation Interest Taxable income 20 21 22 23 Master It! Solution Select destination and press ENTER or choose Paste O Type here to search Data E From table Range All Edit Links Get & Transform Data Queries & Connections Data Types N13 fx A D E F G B Capital intensity ratio H 12 1.73 13 Construct the pro forma financial statements using the parameters you calculated. Your pro forma balance sheets 14 b. 15 16 17 18 19 20 Loftis Company Pro forma Income Statement Sales $ 936,000.00 Costs 498,045.60 Depreciation Interest Taxable income Taxes Net income 21 22 23 24 25 26 27 28 Dividends Additions to retained earnings 29 30 31 32 Loftis Company Master it! Solution Select destination and press ENTER or choose Paste Type here to search 498,041.00 Costs Depreciation Interest Taxable income Taxes Net income Dividends Additions to retained earnings Assets Loftis Company Pro forma Balance Sheet Liabilities and Equity $ 288,000.00 Total debt $ 1,080,591.00 1,619,280.00 Owners' equity 786,920.00 $ 1,907,280.00 Total debt and equity $ 1,867,511.00 Current assets Net fixed assets Total assets Master It! Solution + destination and press ENTER or choose Paste Type here to search o