129 X V fx A B C D E F G H J K L M N O P Q R S T U Demand and profit margin/unit for each product ProductA ProductB ProductC ProductD ProductE ProductF ProductG ProductH 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 "ProductMix" worksheet. Demand 50 50 100 100 100 75 75 75 75 Profit Margin $ 22.50 $ 27.50 $ 22.50 $ 26.50 $ 25.00 $ $ 27.50 $ 27.50 $ 30.00 $ 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 Number of units of each resource that is required for production of one unit of each product Aggregate (to zero decimals). ProductA ProductB ProductC ProductD ProductE ProductF ProductG ProductH ProductI Capacity Workload Resource1 8 6 6 6 5 8 4,700 b) In cell M20, determine the total profit of the proposed product mix, based on the profit Resource2 10 6 10 4,700 margins for each product provided in row 5. Resource3 4,700 c) You should have found, in a, that the proposed product mix results in one or more Resource4 8 8 10 7 4,700 resources having an aggregate workload that exceeds capacity. Assuming that this makes Resource5 6 10 9 4,700 the proposed product mix infeasible, determine the product mix that maximizes profit Resource6 5 10 10 10 4,700 without exceeding aggregate workloads or demand (using Solver). Report the new total 15 DO UT Resource7 5 Do 00 4,700 profit (to two decimals). 16 Resources 4,700 17 18 Management's proposed "product mix" (i.e. number of each product they propose to produce 19 ProductA ProductB ProductC ProductD ProductE ProductF ProductG ProductH ProductI Total Profit 20 Produce: 50 50 80 80 100 75 50 75 75 21 *note - when Solving, it is OK (for the purpose of this question) if these values aren't whole numbers