Financial ratios in Excel: profitability, liquidity and solvency

A financial ratio divides one number from the financial statements by another, so you can compare a business of any size on the same scale. In a spreadsheet, each ratio is a short formula that points at two or three cells. The skill is knowing which cells, and which one goes on top.

Why ratios matter

A net income of $140,000 tells you little on its own. Is that good for a business with $1 million of revenue? With $10 million? Ratios answer that by putting the figure in context. Lenders ask for them before they lend, owners track them from year to year, and analysts compare them across companies.

Ratios fall into three families, and this lesson has one exercise for each:

• Profitability ratios ask how much profit the business makes from its sales and its assets.
• Liquidity ratios ask whether the business can pay the bills that are due in the next year.
• Solvency ratios ask how heavily the business relies on debt, and how comfortably it covers its interest.

All the figures come from two statements: the income statement (revenue, expenses and net income for the year) and the balance sheet (assets, liabilities and equity at year end). In the exercises, both statements sit in columns A and B of one sheet, and you write the ratios in column E.

The ratios, as the exercises define them

Textbooks sometimes define the same ratio in slightly different ways. These are the definitions the exercises check, and each exercise states any choice you need to know.

Two notes. Return on assets here uses total assets at year end, not an average of the opening and closing balances. And earnings before interest and taxes (often shortened to EBIT) is not on the sheet as its own line, so you build it: net income + interest expense + income tax expense.

The sheet layout

Every exercise uses the same layout, which makes the formulas easy to read once you know it. Here it is with the numbers from the free exercise, Northside Print Shop's statements for the year ended December 31, 2025.

Rows 3 and 12 hold the headings "Income statement" and "Balance sheet". Column D lists the three measures you need in rows 4 to 6, and column E is where your answers go. Before you start, it is worth checking that the statements hang together: revenue less cost of goods sold is $473,000 of gross profit, and total assets equal total liabilities plus equity at $2,731,000.

Worked example 1: profitability (Northside Print Shop)

The lender wants gross margin, net profit margin and return on assets in E4:E6, entered as decimals. The cells are already formatted as percentages, so you divide and let the format do the rest.

Gross margin is $473,000 ÷ $1,029,000. For every dollar of sales, about 46 cents is left after paying for the goods sold. Net profit margin is $140,000 ÷ $1,029,000: after every expense, including interest and income tax, about 13.6 cents of each sales dollar is profit. Return on assets is $140,000 ÷ $2,731,000, so the business earned about 5.1 cents of net income for each dollar of assets it holds.

Don't multiply by 100. A cell formatted as a percentage already shows 0.4597 as 45.97%. If you type =B6/B4*100, the cell shows 4596.70%.

Worked example 2: liquidity (Juniper Landscaping)

The second exercise uses Juniper Landscaping, with total current assets of $294,000 in B16 (cash $62,000, accounts receivable $152,000, inventory $80,000) and current liabilities of $209,000 in B19.

The current ratio of 1.41 means Juniper has $1.41 of current assets for every dollar it owes in the next year. The quick ratio is stricter. It leaves out inventory, because inventory has to be sold before it turns into cash. Using only cash and receivables, $214,000 ÷ $209,000 gives 1.02. Notice the parentheses: without them, =B13+B14/B19 divides only the receivables and then adds cash. Working capital is not a ratio but a subtraction, so the answer is a dollar amount: $294,000 − $209,000 = $85,000.

Worked example 3: solvency, rounded (Juniper Landscaping)

The third exercise uses another Juniper Landscaping year: total liabilities $1,034,000 (B21), owner's equity $586,000 (B22), total assets $1,620,000 (B18), net income $393,000 (B10), interest expense $13,000 (B8) and income tax expense $105,000 (B9). This time the task asks you to round each result to two decimal places with a formula, not just with formatting.

The debt-to-equity ratio of 1.76 says creditors have put in $1.76 for every dollar of owner's equity. The debt ratio of 0.64 says about 64% of the assets are financed by liabilities. For times interest earned, earnings before interest and taxes are $393,000 + $13,000 + $105,000 = $511,000. Divided by $13,000 of interest, that is 39.31: the business earned its interest bill more than 39 times over.

Why round with a function? Formatting changes only what the cell displays. The stored value of =B21/B22 is still 1.764505…, and any later formula that uses that cell uses the full value. ROUND(value, 2) changes the stored value itself to 1.76. When a task says to round "with a formula", it wants the stored value rounded.

Common mistakes

These are the mistakes the three exercises recognize. Most of them come from putting the right numbers in the wrong places.

Dividing the wrong way round

Gross margin is gross profit divided by revenue, not revenue divided by gross profit. Flipped, Northside's answer would be =B4/B6, which shows 217.55%: a margin above 100% is a sign something is upside down. The same slip affects the current ratio. =B19/B16 gives Juniper 0.71 instead of 1.41, which would wrongly suggest it cannot cover its current liabilities. A quick sense check: the figure you are measuring goes on top, and the base you compare it with goes underneath.

Net profit margin on gross profit

Both margins use revenue as the base. Dividing net income by gross profit (=B10/B6) gives Northside 29.60% instead of 13.61%, which makes the business look more than twice as profitable as it is.

Leaving inventory in the quick ratio

If you divide total current assets by current liabilities, you have calculated the current ratio again. For Juniper that is 1.41, not 1.02. The whole point of the quick ratio is to leave inventory out, so add only cash and accounts receivable.

Not rounding inside the formula

In the solvency exercise, =B21/B22 formatted to two decimals looks like 1.76, but the cell holds 1.764505…, and the check reads the stored value. Wrap each calculation in ROUND(…,2).

Adding back only interest

Times interest earned uses earnings before interest and taxes. Adding back only interest, =ROUND((B10+B8)/B8,2), gives 31.23 instead of 39.31. Income tax expense was subtracted to reach net income, so it has to be added back too.

MO-220 practice: analyzing financial statements

This lesson covers the "Analyze financial statements" objective in the "Prepare and analyze financial statements" area of the MO-220 exam outline, which asks for profitability, liquidity and solvency ratios. Accounting Exercises is not affiliated with Microsoft or Certiport. As MO-220 practice, it helps to know that exam-style task instructions usually describe what a cell should show, such as "the ratio of current assets to current liabilities" or "round to two decimal places", rather than naming a function. The exercises here are written the same way, so you practice turning a description into a formula.

Ratios sit at the end of a longer process in Microsoft® Excel® workbooks. The statements they use come from a trial balance (trial balance in Excel), which itself depends on the adjustments being right (adjusting entries practice) and on the books being closed properly at year end (closing entries practice). Each exercise in this lesson gives you a new set of statements, and the free one below gives you new numbers each time.