KPI Target and Variance Calculation Review
Check targets, actuals and variance logic.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Review variance calculations. Table of KPIs with targets, actuals and the formulas used: [KPI_TABLE]. Recalculate variance as actual minus target and percentage as variance divided by target, taking care with metrics where lower is better. Check colour rules, thresholds and divisions by zero. Return a table of recalculated values, errors found and a corrected rule set for red, amber and green. Add a worked recalculation with invented figures: target 100, actual 92, variance minus 8, minus 8 percent; for a cost KPI where lower is better, target 100, actual 92, variance minus 8 is good; the sheet shows it red. Fix the rule. Division by zero error where target is blank; add an IF test. Provide a threshold table with editable cells. Add a short note on documentation: each KPI row states whether higher or lower is better, the source and the owner, and the thresholds that drive the colours, so no reader has to guess how colours were chosen. Provide a formula in plain words for percentage variance that avoids dividing by zero.
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 KPIs. Sales: target 100, actual 92, variance minus 8, minus 8 percent; higher is better, so red. Cost per order: target 100, actual 92, variance minus 8, minus 8 percent; lower is better, so it should be green, but the sheet shows red. Fix the rule. Blank target gives a division error; wrap in a test for zero or blank. Thresholds in editable cells: green within plus or minus 5 percent, amber 5 to 10, red beyond. Legend: show higher is better or lower is better beside each KPI so colours are never ambiguous.
Illustrative only, made-up data. Your results will differ and must be reviewed.