Demo: Financial forecasting in a business intelligence project
About this demo
This demo shows how to use SQL Spreads and Excel to create a solution where end users enter forecasting data into a data warehouse.
The user selects for which Month, Company and Region to enter the forecasting data. The Excel spreadsheet is then pre-loaded with data from the data warehouse displaying Budgets, Actuals, and previously entered forecast. The user updates the forecasts and saves the data which is stored in a forecasting table in SQL Server.
- Basic Budget/Forecasting data collection in Business Intelligence/Data Warehouse project
Key Features Used
- Database Import Filters for selecting data (Month, Company and Region).
- Use of a customer Import query to also import eg actual values from a data warehouse or another source table.
- Formula Columns to store a calculated value from an Excel formula into the database
- Validation of data entered into Excel against SQL Server data types.
- Tracking of the time when a row was changed and which user changed the row.
- Conflict Detection to prevent overwriting of changes when several users are working with the same data.
Downloadable Demo files
Installing the demo
The database contains data for all SQL Spreads demos, so you only have to install the demo database once.
- Download and install the SQL Spreads Excel Add-In.
- Download the demo zip-file and copy the folder inside the zip file to your desktop.
- Install the database by opening the "SQL Spreads Demo database.sql" file in SQL Server Management Studio and executing the query.
- Open the demo's Excel file in Excel.
- The demo is set up to use a Microsoft SQL Server on your local machine (localhost). If you see a message that SQL Spreads cannot load data, you must update the connection to the SQL Server where you installed the demo database by clicking the Database Connection button in the SQL Spreads tab in Excel.
Click the Refresh button in the SQL Spreads panel to load the data from the database into Excel.
Click the Save button in the SQL Spreads panel to write any changes you make in the data in Excel back to the database.