Financial Dashboard For E-commerce Businesses

Introduction

E-commerce businesses generate data from multiple channels-website, marketplaces, ads, and logistics. A well designed financial dashboard brings this data together to show true profitability, customer acquisition costs, and cash flow health.

Why an e-commerce financial dashboard matters

E-commerce metrics can be misleading. Gross sales don't show profitability. A dashboard helps you:

See true profit after all costs
Track customer acquisition cost (CAC) by channel
Monitor inventory turnover and cash conversion cycle
Identify profitable vs unprofitable products
Forecast cash flow based on sales trends

Core components of an e-commerce financial dashboard

Revenue streams. Website sales, marketplace sales (Amazon, eBay), wholesale.
Cost structure. Cost of goods, shipping, payment processing, marketplace fees.
Marketing metrics. Ad spend, conversion rates, CAC, return on ad spend (ROAS).
Operational metrics. Inventory turnover, fulfillment costs, return rates.
Customer metrics. Lifetime value (LTV), repeat purchase rate, churn.
Cash flow. Timing of cash inflows vs outflows.

Step by step walkthrough using a real case

Business background

An online retailer sells home goods through:

Their own Shopify store
Amazon marketplace
Wholesale to small boutiques

They struggle to understand:

Which channels are most profitable
Their true customer acquisition cost
Cash flow timing issues
Inventory investment returns

Step 1. Connect data sources

Use Power Query to pull data from:

Sales data:

Shopify export (CSV or API)
Amazon Seller Central reports
Wholesale invoice spreadsheet

Cost data:

Supplier invoices (import to Excel)
Shipping carrier reports
Payment processor statements
Marketplace fee reports

Marketing data:

Google Ads export
Facebook Ads export
Email marketing platform data

Step 2. Build the data model

Create these fact tables:

Sales fact table:

Order ID, date, channel, product, quantity, revenue

Cost fact table:

Product ID, cost per unit, shipping cost, fees

Marketing fact table:

Campaign, date, channel, spend, clicks, conversions

Inventory fact table:

Product ID, date, quantity on hand, cost value

Create dimension tables for:

Date (with financial periods)
Products (with categories)
Customers (with segments)
Channels (website, Amazon, wholesale)

Step 3. Calculate key metrics

Profitability by channel:

Revenue - COGS - shipping - fees - returns = Net revenue
Net revenue - marketing spend = Channel profit

Customer acquisition cost (CAC):

Marketing spend ÷ New customers acquired

Lifetime value (LTV):

(Average order value × Repeat purchase rate) ÷ Churn rate

Return on ad spend (ROAS):

Revenue from campaign ÷ Ad spend

Inventory turnover:

Cost of goods sold ÷ Average inventory value

Cash conversion cycle:

Days inventory outstanding + Days sales outstanding - Days payable outstanding

Step 4. Design the dashboard layout

Create a one page summary with:

Top section - Executive summary:

Monthly revenue vs target
Net profit margin
Cash position
Key alerts (low stock, high CAC, etc.)

Middle section - Channel performance:

Revenue by channel (bar chart)
Profitability by channel (waterfall chart)
CAC and LTV by channel (scatter plot)

Bottom section - Operational metrics:

Inventory turnover trend
Top 10 products by profitability
Marketing ROAS by campaign
Customer retention metrics

Step 5. Add forecasting capabilities

Use historical data to forecast:

Revenue forecast:

Seasonal trends
Growth rate by channel
Marketing impact on sales

Cash flow forecast:

Payment terms by channel (Amazon pays every 14 days, Shopify instant, wholesale 30 days)
Inventory purchase timing
Fixed costs schedule

Inventory forecast:

Sales velocity by product
Lead time from suppliers
Safety stock calculations

Step 6. Set up alerts and thresholds

Create conditional formatting for:

Financial health:

Cash balance below 30 days of expenses
Profit margin below target
CAC exceeding LTV

Operational issues:

Inventory turnover below 4x annually
Return rate above 5%
Shipping cost per order increasing

Marketing efficiency:

ROAS below 2.0
Conversion rate dropping
Customer acquisition cost rising

How to interpret results

Focus on the relationships between metrics:

High ROAS but low profit margin means product costs or fees are too high
Good inventory turnover but cash flow problems means payment terms mismatch
High LTV but low repeat rate means great first purchase experience but poor retention
Channel profitability differences show where to focus growth efforts

Actions the e-commerce business can take based on the dashboard

Shift marketing budget to channels with best LTV:CAC ratio
Renegotiate supplier terms to improve cash conversion cycle
Adjust pricing on low margin products
Increase stock of fast turning, high margin items
Test free shipping thresholds to optimize average order value
Focus retention efforts on high LTV customer segments
Plan inventory purchases based on forecast, not gut feel

Conclusion

An e-commerce financial dashboard turns raw data into actionable insights. It shows where you're making money, where you're losing it, and what levers to pull for growth.

FAQs

How often should I update the dashboard

Update daily for sales and marketing data, weekly for full financial review. Automate data imports where possible.

What if I sell on multiple marketplaces with different fee structures

Create separate calculations for each marketplace. Amazon FBA has different fees than Shopify or eBay. Track each as its own "channel" in your model.

How do I handle returns and refunds in the dashboard

Track gross sales, then subtract returns to get net sales. Include return processing costs in your cost calculations.

What about international sales with different currencies

Convert all currencies to your base currency using daily exchange rates. Track currency gain/loss separately.

How do I account for shipping costs that vary by order

Use average shipping cost per order by destination zone, or integrate with your shipping software API for accurate costs.

Can this dashboard handle subscription e-commerce

Yes, add columns for subscription metrics: MRR, churn, expansion revenue, cohort analysis.

What if I don't have all this data yet

Start with what you have-even basic revenue and cost tracking is better than nothing. Add data sources as you grow.