1. Calculating ratios using Excel Ratio analysis using Excel Quantitative analysis in finance heavily relies on the data from ratio analysis. The process of working with multiple financial statements can be daunting but Excel can help you compute financial ratios-which you can then use to evaluate the financial health of the company. The most recent data from the annual balance sheets of Liukin Holdings Inc. are given in the balance sheet and the income statement given below: A F 1 Balance Sheet Income Statement 2 Assets Liabilities & Equity Net Sales 4,000 Current assets: Current liabilities: 1,2681 Operating costs except depreciation and amortization Cash $2,583 Accounts payable Depreciation and amortization 160 Accounts receivable 945 Accruals Total Operating Costs 1,428 Inventories: 2,772 Notes payable Operating Income (or EBIT) 2,572 Total current assets 6,300 Less: Interest 270 Total current liabilities Earnings before taxes (EBT) 2,302 Long-term bonds Less: Taxes (40%) 921 Net fixed assets: Total debt Net Income. 1,381 3 4 5 6 7 8 9 10 11 12 $0 0 4,725 4,725 5,775 10,500 IU Long-Lenoorus 3,115 11 Net fixed assets: Total debt 10,500 12 13 Net plant and 7,700 Common equity equipment 14 Common stock 2,275 15 Retained earnings 1,225 16 Total common equity 3,500 17 Total assets 14,000 Total liabilities and 14,000 equity Using the data you have and the formula you will be using in ratio analysis, solve for the ratios using Excel commands: B 1 Ratio Formula Excel Solution 2 3 Current ratio Current Assets / Current Liabilities 4 Inventory turnover ratio Sales / Inventories 5 Fixed assets turnover ratio Sales/ Net Fixed Assets 6 Total assets turnover ratio Sales/ Total Assets 7 Debt ratio Total Debt / Total Assets 8 Times interest ratio EBIT/Interest Expense 9 LESS TOAS (70) Net Income 921 1,381