ProductI A group of managers are working on production plans for next week. Their meeting has resulted in the proposed product mix provided in row 20 of the "Product Mix" worksheet. ProductA ProductB Demand 50 50 Profit Margin $ 22.50 $ 27.50 Demand and profit margin unit for each product Productc ProductD Producte Product Product Product 100 100 100 75 75 75 $ 22.50 $ 26.50 $ 25.00 $ 27.50 $ 27.50 $ 30.00 75 25.00 $ a) Please determine the requirements (aggregate workload) for each resource, based on the proposed product mix (use M9:M16). Report the value of the highest aggregate workload (to zero decimals). Aggregate Workload ProductA Number of units of each resource that is required for production of one unit of each product ProductB Productc ProductD Producte Product Product Product ProductI 6 7 5 6 5 6 8 8 10 5 10 8 8 8 b) In cell M20, determine the total profit of the proposed product mix, based on the profit margins for each product provided in row 5. 7 7 7 7 9 5 9 8 9 5 Resource1 Resource2 Resource3 Resource4 Resource5 Resource6 Resource7 Resources 7 7 6 66 58 956 10 8 ONNO 000 0 00 00 00 Capacity 4,700 4,700 4,700 4,700 4,700 4,700 4,700 4,700 6 7 10 96 09 00 00 9 8 6 7 c) You should have found, in a, that the proposed product mix results in one or more resources having an aggregate workload that exceeds capacity. Assuming that this makes the proposed product mix infeasible, determine the product mix that maximizes profit without exceeding aggregate workloads or demand (using Solver). Report the new total profit (to two decimals). 5 5 6 10 6 10 10 7 6 8 7 9 6 8 9 8 6 7 7 8 9 8 8 5 Total Profit Management's proposed "product mix" (i.e. number of each product they propose to produce) ProductA ProductB Product ProductD Producte Product Product ProductH ProductI 50 50 80 80 100 75 50 75 75 *note - when Solving, it is OK (for the purpose of this question) ifthese values aren't whole numbers Produce: ProductI A group of managers are working on production plans for next week. Their meeting has resulted in the proposed product mix provided in row 20 of the "Product Mix" worksheet. ProductA ProductB Demand 50 50 Profit Margin $ 22.50 $ 27.50 Demand and profit margin unit for each product Productc ProductD Producte Product Product Product 100 100 100 75 75 75 $ 22.50 $ 26.50 $ 25.00 $ 27.50 $ 27.50 $ 30.00 75 25.00 $ a) Please determine the requirements (aggregate workload) for each resource, based on the proposed product mix (use M9:M16). Report the value of the highest aggregate workload (to zero decimals). Aggregate Workload ProductA Number of units of each resource that is required for production of one unit of each product ProductB Productc ProductD Producte Product Product Product ProductI 6 7 5 6 5 6 8 8 10 5 10 8 8 8 b) In cell M20, determine the total profit of the proposed product mix, based on the profit margins for each product provided in row 5. 7 7 7 7 9 5 9 8 9 5 Resource1 Resource2 Resource3 Resource4 Resource5 Resource6 Resource7 Resources 7 7 6 66 58 956 10 8 ONNO 000 0 00 00 00 Capacity 4,700 4,700 4,700 4,700 4,700 4,700 4,700 4,700 6 7 10 96 09 00 00 9 8 6 7 c) You should have found, in a, that the proposed product mix results in one or more resources having an aggregate workload that exceeds capacity. Assuming that this makes the proposed product mix infeasible, determine the product mix that maximizes profit without exceeding aggregate workloads or demand (using Solver). Report the new total profit (to two decimals). 5 5 6 10 6 10 10 7 6 8 7 9 6 8 9 8 6 7 7 8 9 8 8 5 Total Profit Management's proposed "product mix" (i.e. number of each product they propose to produce) ProductA ProductB Product ProductD Producte Product Product ProductH ProductI 50 50 80 80 100 75 50 75 75 *note - when Solving, it is OK (for the purpose of this question) ifthese values aren't whole numbers Produce
