Insights · Business Strategy and Management
A dashboard starts with questions, not charts
A useful management dashboard begins with a decision, not a chart style. Define which questions the manager needs to answer and which measures can explain changes in revenue, margins, cash or commercial activity. A visually attractive panel with inconsistent definitions can mislead more than a simple, carefully maintained spreadsheet.
A dashboard should help the owner or manager detect deviations and decide what to change. It is not a competition to display the greatest number of charts. Select a few questions about sales, margins, cash and customer behaviour, then connect each answer to a reliable data source.
Step 1. Define the questions the dashboard should answer
Imagine a fictional office-supplies distributor that needs to know whether sales are rising, whether margins are holding, if actual revenue meets the budget, and whether cash is improving. Turn those questions into a small set of metrics with owners, definitions and a monthly reporting period before designing the workbook.
Start with decisions and objectives: are margins deteriorating, are customers paying later, is conversion weakening or has operating capacity become constrained? Each question calls for a different indicator. Explain the unit, denominator, time horizon and owner before designing the visual presentation.
Step 2. Create a consistent source data sheet
The sample Excel file contains a 'SME Data' sheet with one row for each of twelve months. It holds revenue, direct costs, fixed costs, opportunities, new and existing customers, targets and other inputs. Keep units, period labels and VAT treatment consistent and distinguish actual values from budgets and forecasts.
Keep raw inputs separate from calculations and dashboard outputs. A workbook is easier to audit when a user can see which figures are original observations and which are formulas. Document the source and update frequency, and use consistent dates, currencies and VAT treatment throughout the file.
Step 3. Keep data and calculations separate
Use a separate 'Indicators' sheet to compute measures rather than embedding formulas in manually entered data. Gross margin in euros is revenue minus the direct cost defined in the model; the percentage uses a consistent revenue denominator. Other measures can include budget attainment, conversion and a simplified operating result. Document formulas so they can be inspected.
The accompanying illustrative workbook contains an approximate acquisition-cost indicator that arbitrarily assigns 15% of fixed costs to acquisition. This is not a true customer acquisition cost. For professional decisions, replace the assumption with attributable marketing and sales expenditure divided by the number of new customers acquired under a documented definition.
Step 4. Build the dashboard view
The 'Dashboard' sheet presents selected annual figures, including sales, a weighted gross margin rate, a simplified operating result and net cash flow. Below them, show trends and comparisons that answer real management questions. The dashboard should make unusual results visible without concealing the underlying detail.
Check formulas against a manually worked example. When a percentage appears, verify the numerator, denominator and whether any zero or blank values distort the result. Protect calculated cells, but leave input data easy to replace and label assumptions explicitly.
Step 5. Create charts that actually help
A line chart is useful for monthly sales movements, while columns may compare budget with actual sales. Choose chart types according to the question and avoid decorative complexity. Percentage indicators should have clear denominators; colour alone should never be the only signal that a target has been missed.
Prefer visualisations that show actual results against targets and changes over time. A trend line without units, a favourable colour without a defined threshold or a chart with a truncated axis can mislead readers. Include brief commentary on likely causes and decisions required.
Step 6. Set targets and identify variances
Compare monthly actuals with targets and examine the size of the deviation. A revenue achievement ratio is actual revenue divided by the sales target; it is most useful alongside contribution margin, unpaid invoices and operating capacity. Establish who investigates deviations and how decisions are recorded.
Use filters only where they aid analysis: period, product, customer segment or service line. Avoid fragmenting small samples so much that a fluctuation appears decisive. Allow users to return to the underlying numbers when a chart raises a question.
Step 7. Validate formulas before relying on conclusions
Change a source value to confirm that the dependent cells and charts update. Test zero revenue, unusual cost movements, negative results and invalid denominators. Check that customer losses cannot accidentally create nonsensical retention results. Errors in a formula can make apparently precise dashboards unreliable.
Review the dashboard with finance, sales and operations so that one department's metric does not hide another's costs. Revenue growth can coincide with weaker contribution margin or tighter cash. Encourage teams to discuss exceptions and hypotheses, rather than defend isolated targets.
Step 8. Establish a management review routine
Agree who enters information, when each data set is updated, who validates it and who acts on the findings. A monthly review can record the deviation, suspected cause, decision, owner and date of the next check. Without this routine the dashboard becomes an archive, not a management tool.
Record the action, owner and review date for each relevant deviation. A dashboard without a decision routine is merely a report. A short monthly conversation can distinguish temporary timing effects from structural problems worth escalating.
Download and adapt the template for your SME
The free 'Management Dashboard for SMEs' workbook contains a 'Start' guide, 'SME Data', 'Indicators' and 'Dashboard' sheets. Its twelve-month data are fictional and should be replaced before use in a real company. The workbook itself is currently provided in Portuguese.
When a metric is difficult to maintain, verify whether its decision value justifies the effort. Start with an Excel workbook if that is sufficient. Automation is helpful only after definitions and input quality are reliable.
Free Excel template: Management Dashboard for SMEs
Download the Excel example to explore formula-driven indicators and a monthly dashboard. Replace the sample data, review the calculations and adapt the chosen metrics to your own business objectives rather than relying on the fictional numbers.
The downloadable demonstration includes twelve months of fictitious data, automatic calculations and a management reading panel. Enter your own figures in the “Dados PME” worksheet and recheck formulas and assumptions. Its figures are educational examples, not evidence of actual customers or performance.
Explore management indicators in greater depth
Consult Guide 8 — What Are KPIs For? and explore the management consulting services. Guide 8 — What Are KPIs For? management consulting
Sources and recommended reading
Related articles
Management Metrics: Where Should You Start?