Build a Unified Forecast from Multiple Inputs

This guide shows how to use Model Reef to combine multiple data sources into a single model. Typical sources include PDF or Excel management packs, accounting actuals from Xero or QuickBooks, CSV based data and stock ticker fundamentals.

Before you start

Collect:

• Any management packs or board packs in PDF or Excel format.

• Access to Xero or QuickBooks if you plan to import actuals.

• Any CSV data you want to integrate.

• Any stock tickers whose fundamentals you want to model.

What you will build

• A model with a coherent branch structure.

• Imported historicals from one or more sources.

• Data Library entries representing each imported series.

• Variables that blend imported history with driver based forecasts.

• Unified statements, Cash Waterfall and valuation outputs.

1

Set up the model and structure

• Create a new model with:

  • Currency.

  • Base periodicity.

  • Start and end dates.

• Create branches for:

  • Business units or entities.

  • Any special segments (for example Online, Retail, Projects).

This gives you a target structure for imported data.

2

Import historicals from PDF or Excel

• Use the PDF or Excel import tool.

• Upload management packs, financial statements or trial balances.

• Let Model Reef detect:

  • Tables and date columns.

  • Units and contextual labels.

• On the mapping screen:

  • Map each detected line to:

    • Variable type (Revenue, COGS, Opex, Staff, Asset, Liability, Equity).

    • Category and sub category.

    • Branch.

    • Units and frequency.

• Confirm and import.

This creates Data Library entries and variables populated with historical series.

3

Import accounting actuals from Xero or QuickBooks

• Connect to your accounting system.

• Import:

  • Chart of Accounts.

  • Historical account balances by period.

• Map accounts to:

  • Variable types.

  • Categories and branches.

• Import and allow Model Reef to:

  • Create Data Library entries.

  • Create or update variables.

These series can complement or replace PDF or Excel based imports.

4

Import CSV or external data

For CSV or other external series:

• Use CSV or manual import to create Data Library entries:

  • Macro drivers.

  • Operational series (for example traffic, leads, utilisation).

  • External indices.

• Assign:

  • Name.

  • Type (Driver, Modifier or Custom).

  • Units and frequency.

• Use these entries to drive variables via formulas.

5

Import stock ticker fundamentals

If relevant:

• Use the Stock Ticker import.

• Enter ticker symbols.

• Import historical fundamentals such as:

  • Revenue.

  • COGS.

  • Opex.

  • EBITDA.

These become Data Library entries for external benchmarks or drivers.

6

Blend historicals and forecasts

For each major line:

• Use imported series for historical periods:

  • Mark them as historical in the Data Library where appropriate.

• For future periods:

  • Apply:

    • Growth drivers.

    • Regression based formulas.

    • ML forecasts.

• Ensure that:

  • Forecasts pick up at the end of historical periods.

  • Historical actuals are left untouched.

This creates smooth transitions from historical data to modelled forecasts.

7

Reconcile and review unified statements

• Review P&L:

  • Confirm historical periods match source financials.

  • Ensure forecast periods evolve as expected.

• Review Balance Sheet:

  • Check opening balances imported correctly.

  • Confirm Assets = Liabilities + Equity.

• Review Cashflow and Cash Waterfall:

  • Ensure cash patterns reconcile with historic cash movements where available.

Check your work

• All important data sources are mapped into branches and categories.

• Historical periods in P&L and Balance Sheet match original sources.

• Forecast periods are clearly driven by explicit drivers, not random extrapolation.

• The Cash Waterfall and Cashflow Statement reconcile.

Troubleshooting

Duplicated lines or double counting

Ensure you are not mapping the same source line into multiple variables or categories.

Gaps between historical and forecast periods

Check model start and end dates and the range of imported series.

Inconsistent figures across sources

Decide a priority order (for example accounting system over PDF) and align mapping accordingly.

Related guides

• Capital Projects & Infrastructure

• Money Multiple Calculation

• Historical Fundamentals Imported

• Operational Driver Fields