Background
River Logic partnered with a Top 5 consulting company to design, build and implement a Sales & Operations Planning (S&OP) modeling platform for a large chemical company (“ChemCo”) with operations throughout the Americas and Europe. The impetus for this project was a definable gap in their S&OP planning process.
With a rolling 24-month planning horizon, each month ChemCo balanced sales forecasts and production capability in order to provide an accurate financial and budget plan. The off-the-shelf Supply Chain Planning (SCP) software the company had been using for tactical production and capacity planning included little what-if capabilities. That model only looked forward six months and, most importantly, could only allocate each order to an assigned primary plant and production unit (no secondary options allowed). ChemCo’s management knew this serious limitation hampered their decision-making and negatively impacted the company’s profitability.
ChemCo engaged River Logic and its partner to implement a new, optimization-based modeling approach to replace their old SCP model during the S&OP planning process. Management’s goal was to increase profitability by optimally satisfying all forecasted volume for the projected month and to minimize any spillover into the next monthly period.
Additionally, it was expected the EO model would allow for a range of secondary what-if questions, including:
- Modeling raw material price changes and their impact on future profitability
- Impact of new/lost customers and products
- Impact of opening/closing facilities
- Impact of contract negotiations
- Impact of shifting production to different locations
- Impact of potential customer price changes
- Evaluate exchange rate risk
Enterprise Optimizer Model
ChemCo’s EO model design represented the primary raw material and finished product flows throughout the procurement, manufacturing and distribution process. To maintain flexibility, no proper nouns were attached to any object name except for different sales markets, which were represented by two letter country codes. All remaining objects and links, location and resource names, such as raw material suppliers, manufacturing plants and distribution centers, was data maintained outside the model and input each planning cycle.
Here is the actual model diagram (with some object names altered to protect confidentiality). 
Approvals
There is a crucial aspect to this model that might not be readily apparent. Notice the inventory object named Approvals. This object was added so the model could consider shifting manufacturing from the forecasted plant to any approved plant with available production capacity, including plants with no historical data or a current forecast for a particular SKU.
A global flag enabled or disabled this decision. If off, the model could only make forecasted volumes at the assigned plants. If on, the model was allowed to consider shifting production to alternative plants.
By adding this object and links to the model, and ETL logic to create new BOMs and routings to support processes not defined in the company’s ERP system, it fulfilled management’s primary requirement to allow the EO model to optimize manufacturing capacity in order to satisfy on-time demand forecasts even if it meant incurring higher labor, transportation and inventory carrying costs.
But there was a critical facet to adding these additional choices for the model: customers had the right to veto the alternative manufacturing facility and insist on the forecasted plant! The primary reason was primarily due to product quality, which was extremely important to ChemCo’s customers. Most customers were allowed to decide if a particular order could be made at an alternate plant or not.
But, even if an alternate plant was allowed, customers still wanted to know precisely, by month, where each SKU’s forecasted volume was made. Consequently, the EO model was designed to track each SKU throughout the model by the originally forecasted site code (where it was originally assigned to be manufactured at) regardless of where it was actually made. This made producing a highly accurate manufacturing variance report by time period quite straightforward.
Other Important Modeling Features
This project involved a small, dedicated team of four FTEs plus about a dozen additional managers, planners, and IT personnel. River Logic consultants actively participated in the project, in part to provide experience in designing and implementing these particular features:
Template Model Approach
Although the ChemCo model appears integrated it was intentionally designed to be run separately in both the Americas and in Europe, and then together as a fully Integrated Business Planning (IBP) model.
An important aspect of Enterprise Optimizer is that domain constraint and other matrix rules allow for some combinations of objects and links to remain unpopulated. For ChemCo’s model, it meant that, for example, when data was imported for an Americas-only run the European sales objects (e.g., “UK Trade”, “DE Affiliate”, etc.) and links would not be populated.
The sign of a well-designed EO model is that entirely different datasets can be repeatedly input and solved for over months, even years, with the model flow diagram requiring few, if any, changes. (Ideally, model design changes only occur when the actual business process changes.)
With this in mind, the project team was able to develop one EO model that was reused for all regions and what-if scenarios.
Approvals Matrix
To optimally balance capacity, improve on-time deliveries, and increase profitability, a data table named the “approvals matrix” was created outside the company’s existing ERP data. Its sole purpose was for the EO model to allow forecasted SKUs to be made at alternative manufacturing plants or on alternative production units at the primary plant. Data was added every month from a combination of historical data from the ERP system and manual entry, as needed. The table looked similar to this:
As part of the logic to populate the model, all BOMs, rates and yields for each forecasted SKU was assumed transferrable from the forecasted site (FcstSite) to the alternate manufacturing site (MfgSite). ETL code was developed outside the EO model to facilitate this process. Actual freight costs were used if the MfgSite/ShipTo combination already existed; if not, the data was estimated or derived from other sources.
Transfer Price Eliminations
Look again at the model’s flow diagram above. At the bottom, you will notice two sets of interconnected financial report objects. During the model design phase, it was decided that this model would match the company’s financial reporting hierarchies and maintain two separate sets of financials, one for the Americas and one for Europe. (Note, there is no overall corporate consolidated financials in this model.)
From an accounting standpoint, when materials and finished goods are transferred within the same organization, it’s important to ensure that intra-company sales is not double counted as part of the trade sales revenue. In EO, the technique normally used for this is to represent transfer pricing eliminations as red lines between the financial reporting entities.
Currency and Units of Measure
Each EO model can include as many different currencies and units of measure as needed. There is no need to convert currencies or units outside of the model prior to data import. The only restriction is that each location and financial reporting entity can only have one currency assigned.
Input data, like raw material purchases and product sales prices, can remain in the same, local units as the company’s ERP and accounting systems. This also minimizes any ETL issues and confusion when debugging data directly inside the model. When the model is solved, all currency costs and prices are converted to a base currency so the objective function has consistent coefficients.
For ChemCo’s model there were six currencies: CAD, EUR, GBP, HUF, JPY and USD. All raw material prices, labor and other costs were input in local currency except for oil and gas, which was always in USD. For Europe, costs for England and Hungary were input in their local currencies; all other European countries in EUR.
There were many different units of measure used throughout the model. For example, product units for Europe and Canada were in kilograms, the US in pounds, and South America in metric tons. Oil was purchased and consumed in metric tons everywhere except for the US, where it was always measured in barrels. Here is the complete table by from/to unit type with conversion factors:
Summary
This example of an actual EO model highlights these important points:
- EO can be used effectively for what-if planning as part of a company’s normal S&OP planning process. Data can be extracted from existing ERP systems such as SAP and utilized in a highly customized environment that off-the-shelf planning systems cannot provide.
- A “template” model approach is preferable when planning occurs by geography, or other hierarchy, but with approximately the same business processes. Focusing on reusability as much as possible also reduces the amount of ETL required and the time spent debugging data and validating the model.
- EO data can be customized for specific purposes, such as attaching the originally forecasted plant code to SKU codes and then creating the necessary data to flow from alternative manufacturing locations to distribution to sales.
- Utilizing transfer price elimination techniques means that highly customized and accurate financials (Profit-and-Loss, Balance Sheet, Cash Flow Statements, etc.) can be at the local (e.g., plant, country) level as well as regional and corporate levels.
- Model designers should strive to maintain data in the currency and unit of measure as it exists in the company’s ERP, accounting, and other data systems. EO can easily handle dozens of different currencies and units of measure simultaneously, so keeping data in its natural state will make the ETL and data validation process that much easier.
Overall Company Impact
ChemCo had a decent ERP system for tracking historical data, but their IT systems and personnel initially lacked the infrastructure and data maturity to support the EO model’s need for alternative manufacturing locations and transportation lanes. By undertaking the EO project, it forced ChemCo’s production planners, working alongside their IT colleagues, to undertake a data cleanup effort that lasted for nearly a year. Although at times frustrating, each month the data got better and the planning process became smoother.
In the end, ChemCo’s management believed the project’s ramp-up year had such high value that a positive ROI was achieved even before the model was considered fully validated and the new S&OP process was in steady state. Once part of their routine S&OP process, the EO model’s what-if capabilities enabled the team to interact in new ways to make new decisions they were unable to previously.
