PLEASE ANSWER IN EXCEL FORMAT WITH FORMULAS INCLUDED.

Your CFO sends you, the Director of Finance, a memo stating that the company must maintain the following ratios in order to remain competitive in capital markets and avoid violating bond covenants. Calculate the projected income statement and balance sheet that would be necessary achieve these ratios. 111 Hint: you cannot calculate each row in order. Begin by calculating net income based on the required dividend ratios. If you are having trouble solving for other rows, I recommend writing out the formulas for each ratio. Then, if you are trying to solve for A/R, for example, you can see which ratios include A/R in them. Dividend payout Market price Dividend yield Shares outstanding Return on equity Long-term debt/equity Current ratio Acid-test ratio 45.00% $15.00 5.20% 18,000 13.70% 27.30% 551.00% 407.30% Profit margin Gross margin Return on assets Inventory turnover Operating profit margin Days sales outstanding Days payables outstanding Tax rate 5.10% 27.60% 9.40% 733.30% 8.70% 92.5 28.7 34.00% Your Company Target Income Statement For the Year Ended 12/31/20 Sales Cost of sales Gross profit SG&A Operating profit Interest Earnings before taxes Taxes Net income Cash Accounts receivable Inventory Total current assets PP& E, net Your Company Targeted Balance Sheet 12/31/2020 Accounts payable Other current liabilities Total current liabilities Long-term debt Total liabilities Owners' equity Total liabilities and owners' equity Total assets Your CFO sends you, the Director of Finance, a memo stating that the company must maintain the following ratios in order to remain competitive in capital markets and avoid violating bond covenants. Calculate the projected income statement and balance sheet that would be necessary achieve these ratios. 111 Hint: you cannot calculate each row in order. Begin by calculating net income based on the required dividend ratios. If you are having trouble solving for other rows, I recommend writing out the formulas for each ratio. Then, if you are trying to solve for A/R, for example, you can see which ratios include A/R in them. Dividend payout Market price Dividend yield Shares outstanding Return on equity Long-term debt/equity Current ratio Acid-test ratio 45.00% $15.00 5.20% 18,000 13.70% 27.30% 551.00% 407.30% Profit margin Gross margin Return on assets Inventory turnover Operating profit margin Days sales outstanding Days payables outstanding Tax rate 5.10% 27.60% 9.40% 733.30% 8.70% 92.5 28.7 34.00% Your Company Target Income Statement For the Year Ended 12/31/20 Sales Cost of sales Gross profit SG&A Operating profit Interest Earnings before taxes Taxes Net income Cash Accounts receivable Inventory Total current assets PP& E, net Your Company Targeted Balance Sheet 12/31/2020 Accounts payable Other current liabilities Total current liabilities Long-term debt Total liabilities Owners' equity Total liabilities and owners' equity Total assets