Production Planning to Optimize Revenue with Numerical Methods

by

Imagine having a tool at your fingertips that told you exactly what products to make and when to generate the most possible revenue. This is completely possible. I know, because I built it once. It’s not fortune telling, it’s not even AI, it’s just data, some math and a general understanding of the product.

Here’s how it worked. 

Set the Stage: 

  • The company is an animal protein manufacturer that processes cattle for beef products. 
  • Cattle will be graded at different quality levels which each assume a different price point.
  • Pricing is driven largely by the USDA Market. 
  • Every piece of the animal needs to be sold, but it can be packaged in many different forms. 
  • The product is perishable and must be discounted severely after being in inventory for a certain length of time. 
  • There are standards in place for how each variation of the product must be cut, consisting of fat, bone and muscle and an acceptable variance to that standard. 
  • The number of cattle being processed in a week is generally set with a small, expected variance. 
  • There is a limit to how much of each product the market will tolerate before prices become diluted or the product becomes difficult to sell. 
  • Demand varies based on weather, time of year and economic circumstances. 

 

Parameters:

Understanding all these points will allow one to discover which parameters need to be set so that all possible factors are considered in the model. Failure to address a key driver in the supply vs. demand vs. market pricing relationship could be detrimental in generating a helpful answer.  

So, driven from the factors listed we can determine a set of parameters that look like this:

  • There is a finite amount of each component of the carcass that can be produced and 100% of the animal needs to be accounted for in the production plan. It can neither exceed, nor come short of that percentage.
  • There is a maximum price that each product option can be sold successfully.
    • This price is determined weekly or by month using a weighted average of different price components.
      • 75% : A four-week rolling average of the in-house actual sales price
      • 25% : Market Pricing from the USDA Weekly Report
        • If the product is not reported on the USDA Weekly Report, default to 100% of the in-house sales price
  • There is a maximum quantity of each product that can be sold before the product is too old.
    • Determined by a rolling average of sales over a length of time plus two standard deviations.
    • Can also be manually adjusted based on feedback from sales representatives and customer sentiment.
  • There are sales already in place that must be fulfilled.
    • Production priority goes towards products that need to be produced to fill orders, then any remaining inventory will be allocated based on the pricing and market conditions parameters.
  • Some items are too difficult to produce and only a limited amount can be made in a day, which is the maximum quantity for that product.
  • There might also be a minimum quantity for a product based on the smallest possible box size or changeover requirements from the operations team.

Required Data:

Once the necessary parameters are put in place, it must be determined what data is needed to ensure that the production plan remains within the parameters. An analysis of the stated parameters leads us to the following list of data sets required for the optimization model. 

  • USDA Weekly Price List
  • Open Orders
  • Yield Standards
  • Cattle Forecast
  • Grading Trends
  • Sales History
  • Production Limits
  • Inventory Position

 

The Method:

Once the data has been gathered and the parameters set, it’s time to write the algorithm. Although there are many methods of optimizing a scenario such as this, for this particular case the Linear Simplex method was chosen. There are two possibilities to implement this method.

  1. The first method would be to manually code the algorithm using R, Matlab or any other platform that can handle this type of iterative processing. 
    1. Variables will need to be created to manage the bounds of the parameters, both minimums and maximums.
    2. The formula for the total revenue for the week will need to be created which is expressed as:
      1. xi=1npiyi where
      2. n=number of products produced
      3. p =weighted average of sales price and market price per pound
      4. x=number of cattle processed
      5. y=weight of particular product to be produced in pounds
    3. The algorithm will need to be written to solve for the above formula, starting with an estimated solution where all inputs start with zero and will then iterate from there using the simplex method. When all required conditions are met then the maximum result has been discovered.
    4. The resulting outputs of each variable will indicate how many of each product should be made to make the largest possible revenue while fulfilling all open orders, using every part of the processed cattle and having a viable sale opportunity for each item.
    5. Note: For more detail on the Linear Simplex method of optimization modeling please see a great description here: 4.2: Maximization By The Simplex Method – Mathematics LibreTexts
  2. The second method is much simpler to set up, though we lose greatly in performance. This is to do the calculations in Excel using the Solver Add-In capabilities.
    1. Solver is an interface for which parameters can be configured and a base equation provided.
    2. There is little to no actual coding required to set up the algorithm, solver already has multiple methods embedded, including Linear Simplex. 
    3. The downside is that it is slow and there is a limit on the number of parameters allowed.
    4. It’s an extremely useful tool but is not recommended for large datasets or scenarios requiring high security.

Conclusion: 

This method proved to be highly successful in increasing revenues for this industry, improving margin by more than three (3) times its standard in some programs. It allowed for quick changes in the production plan week after week in a proactive way using historical trends, sales team sentiment and pricing movements. It encouraged major changes in production decisions where items previously only made to order became defaulted items to produce at certain times of the year when it was more profitable. Similar products made in different ways to improve the yields in a way that favored pricing were also encouraged based on the results of the model. It allowed hundreds of products to be viewed at a glance to make quick decisions where it previously was not possible. Millions of dollars in additional revenues were generated due to the impact of this model. This is a format that could easily be repeated in various industries to improve revenues by providing a proactive view of markets and other factors unique to a particular industry.