Financial forecasting in a Business Intelligence project

Prev Next

About this demo

This demo shows how to use SQL Spreads and Excel to create a solution where end users can enter and update forecasting data in Excel, with the data stored in SQL Server tables. The data can then be used in a BI solution and loaded into a data warehouse or shown in Power BI.

The user selects the Month, Region, and Company for which they want to enter forecasting data. The Excel spreadsheet is then pre-loaded with data that can include previous forecasts, budgets, or actuals. The user updates the forecast and saves the data, which is stored in a forecasting table in SQL Server.

Scenarios:

  • Basic Budget/Forecasting data collection in Business Intelligence/Data Warehouse project

Demo video

Download Demo files

This demo zip file contains the script to create the database with the demo data and two Excel files for working with the demo:

  • The Forecast Entry workbook - the workbook where users enter and update the forecasting data
  • Forecast Management workbook - the workbook used to manage the forecast process.

Installing the demo

Please follow these instructions to install the demo:

  1. Start by creating a new database named SQLSpreads_ForecastDemo2025.
  2. Then run the ForecastDemo2025DBv2512 script to create the demo database.
  3. Once installed, open the Forecast Management sheet.
  4. By default, the documents are connected to a database on localhost. If your SQL Server instance has another name, go into the Connection settings and change it to the server where you installed the demo database.
    Image

The Forecast Entry workbook

This is the sheet where the end users enter and update their forecasts.

Below is information about this document and how different features are implemented.

Selecting the forecast

Here we use hierarchical Tree Filters in SQL Spreads, shown on the left side, to select which Forecast, Region, and Company to enter data for.
Image

To pre-load a new forecast with data, we use a Data Processing script in SQL Spreads. When the user selects a forecast where no data yet exists, the script checks the database for any previous forecast. If one exists, it copies the data from that earlier forecast and inserts it as the starting point for the newly selected forecast.
Image

Pivoted view

The forecast sheet use a pivoted view in SQL Spreads to pivot the 12 monthly rows from the database into 12 spreadsheet columns.

Workflow with Commit and Approve

The data entry sheet includes a workflow where users can Commit their entries and a manager can Approve them.
Image

The workflow is implemented using Data Processing scripts in SQL Spreads.
Image

We’ve also added a Data Processing script that prevents users from modifying committed or approved forecasts.
Image

Locking the columns for the past months

Past months are locked using using the Dynamic Column locking.
The formula that calculates which columns to lock is stored on the second sheet, FCSupportSheet.
Image

Showing the selected forecast in the spreadsheet.

To show details about the selected forecast, the document uses a custom import to display the currently selected forecast, region, and company.

This data is imported into the FCSupportSheet worksheet using a database import in the the Advanced Setup in SQL Spread.
Image

Use of Stored Procedures

For the SQL queries in this spreadsheet, we use Stored Procedures in SQL Server and call them from SQL Spreads, as this makes the SQL easier to maintain and test.
Image
Some procedures - such as those used for tree filters - are a bit longer, so keeping them in SQL Server makes it easy to test them in Management Studio to ensure everything works as expected.
Image

Forecast Management

The Forecast Management workbook controls:

  • The different forecast versions
  • Access rights for users entering data and for managers approving data
  • The workflow when a forecast is started, committed, and approved for a specific unit

Forecast Versions

In this sheet, you can open and close forecasts to determine whether they should appear in the tree filter for users. You can also commit and approve the entire forecast at the version level.
Image

Forecast Access

In this sheet, you manage which users have access to different forecasts.

You can set up:

  • The unit manager who enters the forecast data
  • An interim manager who can act as a stand-in
  • An approver who approves the forecast

Image

Forecast Workflow

In this sheet, you can follow the full forecast workflow.

As soon as a user starts working with a new version, a new row is added to the workflow table. You can then see when the user saves the forecast, when it is committed, and when it is approved.

You can also use this sheet to manually Commit or Approve a forecast, or to remove a commit or approval.
Image