How we rewrote bad forecasting for slightly better

Each company is not a stellar technology and super cool programmers, but a huge mountain of bottleneck, inefficiencies and a sum of bad decisions that somehow and does their work. But now you decided to make some changes and immediately start to face the fact that you have problems in a huge number of business processes. Well, these problems, of course, need to be addressed not in an ideal way, but in terms of labor costs.

I want to share one such example related to my topic of data analysis and data management. In many organizations there are financial services, the main purpose of which is to provide financial information to management about the state of the company. Among the many works of these people there is one such task: making a forecast of revenue for the next period (year, quarter, someone else). This forecast of revenue is often the first stages in coordinating plans for the next period and drawing up a general forecast on the profits and losses of the enterprise.

All those involved in this kind of forecasting understand that in this matter it’s not so much the accuracy of the forecasts as the correct relationships between your premises and the results. After all, what do we want from the forecast? We want to know what will happen if we do everything as usual (AS IS) and what will happen if we change something (scripts). In order to make this work, the financial service must come up with some kind of enterprise model that it can easily manage, easily explain to the business how it works, and easily provide data in various sections in which the business wants to look at it.

These are all excellent intentions, but here we are faced with a harsh reality: the methodological and technical skills to perform these tasks in specific enterprises are frankly weak. Models are inconvenient, not quickly changed, not updated, nothing is easily explained, files are not convenient, and it is impossible to obtain cuts or for a very long time. Let's look at a specific example of where everything is bad and how to fix it.

Where will the typical finance officer build the model? Of course in Excel. There are several good reasons for this:

1. Experience is only with this tool.
2. The result of the work is easy to transfer to the customer.
3. You can change any part in the model.
4. Excel quite simply integrates the acquisition, storage, processing, prediction, presentation and visualization of data.
5. You can make it so that consumers of your work can easily understand what you gave them.
6. Allows you to organize a simple interactive and work with the model.

One of the simplest and one of the most common revenue forecasting models is a very simple formula: the number of clients per period * average check = revenue.
What was before us looked like this (the data, of course, replaced by a demo):

What problems are on this sheet:

1. Baseline data for 2017 and 2018 are entered as values. It is understood that these values ​​will be directly corrected to implement different budget versions. This is a large amount of work (since there are about 30 such sheets in various departments), in which errors will probably be made, columns and columns are confused.
2. Although the year 2018 is entered as a value, it has not yet ended, therefore, a value is entered there, taking into account the forecast, which is made somewhere separately.
3. The forecast is made for the whole year. Among the requirements for forecasting was to make a monthly forecast on the one hand, and on the other hand to display it in annual terms, because the monthly forecast is difficult to analyze and evaluate because of the abundance of numbers. Accordingly, one of the difficulties for this model is to build on top of another model that breaks up the data by months, taking into account seasonality. Everything is "backwards", not an annual result from the monthly, but monthly from the annual.
4. The baseline data for the model does not appear clearly anywhere, there are no references to them, and it is not clear whether the figures are correct, what we see, whether they are correctly extracted from the repository and summarized.
5. If there is a desire to re-group the departments, this model will be completely thrown out.
6. Next year there is little that can be reused.

This sheet is a forecast for one of the divisions of the enterprise. There are about 30 such sheets in total. All these sheets are combined into a sheet of the whole enterprise in two sections: by division and by types of departments. Roughly speaking, in each unit you have a department for the production of packaging. You would like to see the general result by divisions, and separately the general result broken down by different specializations, such as the production of packaging. The sheet looks conceptually the same, but it is the sum of the results on the last 30 sheets.
This summation is implemented in the simplest way: by enumerating all the cells needed for summation. Since not every department contains all departments and the position of lines in the 30 sheets of departments may be different, then to assemble the total revenue by department, the employee had to make dozens of formulas in which he explicitly indicated which cells he wanted to fold.

What problems do we see on generalized sheets?

1. Explicit search of cells for summation, which means if someone replaces the values ​​or meaning of the cell to which we refer when summing up, we will not notice this and we will have to look for this error for a long time.
2. When changing the structure of the company, we will have to not only change the lists of departments, which were affected by the changes, but also correct the sheet with the general assembly, because there will not be new objects, and deleted objects will produce errors.
3. When you change the cut, under which we want to look at the unit, this model can do nothing at all and simply does not work. If some big details appear in the departments, then in essence we will have to create one more such generalizing sheet (which will already have 10 times more direct links that need to be pierced, somewhere between 1000 and 10,000).
4. A model is generally completely destroyed if changes in the company lead to a different grouping of entities. All this work is just going to emit.

Thus, we have a rigid, rectilinear structure of the model, which is capable of producing only one result, can withstand small changes in the premises and is associated with huge labor costs for updating or making changes. And even the very creation of this structure is already associated with large labor costs.

For some reason, many companies are ready to "throw the bodies" over to hard-working employees who lack experience and skills to make everything easier, faster and more convenient. Moreover, it is not even a question of money. The implementation of such a file takes about 2 months of work of a person and that, without satisfying all the requirements. A more sensible approach to organizing work with data will require 1 week of not hard work from you! In 2 months of preparation in a “bad” way, you lose not only more money, but also a lot of time and nerves, because accepting the result you will catch mistakes tens of times. This is the dumbest waste of resources. This is the case when, in simple steps, you can get a 10-fold increase in labor productivity!

How we achieved labor productivity increase 10 times and remade the model in the next article.