Data and Dashboards for SMEs

A practical course for owners, managers and the people who build reports in small and mid-sized businesses. You will learn to choose and define KPIs, map where data lives and improve data quality, structure spreadsheets properly, use lookups, pivot tables and Power Query, and avoid spreadsheet errors. You will then design dashboards and choose the right charts, understand business intelligence tools and data models, automate refresh, run data-driven meetings, apply simple analysis and set light governance for security and privacy.

Course structure

• Module 1: Foundations (4 lessons). Why data matters, choosing KPIs, where your data lives and data quality basics.
• Module 2: Spreadsheets Done Right (4 lessons). Tidy data tables, lookups and pivot tables, automated cleaning with Power Query, and avoiding spreadsheet errors.
• Module 3: Dashboards (4 lessons). Designing dashboards, choosing charts, business intelligence tools and data models, and automating refresh.
• Module 4: Using Data Well (4 lessons). Data-driven meetings, simple analysis, governance, security and privacy, and a 90-day plan.

Interactive labs

• Useful KPI, vanity metric, or unclear?: Sort twelve proposed measures: is each a useful KPI, a vanity metric that won't drive decisions, or a measure that needs a clearer definition first? Then check your answers.
• Which reports to automate first?: Place seven reports on a grid of decision value against manual effort each month, to see which to automate first and which to drop.
• Your data maturity check: Rate your business on eight areas of data and reporting. The radar chart shows strengths and gaps, with a next step for each. Retake it after your 90-day plan.

How it works

• Work through 16 short lessons at your own pace. Each has key takeaways, an exercise for your own workplace and a quick knowledge check.
• Try the three interactive labs as you reach them in the course.
• Pass the 20-question final exam (70% or higher, unlimited retakes) to unlock your Chmltech certificate of completion.
• Free, with no sign-up. Your progress is saved in this browser.

Most SMEs have more data than they use. Orders, invoices, inventory, timesheets, quotes and customer records sit in accounting software, ERP systems, CRMs and spreadsheets. Turned into a few clear measures, that data shows what is working, what is slipping and where to act.

Common symptoms of data that isn't working

• Monthly reports take days to assemble by copying and pasting.
• Different people quote different numbers for the same thing.
• Problems such as falling margins or late deliveries are found weeks after they start.
• Decisions rely on gut feel because the numbers are hard to get.

What good decisions need

Start small

You don't need a data warehouse or a data scientist to start. A handful of well-defined KPIs, clean spreadsheets or a basic business intelligence tool, and a monthly review meeting deliver most of the value. This course walks through each step, from choosing KPIs to building and using dashboards.

A key performance indicator (KPI) is a measure that tells you whether you are achieving an important goal. The word "key" matters: a business with 60 KPIs effectively has none.

Start from goals

Begin with your business goals, then ask: what would tell us we are on track? A goal to grow profitably might lead to KPIs for revenue, gross margin percentage and new customers. A goal to improve service might lead to on-time delivery and complaint rate.

A KPI tree

Break a top-level result into the drivers that cause it. Profit depends on revenue and costs; revenue depends on number of customers, order frequency and average order value; and so on. A KPI tree shows managers which driver they control and how it links to the result.

Leading and lagging

Lagging indicators, such as monthly revenue and profit, show results. Leading indicators, such as quotes sent, pipeline value, on-time delivery or machine downtime, predict them. Include both.

Define every KPI

Avoid vanity metrics

Some numbers look good but don't drive decisions, such as total website visits or social media followers. Ask of each KPI: if this changed, would we do something differently? If not, drop it. The lab after this lesson asks you to spot useful KPIs.

Before building reports, map where the data comes from. In most SMEs it is spread across several systems.

Map your data

For each KPI, record which system holds the data, who maintains it, how it can be extracted (built-in report, export to file, direct connection) and how often it changes. This map shows where the reporting effort goes and which sources need connecting.

One version of the truth

