A hedge fund management tool that saved PepsiCo over 2,500 hours a year while increasing their purchasing efficiency.
Pepsico is a global food and beverage company, operating in over 200 countries, generating over $1billion in retail sales a year. PepsiCo was formed in 1965 when the original Pepsi merged with Frito-Lay. PepsiCo has 22 globally recognised brands in its portfolio including Pepsi, Tropicana, Quaker, Doritos and Walkers/Lays.
With hundreds of production facilities spread out across Europe, Pepsico needed as way of alleviating the risks of buying the commodities they needed (rice, wheat, oranges, packing materials). Any unexpected price changes in these commodities would have massive impacts on their retail pricing, potentially removing their competitive edge.
Unfortunately, each of its production facilities were purchasing their commodities independently and mostly from local suppliers. As a result, Pepsico needed to switch to purchasing commodities centrally and to employ a hedge fund methodology of buying commodities. This meant commodities were to be purchased years in advance at pre-agreed prices.
Each production facility had different purchasing and stock management systems, so collating the data needed to centralise buying was a very difficult process. Two interns would spend all their time copying and pasting purchasing data from one spreadsheet to another to collate the data needed.
I worked with PepsiCo at their various offices across Europe and helped them draw up a specification and modelled a proposed solution in Excel. The solution was made up of multiple tools, with each tool serving a specific task.
Firstly, each manufacturing plant had a dedicated tool for gathering data on its projected purchasing needs for the next 5 years. At a plant level, the managers were able to use the tool to apply multiple “what if” scenarios to develop the most efficient purchasing projections.
Secondly, a central tool gathered all these projections and amalgamated them in to purchasing projections for the whole group. The users of the central tool were able to put together reports on each commodity’s projected purchasing requirements, and from that they were able to decide on what hedge funds to buy.
Once the details of the required hedge funds were entered into the tool and confirmed, the tool automatically produced the required instructions for PepsiCo’s New York bank to purchase them. Then, once the purchase of the hedge funds was confirmed, the details were entered into the tool for monitoring purposes. Monthly KPI reports ensured the hedge funds performed as expected.
PepsiCo used the system to manage the hedge funds for over 100 production plants across Europe. Some benefits they saw include;
- Greatly increased accuracy in purchasing forecasts.
- Commonality across all their brands and plants across Europe.
- Fewer missed deadlines for legally time sensitive tasks.
- Easier auditing of hedge funds.
- Saved approximately 2,500 hours a year.
Since completing this project, I have worked with PepsiCo on a number of other projects for them.