Betere besluiten met focus op feiten

Closing the books faster with Data Analytics

on July 22, 2015 Uncategorized with 0 comments

Many organizations are looking for ways to speed up their monthly/quarterly/annual close cycle. This is often easier said than done. Because the close should not only be faster, the data should also be correct. How can this be done when a company has thousands of operators all across the globe that are responsible for entering millions of transactions? Data analytics can actually help speed up the close process of your company. Basically there are two ways: by fighting the symptoms or by solving the root cause.

Let’s look at solving the root cause first. It is all about finding those transactions that are not correct, not complete or not timely. And have operators correct those. One obvious thing to think about is adding a validation routine to the data entry screen of an operator. However, operational data is often known a lot earlier than reliable revenue information. So if you want your operators to put actual revenue data in your system, adding a financial validation algorithm may not bring you a lot further. A second option is to build in a check in the interface between your operational system and financial system such that only valid transactions can go through. But you can’t make the validation too strict, because exceptions do exist and you can’t afford having transactions hanging between two systems. A third way is to let suspect transactions run through to the financial systems and have business controllers act on transactions that don’t comply with certain criteria. This can be thought of as a kind of quarantining. You put those non-compliers in a transaction bucket that are monitored more closely by local and central management.



Suppose that the above is impossible to implement for whatever reason. You can then also create an (partially) estimated accrual based on historical data. That should be done on the detail level, so auditors will be able to validate what actually happens. In case you can prove to the auditors that the estimate based on historic data is actually correct, by reconciling estimates with actuals, you can also close books based on those estimates. Refer to the graph how that looked like in a case we worked on. The amounts have been changed.

Using data analytics as part of the month-end close

There are basically three processes that are used to make the actual estimations in each of the cases described above.

Process 1. Automated model generation

The first process is to create mathematical models that actually estimate revenues and costs based on historical data. Automating the model estimation process has advantages. The first advantage is that the costs of hiring expensive data scientists drop considerably. Secondly, the whole estimation process becomes auditable. Thirdly, multiple models can be used to perform estimates. For instance there can be a preferred model that is normally used, and a shadow model that is only used in specific cases where the former is less precise. Thus, a transaction can be looked at from different angles: a traditional econometric model and a data mining solution. The last advantage is in speed, with the current technology thousands of complex models can be calculated in less than an hour.

Process 2. Automatic forecasting of transactions

The second process is to use the models to estimate new transactions that come in. In this process, the computer rates new transactions. This can also be more advanced. We may estimate each transaction on multiple models at the same time. Let’s consider three:


Logical name


Revenue = 120% * Cost

In case there is not enough data to
estimate, we take the real costs as an estimator of revenue.

Cost + margin

We estimate the revenue by the sum of the actual
costs and the estimated margin.

Advanced modelling

We estimate revenue based on
operational properties of the transaction, using a sophisticated data mining algorithm
(e.g. CART or MARS).


Suppose a transaction has an actual revenue of $ 1,000. If all three methods show an estimated revenue of over $ 10,000 for that transaction, we will then be rather sure that we can earmark it to be put in quarantine.

If we were to pick one of the estimators as the best, we can build a decision tree to pick the best one based on characteristics of a transaction. The decision tree depends on the limitations and weaknesses that each model has.

Process 3: Remodel if estimates get off

Suppose the market conditions for a specific region and/or product change. Typical changes can be in exchange rates, seasonal effects, new competitors, for instance. In this situation, the variances between actuals and estimates will increase. If such variances persist for a period longer than two weeks, this is a signal that there are changes going on. The automatic forecasting model (process 2) can then trigger the model generation process (process 1) to prepare new models for the affected cases.

Additional benefits

Using these techniques helps in many other areas:

  1. Revenue managers get real-time insights in changing market conditions and can act on it. Without advanced tools, it can take months to become aware of such issues.
  2. Rules of thumb used by field experts can be used to make the models better. Inversely, the models can be used to refresh rules of thumb in the organization.
  3. The employees doing the work are empowered to work on data quality, an essential skill in this era of big data.


Is this science fiction?

No it is not. We designed a system that generates 12,000 models automatically to estimate the revenue for a transportation company. We estimated the revenue on day 2 of the close and compared this with the actuals 40 days after the close. They were off by less than 0,2%.
Our work will become better maintainable with SQL Server 2016 going live. Microsoft acquired Revolution Analytics and is now incorporating R into SQL Server. These are precisely the tools we use in our work.


Add comment

CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Decisive Facts