When the same information is kept in two places, such as customer lists in the CRM and in accounting, they drift apart. Decide which system is the system of record for each type of data: customers, products, prices, inventory. Other systems should take their data from it, ideally through an integration, rather than being updated by hand.

Spreadsheets as a warning sign

Spreadsheets are flexible and useful, but critical business data kept only in personal spreadsheets is fragile: it can be lost, overwritten or understood only by one person. When a spreadsheet becomes essential to running the business, consider moving that process into a system. The lab after this lesson asks you to prioritize which KPIs to automate first.

A dashboard built on poor data gives confident wrong answers. Data quality is the unglamorous foundation of good reporting.

Dimensions of data quality

Master data

Master data is the core reference data used across transactions: customers, suppliers, products, prices, units of measure. Errors here spread into every report. Assign an owner to each type of master data, set rules for creating and changing records (for example, check for duplicates before adding a customer) and review it regularly.

Fix at the source

Cleaning data every month in a spreadsheet treats the symptom. Fix the cause: make key fields mandatory in the system, use drop-down lists instead of free text, train staff on why the data matters and give feedback when errors occur.

Measure it

Track a few quality measures, such as the share of orders with a promised date or duplicate customer records, and show them alongside the KPIs they affect. Data quality improves when someone owns it and it is visible.

Most spreadsheet pain comes from how the data is laid out. Analysis tools work best with data arranged as a simple table, often called tidy data.

Rules for a good data table

• One row per record: one order line, one invoice, one shipment.
• One column per field: date, customer, product, quantity, price.
• One header row with clear, unique names.
• No merged cells, blank rows or subtotals inside the data.
• One type of data per column: dates as dates, numbers as numbers.
• Raw data separate from reports: keep data on one sheet and summaries on others.

Wide versus long

A common layout puts months across columns: Customer, Jan, Feb, Mar and so on. It looks like a report but is hard to analyze. The tidy version has columns Customer, Month and Sales, with one row per customer per month. It is longer but much easier to filter, total, chart and refresh.

Why it matters

Tidy tables can be summarized instantly with pivot tables, loaded into business intelligence tools and refreshed by adding new rows. Report-style layouts must be rebuilt by hand each month, which is slow and error-prone.

Use Excel tables

In Excel, format a data range as a table (Insert, Table). Tables expand automatically as rows are added, keep formulas consistent and make references readable, which reduces errors in formulas and pivot tables.

A few spreadsheet features do most of the work in SME reporting.

Lookups

Lookups bring data from one table into another using a shared key, such as a product code. In current versions of Excel, XLOOKUP is the simplest: =XLOOKUP(A2, Products[Code], Products[Category], "Not found") returns the category for the product code in A2. Older versions use VLOOKUP or INDEX/MATCH. Lookups let you add customer type, product category or region to transaction data for analysis.

Pivot tables

A pivot table summarizes a data table in seconds: total sales by month and region, average order value by customer type, count of late shipments by carrier. Drag fields into rows, columns, values and filters. Change the view without writing formulas. When the data table grows, refresh the pivot table.

Other useful features

Let AI help

AI assistants can write and explain formulas: describe what you need in plain language and test the result on rows where you know the answer. The Prompting and AI Productivity course covers this.

Monthly reports often start with the same chore: export files from several systems, delete header rows, fix dates, remove duplicates, combine sheets and add lookups. Doing this by hand every month wastes hours and invites mistakes.

Power Query

Excel and Power BI include Power Query, a tool that records the steps you take to clean and combine data, then repeats them with one click when new data arrives. Typical steps:

• Load an export file or a whole folder of monthly files.
• Remove extra rows and columns, rename headers.
• Change data types: text to dates, text to numbers.
• Split or merge columns, trim spaces, standardize text.
• Remove duplicates and filter out unwanted records.
• Merge with other tables, such as adding product categories.
• Append files with the same layout, such as twelve monthly exports.

