Automation and optimization of State of income forecasting

Inmobilia is a real state company with 35+ projects done in the center region of Chile.

The financial and accounting area presented a requirement to optimize and upgrade their forecast system of their statement of income.

The actual forecast process system was built in Microsoft Excel with a lot of calculated fields and formulas that linked different sheets and workbooks. The main workbook had more than 10 queries to a SQL Database and used the data combined with a lot of dictionaries and reference tables that were on the same workbook. A lot of tweaks were needed to adjust some parameters and update the forecasted statement of income.

In the actual forecaster made in Excel, the client has a sheet with some parameters to adjust and then the workbook and calculated fields would take those parameters and give different values.

I presented the following flowchart that draws a solution to optimize and upgrade the current model using a friendly front-end Excel workbook to modify parameters, Stored Procedures written in T-SQL to automate the calculations and Power BI for visualizations.

If we see the solution in a IPO (Input, Process, Output) layout:

a) Take parametric tables and data in different Excel workbooks and build a process to automate the batch loading of .CSVs from a repository (system's folder) into a SQL Server using T-SQL.

b) Extract accounting system's data corresponding to the past and present accounting movements and load them to the SQL Server.

c) Validate input data and if anything fails send a mail warning.

a) Develop a front end user interface to modify parameters of the process.

b) Develop multiple Stored Procedures written in T-SQL to automate forecast calculations.

c) Develop multiple Stored Procedures to automate the present and past accounting movements.

a) Develop visualizations of the statement of income for past, present and future with filters and different views.

The front-end Excel tool that I developed allows the user to:

Create an unique escenario to modify

Select a previous escenario to continue modifications

Visualize the information of any project in the escenario. (ID of the project, Name, Size, Status, Terrain Value, Milestones Dates, etc)

Edit the information of any project

Execute the Forecasting calculations on the selected escenario

Create N new Projects

Delete any project

Execute inputs update

etc.

To visualize the statement of income the Power BI views allows to filter any information and see data in a table or graphs:

Any further doubt don't hesitate in contacting.

Regards.