Financial Modeling
Build a Formula-Driven KPI Scorecard Panel (Polarity-Aware Attainment, Weights & RAG Status)
Combine higher-is-better growth metrics and lower-is-better payback/defect metrics into one normalized executive KPI scorecard with weighted attainment and Red/Amber/Green status.
Normalizing opposite metric polarities before weighting a scorecard
Executive KPI panels routinely mix higher-is-better metrics (ARR, Gross Margin %, Net Dollar Retention) with lower-is-better metrics (CAC Payback Months, Churn %, Incident MTTR). Dividing Actual / Target across every row would reward an inflated 15-month CAC payback against a 12-month target with 125% attainment!
Switching polarity via IF(Polarity = "Higher", Actual / Target, Target / Actual) places every KPI onto a common attainment scale where >= 1.00 is Green, 0.90 to < 1.00 is Amber, and < 0.90 is Red, allowing SUMPRODUCT(Weight, Attainment) to compute a single board-ready composite index.
| KPI Metric | Actual | Target | Polarity | Attainment | RAG Status |
|---|---|---|---|---|---|
| Net New ARR ($k) | 1,260 | 1,200 | Higher | 1.0500 | Green |
| Gross Margin % | 0.68 | 0.68 | Higher | 1.0000 | Green |
| Net Retention % | 1.08 | 1.125 | Higher | 0.9600 | Amber |
| CAC Payback (Mo) | 15 | 12 | Lower | 0.8000 | Red |
Polarity-Aware Attainment & Weighted Composite Score
Normalize each row for directionality, classify RAG status at 1.00 and 0.90 thresholds, and weight across the scorecard with SUMPRODUCT.
=ROUND(IF(E2 = "Higher", B2 / C2, C2 / B2), 4)Scaffolding KPI Panels & Charts with the Arte Sheets AI Assistant
In Arte Sheets, you can use text or voice commands in the AI Assistant—or attach a monthly .csv/.xlsx export—to add or rename a KPI Summary sheet, populate multi-step attainment formulas, insert supported Column or Line charts over the summary range, and preview role-based sharing invitations according to your account permissions and plan.
=ROUND(SUMPRODUCT(D2:D5, F2:F5), 4)Try the example
Use the original values or change them to test your understanding.