Example

A distributor exports sales from its ERP each month into a folder. A Power Query connected to the folder combines all files, fixes dates, adds product category and customer region from lookup tables and loads one tidy table. The monthly report now takes a minute to refresh instead of half a day.

Good practice

• Keep source files in a consistent folder and format.
• Name query steps clearly so others can follow them.
• Check row counts and totals after each refresh against the source system.
• Document what each query does and who maintains it.

Spreadsheet errors are common and can be costly: a wrong formula in a pricing sheet or a broken link in a forecast can go unnoticed for months. A few habits prevent most of them.

Common errors

Good habits

• Separate inputs, calculations and outputs, with inputs clearly marked.
• Use Excel tables and named ranges so formulas adjust to new data.
• Add check totals: for example, total by region should equal the grand total; show a warning if not.
• Keep one shared master copy in a shared drive or cloud folder with version history, instead of emailing copies.
• Protect formula cells in sheets others fill in.
• Have someone else review important models before decisions are made on them.

Know when to move on

When a spreadsheet is used by many people at once, holds critical data or needs an audit trail, it may be time for a proper system or a business intelligence tool. Spreadsheets are excellent for analysis and prototypes; they are risky as the backbone of operations.

A dashboard is a one-screen view of the measures a person or team needs to make decisions. Good design is about the audience and their questions, not about fitting in as many charts as possible.

Start with the audience and questions

• Who will use it: the owner, a sales manager, a shift supervisor?
• What decisions do they make, and how often?
• What three to five questions should the dashboard answer at a glance? For example: Are we on track for the month? Which customers are slipping? Where are late orders building up?

Layout principles

• Most important at top left, where eyes go first: headline KPIs with targets.
• Trends below: how the KPIs are moving over time.
• Detail last: breakdowns and tables for investigation.
• Compare with something: target, last year or last month. A number alone means little.
• Consistent colours: use colour to highlight exceptions, not to decorate. Avoid relying on red and green alone, since some people cannot tell them apart.
• Fewer, clearer visuals: five to eight is usually plenty.

Different dashboards for different roles

The owner needs a monthly overview across sales, margin, cash and operations. A sales manager needs pipeline and customer detail weekly. A supervisor needs today's output and issues. One dashboard trying to serve everyone usually serves no one well.

Test it

Show a draft to the users and ask them to answer their key questions in under a minute. Watch where they get stuck, and simplify.

The right chart makes the message obvious; the wrong one hides it. Choose the chart by what you want to show.

Clarity rules

• Start bar and column axes at zero, or the differences look larger than they are.
• Label clearly: title that states the message, axis units, data source and date.
• Remove clutter: heavy gridlines, borders, legends you can replace with direct labels.
• Highlight what matters: one colour for the item of interest, grey for the rest.

Write the message as the title

Instead of "Sales by region", write "West region sales fell 12% while others grew". A title that states the takeaway helps busy readers and forces you to know what the chart says.

When spreadsheets become slow or hard to maintain, a business intelligence (BI) tool is the next step. Common options for SMEs include Microsoft Power BI, Google Looker Studio, Tableau, Zoho Analytics and the reporting tools built into many ERP and accounting systems.

What BI tools add

• Connections to many data sources, refreshed automatically.
• Interactive dashboards with filters and drill-down.
• Sharing through a browser or app, with access control.
• Handling of larger data volumes than spreadsheets comfortably manage.

Data model basics

BI tools work best with a simple data model, often called a star schema:

Facts connect to dimensions through keys such as product code and date. This structure keeps calculations correct and dashboards fast. A date table with fiscal periods, weeks and months is almost always worth adding.

Choosing a tool

Consider what you already use (Microsoft 365 or Google Workspace), the data sources you need to connect, licence costs per user, who will build and maintain the reports, and whether your ERP's built-in reporting already covers your needs. Start with one dashboard and grow from there.

