Build a 13-Week Cash Flow Model for E-commerce in Excel
Cash flow crises kill profitable businesses. Learn how to build a rolling 13-week cash flow forecast in Excel that accounts for payout delays, inventory purchases, and seasonal patterns to avoid running out of money.
Platforms:
Shopify
Stripe
The Profitable Company That Ran Out of Money
Your P&L looks great. Revenue up 40%. Healthy margins. Profitable every month. Then you get an email from your supplier. The $45,000 inventory order is due. You check your bank account: $12,000. How did this happen? The Cash Flow Reality:- Week 1: Order $45K inventory, pay supplier
- Week 4: Inventory arrives, list on site
- Week 6-10: Sell through inventory
- Week 8-12: Actually receive cash (payout delays)
Why 13 Weeks? Why Not Annual Budget?
Annual budgets are strategic. 13-week forecasts are tactical.13 weeks (quarter) is perfect because:
1. It's actionable You can actually influence next month's cash. You can't change next year's much. 2. It's rolling Every week, drop week 1 and add week 14. Always seeing 13 weeks ahead. 3. It matches business cycles Most suppliers: 30-45 day payment terms Most payouts: Weekly to biweekly Inventory velocity: 30-90 days 4. It reveals timing crunches Your business might be profitable over a quarter but have a cash crisis in week 7.The Basic Structure
Starting Cash (from last week's ending cash) + Cash In (payouts, refunds collected, other income) - Cash Out (inventory, ads, salaries, software, rent) = Ending Cash (becomes next week's starting cash) Track actuals vs. forecast weekly. Adjust future weeks based on what you learned.Building Your 13-Week Cash Flow Model in Excel
1
Set Up the Weekly Column Structure
Create columns for Week 1 through Week 13 across the top. Rows will be: Starting Cash, Cash In categories, Cash Out categories, Net Cash Change, Ending Cash. Use actual calendar week dates.
2
Map Your Payout Schedule
Shopify Payments: daily with 2-day delay. PayPal: weekly. Stripe: varies by plan. Sales from Jan 1-7 hit your bank Jan 9. Build this delay into your model: =SalesWeek1_ActualDate+PayoutDelay
3
Forecast Sales by Week
Use last year's sales pattern adjusted for growth. Formula: =VLOOKUP(WEEKNUM(date), historical_sales, 2) * growth_multiplier. Account for seasonality - December ≠ February.
4
Model Inventory Purchase Timing
List planned POs: Amount, Order Week, Payment Week, Arrival Week, Sell-Through Weeks. Most critical: Payment Week is when cash leaves. Use supplier terms (Net 30, 50% upfront, etc).
5
Add Fixed Operating Costs
List recurring costs: Shopify subscription, apps, payroll, rent, insurance. These hit same week each month. Some weekly (ads), some monthly (software), some quarterly (insurance).
6
Calculate Weekly Cash Position
Each week: Starting Cash + Total Cash In - Total Cash Out = Ending Cash. Ending Cash becomes next week's Starting Cash. Conditional format: Red if <$10K, Yellow if <$25K.
7
Build Scenario Analysis
Create three versions: Best Case (+20% sales), Base Case (forecast), Worst Case (-20% sales). Keep inventory purchases constant. This shows your cash sensitivity to revenue swings.
8
Add Actual vs. Forecast Tracking
Each week, add 'Actual' row under each 'Forecast' row. Calculate variance: =Actual-Forecast. Big variances signal you need to adjust future weeks' assumptions.
The Critical Cash Flow Formulas
1. Sales → Cash In Conversion (with delay)
=SUMIFS(Sales_Range, Date_Range, >=WeekStart, Date_Range, <=WeekEnd) * (1 - RefundRate)2. Inventory Cash Out (with payment terms)
=IF(WEEKNUM(TODAY())=OrderWeek, DepositAmount,
IF(WEEKNUM(TODAY())=OrderWeek+PaymentTermsDays/7, Balance, 0))3. Cumulative Cash Position
=PriorWeekEnding + ThisWeekIn - ThisWeekOut4. Runway Calculator
=IF(WeeklyBurn>0, CurrentCash/WeeklyBurn, "Infinite")The Inventory Timing Trap
Suppliers want payment before or on delivery. Your cash arrives 4-8 weeks after you sell the inventory. This timing gap is why profitable e-commerce businesses fail. Model it explicitly.
Common Cash Flow Patterns and What to Do
Pattern 1: The Seasonal Dip (Predictable)
Symptoms: Weeks 1-4 positive, weeks 5-7 deeply negative, weeks 8-13 recovery Cause: Q1 inventory purchase for Q2 peak season Solutions:- Line of credit to bridge weeks 5-7
- Negotiate Net 60 terms with supplier
- Spread inventory purchases across weeks 1-6 instead of all in week 1
Pattern 2: The Growth Trap (Dangerous)
Symptoms: Sales growing but cash getting tighter each week Cause: Scaling inventory faster than cash cycles through Solutions:- Slow growth rate to match working capital capacity
- Raise equity/debt financing before crisis
- Improve inventory turnover (smaller, more frequent orders)
Pattern 3: The Choppy Burn (Manageable)
Symptoms: Some weeks positive, some negative, trending slowly down Cause: Variable marketing spend, unpredictable sales Solutions:- Build cash buffer (3 months operating expenses)
- Cap marketing spend at % of trailing 4-week cash in
- Smooth out lumpy costs (monthly billing vs annual)
Pattern 4: The Healthy Accumulation (Goal)
Symptoms: Most weeks positive, cash balance growing steadily Cause: Profitable unit economics + good working capital management Actions:- Invest excess cash in inventory to capture growth
- Build 6-month cash reserve
- Plan expansion or shareholder distributions
| Cash Balance Status | Weeks of Runway | Action Required | Urgency |
|---|---|---|---|
| >$100K | 20+ weeks | Normal operations, invest excess | None |
| $50-100K | 10-20 weeks | Monitor, limit discretionary spend | Low |
| $25-50K | 5-10 weeks | Freeze non-essential costs, negotiate terms | Medium |
| $10-25K | 2-5 weeks | Emergency cost cuts, delay inventory, seek financing | High |
| <$10K | <2 weeks | Crisis mode - sell assets, emergency financing, consider shutdown | Critical |
Common Mistakes to Avoid
Mistake: Forecasting based on order date instead of payout date
Solution: Sales on Monday don't hit your bank until Wednesday (or next Tuesday). Model when cash actually arrives, not when customers click buy.
Mistake: Not updating the forecast weekly with actuals
Solution: A static forecast is useless. Every Monday, add last week's actuals and roll forward one week. This is a living document.
Mistake: Treating refunds as negative sales instead of separate cash out
Solution: Refunds happen on different timing than sales. A December sale might refund in January. Track separately.
Mistake: Forgetting about quarterly/annual costs
Solution: Insurance, domain renewals, annual software subscriptions. These create cash spikes. Add them to the model even if they're 10 weeks out.
Verification Checklist
- Your forecast correctly predicted actual cash position within 10% for the last 4 weeks
- You've identified at least one upcoming cash crunch and made a plan to address it
- You can explain your cash conversion cycle (days from inventory purchase to cash collection)
- You know your minimum cash balance threshold and have alerts when approaching it
Frequently Asked Questions
Week 1-4: Within 10%. Week 5-8: Within 20%. Week 9-13: Within 30%. It's a planning tool, not a crystal ball. Accuracy improves with weekly updates.
Use industry benchmarks and conservative assumptions. Assume longer payout delays, higher refund rates, slower sales. Update aggressively as real data comes in.
No. This is cash flow, not profit. Only include actual cash moving in and out of your bank account. Depreciation is accounting, not cash.
Minimum: 4 weeks of operating expenses. Comfortable: 12 weeks. If you're financing inventory purchases, you need larger buffers.
Options in order: 1) Cut discretionary spending, 2) Delay inventory purchases, 3) Negotiate extended payment terms, 4) Line of credit, 5) Equity raise. Act early - options disappear as cash disappears.