Building a Revenue Forecasting Tool in Quickbase
Summary
Revenue forecasting often breaks down when it doesn’t reflect how revenue actually occurs. Many tools assume lump-sum revenue or rigid rules that don’t work well for projects spanning multiple months or years.
We built a flexible revenue forecasting tool in Quickbase to address this and wanted to share the pattern since it’s reusable across many forecasting scenarios.
The Challenge
Many forecasting approaches assume:
- Revenue can be treated as a single amount
- Projects don’t span months or fiscal years
- Forecast logic is difficult to adapt
What we needed instead:
- Revenue spread across start and end dates
- Clear month-by-month visibility
- Support for multi-year work
- A forecast that updates automatically
When this isn’t supported, forecasting usually ends up in spreadsheets, creating manual effort and risk.
The Approach
Rather than relying on static reports, the approach was to:
- Store forecast data as its own records
- Only generate forecasts when revenue is likely
- Distribute revenue across the months when work is expected
- Let Quickbase handle recalculations
This keeps forecasting system-driven instead of manual.
How It Works
1. Forecast Triggers
Forecast records are created once an Opportunity reaches a defined confidence threshold.
- Keeps early-stage work out of the forecast
- Focuses reporting on realistic revenue
2. Monthly Distribution
A pipeline:
- Reads anticipated start and completion dates from the Opportunity
- Loops through a formula field that identifies which months and years are contained between the start and completion dates
Spreads revenue across the applicable months
In our current model, revenue is spread evenly across the duration, but the logic can be tailored for front-loaded, back-loaded, or milestone-based distributions as needed.
If work crosses a fiscal year, separate records are created per year to keep reporting clean. Revenue does not need to be evenly spread, sometimes
3. Monthly Records vs. Monthly Fields
There are two common ways to model monthly forecasting:
- One record per month (traditional approach)
- One record per year with monthly fields
We use monthly fields so all values for the same year live on one record, making it easier to compare months and make adjustments without working through a list of records.
4. Staying Current
- Changes made on the parent Opportunity (dates or expected revenue) automatically recalculate the related forecast records
- When an Opportunity is marked Closed Won or Closed Lost, its forecast records are removed
- The forecast always reflects pending, expected revenue
Why This Works Well
This pattern:
- Eliminates spreadsheet-based forecasting
- Matches revenue timing more closely to reality
- Keeps forecasts easy to view and adjust
- Scales as complexity grows
Reusing the Pattern
This works anytime:
- Revenue needs to be forecasted over time
- Work spans multiple months or years
- Forecast logic needs flexibility
All you need is:
- A clear trigger for when revenue should be forecasted
- Start and end dates
- Pipelines to generate and maintain forecast records
Alistair Marsden | Solutions Consultant