I keep a secret weapon hidden in my back pocket: my deep knowledge of Excel. If word were to get out that I can build an Excel model in my sleep, who knows what might happen. When I started working at renewable energy company and saw the challenges they were facing as they experienced significant growth in a short period of time, I knew that I had to blow my cover and pull out every Excel skill in my arsenal.
My first challenge was to create a linear optimization model that could optimize their dynamically changing supply chain model. Decisions about how and where to distribute millions of dollars of product were being made manually with responsibility shared across multiple people. This resulted in wasted supply chain costs – a big problem in an environment where every cent per gallon made a difference to the bottom line.
- I deconstructed the supply chain – which happened to be the most complex ethanol distribution network in the industry – and developed excel models to simulate each of the costs.
- I built a linear optimization schema that used smaller models to find most profitable supply chain configuration.
- Worked with team members to redefine supply and demand planning activities.
Identified $6M in annual supply chain cost savings.
Implemented a plan that reduced quarterly barge lease by 40%.
Teams used this model regularly to create distribution plans.
1: I deconstructed the supply chain – which happened to be the most complex ethanol distribution network in the industry – and developed excel models to simulate each of the costs.
After outlining all of the parts within the supply chain, I gathered data about costs and prices. This was not easy given that this information was distributed throughout the organization.
2: I built a linear optimization schema that used smaller models to find most profitable supply chain configuration.
With more than 15 origins and 60 destinations, there were more than 900 possible options for sending product to market. But all of those possibilities weren’t the most profitable. The team needed a model that looked at all of the factors in near real-time to make decisions.
ABOUT THE SETUP
Ultimately, the model included several sub-models:
- Volume – Outlined how much product would be sold in a given time period
- Cost-to-Serve – Estimated how much it would cost to produce and market product within a given time period
- Forecasted Sales – Estimated how much revenue could be generated within a given time period
- Profit – Used previous three models to estimate profitability
- Each origin destination pair (OD) was unique for each of these items.
- Every origin had a different amount of volume and cost to purchase or produce.
- Every destination had a different price that product could be sold.
- And each OD pair had a different transportation cost, possibly using up to 3 modes of transportation to get product to market.
Because of the uniqueness, the model could not just look at cost and sales in aggregate. It had to have these numbers for all 900 combinations.
One of the challenges here was keeping the model updated with the latest information. With a static model and inputs from a variety of places, real-time updates were not feasible.
Instead, team members had to import numbers manually. Formulas were put in place to do the heavy lifting with those numbers in place.
- Financial ERP
- Tool that manages freight costs
- Contracts from various transportation and inventory agreement
- Commodities prices
- Production facilities
The goal was to provide an answer about which OD pairs were most profitable and what amounts of volume should be distributed to each location.
Providing this answer required more than a simple excel model. The excel model would only go so far: I could input volumes and then see the profitability calculation. The team could go through and manually check various volumes to see where the optimal distributions were. But that would take time that they did not have.
So instead, I built a linear optimization macro, which on the press of a button would find the distribution of volume for each OD pair that maximized profit. Since what I was trying to do was not accomplished easily using excels core functionality, I used a plugin.
ABOUT THE SUB-MODELS
Purpose: identify how much product would be sold in a given time period.
- Projected volume for each equity plant
- Projected volume from alliance partners
- Projected volume from 3rd party suppliers (number varied from day-to-day)
- Planned plant shutdowns
Purpose: Estimated how much it would cost to produce and market product within a given time period
- Cost to purchase and or produce gallons- Price of product fluctuated often. Model was based on commodities price projections.
- Transportation costs- Rail, barge, or truck. Some locations required multiple modes.
- Demurrage – if inventory has to sit in railcars, because of timing, a fee is charged
- Inventory costs at terminals – We negotiated agreements in advance. At times, we got creative and transloaded (loading from railcars directly to customer trucks)
- Carrying costs – Depending on the destination, it could take up to 1-2 weeks for product to make it to market
- Adjustments – Over time, I incorporated adjustments to costs into model as historical data revealed trends (i.e. seasonality adjustments)
Purpose: Forecast how much revenue could be generated within a given time period
- Oil price forecast – since our product would be blended with oil, there was a limit to what oil companies would pay based on their own sales projections
- Location – due to supply and demand, sales were higher in some locations than others. For instance, the east coast was saturated and there was a mature distribution network for this type of product. The price was much lower there than in the Southeast, which, at the time didn’t have a network at all. We were the first to be in this market, and as a result were able to charge a premium.
- Weather – weather often slowed down delivery of product, decreased supply, and resulted in an increase in prices.
3: Worked with team members to redefine supply and demand planning activities.
Rather than different people making independent plans using their own tools and methodologies, this became a collaborative activity.
As changes happened in the marketplace, team members could update the model and make quick decisions about supply chain distribution.