The amount of units to produce was acquired by taking the values of the availability of resources (from another table) and the usage of resources per product. This was divided into the former to get a quotient: the maximum use of resources. Both Stevenson (2005)1 and Middleton (2006)2 explain how to use Excel in this fashion via the Solver tool. After this, the amount of units produced was multiplied by the profit per unit to obtain the value for total profit.
X1, X2, X8, X9 and X10 are the profit bringers, and thus they should be placed on the schedule in order of profit received, starting from the highest: X2, X8, S9, X1 and X10. The others, as there is little profit from them, should be moved to the bottom of the list. X4 can likely be eliminated altogether.
From this, using the SUMPRODUCT formula, which adds the sums of products of corresponding arrays, it can then be determined how much of each resource is used per week. For example, X1 can have 15 units produced, at a cost of 1.8 units in M/C 1. Unit 2 can have have 28 units, at a cost of 1.2 in M/C 2, and so on down the list. All these values are added up to get the total usage of resources. Alternatively, all values in the row corresponding to each product can be added to get the total cost per unit.
This table denotes the usage of all resources, based upon the values gained from the procedure described above.
Going by this, it can be seen that M/C 3 and both raw materials are used to optimum efficiency. There is no surplus or deficit.
M/C 2 and M/C 4, hwever, are lacking; there is a a period of 44.3 hours left over for 2, and a period of 57.2 hours for 4. M/C 1, 3, and 5 are used efficiently; there is little available time left for them.
5. Marginal Benefits
M/C 1 Usage
M/C 2 Usage
M/C 3 Usage
M/C 4 Usage
M/C 5 Usage
Mat 1 Usage