3 A Exercise 1 - Resource Allocation Inputs Resources Needed Per Unit of Product Product Product Product Product Available Resource 1 2 3 4 Resources Plastic (ounces) 8 8 8 320 Electrical components . 5 5 4 220 Wiring (hours) 3 4 2 175 Assembly (hours) 3 2 3 110 Revenue (per unit) $ 10 S 12 $ 12 s 11 NINA the following resources of each type: Resources Needed Per Unit of Product Resource Product 1 Product 2 Product 3 Product 4 Plastic (ounces)8888 Electrical components 4554 Wiring (hours) 2 342 + Assembly (hours) 2 3 2 3 In addition, each product generates the following amount of revenue (per unit). Product 1 Product 2 Product 3 Product 4 Revenue (per unit) $ 10 $ 12 S 12 $ 11 Lastly, you have the following amount of each resource available: Resource Available Resources Plastic ounces) 320 Electrical components 220 Wiring (hours) 175 Assembly (hours) 110 STEP 1) On the sheet "Exercise 1", formulate and solve an optimization model to MAXIMIZE TOTAL REVENUES in Excel. Be sure the Solver dialog box is populated and the correct (optimal answer) is displayed in the objective cell 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 Exercise 2: Transportation Problem an 2 C 3 INPUTS 4 C 5 6 7 8 9 10 Um Costlinars Par Palevibran Fan's Cushmer Customer Supply Plant A B D Plant 1 $6 $ 8 $ 6 $ 4 1 100 2 $ 5 $ 13 $ 7 $ 6 2 100 3 $ 4 $ 12 $ 5 $ 7 3 100 Customer A B D Demands 60 70 90 80 Pallets E 11 12 MIN Your company manufactures product at three different plants (1, 2 and 3) and ships them to four different Customers (A,B,C and D). The cost to ship one unit (one pallet) from each Plant to each Customer is shown below. Unit Cost (in dollars Per Pallet) from Plant to Customer Customer Plant ABCD 1$ 6 $ 8 $ 6 $ 4 2 $ 5 $ 13 $ 7 $ 6 3 $ 4 $ 12 $ 5 $ 7 A Each plant has a capacity (supply) of so many Pallets on hand, and each Customer has a demand of so many Pallets, as shown below. Supply (# Pallets) Plant 1100 2 100 3 100 Customer ABCD Demands 60 70 90 80 Pallets STEP 1 On the worksheet "Exercise 2", formulate and solve an optimization model to MINIMIZE TOTAL SHIPPING COSTS in Excel. Be sure the Solver dialog box is populated and the correct (optimal answer) is displayed in your objective cell when you submit your file. STEP 2) In a cell or textbox in the worksheet containing this problem, indicate HOW MANY of each product should be shipped FROM EACH PLANT TO EACH CUSTOMER. For example, how much should be shipped from Plant 1to Customer A? From Plant 1to customer B? etc. (Note there are potentially up to 12 different amounts shipped from three plants to four customers.) STEP 3) In another cell or textbox in the worksheet containing this problem, indio ate what the minimum total shipping cost will be