How to Create Trend Analysis, Forecasting & Modelling using SolveXia

Regardless of the industry in which your business operates, the ability to implement data forecasting and modelling using historical data provides you with a competitive advantage, the ability to mitigate risk, capture trends and insights to make better business decisions. 

Within financial departments, financial forecasts help to prepare for the future and leverage a company's financial statements to build predictions for the future. Financial modelling is where a company can link variables together to create its financial representation, thereby using this visual representation to inform business decisions. 

For many companies, the process of data aggregation, transformation, processing, analysis and reporting is a long and tedious task that is often performed manually. But it doesn't have to be. In fact, by using automation software, you can increase your company's efficiency, accuracy and alleviate the burden on your employees of such repetitive tasks. 

SolveXia's human analytical automation platform can perform all the steps to make forecasting and modelling seamless and accurate within your business. Here, we will take a deep dive into the technicalities behind how forecasting and modelling work within the easy-to-use system. 

Coming Up

How Forecasting and Modelling Works

1 - Ingestion, Transformation and Validation of Source Data

2 - Data Storage for Modelling 

3 - Create and Run Forecasting and Prediction Models

4 - Reporting and Analysis

5 -  Model Validation 

The Bottom Line

How Forecasting and Modelling Works 

The process of modelling and forecasting can be broken down as follows:

While the method above is a high level, each piece of the puzzle provides a crucial step to ensure that the outcome provides accurate and useful information. 

Let's break it down. 

1 - Ingestion, Transformation and Validation of Source Data

The first step of any modelling exercise is to gather and prepare your data. This could include retrieving files from share drives, running extracts from core systems (like SAP) etc. Staff then often need to make changes to the data, which may include: 

• Fixing and cleaning erroneous data - e.g. invalid postcodes
• Enriching data - e.g. looking up a product's full name based on a product code
• Appending, filtering and aggregating information
• Calculating values such as the customer's age based on their date of birth

These changes are vital as the modelling itself expects specific data to function correctly. Staff often perform these manipulations manually in spreadsheets, which can be timely and error-prone. Some team may write scripts and macros, which requires advanced skills. This is a significant step because every subsequent step in the process relies on the accuracy of this data. SolveXia data cleansing can eliminate this human error by automating these processes through the use of data robots that work based on simple or complex rules or patterns.

SolveXia can quickly ingest, extract the data from any file format, including Text/CSV, Excel, Access databases, Word Documents and PDF files. Or, files and data can be input directly by users or collected automatically through Secure FTP and the use of APIs. 

Once data has been captured by SolveXia, our 100+ data instructions are designed to manipulate, cleanse, validate and map your data in seconds to minutes (instead of hours or days) and without the need for any team member to have advanced coding or macro skills. In addition to being fast, this also ensures consistency in data preparation, thereby reducing the risk of errors that would break the modelling. By limiting mistakes in this step, your business can avoid having to rework the same process over and over until it's accurate.  

There are many ways to cleanse data. For example, as pictured in this step, the system will extract all of the text that appears after the "@" symbol in the list of email addresses. You can change the character to a dash for phone numbers, remove spaces, remove the start of codes; the possibilities are truly endless.

2 - Data Storage for Modelling 

To use data for modelling, the collected data that has been manipulated and cleansed (previous step) needs to be stored. This can include just the information being worked on presently or a build-up of data over time. For example, it is common for Finance staff to build up their data over time (e.g. month by month). Most forecasting is done through some form of regression analysis (e.g. Linear) which requires historical time series data to "plot a curve" that can then be used to project forward. 

Finance staff often lack direct access to enterprise data storage tools (like a SQL Server or a data warehouse). Thus, to store their data for modelling, they resort to keeping spreadsheets that grow over time to 100k to 1m records. Or, staff may also use Access databases. 

SolveXia's managed table feature gives Finance users a simple interface to create and store their data safely in our SQL Server Database. This forms a secure and robust "well" of data that can easily save millions, even 10's of millions of records over time. The best part is, it can all be live data. 

With all historical data and action saved, SolveXia creates deep audit trails and version control to enhance compliance and enable regulators and managers to track and lock data and assign permissions. This allows for quicker and easier responses to auditors in their data requests.

