Financial Calculator: Pilot Model

Purpose: Interactive tools to model Lantern's financial performance under different assumptions. Adjust merchant acquisition, churn, ARPU, and costs to see how they impact 12-month profitability.

New: See FUND_ALLOCATION.md for comprehensive allocation framework and run python scripts/fund_allocation_calculator.py for detailed budget analysis with Four Pillars, safe space funding, and profit sharing.

Input Assumptions (edit these)

MERCHANT ASSUMPTIONS
├─ Monthly new merchants (Phase 1, Pilot): 2–3 merchants/month
├─ Monthly new merchants (Phase 2, Scale): 3–4 merchants/month
├─ Monthly new merchants (Phase 3, Expansion): 6–8 merchants/month
├─ Monthly merchant churn: 5% (95% retention)
└─ Average revenue per merchant (ARPU): 165/month (150 flat + 1.50 PPC mix)

USER ASSUMPTIONS
├─ Users per merchant: 50–100 (exposed to offers)
├─ % of users who activate: 20–30% (check-in at least weekly)
├─ % of active users who engage daily: 40% (engaged DAU)
└─ Redemptions per engaged user per month: 1.2–2.9 (increasing with product maturity)

COST ASSUMPTIONS
├─ Monthly infrastructure (Cloudflare, Firebase, domain): 85/month
├─ Monthly contractor support (hired Month 4): 40/month
├─ Monthly API overage: 0–50/month (assumes free tier + occasional overage)
└─ Initial launch cost: 0 (bootstrapped, no hardware/legal setup)

Option 1: Python Calculator (Run Locally)

Create a file pilot_calculator.py in your project:

python

# Pilot Financial Model Calculator
# Usage: python pilot_calculator.py
# Edit INPUT ASSUMPTIONS below, then run to see 12-month projections

# ===== INPUT ASSUMPTIONS (edit these) =====
ARPU = 165  # avg revenue per merchant per month
MONTHLY_COST = 85  # infrastructure cost per month
CONTRACTOR_COST = 40  # contractor cost (active from month 4)
MERCHANT_CHURN = 0.05  # 5% monthly churn
MERCHANTS_PER_MONTH = [2, 3, 3, 2, 4, 4, 4, 3, 3, 3, 8, 8, 8]  # new merchants each month (0-indexed)

# ===== CALCULATION =====
active_merchants = 0
cumulative_profit = 0
results = []

for month, new_merchants in enumerate(MERCHANTS_PER_MONTH):
    # Update active merchants (add new, subtract churn)
    active_merchants += new_merchants
    active_merchants *= (1 - MERCHANT_CHURN)  # apply churn
    active_merchants = int(active_merchants)  # round down
    
    # Calculate monthly metrics
    monthly_revenue = active_merchants * ARPU
    monthly_cost = MONTHLY_COST + (CONTRACTOR_COST if month >= 4 else 0)
    monthly_profit = monthly_revenue - monthly_cost
    cumulative_profit += monthly_profit
    
    # Store results
    results.append({
        'month': month,
        'new_merchants': new_merchants,
        'active_merchants': active_merchants,
        'revenue': monthly_revenue,
        'cost': monthly_cost,
        'profit': monthly_profit,
        'cumulative_profit': cumulative_profit
    })

# ===== DISPLAY RESULTS =====
print("=" * 80)
print("LANTERN PILOT FINANCIAL PROJECTIONS (12-Month)")
print("=" * 80)
print("\nInput Assumptions:")
print(f"  ARPU: ${ARPU}/month")
print(f"  Baseline monthly cost: ${MONTHLY_COST}")
print(f"  Contractor cost: ${CONTRACTOR_COST} (starts month 4)")
print(f"  Monthly churn: {MERCHANT_CHURN*100}%")
print("\n" + "-" * 80)
print("Month | New | Active |  Revenue |   Cost |  Profit | Cumulative")
print("-" * 80)

