Blog · Finance metrics and formulas · Finance and FP&A teams
Copy a reusable workbook layout for comparing profitability and liquidity across periods. The template includes statement inputs, exact formulas, denominator checks, and a worked US distributor example.
A financial ratio analysis Excel template needs consistent statement inputs, formulas that reference those inputs, and visible checks for missing or unsuitable denominators. The copyable layout below compares profitability and liquidity across two periods. It also keeps the source amounts visible so the reviewer can explain a movement without reconstructing the workbook.
Create two sheets: Inputs and Ratios. Put source figures and their statement references on Inputs. Put calculated measures, prior-period comparisons, and review comments on Ratios. Keep the same entity, currency, accounting basis, and period length across both columns.
Income statements cover a period; balance sheets show a position at a date. The SEC's guide to financial statements makes that distinction explicitly. Label the income columns with their full fiscal-year dates and the balance columns with their year-end dates, even when they appear in one input table.
This layout concentrates on workbook structure and controls. The owner page on profitability and liquidity ratios explains the broader formula set and interpretation. Use it when choosing additional ratios rather than expanding the template with measures nobody reviews.
The example is a US distributor with fiscal years ending December 31, 2025 and December 31, 2026. All amounts are USD. Enter expense amounts as positive values, because the formulas subtract them. Gross and operating profit are calculated; net income is taken from the signed-off statement.
Place the line labels in column A, the earlier period in B, and the later period in C. The row numbers below are the actual worksheet rows.
| Row | A: Input or subtotal | B: FY2025 (USD) | C: FY2026 (USD) | Entry rule or formula in B |
|---|---|---|---|---|
| 2 | Net revenue | 12,000,000 | 13,200,000 | Statement input |
| 3 | Cost of goods sold | 7,800,000 | 8,844,000 | Statement input |
| 4 | Gross profit | 4,200,000 | 4,356,000 | =B2-B3 |
| 5 | Operating expenses | 2,700,000 | 2,904,000 | Statement input |
| 6 | Operating profit | 1,500,000 | 1,452,000 | =B4-B5 |
| 7 | Net income | 900,000 | 871,200 | Statement input |
| 8 | Cash and equivalents | 600,000 | 500,000 | Statement input |
| 9 | Net trade receivables | 1,800,000 | 2,200,000 | Statement input |
| 10 | Inventory | 1,600,000 | 2,000,000 | Statement input |
| 11 | Prepaid expenses | 200,000 | 250,000 | Statement input |
| 12 | Current assets | 4,200,000 | 4,950,000 | =SUM(B8:B11) |
| 13 | Current liabilities | 2,800,000 | 3,300,000 | Statement input |
Copy the subtotal formulas into column C. Add a source-reference column beside the table: statement name, reporting date, and line or account mapping. These current assets consist only of the four listed categories; add any other current-asset lines explicitly when adapting the workbook.
Do not enter total current assets and then add their components again. Either calculate the total from mutually exclusive components, as here, or import the statement total and use the components solely for reconciliation.
Use the following layout on Ratios. Each formula shown is for column B; copy it to C for the later period. Format margins as percentages with one decimal place and liquidity ratios to two decimal places. Calculations retain full precision.
| Row | A: Measure | B: FY2025 | C: FY2026 | Formula in B before review guards |
|---|---|---|---|---|
| 2 | Gross margin | 35.0% | 33.0% | =Inputs!B4/Inputs!B2 |
| 3 | Operating margin | 12.5% | 11.0% | =Inputs!B6/Inputs!B2 |
| 4 | Net margin | 7.5% | 6.6% | =Inputs!B7/Inputs!B2 |
| 5 | Current ratio | 1.50 | 1.50 | =Inputs!B12/Inputs!B13 |
| 6 | Quick ratio | 0.86 | 0.82 | =SUM(Inputs!B8:B9)/Inputs!B13 |
The quick ratio here includes cash and net trade receivables. The example has no short-term investments; if yours does, add a separate eligible input and document its inclusion. See gross profit margin for the revenue and cost-of-goods-sold boundary used in the first measure.
In column D, show margin movement in percentage points:
=(C2-B2)*100
Copy through D4 and label the output unit percentage points. Gross margin falls 2.0 percentage points, operating margin falls 1.5, and net margin falls 0.9. For liquidity rows, use =C5-B5 and =C6-B6 as changes in ratio units, retaining the unrounded source ratios.
Revenue growth is =Inputs!C2/Inputs!B2-1, or 10.0%. The unchanged current ratio sits beside a weaker quick ratio because the current-asset mix changed. Those are observations from this example; the amounts alone do not establish why customers paid more slowly or inventory increased.
Replace the unguarded gross-margin formula with this version:
=IF(OR(COUNT(Inputs!B4,Inputs!B2)<>2,Inputs!B2<=0),"CHECK",Inputs!B4/Inputs!B2)
Apply the same pattern to each numerator and denominator. A negative profit numerator remains valid when revenue is positive. A zero or negative denominator requires review. Avoid converting every error to zero: that would make an absent input look like a measured result.
Also check completeness before accepting calculated subtotals. For the current-asset components:
=IF(COUNT(Inputs!B8:B11)=4,"OK","CHECK")
An actual zero is numeric and passes; an empty cell or text amount fails. Microsoft's COUNT documentation confirms that referenced ranges count numeric cells and ignore empty cells and text. The general input-completeness check is =COUNT(B2:B3,B5,B7:B11,B13)=9 on Inputs. Run it for both periods.
Reconcile calculated gross profit, operating profit, and current assets to the signed-off statement subtotals. Their differences must be zero at the statement's precision. The current-asset totals also prove working capital: 4,200,000 minus 2,800,000 is $1,400,000; 4,950,000 minus 3,300,000 is $1,650,000.
Covirage is not affiliated with Microsoft.
Bring your statement exports in a file or Google Sheet; Covirage maps the columns, and its tools compute and check the period comparisons. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for Excel analysis for the file-based workflow.
Start with the ratios needed for the decision: gross, operating, and net margin for profitability; current and quick ratios for liquidity. Add measures only when the workbook contains their required inputs and the finance team has agreed on the calculation.
Yes. Give every column an explicit reporting period and use income-statement amounts covering that period. Liquidity uses balances at the corresponding reporting date. Compare equivalent months or explain seasonality before treating a change as a persistent trend.
Show a review flag rather than a zero margin. A margin cannot be calculated from a zero revenue denominator. Keep zero profit with positive revenue as a valid zero margin, and retain negative profit as a valid negative result.
No. Current assets may include inventory or receivables that cannot become cash before obligations fall due. Review their quality, collection timing, and payment dates. The quick ratio provides another view, but neither ratio replaces a dated cash forecast.