Managed tables are easy to create and maintain. It is also incredibly easy to get data in and out of these tables - using the library of drag-and-drop instructions. For example, users can add a step to their process to copy their cleansed data from a spreadsheet into their managed table. The data from managed tables is accessible by all of the different functions and capabilities of SolveXia, including for use in modelling (see next point).

3 - Create and Run Forecasting and Prediction Models 

Here comes the good stuff! 

With data cleansed and stored, staff can then run their models. This is often done by:

• Writing and executing code in languages such as Python and R
• Writing and executing macro scripts
• Specialist modelling platforms like SAS
• Actuarial modelling systems (Life insurance specific) like Prophet and MoSes

However, depending on your business' resources, creating such labour intensive prediction models can be costly, timely and undoubtedly requires hard skills. If you already utilise third-party modelling systems, you can connect them to SolveXia through APIs. The system can collect results and create reports, thereby saving you time, money and provide deeper insights.

SolveXia can feed data into and run your existing models. This includes running Python, R and Macro scripts directly as a step within a process. SolveXia also has specific modelling instructions like "Run Calculation Model" that can follow a looping pattern by inserting a data set into an Excel workbook, recalculating the workbook and then extracting and aggregating the results (over-and-over again for however many data sets need to be run through the model). 

You can equip your business with the ability to forecast data such as future revenue, expected interest rates, and how the changing marketplace will impact your business. With this data and the understanding of past trends, you can make informed decisions on how to act now to reduce risk in the future. 

4 - Reporting and Analysis

The primary outcome of most modelling tasks is the creation of a report or some analysis. Staff need to collect results from the model after it has been run, perform their analysis and work the findings and insights into a report for the management or other teams outside of finance. 

This is a sweet spot for SolveXia as the system is designed to produce reports and analysis in any format, including:

• Excel reports and dashboards
• PDF documents
• Word documents
• Online dashboards and analytics

Outputs can include data, tables, charts and graphs. Outputs can even be sent directly to the final stakeholders straight from SolveXia or an ad-hoc or regular basis. End-users can utilise SolveXia's built-in dashboard capabilities to drill-down into the results from the modelling. You can also quickly customise your report with the easy-to-use drag and drop system. As you make your selections, the data will be shown in real-time in your chosen format. 

Gone are the days when your data and insights are fragmented. The challenge of understanding business insights and making important business decisions based on information from different departments and reports is a practice of the past. With SolveXia, your data comes together in one place and in the format that fits your needs. Plus, you can easily share the information with those who need it most automatically. These little details won't go unnoticed, and it will alleviate your burden of having to remember to share the reports. 

5 -  Model Validation 

When doing forecasting or predicting of any kind, staff must check the predictions against actuals regularly. This is called a "control cycle." 

To review the experience from a forecast, staff must often source and prepare "actuals" for comparison. For example, comparing actual sales in a given month against the estimates for the same month, validation is required to analyse the accuracy of the forecast and check for significant deviations. By doing this, staff can tweak and optimise the models themselves, to improve their predictions with each cycle. 

The Control Cycle is often ignored because staff don't have the time to do it - they are too busy with business as usual or doing all the manual work to run the model in the first place. But, it's an important step to make sure that forecasts are adequately prepared and can be useful, rather than potentially harmful. 

With SolveXia, the control cycle can be automated, meaning it can be performed more frequently. The reason for this is that SolveXia can handle all of the data ingestion and preparation needed to do the control checks quickly and easily. Therefore, the control checks are performed, and they are done so at regular intervals (e.g. monthly or quarterly). 

SolveXia can also be set up to set alerts when significant deviations occur, e.g. actuals, are very different from what was forecast. This allows the business to react sooner and recalibrate their models, rather than blindly following incorrect predictions (which could ultimately lead to a significant underperformance for the company).

The Bottom Line 

The most important and impactful outcome of data forecasting and modelling results in better decision making. Using historical data to predict the future can help a business in the present moment to make better decisions to optimise its future, mitigate risk, and improve performance. 

By using SolveXia, every step of forecasting and modelling can be automatically handled by the platform, which will free up time for your staff to perform the tasks they were hired for. Importantly, this allows them to focus on the human analytical aspects of their job that machines can't achieve.