Why Build in Google Sheets?
Expensive BI tools like Tableau, Power BI, or Zoho Analytics are powerful — but they require setup time, technical skill, and ongoing subscription cost. For most SMEs, a well-built Google Sheets dashboard delivers 90% of the value at 10% of the cost.
The dashboard we'll build:
- Monthly P&L summary with trend charts
- Revenue breakdown by category
- Gross and net margin tracker
- Cash position and burn rate
- AR and AP aging summary
- KPI scorecards with color coding
Building time: 2–3 hours. Ongoing update time: 20–30 minutes/month.
Table of Contents
- Sheet 1: Data Input ("Raw Data" tab)
- Sheet 2: Monthly P&L ("P&L" tab)
- Sheet 3: Dashboard ("Dashboard" tab)
- Advanced Tips
- FAQ
- Conclusion
Sheet 1: Data Input ("Raw Data" tab)
All data goes in one place. Structure:
Revenue table: Month | Category | Amount Expense table: Month | Category | Amount AR aging summary: Month | Current | 1-30 days | 31-60 days | 61-90 days | 90+ days Cash position: Month | Opening Cash | Receipts | Payments | Closing Cash
This tab is the only one you update monthly. All other tabs pull from here.
Sheet 2: Monthly P&L ("P&L" tab)
Use SUMIFS to aggregate from Raw Data:
Monthly revenue: =SUMIFS(Revenue[Amount], Revenue[Month], A2, Revenue[Category], "*")
Category breakdown: =SUMIFS(Revenue[Amount], Revenue[Month], A2, Revenue[Category], "Product Sales")
Build 12 months across columns, line items down rows. Calculate:
- Gross Profit = Revenue - COGS
- Gross Margin % = Gross Profit / Revenue (format as %)
- EBITDA = Gross Profit - OpEx
- Net Profit = EBITDA - Depreciation - Interest - Tax
Sheet 3: Dashboard ("Dashboard" tab)
This is the one-page view you share with stakeholders.
Section 1: KPI Row (top of page) Current month values for: Revenue | Gross Margin | Net Margin | Cash Balance | DSO With: value, vs. prior month change, vs. target
Conditional formatting:
- Green: above target by >5%
- Yellow: within 5% of target
- Red: below target by >5%
Section 2: Revenue Chart (monthly bar chart) Data: P&L tab revenue row, last 12 months. Add a target line as a line series.
Section 3: Margin Trend (line chart) Gross margin % and net margin % over 12 months. Shows if margins are compressing.
Section 4: Cost Breakdown (donut chart) Current month expenses by category. Immediately shows if any category is unusually high.
Section 5: Cash Flow Waterfall (bar chart) Opening cash → add receipts → subtract payments → closing cash. Shows cash movement visually.
Section 6: AR Aging (stacked bar) AR aging buckets over 6 months. A growing 61-90 days bucket is visible immediately.
Advanced Tips
Auto-Importing from Zoho Books
Zoho Books allows CSV export of P&L and Balance Sheet. Use Google Sheets IMPORTDATA or a manual monthly import:
- Export P&L from Zoho as CSV
- Open in Google Sheets
- Copy the relevant rows into your Raw Data tab
- Dashboard updates automatically
Sharing with Stakeholders
- Share the Dashboard tab only: File → Share → specify recipients, "Viewer" access
- Create a shareable link to the Dashboard tab specifically
- Schedule a monthly email from Google Sheets: Extensions → AppSheet (or use a Google Apps Script)
Adding Commentary
Next to the KPI row, add a "Commentary" column for each month: "Revenue: $280,000 (down 8% vs. target; 2 enterprise deals slipped to Q3)"
This makes the dashboard self-explanatory without a call.
Conclusion
A functional financial dashboard doesn't require expensive tools or a data science team. The Google Sheets approach above gives you everything you need to make informed financial decisions — and takes an afternoon to build.
If you'd like our pre-built Google Sheets financial dashboard template, reach out at hello@financebridge.tech. We'll send it across with the formula structure already set up.