This article describes the Production Mix Problem by way of an example and shows how to solve it using the Excel solver. Here we look at how many products to make of each kind when the products use some of the same components of limited availability.
Many manufacturers produce multiple products that are somewhat related and share some of the same components. In this case the Production Mix Problem occurs. This is a problem that can be formulated as linear program (LP) and solved to optimality. In this post we will give a minimal example in terms of the product and component number as well as the complexity of the problem.
The objective in the Production Mix Problem is to maximize the total profit over all products. The decision to be made is how much to produce of each product to achieve this. The constraint is to not exceed the amount of inventory for each of the needed components.
The input data needed to solve this problem are the quantities needed of each component for each product, the inventory of each component, and the profit per unit of each product. An example for the input data can be found in the table below.
In this example, the production of 1 unit of Product 1 requires 2 units of Component 1, 1 unit of Component 2 and 1 unit of Component 3 (none of Component 4). Selling one unit of product 1 will earn a profit of $20. There are 200 units of Component 1 in stock, 150 of Component 2 and so on.
|Components||Product 1||Product 2||Product 3||Product 4||Inventory|
|Profit per product||20||40||25||30|
Connecting the input data with the results through formulas
In the next step, a few formulas have to be entered into the spread sheet, see the screenshot below. This provides the Excel solver with important information about the problem. In the column UNITS USED, we compute the inventory for each component that remains after production of all planned units. Beforehand, when there is no entry for the decision variable yet, this will simply be zero, but will be updated as the solution is obtained. The INVENTORY AFTER is the difference between the inventory before and the units used. The PROFIT PER PRODUCT equals the product of profit per product and the quantity made for each product. In the cell for the total profit, these are added up over all products.
After these formulas are entered, the optimization problem can be solved using the following objective function and constraints. The objective function is the total profit which is the sum over all products of the quantity products per product multiplied by the profit for that product.
The constraints are that (1) the decision variable which is the number of products produced per product is integer and non-negative and (2) that the inventory needed to produce those quantities does not exceed the given inventory.
Entering the information into the Excel solver
The solver dialogue looks like shown in the graph below. The “Set Objective” (here $H$22) is the cell computing the total profit. The “Variable Cells” are the cells with the decision variables. In our case this is the number of products to be produced. Furthermore, there are constraints. The decision variable has to be both integer and non-negative which is expressed in the first two lines of the “Constraints”. The last line expresses that the used inventory must not exceed the inventory at hand.
Pressing solve in the above menu will give the following results using the Excel solver:
|UNITS USED||INVENTORY AFTER|
|Product 1||Product 2||Product 3||Product 4|
|NO. OF PRODUCTS MADE||48||22||34||34||TOTAL PROFIT:|
|PROFIT PER PRODUCT||$960||$880||$850||$1,020||$3,710|
The solution states that 48 units of Product 1, 22 units of Product 2, 34 units of Product 3, and 34 units of Product 4 should be produced to maximize the total profit which amounts to $3,710 in this example. The units used and the inventory after can be found in the column above as well.
Extensions and Remarks
The presented problem can be extended by several other constraints, for example by a minimum or maximum for the number of products made, or different profits depending on the quantity produced. Also alternate methods to produce a product (needing different amounts of components) is a possible extension of this problem.
A slightly different problem that has the exact same architecture arises when considering different machines instead of components. In that case there are several products that share machines. Each product takes a certain amount of time on each machine (can also be zero). The machine has limited capacity (before: inventory of the components) which is measured in time. Again, the objective is to find the profit maximizing production quantities for each product.
The mathematically identical problem, however with a somewhat different context occurs in the Production Mix Problem with Multiple Machines. The difference is that instead of products using the same components, these products share the same machines. And instead of inventory of components measured in pieces, we now have production capacity measured in time units (for example hours). You can read about it here. In that post, we used the exact same numerical example to highlight the similarities and differences.
If you would like this or a similar problem to be solved by us, please contact us.