Financial ratio analysis dashboard (three years)

What does this template do?

This workbook analyzes three fiscal years of an income statement, a balance sheet and a cash flow summary for a fictional mid-size distributor. Enter the figures on the Statements tab in USD thousands. The file checks that assets equal liabilities plus equity in each year, and that the change in cash matches the operating cash flow, capital expenditure, dividends and debt.

The Ratios tab calculates twenty ratios by year in four groups: liquidity, profitability, efficiency and solvency. Return on assets, return on equity and the equity multiplier use average balances for 2025 and 2026. The 2024 figures use year-end balances, because the file has no 2023 balance sheet. Each ratio shows its change from 2025 to 2026, a trend arrow with a better or worse reading, a sparkline across the three years, a target and a status.

The DuPont tab splits return on equity into net margin, asset turnover and equity multiplier, and checks that the three factors multiply back to the same return. The Dashboard shows four headline figures, margins by year, liquidity against target and a scorecard by group. The figures and targets are example data.

What’s inside

• Twenty ratios for three years across liquidity, profitability, efficiency and solvency
• Trend arrows read against each ratio's direction, so a fall in days outstanding shows as better
• Average balances for return on assets and equity, with the 2024 treatment stated
• DuPont breakdown with a check that the three factors multiply to return on equity
• Balance and cash checks on the statements, and a scorecard of targets met by group

Which tabs does the workbook have?

What formulas does this template use?

This template holds 381 formulas in 748 cells across 5 tabs, so 51% of its cells calculate. They use 16 distinct functions; the longest formula is 333 characters and 105 of them read from another tab.

The 12 most used of 16 functions. Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

1. On the Statements tab, enter revenue, costs, the balance sheet and cash flows for 2024, 2025 and 2026 in the blue cells.
2. Enter the days in the year, and check that the balance check and the cash check read OK for each year.
3. On the Ratios tab, set each ratio's target and whether a higher or a lower value is better.
4. Read the headline figures and the scorecard on the Dashboard.
5. Use the DuPont tab to see which factor drives the return on equity.

What is it good for?

• Preparing a three-year review of a distributor's liquidity and margins for a management meeting
• Comparing efficiency ratios against internal targets
• Explaining what drives return on equity with the DuPont breakdown
• Checking a set of statements for balance and cash tie-out errors before ratio analysis

Questions about this sheet