We encourage the use of this type of dashboard tool because;
- It is difficult to visualize what’s happening in your business with just a jumble of numbers and
- you will be doing this analysis regularly so you should automate it as much as possible to make it a “good” Routine
To allow you to set it up in the spreadsheet of your choice, we just give the design that you can plug into your tool.
We will use two sheets:
Sheet 1:
Data Input & Calculation: Where the raw numbers and calculated ratios are housed.Sheet 2:
Dashboard Summary: A clean, graphical view showing only the trends and stoplight colors.
Sheet 1: Data Input & Calculation Structure
Start by setting up the sheet with a column for each quarterly period (Q1, Q2, Q3, Q4, etc.) and rows for the key inputs from your financial statements.
| A | B (Q1, 2024) | C (Q2, 2024) | D (Q3, 2024) |
| FINANCIAL STATEMENT INPUTS | |||
| 1. Net Sales | 100,000 | 110,000 | 95,000 |
| 2. Gross Profit | 60,000 | 68,000 | 50,000 |
| 3. Operating Expenses (Overhead) | 30,000 | 33,000 | 25,000 |
| 4. Net Income | 15,000 | 17,000 | 12,000 |
| 5. Current Assets | 50,000 | 55,000 | 45,000 |
| 6. Current Liabilities | 25,000 | 26,000 | 23,000 |
| 7. Total Liabilities | 150,000 | 160,000 | 145,000 |
| 8. Total Equity | 100,000 | 105,000 | 102,000 |
| 9. Average Accounts Receivable (A/R) | 20,000 | 22,000 | 18,000 |
| CALCULATED RATIOS (Use cell references for formulas) | |||
| 10. Gross Profit Margin | (B2/B1) | (C2/C1) | (D2/D1) |
| 11. Overhead Cost Ratio | (B3/B1) | (C3/C1) | (D3/D1) |
| 12. Net Profit Margin | (B4/B1) | (C4/C1) | (D4/D1) |
| 13. Current Ratio | (B5/B6) | (C5/C6) | (D5/D6) |
| 14. DSO | 365 / (B1/B9) | 365 / (C1/C9) | 365 / (D1/D9) |
| 15. D/E Ratio | (B7/B8) | (C7/C8) | (D7/D8) |
Step 2: Set Up Conditional Formatting (Stoplight Trend)
The “Red/Green” coloring should compare the current period’s ratio result to the previous period’s ratio result.
Apply the following Conditional Formatting Rules to the ratio cells (Rows 10-15) across all periods. The general logic is:
| Ratio | Good Trend (Green) | Bad Trend (Red) | Example: Applying to Cell D10 (Q3 GPM) |
| 1. GPM (More is Good) | Value in current cell is Greater Than the value in the previous column. | Value in current cell is Less Than the value in the previous column. | Green Rule: =$D10 > $C10 |
| 2. OCR (Less is Good) | Value in current cell is Less Than the value in the previous column. | Value in current cell is Greater Than the value in the previous column. | Green Rule: =$D11 < $C11 |
| 3. NPM (More is Good) | Value in current cell is Greater Than the value in the previous column. | Value in current cell is Less Than the value in the previous column. | Green Rule: =$D12 > $C12 |
| 4. Current Ratio (More is Good) | Value in current cell is Greater Than the value in the previous column. | Value in current cell is Less Than the value in the previous column. | Green Rule: =$D13 > $C13 |
| 5. DSO (Less is Good) | Value in current cell is Less Than the value in the previous column. | Value in current cell is Greater Than the value in the previous column. | Green Rule: =$D14 < $C14 |
| 6. D/E Ratio (Less is Good) | Value in current cell is Less Than the value in the previous column. | Value in current cell is Greater Than the value in the previous column. | Green Rule: =$D15 < $C15 |
Step 3: Create the Dashboard Sheet (Dashboard Summary)
Create a new sheet called Dashboard Summary. This sheet will contain the trend graphs.
For each of the six ratios, insert a simple line chart that pulls its data from the corresponding row in the Data Input & Calculation sheet.
Example: Gross Profit Margin Chart
Select the GPM row data (e.g., A10: D10) on the
Data Input & Calculationsheet.Go to Insert > Chart.
Choose a Line Chart or a Column Chart.
Set the X-axis (horizontal axis) to use the time headers (B1: D1).
Title the chart clearly: “Ratio 1: Gross Profit Margin Trend”.
Repeat this process for all six ratios.
Final Dashboard Output
The final Dashboard Summary sheet will feature six small, clean line graphs, allowing the SBO to instantly see:
The Trend: Is the line going up or down over time?
The Status: The corresponding colored cell (Green/Red) on the
Data Input & Calculationsheet confirms the direction of the latest change.
This setup ensures the SBO can input their data once and immediately review their performance without manual calculations.
Turn this into a Routine
We want to make this a regular Routine for your business in order to ensure your financials stay healthy.
This sort of Routine lends itself to a simple spreadsheet so you can;
- calculate the results for the selected period
- see at a glance if the results are improving or worsening over time
- take any remedial action required and monitor if that works
If you are familiar with spreadsheets, you can automatically insert so-called ‘Traffic lights” by colour coding Bad (red), Watch (yellow) and OK (green) to instantly catch your attention if results change. See our template spreadsheet that does all this
This article was provided by Scott Williams AO FAIDC. It describes the way best practice in many management areas has been brought together in the 12Faces GamePlan System. The Goal is to provide an easy to use and repeatable “flywheel” to improve small business owners’ outcomes. Scott is the Founder of NFP small business support services 12Faces and MentorSME and has a Philanthropic Foundation supporting students in education. Scott’s business Petals Network was National Small Business of the Year and 4 times in the Top 100 Fastest Growing Australian Businesses LinkedIn