Company aims to determine the optimal number of products to be produced in order to maximize the total profit.a)Formulate the problem using algebraic method.b) Solve the model using the graphical method (indicate optimal solution and profit).C)again).Use graphical method to determine the shadow price for each of these resources (based on the definition of shadow price and by increasing each resource by one unit and solving the problemd)Use the Excel solver to do parts b and c.e)follow:Using Solver Table generate the optimal solution and the total profit for each resource ase1: Consider unit profit for product 1 (use range from 0 to 4 and increment of 1)e2: Consider unit profit for product 2 (use range from 0 to 4 and increment of 1)е3: Consider simultaneous changes for both unit profits in part e1 and e2 using given ranges.e4: Consider available resource of Raw material 1 (use range from 2 to 14 and increment of 1)e5: Consider available resource of Raw material 2 (use range from 2 to 10 and increment of 1)e6: Consider simultaneous changes for both resources in part e4 and e5 using given ranges.f)Using Excel Solver generate a sensitivity report. Determine allowable ranges for coefficients of objective function and Right-hand side resources. Explain what they mean. Find the shadow price for each resource, explain their meaning and advantages/flexibilities they can provide for management. Question 1:A manufacturing plant is producing the following products using available raw materials shown inthe below Table.ResourceRaw material 1Raw material 2Unit profitd)e)follow:Resource usage per unit productProduct 2312Product 1111Available resource84Company aims to determine the optimal number of products to be produced in order to maximizethe total profit.a)Formulate the problem using algebraic method.b)Solve the model using the graphical method (indicate optimal solution and profit).c)Use graphical method to determine the shadow price for each of these resources (based onthe definition of shadow price and by increasing each resource by one unit and solving the problemagain).Use the Excel solver to do parts b and c.Using Solver Table generate the optimal solution and the total profit for each resource ase1: Consider unit profit for product 1 (use range from 0 to 4 and increment of 1)e2: Consider unit profit for product 2 (use range from 0 to 4 and increment of 1)e3: Consider simultaneous changes for both unit profits in part el and e2 using given ranges.e4: Consider available resource of Raw material 1 (use range from 2 to 14 and increment of 1)e5: Consider available resource of Raw material 2 (use range from 2 to 10 and increment of 1)e6: Consider simultaneous changes for both resources in part e4 and e5 using given ranges.f)Using Excel Solver generate a sensitivity report. Determine allowable ranges for coefficientsof objective function and Right-hand side resources. Explain what they mean. Find the shadow pricefor each resource, explain their meaning and advantages/flexibilities they can provide formanagement.

Find the given details below: ResourceResource UsageAvailable ResourcesProduct 1Product 2Raw…

A manufacturing plant is producing the following products using available raw materials shown in the below Table. Resource d) e) follow: Raw material 1 Raw material 2 Unit profit Resource usage per unit product Product 2 3 1 2 Product 1 1 1 1 Available resource 8 4 Company aims to determine the optimal number of products to be produced in order to maximize the total profit. a) Formulate the problem using algebraic method. b) Solve the model using the graphical method (indicate optimal solution and profit). c) Use graphical method to determine the shadow price for each of these resources (based on the definition of shadow price and by increasing each resource by one unit and solving the problem again). Use the Excel solver to do parts b and c. Using Solver Table generate the optimal solution and the total profit for each resource as e1: Consider unit profit for product 1 (use range from 0 to 4 and increment of 1) e2: Consider unit profit for product 2 (use range from 0 to 4 and increment of 1) e3: Consider simultaneous changes for both unit profits in part e1 and e2 using given ranges. e4: Consider available resource of Raw material 1 (use range from 2 to 14 and increment of 1) e5: Consider available resource of Raw material 2 (use range from 2 to 10 and increment of 1) e6: Consider simultaneous changes for both resources in part e4 and e5 using given ranges. f) Using Excel Solver generate a sensitivity report. Determine allowable ranges for coefficients of objective function and Right-hand side resources. Explain what they mean. Find the shadow price for each resource, explain their meaning and advantages/flexibilities they can provide for management.

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...