## Resource Allocation Problem

This article describes the Resource Allocation 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.

## Introduction

In the Resource Allocation Problem, there are multiple machine types with a given amount of operation time and several products to produce. The products can be manufactured on the different machines, however the amount of time to make these products differs from machine to machine. Each product has a certain profit and the objective is to decide over production quantities and assign machines for the production such that the profit is maximized.

## Input data

The input data are given in the table below. There is the number of machines per machine type, the hours of operation per machine (let’s assume per week, but any other planning time frame may be chosen), resulting in weekly machine availability. In addition, each machine takes a certain time to produce these products, for example for Product 1, Machine 1 takes 1 hour, the other machines take 2 hours each. The profit per unit  is given in the last line of the table for each product.

 Time to produce one unit of product.. Number of Machines Hours of Operation Machine availability Product 1 Product 2 Product 3 Product 4 Machine 1 4 40 160 1 4 5 3 Machine 2 3 40 120 2 3 4 2 Machine 3 2 40 80 2 2 5 3 Machine 4 3 40 120 2 1 3 2 Profit per product 30 25 60 20

## Connecting the input data with the results through formulas

The screenshot below shows the formulas (and input in the first lines) used for this problem. The area highlighted in yellow marks the decision variables which are initially empty. In the line below, the quantities are summed up over all machines, for each product. This will be needed later (here in line 26) to compute the profit per product.

The table highlighted in blue multiplies the hours per product and machine with the amounts made, resulting in the total production hours per product and machine. This is for bookkeeping purposes: In column I, these hours are added up for each machine to obtain the total used machine hours per machine type. This number will be needed in the next step for the constraints.

Lastly, the total profit is obtained by summing up the profit per product. That value is the objective value which is to be maximized. ## Entering the information into the Excel solver

As mentioned above, the objective value is the total profit, given in cell I26 and is to be maximized. The decision variables (here “Changing Variable Cells”) are the table marked in yellow, in the example from E12 to H15.

The only constraint in this model is that the used machine hours may not exceed the available machine hours. ## The solution

Hitting solve gives the optimal number of products to be made, see table below. Machine 1 only produces Product 1, Machine 2 does Product 3, Machine 3 also makes Product 1 and Machine 4 produces Product 2. Product 4 is not manufactured in this example (as it takes a relatively large amount of time to make, with profits being the lowest.

 Product 1 Product 2 Product 3 Product 4 Machine 1 160 0 0 0 Machine 2 0 0 30 0 Machine 3 40 0 0 0 Machine 4 0 120 0 0 total 200 120 30 0

The resulting hours on each machine per product and profits per product are as follows. The formulas for the profit per product have been put in prior to solving (see above). The total profit in this example amounts to \$10,800.

 Product 1 Product 2 Product 3 Product 4 Machine hours used Machine 1 160 0 0 0 160 Machine 2 0 0 120 0 120 Machine 3 80 0 0 0 80 Machine 4 0 120 0 0 120 Total profit Profit per product \$6,000 \$3,000 \$1,800 \$0 \$10,800

## Extensions and Remarks

This model is a generalization of the Production Mix Problem as the Resource Allocation Problem allows for multiple machines of each kind (whereas in the Production Mix Problem there is only one machine per type).

The restrictions of this model are the following: The model is not taking any given demand into account and also it assumes that any number of items can be sold. Furthermore, costs of production are not taking into consideration. Also it models only one time period. All these things are addressed in the more complex Extended Resource Allocation Problem.

## Production Mix Problem (with Multiple Machines)

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.

## Introduction

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.

## Input data

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.

 Product Machines Product 1 Product 2 Product 3 Product 4 Production capacity Machine 1 2 0 2 1 200 Machine 2 1 0 1 2 150 Machine 3 1 6 0 2 250 Machine 4 0 3 1 0 100 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. ## The solution

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 Component 1 198 2 Component 2 150 0 Component 3 248 2 Component 4 100 0

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).

## Production Mix Problem (with Multiple Components) 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.

## Introduction

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.

## Input data

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.

 Product Components Product 1 Product 2 Product 3 Product 4 Inventory Component 1 2 0 2 1 200 Component 2 1 0 1 2 150 Component 3 1 6 0 2 250 Component 4 0 3 1 0 100 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. ## The solution

Pressing solve in the above menu will give the following results using the Excel solver:

 UNITS USED INVENTORY AFTER Component 1 198 2 Component 2 150 0 Component 3 248 2 Component 4 100 0
 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.