Skip to main content

CODITECT Financial Model - Structural Issues Analysis

Summary​

Analysis identified 12 structural issues ranging from critical formula problems to enhancement opportunities. Issues are categorized by severity and impact on model reliability.

CRITICAL ISSUES (Fix Immediately)​

Issue #1: Duplicate Column Header in Expenses Sheet​

Location: Expenses!I1 and Expenses!J1
Problem: Both columns labeled "Cloud Storage"
Impact: Confusion in formula references, potential double-counting
Evidence:

Headers: ['Month', 'Month', 'Headcount', 'Salaries', 'Marketing', 'R&D', 
          'G&A', 'Office', 'Cloud Storage', 'Cloud Storage', 'Subscriptions', ...]

Recommendation: Rename column J to "Cloud Compute" or consolidate

Issue #2: Hardcoded Dashboard Metrics​

Location: Dashboard!E8 (Burn Rate)
Problem: Shows fixed $50,000 instead of formula reference
Impact: Dashboard doesn't reflect actual model calculations
Evidence: Value is static regardless of P&L changes
Recommendation: Replace with =IF('Cash Flow'!F[last_burn_month]>0, 'Cash Flow'!F[last_burn_month], 0)

Issue #3: Customer Count Display Error​

Location: Dashboard!E7 (Customers)
Problem: Shows "0.7" which appears to be a percentage, not customer count
Impact: Misleading KPI display
Evidence: Should show ~900K customers at month 60, displays 0.7
Recommendation: Fix formula to reference Revenue!F[target_month]

Issue #4: Missing Customer Mix Validation​

Location: Revenue sheet, columns H:J (Ind%, Team%, Ent%)
Problem: No validation that percentages sum to 100%
Impact: Model could have impossible states (e.g., 80% + 30% + 10% = 120%)
Evidence: Formula structure:

Ind% = interpolation formula
Team% = (1-Ind%)*0.8  ← assumes 80% of remainder
Ent% = 1-Ind%-Team%   ← residual

Recommendation: Add validation row: =IF(H+I+J<>1, "ERROR", "OK")

HIGH PRIORITY ISSUES (Fix Before Use)​

Issue #5: Pricing Dependency Chain​

Location: Assumptions!C3, C4
Problem: Team and Enterprise prices are formulas based on Individual price

Team Price: =C2*10+100     → $15*10+100 = $250
Enterprise: =1000+C2*20    → $1000+$15*20 = $1,300

Impact: Changing Individual price cascades unexpectedly
Risk Level: Medium - may be intentional design
Recommendation: Either:

  • Document this dependency clearly, OR
  • Make all prices independent inputs (blue cells)

Issue #6: Churn Formula Complexity​

Location: Revenue!F (Total Customers formula)
Problem: Complex nested formula for churn improvement transition

=F9+E10-K9*(Assumptions!$C$23-(Assumptions!$C$23-Assumptions!$C$26)*MIN(1,MAX(0,(D10-24)/12)))...

Impact: Difficult to audit, potential off-by-one errors
Recommendation: Break into helper columns:

  • Column for "Churn Rate This Month"
  • Column for "Churned Customers"
  • Cleaner Total Customers formula

Issue #7: No Funding Timing Granularity​

Location: Revenue!C (Total Funding) and Cash Flow
Problem: Funding appears as cumulative total, not discrete events
Evidence: All months after Month 6 show $10,500,000
Impact: Cannot model partial funding rounds or different timing scenarios
Recommendation: Add separate columns:

  • Angel Amount / Angel Month
  • Seed Amount / Seed Month
  • Series A Amount / Series A Month

Issue #8: Missing Net Revenue Retention (NRR)​

Location: Not present
Problem: Model lacks expansion revenue tracking
Impact: Cannot model upgrades (Individual→Team, Team→Enterprise)
Recommendation: Add columns for:

  • Upgrade revenue
  • Expansion MRR
  • NRR calculation (target: >100%)

MEDIUM PRIORITY ISSUES (Enhance When Possible)​

Issue #9: No Working Capital Model​

Location: Cash Flow sheet
Problem: Assumes instant cash collection (Revenue = Cash)
Impact: Overstates cash position, especially for Enterprise with Net-30/60 terms
Recommendation: Add:

  • Days Sales Outstanding (DSO) assumption
  • Accounts Receivable tracking
  • Cash vs. Accrual revenue distinction

Issue #10: Fixed Expense Ratios​

Location: Expenses formulas
Problem: Marketing (35%), R&D (3.75%), G&A (10%) are hardcoded percentages
Evidence: Marketing = Revenue * 0.35 embedded in formula
Impact: Cannot easily run efficiency scenarios
Recommendation: Move ratios to Assumptions sheet as named inputs

Issue #11: No Scenario/Sensitivity Framework​

Location: Not present
Problem: Single-point forecast only
Impact: Cannot assess risk ranges or present multiple outcomes to investors
Recommendation: Add Scenarios sheet with:

  • Base / Optimistic / Pessimistic cases
  • Sensitivity toggles for key drivers
  • Monte Carlo capability (advanced)

Issue #12: Inconsistent Date Handling​

Location: Multiple sheets
Problem: Dates stored as datetime objects, display inconsistent
Evidence: "2025-08-01 00:00:00" in some cells
Impact: Sorting/filtering issues, confusing display
Recommendation: Standardize to:

  • Column A: Date (formatted as MMM-YYYY)
  • Column B: Month Number (integer 0-60)

FORMULA QUALITY NOTES​

Good Practices Found ✓​

  • Assumptions sheet properly separates inputs
  • Most calculations reference Assumptions (356 references found)
  • Churn rates use clear formulas (=1/3 for 3-month retention)
  • P&L properly links to Revenue and Expenses sheets

Areas for Improvement​

  • Consider named ranges for key assumptions (e.g., IndividualPrice instead of Assumptions!$C$2)
  • Add data validation dropdowns for categorical inputs
  • Include assumption audit trail (date changed, by whom, why)
PriorityIssueEffortImpact
1#3 Customer Count Display5 minHigh
2#1 Duplicate Column10 minMedium
3#2 Hardcoded Burn Rate10 minHigh
4#4 Mix Validation15 minMedium
5#11 Scenario Framework2 hrsHigh
6#6 Churn Formula30 minMedium
7#10 Expense Ratios20 minMedium
8#5 Pricing Documentation10 minLow
9#7 Funding Granularity1 hrMedium
10#8 NRR Tracking2 hrsHigh
11#9 Working Capital2 hrsMedium
12#12 Date Handling30 minLow

Next Steps​

  1. Immediate: Fix issues #1-4 in current model
  2. This Sprint: Implement Scenario Framework (#11) - see companion sensitivity model
  3. Next Sprint: Add NRR tracking (#8) and working capital (#9)
  4. Backlog: Remaining enhancements

Analysis Date: February 5, 2026
Author: Hal Casteel with assistance from Claude 4.5
Source: CODITECT_Financial_Model_Final.xlsx