Client
Our client is a dairy cooperative that produces over 200 million litres of milk annually, involving a complex supply chain and broad distribution function.
Purpose
Our client required a detailed supply chain and plant viability financial model to be developed, in order to assess the viability of one or more of the milk processing plants.
The key requirements included:
- Data Input – the ability to import a weekly demand forecast, bill of materials for over 100 products, packaging and raw material input costs and product pricing in an easy to update format.
- Scenario Analysis – the inclusion of a comprehensive scenario analysis, capture and compare tool which allowed detailed volume reallocations between plants down to the product level. The model also had to allow for a number of sensitivities to be tested including a reduction in demand and an increase in milk price and raw material costs.
- Outputs and Dashboards – display of project metrics by product or product family, financials by plant and for the company and an export tool to a separate model.
- Operational Outputs – the key metrics required were project margin and profitability at the product and product family level, facility revenue and costs, and product and input volume.
- Export tool – the model required a sheet which could export a number of captured scenarios to another model, which housed the calculation of rationalisation and remediation costs in the event of a plant closure.
- Viability Outputs – the output sheets for this part of the project allowed a comparison to be drawn between the NPV, IRR and ROI for 2 selected options.
Approach
Forecast initially attended a 2-day on-site workshop at the milk processing plant in order to understand the operations for the project. Following this, regular meetings between the project teams to ensure the model was covering all aspects of the supply chain and distribution function.
Outcome
Forecast delivered a sophisticated Excel tool that allowed the client to run various combinations of scenarios that would be invaluable to the strategic decision-making process of the closing of the facility.