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 can be made on different machines which have limited availability.
Many manufacturers produce multiple products and have several, different machines that can all produce these products. 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 machine 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 production capacity that is available for each machine.
The input data needed to solve this problem are the hours (or minutes or any other time unit) needed for each product on each machine, the production capacity of each machine, 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 hours when using Machine 1, 1 hour when using Machine 2 and 1 hour when using Machine 3 (Machine 4 is not suitable for Product 1). Selling one unit of product 1 will earn a profit of $20. There are 200 hours available on Machine 1, 150 hours on Machine 2 and so on.
|Machines||Product 1||Product 2||Product 3||Product 4||Production capacity|
|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 machine hours needed to produce those quantities d not exceed the given capacity for each machine.
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 integer and non-negative which is expressed in the first two lines of the “Constraints”. The last line ensures that the used production time must not exceed the maximum production capacity for each machine.
Pressing solve in the above menu will give the following results using the Excel solver:
The capacity used and the remaining capacity can be found in the table below.
|CAPACITY USED||REMAINING CAPACITY|
The results for the decision variables (no. of products made) and the profits are given below:
|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 (sum over all products) in this example.
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 Components. The difference is that instead of sharing the same machines, these products use the same components. And instead of production capacity measured in time units, we now have inventory of components measured in pieces. You can read about it here. In that post, we used the exact same numerical example to highlight the similarities and differences.
Also, the Production Mix Problem (with Multiple Machines) is a special case of the Resource Allocation Problem. In the Resource Allocation Problem, there are several machines of each kind (whereas in the Production Mix Problem there may only be one machine of each kind).
If you would like this or a similar problem to be solved by us, please contact us.