The client required a detailed model to forecast daily enterprise service order volumes and their movement through the development and activation process. The client receives over 250,000 enterprise client orders annually and required an ability to predict future volumes to manage their resources and delivery times. The forecast tool was needed to assist with resource capacity planning for a rolling daily 3-month forecast, as well as a monthly 12-month forecast to assist with medium-term planning.
Key deliverables and functionality of the model included:
- The forecast to be split by order type, order complexity, product family, customer location, and allocation to a larger project.
- Detailed regression analysis of a number of client data sources to plan the model approach and to populate the initial set of assumptions. This involved various time series analytical techniques and exponential curve fitting.
- The ability to run, capture and compare multiple scenarios to examine the impact of marketing campaigns, delivery delays, project delays, and other business changes.
- The ability to run back testing to analyse the accuracy of the model outputs.
Forecast spent time with key stakeholders at the beginning of the project to understand both the inputs and the required outputs needed for the forecast. This was crucial to plan the approach and to grasp the nuances of the various data sets. This allowed Forecast team members to work concurrently on the data analysis and the structural build of the model in order to deliver the model within a tight deadline.
Due to the significant number of combinations of order type, product, location, and project type, Forecast developed a User Defined Function (UDF) in VBA to replace complex, inefficient formulae. This improved calculation speeds significantly and allowed management to examine outputs without unnecessary model recalculations.
The final model provided management with detailed view of orders for the short and medium-term. Forecast has continued to support the client with optimising the demand forecasting process. The tool will be further developed in the future to include a resource requirements and optimisation calculator.