Design a KPI dashboard for the business owner

Manager · Time: 40 min · Spreadsheets

Tools that fit this task

• ChatGPT $20/mo
• Copilot in Excel $20/mo
• Gemini in Google Sheets $14/mo
• Rows AI Free
• Salesforce Einstein Free

Prompt

Role: you are a financial controller designing a KPI dashboard for a business owner.
Context: the business {{what it does}}, the owner's monthly decisions: {{five decisions}}. Data: {{divisions or products}} by month with columns plan and actual for revenue, costs, margin, {{additional data}}. Materiality threshold {{percent}}%.
Task: design the dashboard: 1) five to seven metrics with the calculation formula and the decision each serves, 2) a visualization for each (card, trend, variance table) with the reason for the choice, 3) green and red alert thresholds as numbers, 4) a five-sentence comment for the owner on the current data with [CAUSE, confirm] where the cause needs confirmation.
Format: a table metric | formula | decision | visualization | thresholds. Then the layout on one screen described in words. No long dashes.

Steps

1. Write down five decisions the owner makes every month and the numbers needed for them
2. Prepare a data slice: divisions or products by month with plan and actual revenue, costs and margin
3. Give the assistant the decisions and the data structure with a prompt for a dashboard design with thresholds
4. Get a list of metrics, visualizations and alert thresholds with the reasoning
5. Build the dashboard in Sheets or Excel from the description and connect it to live data with formulas
6. Show it to the owner, note which questions they ask most and add metrics for them

How to check the result

Every metric on the dashboard serves one of the owner's decisions, green and red thresholds are set as numbers, and the dashboard updates by formula without manual copying

Pitfalls

• The model proposes twenty metrics, most of which the owner does not need: keep five to seven
• Financial data in a personal account of a third-party service is a leak, so build the dashboard in the corporate workspace

Data that stays out of public AI tools

• Meeting recordings that discuss salaries, dismissals and deals: anonymize the transcript before uploading it to a public service
• Plans, prices and terms for specific clients: a trade secret, refer to the client as [Client]
• Ratings and feedback for specific people: personal data, into the prompt as [Employee 1]

Other tasks for managers

• Write meeting minutes from an audio recording
• Write a weekly report to your manager
• Write a business letter
• Build a quarterly results presentation
• Write AI usage guidelines for your team
• Frame feedback for an employee
• Summarize a long PDF
• Compare two versions of a contract
• Run a quick counterparty check before a deal
• Write a sales proposal
• Build a 12-week cash flow forecast
• Write a job ad and a job description
• Build a 30/60/90-day onboarding plan

AI tools for managers · Prompt: Design a KPI dashboard for the business owner