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