How to calculate the current ratio and quick ratio in Excel

Step 1: A balance list with a class column

Copy the balances from your balance sheet at month end, one account per row, and add two columns: Class (current asset, current liability, long-term asset, long-term liability) and Kind (cash, receivable, inventory and so on). The tags are what let SUMIFS add the right rows.

"Current" means cash, or something that turns into cash or must be paid within the next 12 months. Split a loan in two: the part due in the next 12 months is a current liability ($9,600 here) and the rest is long-term ($38,400). Your loan statement or amortization schedule shows the split.

Equipment and the long-term part of the loan stay out of both ratios.

Step 2: The current ratio

Divide current assets by current liabilities. $120,900 / $48,500 = 2.49: the business has about $2.49 of short-term assets for every $1 it owes in the next year.

Working capital is the same comparison as a difference: =G5-I5 gives $72,400. The ratio is easier to compare between months and businesses of different sizes; the difference tells you how many dollars of cushion there are.

Step 3: The quick ratio (acid test)

Inventory and prepaid expenses count as current assets, but you can't pay a bill with them this week. The quick ratio keeps only the assets that are cash or close to it: cash, receivables and short-term investments you could sell quickly.

SUMIFS with an array constant adds two kinds at once: =SUM(SUMIFS(D2:D12,C2:C12,{"Cash","Receivable"})) gives $75,000. Divided by $48,500, that's 1.55.

The gap between the two ratios shows how much depends on stock. Here inventory is $42,300 of the $120,900, so the current ratio of 2.49 drops to 1.55 without it. The strictest version, the cash ratio, keeps only cash: $43,400 / $48,500 = 0.89.

A month-end trend

One date tells you little. Keep a small table with one row per month end: current assets, quick assets and current liabilities, then a ratio column for each. In F2:K5, July to September are typed in from earlier balance sheets and October's row uses the SUMIFS formulas above.

The current ratio went 2.73, 2.67, 2.37, 2.49; the quick ratio 1.62, 1.57, 1.30, 1.55. September was the low point for both, and the quick ratio recovered more (+0.25) than the current ratio (+0.12), so cash and receivables did the recovering, not stock.

In Excel 365, =G2:G5/I2:I5 spills all four current ratios at once.

What one big bill or loan does to the ratio

Paying a $15,000 supplier bill from cash lowers current assets and current liabilities by the same amount. Working capital stays at $72,400, but the ratio rises from 2.49 to 3.16, because both sides shrink and the smaller one shrinks more in percentage terms. A ratio can look better at month end just because bills were paid on the 30th.

Other moves go the other way. Buying $15,000 of stock on credit drops the current ratio to 2.14 and the quick ratio to 1.18. Borrowing $20,000 short term into the bank drops the current ratio to 2.06. And when the long-term part of a loan comes due within 12 months, it moves into current liabilities: with the whole $48,000 loan current, the ratio falls to 1.39 and the quick ratio to 0.86.

Test a move before you make it by changing the numbers in the formula: =(G5-15000)/(I5-15000).