How to automate mortgage budgeting and forecasting

Build the lending forecast from approved funded-loan counts, revenue per funded loan, direct production cost, and overhead. Lock the original budget as a comparison, load signed actuals by period, and publish a new forecast version with a bridge that explains every material change.

Use the guide for a monthly lender operating outlook. It begins with accepted finance actuals and approved production inputs. It ends with a reviewed forecast for future periods, a same-period comparison to budget, and a list of open assumptions with named owners.

Map the forecast cycle

What to automate

Load signed close and funded-loan production data

At the start of the forecast cycle, ingest signed ledger actuals, accepted production counts, and the prior approved outlook. Align entity, fiscal period, product, and channel to the planning calendar and mapping. Compare period totals to the signed close and funded counts to their production control. Route mismatched periods, entities, or groups to FP&A with both source references. Freeze the accepted baseline as a versioned input for future runs.

Version and approve operating assumptions

Gather volume, unit revenue, direct variable cost, and overhead drivers from the responsible operating owners. Store each driver with its source, scope, effective period, scenario, owner, and approval state. Compare proposed assumptions with the prior outlook and put overlapping, expired, or unapproved values into a review register. Keep pending inputs in a draft scenario while the finance lead reviews the approved forecast changes.

Build the monthly outlook by product and channel

Join each future period and loan group to the approved funded volume and per-loan drivers. Multiply volumes by revenue and direct variable costs, then apply fixed and step costs on their approved effective basis. Preserve signed actual periods and keep them visible alongside forecast periods. Hold official output if an approved driver is missing and make the gap easy for FP&A to trace. Check period and entity rollups against the underlying model grain.

Explain contribution changes with a reproducible variance bridge

Join the budget and current forecast at a consistent period, entity, product, and channel grain. Calculate volume, mix, unit economics, and overhead effects in Finance’s documented order, then add the effects back to the total contribution change. Handle new products and missing budget rows with the approved convention or a review item. Send unexplained residuals to FP&A and link each displayed effect to its driver versions.

Approve and publish a reproducible forecast

Give the finance lead a review workbook or governed dataset containing actuals, budget, forecast, driver changes, and open items. Include the scenario version, input cutoff, reviewer, and recorded approval. Hold the official release when the close is incomplete or a material assumption is pending and show that status in the review copy. Archive each accepted forecast and its inputs so anyone with access can reproduce the month before later revisions.

Design the model around the funded-loan grain

Use one planning row per legal entity, period, channel, product, scenario, and version. Preserve actual, original budget, and current forecast as distinct scenarios. An actual already accepted by Finance should keep its signed value when later periods are recalculated. Oracle Planning documents this kind of scenario/version separation and forecast preparation from selected actual periods. Oracle planning versions · Oracle forecast preparation

For a loan group and period, multiply funded count by the approved revenue per loan to forecast production revenue. Multiply funded count by the direct variable cost per loan to forecast variable production costs. Model fixed overhead and known step costs separately, then subtract the costs to calculate the forecast contribution. Preserve the income and cost definitions used by the closed-month profitability report so the bridge can explain differences in operating mix or accounting basis.

Keep expected production from Capital Markets separate from Finance's approved plan until an owner accepts it. Code should calculate the forecast and check the bridge. An assistant can draft variance commentary from verified changes and cite the affected driver rows. Finance owns the plan, assumption changes, and approval to publish.

Reconcile a synthetic change to the budget

Assume the budget calls for 100 funded loans at $10,000 revenue and $7,000 variable cost per loan, with $100,000 fixed overhead. The latest forecast expects 90 loans at $10,200 revenue and $7,100 variable cost per loan, with fixed overhead unchanged.

The contribution declined by $21,000. Apply the volume change first at the original $3,000 unit contribution, for a $30,000 decline. Apply the $100 change in unit contribution to the 90 forecast loans, for a $9,000 offset. The two effects reconcile to the $21,000 net decline. For several product groups, use a fixed order for volume, mix, and rate. Document the chosen order so another analyst can reproduce the bridge.

Test the published forecast

Replay one accepted month against the current process. Seed a late actual, a duplicate funded loan, a volume change, and an unapproved driver. Confirm that the original budget and signed actuals stay reproducible, and that a draft assumption cannot appear as approved. Track forecast error by horizon, active preparation and review minutes, publication delay, and the share of material variances that can be traced to an accepted driver. Keep missing and late inputs visible in the run record.

Implement a versioned monthly driver model

1. Define the close and planning grains. Use signed actuals at entity × fiscal_period × account and production data at entity × fiscal_period × product × channel. Reconcile production count and ledger totals independently. Never expand an entity-level overhead account across all channel rows without an approved allocation.
2. Load assumptions as records. Store effective_from, effective_to, segment, driver, unit, value, scenario, version, source_ref, owner, approved_at. Use a named hierarchy for segment defaults and expose each fallback. Lock closed months to actuals unless a separately authorized restatement exists.
3. Calculate the open months. Revenue and variable expense are funded volume times the approved unit values. Fixed overhead follows the owned calendar. Step costs, such as an additional shift, get their own explicit threshold, effective date and scenario.
4. Bridge budget to forecast in a fixed order. For a single segment, apply volume at the budget unit margin, then apply unit margin at forecast volume, then apply the difference in overhead. For multiple segments, separate mix by applying budget segment weights first and current segment weights second. Reconcile to the overall change before any commentary is generated.
5. Publish with provenance. Require totals, tied variance bridge, open assumption exceptions, and finance approval. Hash or version the manifest for each signed run. Preserve both plan versions when a driver changes after publication.

