How to do this using excel solver? 1 2 3 4 5 6 7 8 9 10

How to do this using excel solver?

 

Transcribed Image Text:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 20 Inputs Exercise 1 - Resource Allocation Resources Needed Per Unit of Product Resource Product 1 Product 2 Product 3 Product 4 Plastic (ounces) 6 6 6 6 3 4 4 3 2 1 1 1 Product 1 8 1.5 2 Product 2 1 2 Product 3 Product 4 $ 10 $ 11 $ 9 Electrical components Wiring (hours) Assembly (hours) Revenue (per unit) $ Plastic (ounces) Electrical components Wiring (hours) Assembly (hours) Available Resources 250 120 80 75 Your company makes four different products (Products 1, 2, 3 and 4). Each unit of product requires the following resources of each type: Resources Needed Per Unit of Product Resources Product 1 Product 2 Product 3 Product 4 Plastic (ounces) 66 66 Electrical components 3 4 4 3 Wiring (hours) 11.5 21 Assembly (hours) 1212 In addition, each product generates the following amount of revenue (per unit). Product 1 Product 2 Product 3 Product 4 $ 8 $ 10 $ 11 $ 9 Lastly, you have the following amount of each resource available: Resource Available Resources Plastic (ounces) 250 Electrical components 120 Wiring (hours) 80 Assembly (hours) 75 (Step 1) Formulate and solve an optimization model in Excel. Be sure the Solver dialog box is populated and the correct (optimal answer) is displayed when you submit your file. (Step 2) In a cell or textbox in the worksheet containing this problem, indicate HOW MANY of each product the company should make AND what the maximum revenue will be. (Step 3) In another cell or textbox in the worksheet containing this problem, indicate which resources are "scarce" meaning they are preventing you from making more products and therefore more revenue. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 20 Inputs Exercise 1 - Resource Allocation Resources Needed Per Unit of Product Resource Product 1 Product 2 Product 3 Product 4 Plastic (ounces) 6 6 6 6 3 4 4 3 2 1 1 1 Product 1 8 1.5 2 Product 2 1 2 Product 3 Product 4 $ 10 $ 11 $ 9 Electrical components Wiring (hours) Assembly (hours) Revenue (per unit) $ Plastic (ounces) Electrical components Wiring (hours) Assembly (hours) Available Resources 250 120 80 75 Your company makes four different products (Products 1, 2, 3 and 4). Each unit of product requires the following resources of each type: Resources Needed Per Unit of Product Resources Product 1 Product 2 Product 3 Product 4 Plastic (ounces) 66 66 Electrical components 3 4 4 3 Wiring (hours) 11.5 21 Assembly (hours) 1212 In addition, each product generates the following amount of revenue (per unit). Product 1 Product 2 Product 3 Product 4 $ 8 $ 10 $ 11 $ 9 Lastly, you have the following amount of each resource available: Resource Available Resources Plastic (ounces) 250 Electrical components 120 Wiring (hours) 80 Assembly (hours) 75 (Step 1) Formulate and solve an optimization model in Excel. Be sure the Solver dialog box is populated and the correct (optimal answer) is displayed when you submit your file. (Step 2) In a cell or textbox in the worksheet containing this problem, indicate HOW MANY of each product the company should make AND what the maximum revenue will be. (Step 3) In another cell or textbox in the worksheet containing this problem, indicate which resources are "scarce" meaning they are preventing you from making more products and therefore more revenue.

Answer rating: 100% (QA)

Let a be the units produced of product 1 b be the ... View the full answer