Most small business owners in Brisbane know they’re busy, but they don’t always know if they’re actually making progress. A simple revenue dashboard cuts through the noise of your bank statement and tells you exactly where your money is coming from and where it’s going.
Building this doesn't require expensive software or a degree in data science. We’re going to use Google Sheets because it's free, collaborative, and honestly, more flexible than most 'all-in-one' reporting tools that cost $50 a month.
Why this matters for your business
Without a visual dashboard, you're making decisions based on 'gut feel'. A dashboard helps you spot when a specific service is declining before it becomes a crisis, or identify which months you need to ramp up your marketing to beat the seasonal Brisbane slump.---
Prerequisites: What you’ll need
- A Google Account (for Google Sheets).
- Access to your sales data (from Xero, MYOB, Square, or your POS).
- About 45 minutes of quiet time.
- A cup of coffee (highly recommended).
---
Step 1: Set up your 'Raw Data' Tab
First things first: never mix your 'maths' with your 'visuals'. We need a clean place to dump your data.- Open a new Google Sheet.
- Rename the first tab to
Raw_Data. - Create the following headers in Row 1:
Date,Client/Project,Service Category,Gross Revenue,Cost of Goods (COGS), andNet Revenue.
Step 2: Input your data (The boring but vital bit)
Export your sales for the last 3-6 months from your accounting software (like Xero or MYOB).- Screenshot description: You should see a long list of transactions. Ensure the 'Date' column is formatted as a date (Format > Number > Date).
- The COGS column: This is where most people get stuck. If you’re a service provider, your COGS might just be external contractor fees or software specific to that job. If you’re a cafe in New Farm, it’s your milk, beans, and packaging.
- Net Revenue: In cell F2, type
=D2-E2. This gives you your actual profit before overheads.
Step 3: Create your 'Calculations' Tab
We don't want to build charts directly off the raw data because it’s too 'noisy'. We need to summarise it.- Create a second tab and call it
Calculations. - In Column A, list the last 6 months (e.g., Jan 2024, Feb 2024, etc.).
- In Column B, we’ll use the
SUMIFSformula to pull data from your first tab.
=SUMIFS(Raw_Data!D:D, Raw_Data!A:A, ">=1/1/2024", Raw_Data!A:A, "<=31/1/2024")
Don't worry if this looks like gibberish! It basically tells Google: "Add up everything in the Revenue column if the date falls within January." You’ll need to adjust the dates for each month. Yes, this step is annoyingly fiddly, but once it's done, you won't have to touch it again.
Step 4: Building the 'Dashboard' View
Now for the fun part where you actually see the results of your hard work.- Create a third tab and call it
DASHBOARD. - Go to View > Show and uncheck Gridlines. This makes it look like a professional app rather than a spreadsheet.
- Create a Revenue Trend Chart: Highlight your months and total revenue in the Calculations tab, then click Insert > Chart. Choose a 'Smooth Line Chart'.
- Create a Category Breakdown: Highlight your Service Categories and their totals, then Insert a Pie Chart or Donut Chart.
Step 5: Adding the 'Quick Stats' Scorecard
At the top of your Dashboard tab, we want three big numbers that give you the 'vibe' of the business instantly.- Total Revenue (Year to Date): Use
=SUM(Calculations!B:B). - Average Monthly Profit: Use
=AVERAGE(Calculations!C:C). - Profit Margin %: Use
=(Total Profit / Total Revenue).
Format these with big, bold fonts. I like to use a dark navy or 'Local Marketing Group blue' for the headers to make it look sharp.
Step 6: The Monthly Maintenance Routine
A dashboard is only useful if the data is fresh. Set a calendar invite for the 3rd of every month (once your bank feeds have cleared in Xero).- Export last month’s sales.
- Paste them at the bottom of the
Raw_Datatab. - Watch your charts automatically update.
Common Mistake: Including GST in your revenue. Always track 'Net of GST' figures. If you include GST, you’re looking at money that belongs to the ATO, not you, which gives you a false sense of security!
---
Troubleshooting Common Issues
"My charts are blank!" Check your dates. Google Sheets is picky. Ensure your dates in theRaw_Data tab are actual dates and not just text. A quick way to tell? Dates usually align to the right of a cell; text aligns to the left.
"The SUMIFS formula is giving me an error."
This is usually a missing comma or a misplaced quotation mark. Double-check that your tab name matches exactly (if you named it 'Raw Data' with a space, the formula needs to be 'Raw Data'!D:D).
"I have too many categories and the pie chart is messy."
Group smaller items into an 'Other' category. If a service makes up less than 5% of your revenue, it doesn't need its own slice of the pie.
---
Next Steps
Once you've mastered your revenue dashboard, the next level is tracking where those leads came from (Marketing Attribution). Knowing you made $10k is great; knowing that $8k of it came from Google Maps is even better.
If you find the technical setup a bit overwhelming or you want a more advanced dashboard that connects directly to your Google Ads and website traffic, we can help.
Contact the team at Local Marketing Group and we can chat about setting up a custom 'Marketing & Revenue' command centre for your Brisbane business. You've got this! The first time is the hardest—after that, it's just data entry.