Reproduce the contribution bridge

budget_volume, forecast_volume = 1_000, 900
budget_unit_margin, forecast_unit_margin = 1_000 - 700, 1_020 - 710
budget_overhead, forecast_overhead = 100_000, 100_000

budget = budget_volume * budget_unit_margin - budget_overhead
forecast = forecast_volume * forecast_unit_margin - forecast_overhead
volume = (forecast_volume - budget_volume) * budget_unit_margin
unit_margin = forecast_volume * (forecast_unit_margin - budget_unit_margin)
overhead = budget_overhead - forecast_overhead

assert (budget, forecast) == (200_000, 179_000)
assert (volume, unit_margin, overhead) == (-30_000, 9_000, 0)
assert volume + unit_margin + overhead == forecast - budget

Use the model in parallel through at least one close. Compare input availability, close-to-publish elapsed time, actuals reconciliation, missing assumptions and revisions after signoff. A forecast can be technically reproducible while an assumption still needs approval.

Implement the work step by step

Build from signed actuals and approved volume. Version the baseline, scenarios and assumptions independently. Publish after the bridge accounts for all movement at the same period and reporting grain.

FA-08 · Budget, forecast and explain variances

• FA-08.01 Tie signed actuals and funded-volume inputs to comparable periods. Join signed actuals and accepted volume by entity, period, product and channel. Reconcile source totals. Read Signed close, approved funded volume, budget and versioned drivers. Record Comparable planning baseline. Owner Finance lead approves assumptions, report, and any restatement. Check Seed a mismatched cutoff and leave the original actuals intact.
• FA-08.02 Version scenario assumptions and unit drivers. Version each scenario driver, its effective dates, source, owner and approval state. Read Signed close, approved funded volume, budget and versioned drivers. Record Reviewable scenario register. Owner Finance lead approves assumptions, report, and any restatement. Check An unapproved assumption remains draft.
• FA-08.03 Forecast revenue, cost and profit. Calculate revenue and variable costs from funded counts and approved drivers. Retain fixed and step costs separately. Read Signed close, approved funded volume, budget and versioned drivers. Record Period forecast by product and channel. Owner Finance lead approves assumptions, report, and any restatement. Check Replay the synthetic example to $918k revenue and $179k contribution.
• FA-08.04 Bridge volume, mix, rate and expense differences. Build a same-period variance bridge using the agreed volume/mix/rate/overhead order. Read Signed close, approved funded volume, budget and versioned drivers. Record Reconciled contribution variance. Owner Finance lead approves assumptions, report, and any restatement. Check Volume effect -$30k plus unit contribution +$9k ties to -$21k.
• FA-08.05 Review and publish. Require owner review. Retain published version, cutoff and changes. Read Signed close, approved funded volume, budget and versioned drivers. Record Accepted forecast and open assumptions. Owner Finance lead approves assumptions, report, and any restatement. Check Retry yields one publication and preserves the approved original budget.

Build it yourself with AI

Model one forecast period in a workbook or local script

Start with actuals.csv, budget.csv, forecast_drivers.csv, and a planning_rules.md approved by FP&A. Define the fiscal periods, model grain, locked actuals, required driver approvals, and the bridge order. You can build the same prototype in a spreadsheet with protected inputs or a local Python script. Use a fabricated month and assign a stable ID to each driver row.

Prompt 1. Map the model and its approval points

Read the file headers and planning_rules.md. Propose input and output tables for a model by entity, fiscal period, product, channel, and scenario version. Keep actual, budget, draft, and approved forecast separate. Identify missing, overlapping, or unapproved assumptions and show which owner should resolve each. Ask FP&A about any undefined bridge rule.

Prompt 2. Create the forecast and reconcile the bridge

Implement workbook formulas or a local Python model that calculates revenue, variable costs, fixed and step costs, and contribution. Freeze signed actual periods. Build the volume, mix, unit economics, and overhead bridge in the supplied order and assert its effects add up to the contribution variance. Return a formula map or script, fixture outputs, and unresolved driver IDs.

Prompt 3. Draft review notes and a publication checklist

Write short variance commentary based only on reconciled bridge rows and cite the underlying driver IDs. Return a signoff checklist covering baseline totals, scenario approval, the bridge tie-out, and the input versions to archive. Mark unknown causes as questions for the named owner. Leave official publication to Finance.

Try it. Use a budget of 100 funded loans at $10,000 revenue and $7,000 variable cost per loan and a forecast of 90 at $10,200 and $7,100. Give both scenarios $100,000 of fixed overhead. Expect contribution to move from $200,000 to $179,000. The volume effect is negative $30,000 and the unit contribution effect is positive $9,000. Remove one approved driver in a second fixture and verify that publication is held. Compare the model and review checklist with one accepted forecast cycle before adopting it.

Need help automating this?

See how Mage helps lenders connect closed actuals, production volume and operating assumptions to a recurring forecast. See Mage's mortgage budgeting and forecasting solution.