This case study will elaborate on the work achieved by Jetair in collaboration with element61 in defining and building a driver-based planning solution to come to a companywide Profit and Loss statement.
The Customer
Jetair is part of the World of TUI, the largest tourism group in the world. The international group is active with tour operators, travel agencies, airlines, hotels and incoming agents. Another core activity is shipping (containers and cruiseships). TUI AG is quoted on the Frankfurt stock exchange.
Jetair consists out of the following brands: Jetair, VIP Selection, Sunjets, Seagull, VTB and Splash travel. Tour operator Jetair is present in all segments of organized leisure tourism and offers the largest variety of holidays in the Belgian market. The distribution of Jetair-holidays is the broadest possible, with retail in all Belgian travel agencies, including the group owned Jetaircenter network. In parallel with the retail agencies, also booking through the internet is possible. The Jetair-owned airline Jetairfly operates the majority of the flights for Jetairs flight holidays program. Currently (2010 information) Jetairfly has 15 planes which are flying to 80+ destinations with a crew of 650 people.
Through the years, Jetair has been building a large data warehouse containing all their operational data to focus on the operational performance of the company. The next step was integrating all the financial data and building a companywide Planning solution to come to a complete closed-loop Performance Management environment.
Hence a Planning, Budgeting & Forecasting-project was started. As there was already a first project executed in the past with Cognos Planning, this existing solution was the starting point of the new project. The aim of the project was to automate the Profit & Loss-statement, integrating all accounts and cost- and profit centers to allow for both bottom-up and top-down planning. As part of the project, this application has been extended with driver-based data being fed to the application for some specific areas. Main focus of the project was on to the operational business of selling vacation over land (TO Overland) and by plane (TO Flight). The overhead cost centers have been integrated by basic enduser-input to come to a full P&L. As the business of Jetair is rather complex, the project started small and over a period of one year the basic set of drivers were adjusted and enlarged. This gave Jetair the opportunity to get to know the Planning solution and its functionalities and to evolve with the tool to come to a better design and resulting solution. The first version of the driver based solution was used during the budgeting cycle. After this first round, a number of adjustments were made to use the solution for the first Forecasting cycle.
Analyzing the needs
The controlling department of Jetair was in the lead and explained the need for a driver based planning solution to come to a full P&L. The process of budgeting and forecasting was already in place but there was no environment to support this process. The process was cumbersome due to the typical issues associated to running these processes in Microsoft Excel. A general summary P&L application was already created with Cognos Planning but the input was only through amounts per account. This was supposed to change to a system that fully works with drivers, fed into the system in an automatic way. The main focus of the project was the Tour Operating business. The overhead was decided to remain as being entered as an input on account-level.
The first step in defining the real needs of a driver based planning solution was understanding the organization and its structure. We used the element61 elementary methodology and templates to define and capture the business needs. The tour operating business is pretty complex and the correct focus was important. Jetair is a very centralized company with its base in Oostende where most of the overhead departments are located and the main group of operational people are situated. The tour operating business is split into 2 large buckets: TO Overland and TO Flight and both have their own calculation and planning process. For TO Overland the main cost is the hotel cost, as where for TO Flight also the transportation cost is important. This cost of the plane, its taxes and other costs are calculated with the flight plan that is uploaded into the system. The planning of the different planes (15 own planes) is not part of the system as there is already an operational planning system to that purpose.
By interviewing the controllers and other stakeholders, the model was defined as acting on a cost-plus model where the cost per brand and per destination is used to calculate the revenue based on a margin that is needed per brand. However the flexibility had to be build in to override the cost-plus calculation if a specific revenue target was required. The personnel cost is as in many organisations an important cost factor on the profit and loss statement. Information maintained by the HR department was loaded into the planning tool on a department-basis. The cost (and revenues) of the overhead departments are not driver based and are entered by manual input per account, in this iteration of the project.
The design consists out of 1 model containing 14 applications with 54 cubes and 62 dimensions in total. Many of these dimensions are sourced from the data warehouse. Only the drivers are manually created. Some large dimensions like the destinations needed some rethinking as big dimensions could slow down the system and are not always needed for planning purposes. The largest dimension (besides the accounts) is the destination dimension.
Within the planning model, the resulting data set from one part of the Planning solution can become input of another part of the process. The seat-cost per brand is based on the split of the seats per brand. Changing this split will alter the total cost of the plane as the VIP-brand costs more in terms of meals. So different drivers where foreseen with different scales. The seat cost is the same for every brand but for some brands an extra cost is added. The meals follow the same rule.The flight plan with the seats per destination are loaded into the system but can be overwritten.
Designing the new environment
Because of the differences in calculation, a split of the tour operating business into different applications was needed. This way the number of destinations per application remain limited and therefor the resulting application was smaller and easier to handle. Three different TO applications were created : TO Overland, TO Specialist and TO Flight. The last application was extended with some additional applications, each containing a specific part of the transportation cost. All data is grouped into the Flight Plan application where the transportation cost is fully calculated.
For every dimension, a draft "d-list was made and also the "e-lists and "d-cubes were defined and created preliminary to building the first prototype. Proto-typing is an ideal way to further refine and test the correct translation of business requirements into the application design. The limitation of approximately 20 million cells within an IBM Cognos Planning application is important to keep in mind when designing the final solution. The dimension of the General Ledger had 650 items and also some other dimensions were quite large. Using all these dimensions, even within separate cubes, in combination with 13 time periods (12 months and full year) exceeded the limit of 20 million cells. We must keep in mind that some dimensions like destinations and GL accounts will keep changing and growing during the year because of normal business growth and change. Access Tables and Cut-down models were used to shrink the application to the needs of the end-user without losing any functionality. However this technique will make the creation of the application slower and therefor make the administrator take more time to run updates of the applications. By challenging each dimension and lowering the number of items used, we can avoid using the cut-down models and still speed up the system. For the accounts, we used only 370 items out of 650, thus lowering the size of the application by 40%.
Building the solution
For some dimensions we load data from the DWH into a small application, where the dimension can be maintained and load only those members that are required for planning purposes. The necessary update procedures are made to load new items of these dimensions automatically when running a macro. For the financial actuals needed in the planning application, a load is made from the RACS financial accounting-system into the different Cognos Planning Applications. The drivers are partially loaded from the DWH (where they exist like flight plan, margins, ), but others are manually entered.
Five main applications were built : Personnel, TO Flight, TO Overland, TO Specialist and the resulting P&L application. Several smaller applications were created to support the TO Flight application. The P&L application is the end-result but has two extra views on the data: P&L per brand and P&L per distribution channel. For external reporting, purposes an extra P&L view is created to transpose the normal P&L to a Hyperion Financial Management view.
Following steps were taken during the application build process :
- Creating all the dimensions and populating them manually or with data coming from RACS or the DWH
- Combining the dimensions into cubes and creating the applications
- Building links within the applications to let data flow from one cube to another
- Building inter-applications links to link data from one application to another
- Creating the application hierarchy and setting up the security
- Using access tables to limit the user input to the necessary fields
- Loading actuals data
- Initial testing of the environment
- Creating an administrator & user manual
- Training & Coaching of the future IBM Cognos Planning Administrators
Finally the solution was tested and accepted by the user community.
Technology used
The latest release of IBM Cognos Planning was used, 8.4.1.
element61 responsibilities
element61 has been responsible for the following roles & activities:
- Analyzing the requirements
- Defining and designing the new solution
- Installing the software
- Building the planning applications
- Rollout, training & coaching of the customer both on the concepts & technology deployed.
Conclusion
Jetair now has an automated and driver based planning solution that is fully managed by the Finance department. Instead of running the planning process in MS Excel and add them together with information of other departments, the new solution required just the input of the drivers and will do the calculation automatically and without any error. By using multiple versions, the variation of the driver versus previous versions is now the start of the analysis to look for an answer on why the numbers are changing at the bottom of the profit and loss statement. Less time is spent on looking for and manipulating numbers and more time is freed up for analysis and reworking the numbers towards a new forecast or budget, based on managements decisions & strategy.
Project recognition by the industry
An article on the Performance Management journey of TUI Jetair can be found here. This article appeared in the CFO Magazine Yearbook 2010 as published for the Best Finance Team of the Year event 2010, where Jetair was awarded a Performance Management Best Practise award.
You can find the press release on the "Award for Best Practice in Performance Management" at the CFO Best Finance Team of the Year event here.
