The client required a comprehensive demand forecasting model capable of predicting fuel volume sales, revenue, costs, and loyalty discounts on a 2-year rolling weekly basis. The model needed to take into consideration holiday periods, relative pricing to competitors, cyclical price trends, and other market variables.
The key requirements of the model included:
- Dynamic dashboard outputs to summarise and breakdown volume by week / grade / geography, as well as fuel revenues, costs, and margins.
- Scenario handler and model capture tools to assist management examine the outcomes of changes in key variables, and to understand the impact of changes in forecasts over time.
- A simple and robust method for importing actual data from various sources.
- The ability to build up fuel cost prices according to supplier contract specifics such as crude oil costs, refining margins, sea freight rates, port and delivery costs, handling costs, and land freight costs.
Working closely with the client we used their industry knowledge and existing data sets to develop an approach combining advanced analytics and best-practice financial modelling. Variables were analysed and modelled in R using an extended version of an auto-regressive time series modelling technique. The outputs from this analysis were included in an Excel based financial model which allowed the fuel team to use a familiar piece of software that was transparent, easy to update, and present to senior stakeholders in the business.
By taking a statistical approach we enabled the client to understand and quantify the fundamentals of the fuel business and reduce time-consuming manual processes. The best practice model design reduced key-person dependency, allowing simple data and assumption updates to deliver real insights to senior management in a timely manner. As part of the model development, extensive back-testing was completed to ensure the model outputs were accurate and provided assurance to senior management that the model outputs could be relied upon to aid the decision-making process.