Dashboard Metric Definition and Source Audit
Audit dashboard metric definitions and sources, so every number can be traced, explained and trusted by the reader.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Audit a spreadsheet dashboard. Description of each metric with formula and source range: [METRIC_DEFINITIONS]. Audience and decisions: [AUDIENCE_AND_DECISIONS]. For each metric check the definition is written down, the formula matches the definition, the source data is complete for the period, filters are visible, and totals reconcile to the underlying data. Look for hard-coded numbers, double counting, mixed periods and percentages averaged incorrectly. Return a table with Metric, Definition, Check and Result, corrected formulas in plain text, and a reconciliation test with three numbers calculated by hand. Show the arithmetic. Add a worked reconciliation with invented numbers. Metric: monthly revenue; formula sums column D; definition says revenue excludes refunds, but column D includes them; dashboard shows 52,400; revenue without refunds is 52,400 minus 1,900 refunds = 50,500; the difference is 3.6 percent of the reported figure. Metric: conversion rate; formula averages the monthly percentages instead of total conversions divided by total visits; monthly rates 4 percent on 1,000 visits and 2 percent on 9,000 visits average to 3 percent, but the true rate is (40 + 180) divided by 10,000, which is 2.2 percent. Metric: active customers; counts rows, so repeat purchasers appear twice; use a distinct count. Provide the corrected formulas in plain text. Add three hand-calculated spot checks to run each month, a metric dictionary tab with definition, formula, source and owner for each metric, and a change log. Suggest locking formula cells and labelling inputs. Add a rule that no number appears on the dashboard without a source range. Remind me that I am checking descriptions that I supplied and should verify in the workbook itself. Add a short template for the metric dictionary: name, definition, formula, source, refresh time, owner, known limits and last reviewed date, and ask each owner to confirm quarterly. Close with a one-page summary for the report's readers stating which numbers were corrected and by how much, with the date of the audit.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.
See an example result (fictional data)
Invented checks. Monthly revenue: definition excludes refunds; formula includes them. Reported 52,400; refunds 1,900; correct 50,500; difference 1,900 / 52,400 = 3.6 percent. Conversion rate: formula averages monthly rates (4 percent on 1,000 visits and 2 percent on 9,000 visits give 3 percent), but the true rate = (40 + 180) / 10,000 = 2.2 percent. Active customers: counts rows, so repeat buyers appear twice; use a distinct count. Spot checks each month: three numbers recalculated by hand. Owner confirms the metric dictionary every quarter; a change log records each correction with its date.
Illustrative only, made-up data. Your results will differ and must be reviewed.