How we helped the client prepare a Supply and Demand model that helped improve Operational Efficiency

March 16, 2023

The Context

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.

The Objective

To analyze and model the same metrics across the segments for like-for-like comparison and summarization.

The Approach

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 Challenges We Overcame

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.

Final Product (Sanitized)

The Value Add – Catalyzing the Client’s Impact

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.