A company is developing its weekly production plan. The company produces two products, A and B, which are processed in two departments. Setting up each batch of A requires $60 of labor while setting up a batch of B costs $80. Each unit of A generates a profit of $17 while a unit of B earns a profit of $21. The company can sell all the units it produces. The data for the problem are summarized below. What is the appropriate formula to use in cell B15 of the following Excel implementation of the ILP model for this problem?
A) =B5 − MIN($E$11/B11, $E$11/C11)*B14
B) =B5 − MIN($E$11/B11, $E$12/B12)
C) =B5 − $E$12/B12*B14
D) =B5 − MIN($E$11/B11, $E$12/B12)*B14

Question

A company is developing its weekly production plan. The company produces two products, A and B, which are processed in two departments. Setting up each batch of A requires $60 of labor while setting up a batch of B costs $80. Each unit of A generates a profit of $17 while a unit of B earns a profit of $21. The company can sell all the units it produces. The data for the problem are summarized below.  ​ What is the appropriate formula to use in cell B15 of the following Excel implementation of the ILP model for this problem? 
A) =B5 − MIN($E$11/B11, $E$11/C11)*B14 
B) =B5 − MIN($E$11/B11, $E$12/B12) 
C) =B5 − $E$12/B12*B14 
D) =B5 − MIN($E$11/B11, $E$12/B12)*B14

Quizplus · Accepted Answer

The formula in cell B15 calculates the total profit by subtracting the setup costs from the total revenue. In this problem, the setup cost for each product is proportional to the number of batches produced, which is given by the expression MIN($E$11/B11, $E$12/B12). Therefore, the appropriate formula to use in cell B15 is D, which includes this expression multiplied by the setup cost per batch (B14).

A Company Is Developing Its Weekly Production Plan