The founder of a 18-person AI agency in Nashville was considering hiring three more engineers. The demand was there — the pipeline was strong, and two clients had asked to expand their engagements. But she hesitated. Could the agency afford three new hires? What if one of the pipeline deals fell through? How long could the agency sustain the additional payroll before the new revenue materialized? What would the margin impact be during the ramp-up period? She had gut feelings about the answers but no data. She was making a $600,000 annual commitment ($200,000 per engineer fully loaded) based on intuition.
After building a financial model, the answers became clear: the agency could afford two hires immediately with comfortable margin preservation, but a third hire would require either closing one of the pipeline deals first or drawing on reserves for approximately 10 weeks. The model showed that if both pipeline deals closed on schedule, the three hires would improve net margin by 4 percentage points. If only one deal closed, margin would drop temporarily but recover within a quarter. If neither deal closed, the agency would burn through 40% of its cash reserve before reaching break-even on the new hires. With this information, she hired two engineers immediately and made the third conditional on a pipeline deal closing — a decision she could not have made with confidence without the model.
A financial model is not a prediction of the future. It is a tool for testing decisions against scenarios. It answers "what if" questions with numbers instead of guesses, and it makes the financial implications of operational decisions visible before you commit to them.
What a Financial Model Does
Scenario Testing
The primary value of a financial model is testing scenarios before they happen:
- "What if we hire three engineers?" — shows the cost impact and the revenue needed to cover them
- "What if our largest client reduces their engagement by 50%?" — shows the margin and cash flow impact
- "What if we raise rates by 10%?" — shows the revenue impact assuming some client attrition
- "What if utilization drops from 80% to 70%?" — shows the margin impact and the cash runway implication
- "What if we add a new service line?" — shows the investment required and the break-even timeline
Financial Visibility
The model consolidates all financial data into a single view:
- Current financial position (revenue, costs, margin, cash)
- Forward-looking projections (next 12-24 months)
- Key financial ratios and health indicators
- Budget vs. actual tracking
Investor and Lender Communication
If you seek outside capital — whether a bank loan, a line of credit, or investor funding — a financial model is required. Lenders and investors want to see projections, assumptions, and scenario analysis. A well-built model demonstrates financial sophistication and reduces perceived risk.
Building the Model: Structure
Tab 1: Assumptions
This is the most important tab. Every number in the model flows from assumptions that are documented and adjustable in one place.
Revenue Assumptions:
- Average hourly rate by role (e.g., Senior ML Engineer: $250/hr, Mid-level: $200/hr, Junior: $150/hr, PM: $175/hr)
- Target utilization by role (e.g., Senior: 70%, Mid: 80%, Junior: 85%, PM: 75%)
- Working days per month (typically 21-22)
- Working hours per day (8)
- Rate escalation (annual rate increase percentage, e.g., 3-5%)
- Revenue mix (percentage from T&M, fixed-price, retainer)
- Project win rate (percentage of proposals that convert to signed deals)
- Average project size and duration
Cost Assumptions:
- Average salary by role (base salary for each position level)
- Benefits multiplier (typically 1.20-1.35x base salary to account for health insurance, 401k, payroll taxes)
- Contractor rates (for augmenting capacity)
- Cloud infrastructure cost per project (average monthly spend)
- SaaS and tool costs (monthly total)
- Office and facilities cost (monthly)
- Insurance cost (monthly)
- Professional services (legal, accounting — monthly)
- Marketing and sales cost (monthly)
- Training and development budget
Growth Assumptions:
- Hiring plan (when new roles will be added, at what salary)
- Revenue growth rate (monthly or quarterly expected growth)
- Client acquisition rate (new clients per quarter)
- Client churn rate (percentage of clients lost per year)
- Seasonal patterns (if revenue varies predictably by quarter)
Tab 2: Revenue Model
Calculate projected revenue from assumptions.
For each team member (or aggregate by role):
Monthly Billable Revenue = Hourly Rate x Hours Per Day x Working Days x Utilization Rate
Example for one senior ML engineer: $250/hr x 8 hours x 21 days x 70% utilization = $29,400/month
Aggregate by role and sum for total monthly revenue.
Account for revenue types:
- T&M revenue: Directly calculated from utilization and rates
- Fixed-price revenue: Allocated monthly based on project timelines and milestone schedules
- Retainer revenue: Fixed monthly amounts per active retainer client
Account for revenue variability:
- Not all months are equal. Apply seasonal adjustments if your business has predictable patterns (many enterprise-focused agencies see dips in December and August)
- Apply a realization factor if your historical data shows that actual billed revenue is typically 90-95% of theoretical maximum (due to write-offs, discounts, and scope adjustments)
Tab 3: Cost Model
Direct Costs (Cost of Delivery):
For each team member in a delivery role: Monthly Cost = Annual Salary / 12 x Benefits Multiplier
Plus:
- Contractor costs (for any supplemental delivery resources)
- Project-specific cloud and infrastructure costs
- Project-specific tools and licenses
Gross Profit = Revenue - Direct Costs
Overhead Costs:
- Non-delivery salaries (sales, admin, management) with benefits
- Office and facilities
- General SaaS and tools
- Insurance
- Professional services
- Marketing and business development
- Training and development
- Miscellaneous and contingency
Net Operating Profit = Gross Profit - Overhead
Tab 4: Cash Flow Model
Revenue and profit are accounting concepts. Cash flow is what keeps the lights on.
Monthly Cash Flow:
Cash In:
- Collections from invoices (not revenue — actual cash received, accounting for payment terms and collection delays)
- Other income (interest, one-time payments)
Cash Out:
- Payroll (typically biweekly)
- Rent and facilities (monthly)
- Cloud infrastructure (monthly)
- SaaS subscriptions (monthly)
- Insurance premiums (monthly or quarterly)
- Tax payments (quarterly estimated taxes)
- Loan payments (if applicable)
- Owner distributions (monthly or quarterly)
Net Cash Flow = Cash In - Cash Out
Ending Cash Balance = Beginning Cash Balance + Net Cash Flow
The critical conversion: Revenue does not equal cash. If you bill $100,000 in January with Net-30 terms, you collect $100,000 in February (assuming on-time payment). Your cash flow model must account for this delay.
Apply a collection pattern based on your historical data:
- 70% of invoiced revenue collected in the month after invoicing
- 20% collected two months after invoicing
- 8% collected three months after invoicing
- 2% written off as uncollectible
Tab 5: Headcount Plan
Project your team size month by month:
For each position:
- Role title and level
- Start date
- Annual salary
- Benefits cost
- Fully loaded monthly cost
- Whether the role is delivery (generates revenue) or overhead
The headcount plan drives both the revenue model (more delivery staff = more potential revenue) and the cost model (every hire increases expenses). It is the primary tool for workforce planning.
Tab 6: Scenario Analysis
Create three scenarios using the same model structure with different assumptions:
Base Case: Your most likely projection based on current trends and committed pipeline.
Upside Case: Best reasonable scenario — all pipeline deals close, no client churn, utilization at target levels.
Downside Case: Conservative scenario — half the pipeline closes, one major client reduces scope, utilization drops 10 points below target.
For each scenario, track:
- Monthly revenue
- Monthly net profit
- Monthly ending cash balance
- Months of cash reserve
The downside case is the most important. It answers: "If things go wrong, how bad does it get, and can we survive it?"
Tab 7: KPI Dashboard
A summary tab that pulls key metrics from the other tabs:
Revenue Metrics:
- Monthly revenue (actual vs. budget)
- Revenue per employee
- Revenue growth rate (month-over-month, year-over-year)
- Revenue concentration (percentage from top 3 clients)
Profitability Metrics:
- Gross margin percentage
- Net margin percentage
- EBITDA
- Profit per employee
Cash Metrics:
- Cash balance
- Months of cash reserve
- Days Sales Outstanding
- Cash conversion cycle
Operational Metrics:
- Headcount (actual vs. plan)
- Utilization rate (actual vs. target)
- Average billing rate (effective rate after adjustments)
- Overhead ratio (overhead as percentage of revenue)
Building the Model: Practical Steps
Step 1: Gather Historical Data
Pull the last 12-24 months of financial data from your accounting system:
- Monthly revenue by client
- Monthly costs by category
- Monthly cash balances
- Monthly headcount
- Utilization data (from time tracking)
Historical data calibrates your assumptions. If your model assumes 80% utilization but historical utilization has been 72%, your revenue projections will be too optimistic.
Step 2: Build the Assumptions Tab
Enter all assumptions with clear labels and sources. Use cell references so that changing an assumption automatically updates the entire model.
Color code: Assumptions that you change (inputs) should be one color (typically blue font). Calculated values should be another color (typically black). This visual distinction makes it clear what is an assumption vs. a formula.
Step 3: Build Revenue and Cost Tabs
Construct formulas that calculate monthly revenue and costs from the assumptions. Use helper rows to show intermediate calculations (hours available, hours billed, revenue per person) so the model is auditable.
Step 4: Build the Cash Flow Tab
Convert the revenue and cost projections into cash flow using your collection pattern and payment timing. This tab should show the monthly opening balance, cash in, cash out, and closing balance.
Step 5: Build the Scenario Tabs
Duplicate the base case and modify assumptions for upside and downside scenarios. Link the KPI dashboard to show all three scenarios side by side.
Step 6: Validate Against Actuals
Compare your model's projections for the most recent months against actual results. If the model projected $300,000 in January revenue and actual was $280,000, investigate why. Common sources of error:
- Utilization assumption too high
- Rate assumption not accounting for discounts
- Collection pattern not matching reality
- Overhead costs underestimated
Adjust assumptions until the model's historical projections reasonably match actual results (within 5-10%). This calibration makes forward projections more reliable.
Using the Model for Key Decisions
Hiring Decisions
Question: "Should we hire a senior ML engineer at $190,000/year?"
Model inputs: Add the position to the headcount plan with the start date and salary. Estimate the ramp-up period (typically 2-3 months before the new hire is fully productive and billable).
Model outputs:
- Impact on monthly costs (approximately $22,000/month fully loaded)
- Revenue generated once productive (approximately $29,000/month at target utilization)
- Break-even timeline (months until cumulative revenue exceeds cumulative cost)
- Impact on cash reserves during ramp-up
- Net margin impact once fully ramped
Pricing Decisions
Question: "What happens if we raise rates by 10%?"
Model inputs: Increase rate assumptions by 10%. Apply an estimated client attrition rate (e.g., 5-10% of clients might leave or reduce scope due to the price increase).
Model outputs:
- Revenue impact (net of attrition)
- Margin impact
- Per-client profitability change
- Break-even attrition rate (how many clients could you lose before the rate increase becomes net negative?)
Growth Investment Decisions
Question: "Should we invest $150,000 in a marketing program to generate enterprise leads?"
Model inputs: Add $150,000 in marketing spend (spread over the investment period). Estimate the number of leads, the conversion rate, and the average deal size. Apply a delay for pipeline development and close timing.
Model outputs:
- Monthly cash flow impact of the investment
- Expected revenue from resulting deals
- Payback period (months until the investment generates enough revenue to recover costs)
- ROI under base, upside, and downside scenarios
Client Concentration Decisions
Question: "Our largest client is 35% of revenue. What happens if they leave?"
Model inputs: Remove the client's revenue starting at a specific month. Keep all costs constant (you cannot downsize instantly).
Model outputs:
- Revenue gap created
- Cash runway at current burn rate
- How many months before cash reserves are depleted
- What cost reductions would be needed to stabilize
Common Financial Model Mistakes
Optimistic Utilization
New agencies often model utilization at 85-90%. Real utilization for established agencies is typically 70-80%. Model conservatively and be pleasantly surprised, rather than model optimistically and face a cash crisis.
Ignoring Ramp-Up Periods
New hires are not productive on day one. Model a 2-3 month ramp-up period where new delivery staff are at 25-50% of target utilization. This ramp-up cost is real and often forgotten.
Treating Revenue as Cash
Revenue recognized when work is performed is not the same as cash collected when the client pays. Always model cash flow based on collection patterns, not revenue timing.
Static Overhead
Overhead is not fixed. As you grow, overhead costs grow — more management, more tools, more office space, more administrative support. Model overhead as partially variable, increasing with headcount.
No Contingency
Every model should include a contingency buffer — typically 5-10% of projected costs. This covers the expenses that do not fit neatly into categories: unexpected legal fees, emergency equipment replacement, unplanned travel, and the many small costs that accumulate unpredictably.
Over-Complexity
A model that is too complex is a model that nobody uses. Start simple — revenue, costs, cash flow. Add complexity only when simpler approaches fail to answer the questions you need answered. A model you actually use to make decisions is infinitely more valuable than a beautiful but abandoned Excel masterpiece.
Maintaining Your Model
Monthly Actuals Update
Every month, enter actual financial results alongside projections. This creates a living comparison that reveals where your assumptions are accurate and where they need adjustment.
Quarterly Reforecast
Every quarter, update your assumptions based on the most recent actual performance and adjusted expectations. Reforecasting keeps the model relevant — a 12-month-old projection based on 12-month-old assumptions is fiction.
Annual Rebuild
Once a year, rebuild the model from scratch rather than continuously patching the existing one. Annual rebuilds incorporate structural changes (new service lines, different team structure, changed business model) that are difficult to patch into an existing model.
Tools for Financial Modeling
Google Sheets or Excel: The standard for most agency financial models. Flexible, powerful, and familiar. Google Sheets has the advantage of real-time collaboration and accessibility.
LivePlan: A guided financial planning tool that structures the model for you. Good for founders who are not spreadsheet-native.
Jirav or Mosaic: Financial planning and analysis (FP&A) platforms that connect to your accounting software and build models from your actual data. More powerful than spreadsheets but more complex and expensive.
For most AI agencies under $10M in revenue, a well-built Google Sheet is the right tool. It provides the flexibility to model your specific business without the cost and complexity of dedicated platforms.
Your Next Step
Open a new Google Sheet today and build the simplest version of your financial model. Start with three columns: Monthly Revenue (what you bill), Monthly Costs (what you spend), and Monthly Cash Balance (what you have). Fill in the last six months with actual data and project the next six months based on your current trajectory. This takes about two hours and gives you a forward-looking financial view that most agencies do not have. From there, add the assumptions tab — break revenue into rate times utilization times headcount, and break costs into delivery costs and overhead. Each layer of sophistication you add makes the model more useful for decision-making. But even the simplest version — revenue minus costs equals cash impact — is better than the gut feeling that most agency founders rely on today.