Past Projects

My Past Projects

Below is just a handful of some of the projects I have worked on in the past. Please get in touch if you would like more details.

Extract, Transform and Load (ETL) Tool

When ALUK, a provider of aluminium building systems, bought out a similar company, they found themselves struggling with two incompatible stock control systems. They needed to rapidly develop an ETL tool that would combine the two sets of stock.

Although the raw materials could be translated from one system to another with a simple dictionary lookup, both companies also manufactured a wide range of pre-assembled products. Some of these pre-assembled products were constructed using sub-assemblies that they also manufactured.

Using VBA I built a tool that converted stock references and rebuilt the bill of materials (BOM) used to make up each assembly and its sub-assemblies. The new BOMs were then used to update CAD and other systems.

The tool was prototyped and tested within a week. The following week it helped ensure the smooth merging of the two stock systems.

Financial Modelling Tool

The FLP Financial Group in Connecticut were providing the financial planning for a large start-up medical business. Their client sold and leased specialised medical beds to hospitals across the US via a franchise model.

They needed a tool that would model a wide range of sales scenarios and their impacts on their financial plans. I developed a VBA tool that allowed FLP to set a wide range of criteria and receive an instant financial projection.

On a state by state level, FLP could set criteria as

🔹 No of franchisees expected to be appointed
🔹 Percentage split of sales and leases
🔹 Projected lease term lengths

The tool produced financial projections based on sales income, expected lease payments, devaluation of the beds, repayments on loans to purchase the beds, various state taxes and a wide range of other factors.

Hedge Fund Management Tool

Pepsico owns numerous brands (Walkers, Quaker Oats, Tropicana, Mountain Dew, Lipton Teas etc), each with dozens of production facilities spread across Europe. All these facilities consumed large amounts of commodities (aluminium, oats, potatoes, oranges etc) and Pepsico needed to hedge the purchasing of these commodities to ensure a stable supply.

Most of the brands were freshly acquired and as a result, there were very few shared IT and data resources to call upon. The directors of Pepsico Europe in Geneva contacted me to build a bespoke tool to manage the hedging of these purchases.

I developed a multistage VBA tool.

🔹 Stage 1 – Gathered and commonized purchasing forecast data from all their 140+ production facilities.
🔹Stage 2 – Consolidated this data and converted it into a month by month commodity shopping list spanning 6 years.
🔹Stage 3 – When confirmed by human operators, the programming automatically sent the legal documents to process the hedge funds.

The tool reduced the work of 3 full-time interns to a process that took less than an hour to run. As a result of this work, Pepsico became my most regular client.

Pricing Comparison Tool

As a broker, Autorama provides personal and commercial vehicle leases across the UK. They rely on dozens of leasing companies to provide leasing deals on the widest range of vehicles possible. When the number of vehicle variations is combined with the number of different leasing options (length, deposit, final payment options etc), Autorama found itself trying to process millions of price points.

The situation was further complicated by each lease provider, supplying their price books in different formats and with differing options. Some only provided sample prices and brokers like Autorama needed to extrapolate other prices.

I developed a three-stage VBA based tool

🔹 Stage 1 – Imported and commonized the pricing books from all the leasing companies.
🔹 Stage 2 – Extrapolated missing price points and identified the best deals to highlight on the Autorma website.
🔹 Stage 3 – Exported the final data ready for importing in their website and internal systems.

The tool replaced a system that relied on the cutting and pasting of data, and the applying of numerous formulas. As a result, the tool saved considerable time and eliminated all errors.

Production Scheduling Tool

The Weedon Group print and produce corrugated packaging for major UK supermarkets and global brands. Their clients have contracts that require the Weedon Group to turn around orders within agreed upon Service Level Agreements (SLA). This could be within just a few days. Other clients have just in time ordering agreements.

Most of their products require passing through several production processes (design, printing, cutting, folding, glueing etc). This meant that they needed to plan their production on an hour by hour basis, with some jobs being sidelined to allow higher priority jobs to pass through. This was done by printing out new job orders for each machine in the factory and physically passing it to the operator.

I developed a VBA based system that allowed their office staff to easily enter the details of new orders as and when they arrived. This then automatically adjusted work orders and displayed the updates on PCs installed next to each machine in the factory.

The tool greatly simplified the planning process and eliminated potential communication errors.

All of the code featured on this site is available to download on my GitGub page. Click here to visit it.