In determining budget for future period(s), companies tend to use various data modelling methods that create relationship between the predictor(s) and response(s). Generally, spreadsheets or programming languages are used to determine the projected response(s).

In one of Data School projects, Tableau Prep was used for data preparation. In Tableau Prep, a tool named ‘New Rows’ can be used to make the projection. An illustration of the workflow containing the ‘New Rows’ tool is shown in Figure 1.

Figure 1. Addition of ‘New Row’ tool into Tableau Prep workflow

The first step in doing a projection is defining the projection timeframe, which is the next 5 years from 2022. This step was conducted by configuring the method of adding new rows to ‘Value ranges from two fields’ and setting ‘Start Year <= End Year’. Configuration of this field can be seen in Figure 2a, while Figure 2b shows the resulting projection year field.

Figure 2a. Configuration of New Row tool – adjusting method to add new rows

Figure 2b. Resulting projection of Year field

After the year field had been generated, the remaining design life of every component was calculated based on the year difference between the current year and projected year. When the difference becomes zero, the remaining design life was being reset to the nominated design life. Meanwhile, when the year difference reaches below zero, the remaining design life was adjusted based on the design life that had been reset. Figure 3 show the calculation and generation of remaining design life.

Figure 3a. Calculation of remaining Design Life

Figure 3b. Resulting calculation of remaining design life part 1

Figure 3c. Resulting calculation of remaining design life part 2

After all year and remaining design life for the upcoming 5 years had been set, the field which are not needed were removed to create a cleaner dataset for the visualisation.

Figure 4. Overall projection result in Excel view

Kristiadi Uisan
Author: Kristiadi Uisan