Retainer & Project Pipeline Forecast
This use case explains how to forecast agency or studio revenue from retainers and project work in Model Reef.
You will:
Represent retainers, projects and clients in the branch and category structure.
Build drivers for retainers, active projects and win rates by stage.
Model timing of revenue recognition and cash receipts.
Connect the pipeline to staffing, delivery and profitability views.
Model Reef is not a CRM or job management tool. It consumes high level pipeline and contract assumptions and converts them into financial statements, cashflows and valuations.
When to use this pattern
Use this pattern when:
Your agency or studio has a mix of retainers and project work.
The sales pipeline is material to future revenue.
You need to explain revenue shape and timing to owners, finance or investors.
You want to test utilisation, hiring and profitability under different win and pricing scenarios.
You can combine this with:
Staffing and Creative Resource Planning
Production Budgeting and Delivery Models
Multi Client Profitability
Build a Driver Based Forecast
Architecture overview
Set up branches for studios and key clients
In the branch tree, create a structure that matches how you manage the business, for example:
Agency Group
Studio - Brand and Creative
Studio - Digital and Product
Studio - Production
Key Client - Global FMCG A
Key Client - Tech B
Central Overheads
Optionally, give very large clients their own branches so you can see their P&L and capacity impact directly. Smaller clients can be grouped and tracked via categories or Data Library entries.
Create retainer revenue drivers
In the Data Library, create a table of retainers, including:
Client name or group.
Branch or studio that services them.
Retainer start and end dates.
Monthly or quarterly fee.
Fee indexation rate or scheduled increases.
Probability of renewal on expiry.
For each retainer, create a Revenue variable such as:
Revenue - Retainer - Client X - Studio Brand.
Use timing fields to set start and end dates. Use a simple formula or preset to apply:
Flat or escalated fee over the retainer term.
Gap periods for planned pauses if required.
Cash timing comes from payment terms and delay settings, for example 30 days or 60 days from invoice.
Create project pipeline stages and drivers
Define pipeline stages that reflect your sales process, for example:
Stage 1: Qualified.
Stage 2: Proposal.
Stage 3: Verbal.
Stage 4: Contracted.
In the Data Library, create series for each stage such as:
Pipeline Value - Stage 2 - Studio Brand.
Pipeline Value - Stage 3 - Studio Digital.
You can maintain these as:
Total expected contract value per stage per period, or
Number of deals and average deal size per stage.
For each stage, assign a win rate driver, for example 20 percent for Stage 2, 60 percent for Stage 3, 90 percent for Stage 4. Then compute weighted pipeline:
Weighted Pipeline = Stage Value multiplied by Win Rate.
This provides the expected value of work likely to be won in each period.
Translate pipeline into forecast projects
To convert pipeline into revenue, define drivers for:
Average project duration in months or weeks.
Revenue recognition pattern, for example even over duration, front loaded or milestone based.
Expected start lag from pipeline recognition to project commencement.
You can approximate this at aggregate level by:
Forecast Projects Won per Period = Weighted Pipeline Value divided by Average Project Value.
Revenue from Projects = Projects Won multiplied by Revenue per Project, spread over duration using schedules or presets.
If you want finer control, you can maintain a Data Library of named large projects with expected start dates, values and durations, then create Revenue variables per project.
Combine retainers and projects into total revenue
Total revenue per studio or client branch is the sum of:
Retainer Revenue variables.
Project Revenue variables derived from the pipeline.
Any production or media pass through where you choose to model this separately.
Use categories and sub categories to distinguish:
Retainer revenue.
Project revenue.
Pass through or third party costs if relevant.
Dashboards can then show revenue by type, client, studio and scenario.
Use scenarios for pricing, win rate and renewal risk
Clone the base model into scenario models to test:
Higher or lower win rates by stage.
Different pricing or fee structures for projects and retainers.
Retainer renewal and churn rates.
Delays in project start dates or extensions to delivery schedules.
In each scenario, adjust:
Win rate and pipeline value drivers.
Retainer fee and renewal assumptions.
Project duration and revenue recognition patterns.
Payment term delays and working capital settings.
Compare scenarios using:
Revenue growth and mix between retainer and project work.
Studio level utilisation when combined with staffing models.
Cash timing and funding needs.
Valuation outputs where relevant.
Check your work
Retainer and pipeline values reconcile with your CRM or sales reports where possible.
Revenue forecasts align with realistic delivery and staffing capacity.
Scenario outcomes are defensible when discussed with commercial and delivery teams.
The level of project detail is appropriate to your planning horizon and materiality.
Troubleshooting
Related guides
Last updated