Accounting Spreadsheet Dashboard
Create interactive financial reports and dynamic accounting dashboards with spreadsheet
Accounting Dashboard Overview
Spreadsheet Dashboard for Accounting in Odoo 19 allows you to create interactive financial reports with real-time data from the accounting system. Combine the power of spreadsheet with accounting data to create P&L dashboards, balance sheets, cash flow, and in-depth financial analysis.
Key points
• Create dynamic P&L (Profit & Loss) dashboard with detailed drill-down
• Interactive balance sheet with asset and liability analysis
• Real-time cash flow analysis
• Track accounts receivable and payable with aging analysis
• Comprehensive financial dashboard with key KPIs
• Spreadsheet formulas directly integrated with accounting data
• Interactive charts and pivot tables for multi-dimensional analysis
• Automatically updates when accounting data changes
Terminology Reference
Key terminology in Accounting Spreadsheet Dashboard:
| Vietnamese | English | Description |
|---|---|---|
| Bảng cân đối kế toán | Balance Sheet | Report of assets, liabilities and equity |
| Báo cáo lãi lỗ | P&L / Income Statement | Report of revenue, expenses and profit |
| Dòng tiền | Cash Flow | Inflow and outflow of cash in business |
| Công nợ phải thu | Accounts Receivable | Amount customers owe the business |
| Công nợ phải trả | Accounts Payable | Amount business owes suppliers |
| Phân tích tuổi nợ | Aging Analysis | Classification of debt by overdue period |
| Tài khoản kế toán | Chart of Accounts | List of accounting accounts |
| Bút toán | Journal Entry | Accounting entry recording transaction |
| Pivot Table | Pivot Table | Multi-dimensional data summary table |
| KPI | Key Performance Indicator | Important performance measurement metric |
| Drill-down | Drill-down | View details from summary to transaction |
| Formula | Formula | Calculation formula in spreadsheet |
Creating P&L Dashboard
Profit & Loss Dashboard allows you to track revenue, expenses and profit in real-time with detailed drill-down capability.
Steps
1. Go to Accounting > Reporting > Spreadsheet Dashboard
2. Create new dashboard and select "P&L Report" template
3. Select time period (month, quarter, year) and compare with previous period
4. Add ODOO.ACCOUNT.BALANCE() formula to get account balance
5. Create sections: Revenue, Cost of Sales, Operating Expenses, Net Profit
6. Add column chart comparing revenue vs expenses by month
7. Add pivot table analyzing expenses by department
8. Set up drill-down to view detailed journal entries
Balance Sheet Dashboard
Create interactive balance sheet displaying assets, liabilities and equity with trend analysis.
Key points
• Use ODOO.ACCOUNT.BALANCE() formula with filter by account type
• Classify assets: Current Assets, Fixed Assets, Intangible Assets
• Classify liabilities: Current Liabilities, Long-term Liabilities
• Automatic calculation: Total Assets = Total Liabilities + Equity
• Pie chart displaying asset structure
• Trend analysis comparing balance sheet across periods
• Calculate financial ratios: Current Ratio, Debt-to-Equity
• Drill-down from account group to individual accounts
Cash Flow Analysis
Cash flow dashboard tracks cash inflows and outflows, analyzed by operating, investing and financing activities.
Key points
• Waterfall chart displaying cash flow breakdown
• Trend line tracking cash position over time
• Forecast cash flow based on historical data
• Alert when cash balance below minimum level
| Cash Flow Type | Formula | Purpose |
|---|---|---|
| Operating Cash Flow | ODOO.ACCOUNT.BALANCE(operating_accounts) | Cash from main business operations |
| Investing Cash Flow | ODOO.ACCOUNT.BALANCE(investing_accounts) | Cash from asset purchases, investments |
| Financing Cash Flow | ODOO.ACCOUNT.BALANCE(financing_accounts) | Cash from loans, stock issuance |
| Net Cash Flow | SUM(Operating + Investing + Financing) | Total cash change in period |
Receivables Dashboard
Track accounts receivable and payable with aging analysis and collection forecast.
Key points
• Use ODOO.PARTNER.RECEIVABLE() to get receivables by customer
• Aging buckets: Current, 1-30 days, 31-60 days, 61-90 days, 90+ days
• Stacked bar chart displaying aging distribution
• Top 10 customers by outstanding amount
• DSO (Days Sales Outstanding) calculation and trend
• Payment forecast based on payment terms
• Drill-down to individual invoices
• Similar for Accounts Payable with ODOO.PARTNER.PAYABLE()
Spreadsheet Formulas for Accounting
Specialized formulas to integrate accounting data into spreadsheet:
| Formula | Parameters | Example |
|---|---|---|
| ODOO.ACCOUNT.BALANCE() | account_id, date_from, date_to | =ODOO.ACCOUNT.BALANCE("400000", "2024-01-01", "2024-12-31") |
| ODOO.ACCOUNT.MOVE.LINE() | account_id, filters | =ODOO.ACCOUNT.MOVE.LINE("100000", {"partner_id": 5}) |
| ODOO.PARTNER.RECEIVABLE() | partner_id, date | =ODOO.PARTNER.RECEIVABLE(5, "2024-12-31") |
| ODOO.PARTNER.PAYABLE() | partner_id, date | =ODOO.PARTNER.PAYABLE(10, "2024-12-31") |
| ODOO.FISCAL.POSITION() | position_id | =ODOO.FISCAL.POSITION(1) |
Financial KPIs
Important financial metrics that can be calculated and displayed in dashboard:
Key points
• Gross Profit Margin = (Revenue - COGS) / Revenue
• Net Profit Margin = Net Profit / Revenue
• Current Ratio = Current Assets / Current Liabilities
• Quick Ratio = (Current Assets - Inventory) / Current Liabilities
• Debt-to-Equity Ratio = Total Debt / Total Equity
• Return on Assets (ROA) = Net Income / Total Assets
• Return on Equity (ROE) = Net Income / Shareholders Equity
• Working Capital = Current Assets - Current Liabilities
• Days Sales Outstanding (DSO) = (Accounts Receivable / Revenue) × 365
• Days Payable Outstanding (DPO) = (Accounts Payable / COGS) × 365
Best Practices
Tips to optimize accounting dashboard:
Key points
• Use named ranges for account codes to easily maintain formulas
• Create separate sheets for raw data and presentation
• Set up auto-refresh to keep dashboard updated
• Use conditional formatting to highlight negative values
• Create comparison with previous period or budget
• Export dashboard to PDF for board meetings
• Set up access rights to protect sensitive financial data
• Use filters to view data by company, department, or project
• Create drill-down links to detailed reports in Accounting module
• Document formulas and assumptions for auditing purposes