A dashboard that someone must update by hand each week tends to fall behind. Automating the data flow keeps it current and frees time for analysis.

Levels of automation

Practical tips

• Check which connectors your systems and BI tool support; many cloud applications offer ready-made connectors.
• Use a service account with read-only access for data connections, not a personal login.
• Schedule refreshes to match decisions: daily for operations, weekly or monthly for management.
• Show the "last refreshed" time on the dashboard so users know how current it is.
• Set up alerts when a refresh fails, and name who fixes it.

Start where the pain is

Automate the reports that take the most manual time or matter most for decisions first. Many SMEs find that automating three or four core reports covers most of their needs.

Dashboards create value only when they change decisions. A short, regular meeting built around the dashboard turns data into action.

A simple agenda (30 to 60 minutes)

1. Review KPIs against target quickly: on track, watch or off track.
2. Discuss exceptions only: spend time on what is off track or changing, not on what is fine.
3. Ask why: use the dashboard's detail to find causes, such as which customers, products or shifts.
4. Agree actions with owners and dates.
5. Review last meeting's actions and whether they worked.

Good habits

• Everyone looks at the same dashboard, on screen or printed.
• Data arrives before the meeting, not during it.
• Questions about data accuracy are logged and fixed, not argued over in the meeting.
• Look at trends, not single points: one bad week may be noise; four in a row is a pattern.

Avoid common traps

• Reporting without acting: reading numbers aloud and moving on.
• Blame: people hide or massage numbers if KPIs are used to punish.
• Too many measures: attention spreads thin and nothing changes.

The Business Basics for Owners course covers the wider management rhythm of weekly, monthly and quarterly reviews.

You don't need advanced statistics to learn a lot from business data. A handful of simple techniques answer most management questions.

Segment before you conclude

Averages hide important differences. Overall on-time delivery of 92% might be 98% for local customers and 75% for one region served by a particular carrier. Breaking data down by customer, product, region, channel or time usually reveals where the real issue is.

Correlation is not causation

Two measures moving together does not mean one causes the other. Sales and overtime may both rise in busy months. Before acting, look for the mechanism and, where possible, test a change and measure the result.

Small numbers

Percentages based on few cases swing wildly. Three late orders out of ten is 30%; next week it could be 0%. Show counts alongside percentages, and judge small groups over longer periods.

As data becomes more central, a light set of rules keeps it trustworthy, secure and compliant.

Governance in plain language

• Owners: each key data type and each KPI has a named owner.
• Definitions: a shared list of KPI definitions, so everyone means the same thing.
• Change control: changes to definitions, calculations or source systems are agreed and recorded.
• Quality checks: regular checks and visible quality measures.

Access and security

• Give people access to the data and dashboards they need, not everything. Salary or customer-specific data may need restricting.
• Use work accounts with multi-factor authentication for BI tools and shared drives.
• Be careful when exporting data to personal devices or sharing dashboards by public link.
• Remove access when people leave.

Privacy

Customer and employee personal information is protected by privacy law, including PIPEDA. Collect only what you need, use it for the purposes people were told about, protect it and keep it no longer than necessary. For many dashboards, aggregated or anonymized data is enough; avoid showing personal details when totals will do.

AI and data

When using AI tools to analyze data, follow your organization's rules about which tools may handle which data. The Prompting and AI Productivity and Cybersecurity courses cover these practices.

Bring the course together in a 90-day plan that produces one trusted, automated dashboard and the habit of using it.

Signs of success

• The monthly report refreshes in minutes instead of days.
• Everyone quotes the same numbers.
• Meetings end with actions based on the data.
• Problems are spotted weeks earlier than before.

Where to go next

Business Basics for Owners helps choose financial KPIs; OEE and Inventory Management give detailed operational measures; and Digital Automation for SMEs helps plan system integrations that feed dashboards automatically.

Use the self-assessment lab after this lesson to see your data strengths and gaps, and retake it after 90 days.