for r in results:
    print(f"{r['month']:2d}    | {r['new_merchants']:3d} | {r['active_merchants']:6d} | ${r['revenue']:7,.0f} | ${r['cost']:5,.0f} | ${r['profit']:7,.0f} | ${r['cumulative_profit']:11,.0f}")

total_revenue = sum(r['revenue'] for r in results)
total_cost = sum(r['cost'] for r in results)
total_profit = results[-1]['cumulative_profit']
profit_margin = (total_profit / total_revenue) * 100 if total_revenue > 0 else 0

print("-" * 80)
print(f"\n12-MONTH SUMMARY:")
print(f"  Total Revenue:    ${total_revenue:>12,.0f}")
print(f"  Total Cost:       ${total_cost:>12,.0f}")
print(f"  Net Profit:       ${total_profit:>12,.0f}")
print(f"  Profit Margin:    {profit_margin:>12.1f}%")
print(f"  Final Merchants:  {results[-1]['active_merchants']:>12}")
print("\n" + "=" * 80)

How to Use

Save as pilot_calculator.py in your project root
Edit the INPUT ASSUMPTIONS at the top (ARPU, churn rate, monthly new merchants)
Run:
bash
```
python pilot_calculator.py
```
1
View output showing month-by-month revenue, costs, profit, and cumulative impact

Example: What if churn is 10%?

python

MERCHANT_CHURN = 0.10  # Change from 0.05 to 0.10

Then run again to see how higher churn impacts profitability.

Example: What if we acquire 5/month (not 2–4)?

python

MERCHANTS_PER_MONTH = [5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5]

Then run to see accelerated growth scenario.

Option 2: Google Sheets Calculator

Recommended for non-technical users or team collaboration.

Setup (5 minutes)

Open Google Sheets: https://sheets.google.com
Create a new spreadsheet titled "Lantern Pilot Financial Model"
Set up columns:
- A: Month (0–12)
- B: New Merchants
- C: Active Merchants
- D: Revenue
- E: Cost
- F: Monthly Profit
- G: Cumulative Profit

Add input cells (top-right corner):

ARPU: [165]
Monthly Cost: [85]
Contractor Cost: [40]
Contractor Starts Month: [4]
Monthly Churn: [5%]

Add formulas:
- C2 (Active Merchants): =IF(ROW()=2, B2, C1 + B2 - (C1 * $I$5)) (adjust I5 to your churn cell)
- D2 (Revenue): =C2 * $I$2 (ARPU in I2)
- E2 (Cost): =IF(ROW()>=6, $I$3 + $I$4, $I$3) (baseline cost in I3, contractor in I4)
- F2 (Monthly Profit): =D2 - E2
- G2 (Cumulative): =IF(ROW()=2, F2, G1 + F2)
Copy formulas down to row 14 (12 months)
Create summary cells below the table:
- Total Revenue: =SUM(D:D)
- Total Cost: =SUM(E:E)
- Net Profit: =G14
- Margin: =G14 / SUM(D:D)

How to Use

Edit input cells (ARPU, costs, churn %) and see results update instantly
Change "New Merchants" column for different acquisition scenarios
Share with team for collaborative planning
Export as PDF for presentations

Option 3: Manual Spreadsheet (Excel, Numbers, LibreOffice)

If you prefer a local spreadsheet:

Create columns: Month, New Merchants, Active Merchants, Revenue, Cost, Profit, Cumulative Profit
Copy the month-by-month table from PILOT_STRATEGY.md as a starting point
Adjust inputs:
- Change ARPU from 165 to your estimate
- Change monthly new merchants (e.g., 3 per month instead of 2–4)
- Adjust churn from 5% to your assumption
Update formulas:
- Revenue = Active Merchants × ARPU
- Profit = Revenue − Cost
- Cumulative = Previous Cumulative + Current Profit
Re-calculate and iterate

Key Sensitivities to Test

1. Merchant Acquisition Rate

Questions: What if we acquire faster or slower?

