The client wanted the TresVista team to prepare a model for the target company, which the client recently acquired. The business was divided into six broad segments depending on the bus transportation services provided. The client wanted to analyze different metrics for asset allocation and staffing each month.
To analyze and model the same metrics across the segments for like-for-like comparison and summarization.
The TresVista team followed the following process:
Data Segregation: Analyzed the input data and segregated it based on business segments of the company
Calculation and Comparison of Metrics: Decided and calculated the metrics across segments based on the segregated data
Output Representation: Summarized the data calculated for analysis and comparison to take operational decisions
The major hurdles faced by the TresVista team were choosing metrics specific to the target company and industry, as information was limited; inconsistent data cross segments; and the Client’s excel software compatibility.
The TresVista team overcame these hurdles by consolidating the data calculated for all the metrics across the business segments for uniform comparison and analysis by keeping the same metrics across the segments for like-for-like comparison and summarization. The team also had to update a lot of formulae to make them compatible with the client’s excel version.
The TresVista team provided the dashboard summary using the pivot table that included the filters on variables like depot, maximum age of bus, bus type, and island that optimized the analysis and streamlined the output. The team also included dynamic metrics in the calculation of net supply and surplus like forecasted supply and out-of-age buses.
