Can this be done on Excel with formulas please.
15.00x 3. Cristal Clear, Inc., a manufacturer of water purification systems, has accidentally erased parts of its 2017 and 2016 financial statements. Help the company fill in the missing data using your knowledge of financial ratios. Cristal Clear, Inc. Ratios 2017 2016 Liquidity Ratios Current Ratio 2.35x Ratio Quick Ratio Ratio 0.80x Efficiency Ratios Inventory Turnover Ratio Ratio 3.50x A/R Turnover Ratio 14.00x Ratio Average Collection Period Ratio 40.00 days Fixed Asset Turnover Ratio Total Asset Turnover Ratio Ratio Leverage Ratios Total Debt Ratio 60.00% Ratio Long-Term Debt Ratio Ratio Ratio LTD to Total Capitalization Ratio Ratio Debt to Equity Ratio Ratio Long-Term Debt to Equity Ratio Ratio Coverage Ratios Times Interest Earned Ratio 4.00x Cash Coverage Ratio 3.00x Ratio Profitability Ratios Gross Profit Margin Ratio Ratio Operating Profit Margin Ratio Ratio Net Profit Margin Ratio 3.00% Return on Total Assets 4.20% Ratio Return on Equity Ratio 12.00% Return on Common Equity Ratio Ratio 2016 115,000 Ratio Cristal Clear, Inc. Cristal Clear, Inc. Income Statement Balance Sheet For the Year Ended Dec. 31, 2017 As of Dec. 31, 2017 2017 Assets 2017 2016 Sales Ratio Ratio Cash and Equivalents 46,000 50,000 Cost of Goods Ratio 2,466,000 Accounts Receivable Ratio Ratio Gross Profit Formula Formula Inventory Formula Ratio G&A Expenses Formula 207,000 Total Current Assets Ratio Formula Other Expenses Formula Plant & Equipment Formula 435,000 Depreciation Ratio Ratio Accumulated Depreciation 146,000 Formula EBIT 173,000 Ratio Net Fixed Assets Formula Formula Interest Expense Formula 54,000 Total Assets 1,490,000 EBT Formula Formula Liabilities and Owners' Equity Taxes Formula Formula Accounts Payable Formula 131,000 Net Income 59,500 Formula Short-term Notes Payable 201,000 Formula Other Current Liabilities 121,000 117,000 Total Current Liabilities 480,000 Ratio Long-term Debt Formula Formula Total Liabilities Ratio Formula Common Stock Formula 408,000 Retained Earnings 199,000 Formula Total Shareholder's Equity Formula Ratio Total Liab, and OE Formula Formula a) Using the ratios provided, recreate the financial statements as shown using formulas. b) Complete the financial statements using formulas that refer to existing data to fill in the remaining cells. Assume tax rates for both years at 40%, depreciation for 2016 is negative $20,000, and, inventory turnover ratio for 2016 to be 3.88900415x. 15.00x 3. Cristal Clear, Inc., a manufacturer of water purification systems, has accidentally erased parts of its 2017 and 2016 financial statements. Help the company fill in the missing data using your knowledge of financial ratios. Cristal Clear, Inc. Ratios 2017 2016 Liquidity Ratios Current Ratio 2.35x Ratio Quick Ratio Ratio 0.80x Efficiency Ratios Inventory Turnover Ratio Ratio 3.50x A/R Turnover Ratio 14.00x Ratio Average Collection Period Ratio 40.00 days Fixed Asset Turnover Ratio Total Asset Turnover Ratio Ratio Leverage Ratios Total Debt Ratio 60.00% Ratio Long-Term Debt Ratio Ratio Ratio LTD to Total Capitalization Ratio Ratio Debt to Equity Ratio Ratio Long-Term Debt to Equity Ratio Ratio Coverage Ratios Times Interest Earned Ratio 4.00x Cash Coverage Ratio 3.00x Ratio Profitability Ratios Gross Profit Margin Ratio Ratio Operating Profit Margin Ratio Ratio Net Profit Margin Ratio 3.00% Return on Total Assets 4.20% Ratio Return on Equity Ratio 12.00% Return on Common Equity Ratio Ratio 2016 115,000 Ratio Cristal Clear, Inc. Cristal Clear, Inc. Income Statement Balance Sheet For the Year Ended Dec. 31, 2017 As of Dec. 31, 2017 2017 Assets 2017 2016 Sales Ratio Ratio Cash and Equivalents 46,000 50,000 Cost of Goods Ratio 2,466,000 Accounts Receivable Ratio Ratio Gross Profit Formula Formula Inventory Formula Ratio G&A Expenses Formula 207,000 Total Current Assets Ratio Formula Other Expenses Formula Plant & Equipment Formula 435,000 Depreciation Ratio Ratio Accumulated Depreciation 146,000 Formula EBIT 173,000 Ratio Net Fixed Assets Formula Formula Interest Expense Formula 54,000 Total Assets 1,490,000 EBT Formula Formula Liabilities and Owners' Equity Taxes Formula Formula Accounts Payable Formula 131,000 Net Income 59,500 Formula Short-term Notes Payable 201,000 Formula Other Current Liabilities 121,000 117,000 Total Current Liabilities 480,000 Ratio Long-term Debt Formula Formula Total Liabilities Ratio Formula Common Stock Formula 408,000 Retained Earnings 199,000 Formula Total Shareholder's Equity Formula Ratio Total Liab, and OE Formula Formula a) Using the ratios provided, recreate the financial statements as shown using formulas. b) Complete the financial statements using formulas that refer to existing data to fill in the remaining cells. Assume tax rates for both years at 40%, depreciation for 2016 is negative $20,000, and, inventory turnover ratio for 2016 to be 3.88900415x