Scenario	Merchants/month	Year 1 Profit	Insight
Conservative	1–2	~12K	Slow growth; conservative
Baseline	2–4	~37K	Our target scenario
Aggressive	5–6	~60K+	Requires paid acquisition or strong partnerships

Test: Change MERCHANTS_PER_MONTH to [1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 5, 5, 5] and run calculator.

2. ARPU (Average Revenue Per Merchant)

Questions: What if merchants pay less (PPC-only) or more (higher flat fees)?

Scenario	ARPU	Year 1 Profit	Insight
Low (PPC-only)	50	~3K	Barely viable; need higher acquisition
Medium (baseline)	165	~37K	Sweet spot; sustainable
High (premium tier)	250	~60K	Better margins; requires stronger value prop

Test: Change ARPU to 50, 165, or 250 and run calculator.

3. Merchant Churn

Questions: What if merchants don't renew?

Scenario	Monthly Churn	Year 1 Profit	Insight
Low	2%	~45K	Excellent retention; high LTV
Medium (baseline)	5%	~37K	Our assumption; manageable
High	10%	~25K	Concerning; focus on retention
Very High	15%	~10K	Model breaks; pivot needed

Test: Change MERCHANT_CHURN to 0.02, 0.05, 0.10, or 0.15 and run calculator.

4. Hiring Timing

Questions: When should we hire a contractor or full-time support?

Scenario	Hire Month	Cost Impact	Year 1 Profit
Baseline (Month 4)	4	+$40/month	~37K
Early (Month 2)	2	+$40 from Month 2	~30K
Late (Month 6)	6	+$40 from Month 6	~38K
Never	—	$0	~42K

Test: Change CONTRACTOR_COST in the code or adjust contractor start month.

5. Combined Stress Test

Questions: What if multiple bad things happen?

Scenario: Low ARPU (100) + High churn (10%) + Hire early (Month 2)

python

ARPU = 100
MERCHANT_CHURN = 0.10
MONTHLY_COST = 85 + 40  # Start contractor immediately

Result: Year 1 profit drops to ~8K (vs. 37K baseline). Still viable, but tight.

Recommendations

Start with Baseline (ARPU 165, 2–4 merchants/month, 5% churn)
- Run calculator to confirm 37K Year 1 profit
Test Your Key Assumptions
- If you're unsure about ARPU, test 100, 165, 250
- If customer retention is uncertain, test 2%, 5%, 10% churn
Use Stress Tests to Set Milestones
- "If churn exceeds 8%/month by Month 6, we pivot to retention focus"
- "If acquisition is <1/month by Month 4, we increase marketing spend"
Share Scenarios with Team
- Use Google Sheets to run scenarios collaboratively
- Document assumptions so everyone's on the same page

Financial Calculator: Pilot Model ​

Input Assumptions (edit these) ​

Option 1: Python Calculator (Run Locally) ​

How to Use ​

Example: What if churn is 10%? ​

Example: What if we acquire 5/month (not 2–4)? ​

Option 2: Google Sheets Calculator ​

Setup (5 minutes) ​

How to Use ​

Option 3: Manual Spreadsheet (Excel, Numbers, LibreOffice) ​

Key Sensitivities to Test ​

1. Merchant Acquisition Rate ​

2. ARPU (Average Revenue Per Merchant) ​

3. Merchant Churn ​

4. Hiring Timing ​

5. Combined Stress Test ​

Recommendations ​

See Also ​

Financial Calculator: Pilot Model

Input Assumptions (edit these)

Option 1: Python Calculator (Run Locally)

How to Use

Example: What if churn is 10%?

Example: What if we acquire 5/month (not 2–4)?

Option 2: Google Sheets Calculator

Setup (5 minutes)

How to Use

Option 3: Manual Spreadsheet (Excel, Numbers, LibreOffice)

Key Sensitivities to Test

1. Merchant Acquisition Rate

2. ARPU (Average Revenue Per Merchant)

3. Merchant Churn

4. Hiring Timing

5. Combined Stress Test

Recommendations

See Also