Build it in the spreadsheet you already use. Put four to seven metrics on a Scorecard tab that shows, for the current month, the target, the actual, the variance, and a green, yellow, or red status. Keep the twelve-month history on a Trend tab, calculations on a Data tab, and each raw export on its own import tab. Start targets from your own recent history, and update the sheet the same way every month after the books close.
That is the whole design. Most of the work is in the decisions behind each cell: which metrics earn a row, what counts as on target, and how the numbers get in without someone retyping them.
What a Monthly Scorecard Is For
A scorecard answers one question each month: is the business on track, and if not, where? It is not your accounting system and it is not a report. It is a single view that someone can read in a minute and act on.
That purpose sets the rules:
- Four to seven metrics. My rule of thumb, depending on the business, is four to seven, and no vanity metrics. Each one has to move the needle: when it changes, someone makes a decision. How Many KPIs Should a Small Business Track? covers how to choose them.
- One screen. The Scorecard tab fits on a laptop screen without scrolling sideways. If it doesn’t, you have too many metrics or too many columns. The full history lives on another tab.
- Metrics in rows, in the same order on every tab. Row 6 is the same metric on the Scorecard and the Trend tab, which keeps formulas simple to copy.
- Every number has a comparison. A figure with no target beside it doesn’t tell anyone whether to act.
Use Google Sheets if your business runs on Google Workspace, and Excel if it runs on Microsoft 365. My view is that a small business should stay with the tools it already pays for as long as they do the job, and a monthly scorecard is well within what either can do.
Step 1: Choose the Rows
Group the metrics so the sheet reads in a consistent order. A practical split for most small businesses:
- Money (two or three rows): revenue, gross margin, operating cash flow.
- Operations or capacity (one to three rows): billable utilization for a service firm, first-pass yield for a shop, jobs completed for a trade business.
- Customers and pipeline (one or two rows): qualified pipeline value, on-time delivery, customer retention.
Pick from these, don’t take all of them. If you already track seven metrics and want an eighth, one of the seven should go. For service-business examples with formulas, see KPIs for a Small Professional Services Firm.
For each metric, write down four things before you build anything:
- Definition, including where the number comes from.
- Unit: dollars, percent, days, or count.
- Direction: whether higher or lower is better. Revenue is better high. Days sales outstanding is better low. The sheet needs to know the difference, or it will color a collections problem green.
- Type: a flow, a ratio, or a point-in-time figure. This decides how year-to-date is calculated in Step 3.
Step 2: Lay Out the Tabs
Build the workbook in layers, so the tab people read never touches raw data.
Scorecard tab. This is what people read. A header block at the top holds the company name, the reporting month, the date last updated, and who updated it. Put the reporting month in its own cell (say, B2), because the formulas will use it. Below that, each metric gets one row:
| Column | Contents |
|---|---|
| A | Metric name |
| B | Owner |
| C | Unit ($, %, days, count) |
| D | Direction (higher or lower is better) |
| E | This month’s target |
| F | This month’s actual |
| G | Variance |
| H | Status (green, yellow, red) |
| I | Year-to-date actual |
| J | Year-to-date target |
Ten columns fit on one screen. If a comparison with the same month last year matters in your business, add it after F, as long as the tab still fits.
Trend tab. The same metric rows, with January through December in columns C through N and the month names in row 5. This is where people look when they want to know whether this month is a blip or a trend. Below the metric rows, keep the components that ratios need, such as monthly gross profit and revenue, or billable and available hours.
Data tab. Calculations only. Each metric’s monthly value is calculated here from the import tabs, in a labeled block. Nobody pastes anything onto this tab.
Import tabs. One tab per source: accounting, CRM, time tracking. Each month, the owner clears the tab and pastes that source’s standard export into cell A1. Nothing is typed or calculated on an import tab, so a longer or shorter export can’t overwrite a formula. On the Data tab, refer to whole columns or use lookups by label, so the calculations still work when the export has more rows than last month.
Step 3: Add the Formulas
These formulas behave the same way in Google Sheets and Excel. The examples use row 6.
Pull this month’s actual. Rather than retyping, have column F on the Scorecard look up the month named in B2 from the Trend tab. The month in B2 has to be spelled exactly as it is in the Trend headers. With the month names in C5:N5:
=INDEX(Trend!C6:N6, 1, MATCH($B$2, Trend!$C$5:$N$5, 0))
The 1 tells INDEX to stay in the first (and only) row of the range, and MATCH supplies the column. Change B2 next month and every row updates.
Calculate the variance. For dollars and counts, use the percentage difference from target:
=(F6-E6)/E6
For metrics that are already percentages, such as gross margin or utilization, use the difference in points instead:
=F6-E6
The two give different impressions. A margin target of 42% against an actual of 38.5% is 3.5 points below target, which is also 8.3% below it. “3.5 points” is the figure most people understand when they read a margin. Pick one convention per metric, label it, and keep it.
Flip the sign for lower-is-better metrics. If D6 says “lower,” multiply the variance by −1 so that a negative number always means worse:
=IF(D6="lower", -1, 1) * (F6-E6)/E6
Handle zero targets. A percentage variance divides by the target, so a target of zero, such as zero overdue invoices or zero safety incidents, returns an error. Track those metrics in units instead: the variance is simply the count. Give that row its own status rule, for example =IF(F6="", "No data", IF(F6>0, "Red", "Green")), in place of the band formula in Step 5. Give “No data” a neutral style so a missing actual is never shown as Green.
Calculate year-to-date by metric type. Averaging twelve monthly figures is right for almost nothing. Use the type you wrote down in Step 1:
- For flows such as revenue, jobs completed, or operating cash flow, sum the months.
- For ratios such as gross margin, utilization, or on-time delivery, recompute them from the summed components. Year-to-date margin is total gross profit divided by total revenue; year-to-date utilization is total billable hours divided by total available hours.
- Point-in-time figures such as pipeline value, days sales outstanding, or cash balance: use the latest month-end value, or another method you define and write down.
Retention depends on how you define it, so write the year-to-date rule into its definition. The year-to-date target follows the same rule as the actual.
The ratio difference is real. Suppose January brings in $50,000 at a 40% margin ($20,000 gross profit) and February brings in $100,000 at 30% ($30,000). The average of the two percentages is 35%. The actual year-to-date margin is $50,000 ÷ $150,000, or 33.3%, because February’s larger month counts for more.
Step 4: Set Targets, Starting From Your Own History
A target picked because it sounds good gets ignored after the first miss. Start from what the business has actually done, then decide what it should do.
- Find a baseline. The trailing three-month average is a reasonable starting point, because recent months reflect your current staff, prices, and customers. If your business is seasonal, look at the same month last year as well.
- Turn the baseline into a target. Check it against your budget, your capacity, commitments you’ve already made, the season, and any improvement you’re planning. A baseline built from weak months will simply repeat them if you adopt it unchanged.
- Write down the reason for any difference between baseline and target, so the target can be explained later.
- Round to a number people can remember.
Here is an example, not a client case. A business had revenue of $78,400 in June, $81,200 in July, and $76,900 in August. The three-month baseline is about $78,800. September is usually similar to the summer months; nothing unusual is planned, and the budget agrees, so the target is set at $79,000. September comes in at $72,600, which is $6,400 short, or 8.1% below target.
Whether 8.1% is a problem depends on the band you set.
Step 5: Set Tolerance Bands
A tolerance band says how far a metric can move before anyone needs to act. Without one, every small dip gets discussed, and the meetings lose their point.
I set bands from the business’s own history. I look at how much each metric actually moved over the last six to twelve months. Movement inside that normal range I treat as noise. Movement outside it means something changed. One caution: several months drifting the same way inside the band is still a trend, which is why the Trend tab matters.
I also set them metric by metric, because metrics don’t behave the same way. A 5% miss on gross margin can be serious, while a 5% dip in inquiries may be an ordinary month.
If you don’t have enough history yet, you still need a placeholder. There is no standard band. As a rough starting point, not a hard rule, some businesses could begin with something like:
- Green: within 5% of target, or better.
- Yellow: 5% to 15% worse than target.
- Red: more than 15% worse than target.
Replace it with bands from your own data once you have a few months. On that sample starting point, the September revenue example above (8.1% below) is yellow. A days sales outstanding target of 45 days against an actual of 52 is 15.6% worse, so it’s red.
To fill the Status column, add a formula that returns a word:
=IF(G6>=-0.05, "Green", IF(G6>=-0.15, "Yellow", "Red"))
Then add three conditional formatting rules to column H: text is exactly “Green,” “Yellow,” or “Red.” Use the word as well as the color so the status still reads correctly in a printout or for anyone who has trouble telling the colors apart. Keep the rest of the sheet in neutral tones so the color means something when it appears.
For percentage metrics tracked in points, the formula compares against point thresholds instead, for example −1 and −3 points. Record the band for each metric in a note on the Data tab so nobody has to guess later why margin turned red at a smaller miss than revenue did.
A red status should trigger a short note from the metric’s owner: what happened, what it means, what we’ll do, and who does it by when. How to Get Your Team to Actually Use Your Reports covers that note and the meeting around it.
Step 6: Update It the Same Way Every Month
A scorecard that takes an afternoon to update gets skipped within a few months. If the update drags, the problem is usually how the data gets in, not the scorecard.
Here is an example routine for a business that finishes its month-end bookkeeping within the first few business days. Adjust the days to your own close.
- Books closed. Wait until the month’s transactions are reconciled. Numbers pulled earlier will change, and the scorecard will disagree with your accounting.
- Exports in. Each metric owner clears their import tab and pastes the same standard export into cell A1. Save each export with the same filters and date range every month, and write those settings down.
- Record the month. Copy the Data tab’s results into that month’s column on the Trend tab using paste values, so next month’s imports don’t change them.
- Review. Change the reporting month in B2. Check the status column, then check that two or three headline figures match your accounting system before anyone else reads the sheet.
- Lock the month. Protect the finished month’s column on the Trend tab so nobody edits it by accident. In Google Sheets, use Protect sheets and ranges. In Excel, lock the cells and turn on Protect Sheet.
When numbers do disagree, don’t adjust the scorecard to make them match. Find the cause. Revenue that differs between your payment processor, your bank, and your books usually has an ordinary explanation: payout timing, processor fees, or a different cutoff date. The Business Data You Already Have explains why those sources rarely line up.
Monthly Scorecard Checklist
- Four to seven metrics, each with a written definition, unit, direction, and type.
- One owner per metric.
- A Scorecard tab that fits on one screen, with a Trend tab, a Data tab, and one import tab per source behind it.
- This month’s target, actual, variance, and status in adjacent columns.
- Year-to-date figures calculated by type: flows summed, ratios recomputed from components, point-in-time figures taken at month end.
- Targets that start from a baseline and account for budget, capacity, and season, with adjustments written down.
- A tolerance band for each metric, and a separate rule for any metric with a zero target.
- A written monthly routine, with each finished month recorded as values and locked.
Frequently Asked Questions
Should the scorecard be weekly or monthly?
Both can exist. Weekly suits operating numbers people can act on quickly. Monthly suits numbers that only settle after the books close, such as margin and cash flow. Build the monthly scorecard first if your accounting only closes monthly.
Can I download a template instead?
Yes, if it’s close to what you need. Check that it lets you set your own four to seven metrics, keeps raw data away from the results, and uses formulas you can follow. If you’d have to delete most of it, building the tabs yourself may be simpler. Either way, make sure you understand every formula before something breaks.
When should the scorecard move out of a spreadsheet?
When the spreadsheet becomes the hard part: refreshing the data takes more time than acting on it, different people need different access, you need a record of who changed what, the data volume slows the file down, or keeping it working has become a job of its own. Until then, a spreadsheet is easier to change as you learn which metrics matter.