Home Insert Draw Page Layout Formulas Data Review View ? Tell me LE Share Comments Calibri (Body) v 11 AA a Wrap Text v General Insert v Ex AY- O. x Delete Paste BI UVV DVAv = E Merge & Center $ ~ % 9 00 308 Conditional Format Cell Sort & Find & Analyze Sensitivity Formatting as Table Styles Format Filter Select Data H33 4 X V fx A B C D F G H K L M N P Q R S T U V W X Exercise 1 - Resource Allocation Your company makes four different products (Products A, B, C and D). Each unit of product requires the following resources of each type: Inputs Resources Needed Per Unit of Product Resources Needed Per Unit of Product Available ResourceProduct AProduct BProduct CProduct D Resource Product A Product B Product C Product D Resources Metal (ounces) 10 8 9 Metal (ounces) 10 505 Wood (ounces) 6 7 8 6 Wood (ounces 420 Assembly (hours) 3 3 3 2 Assembly (hours) 3 2 300 Profit (per unit) $8 $10 $7 In addition, each product generates the following amount of Profit (per unit). Product 1Product 2Product 3Product 4 Profit (per unit)$ 9 5 8 $ 10 5 Lastly, you have the following amount of each resource available: ResourceAvailable Resources Metal (ounces)505 Wood (ounces)420 Assembly (hours)300 STEP 1)On the sheet "Exercise 1", formulate and solve an optimization model to MAXIMIZE TOTAL PROFIT 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. W N - 8 34 35 D Exercise1 Exercise 2 +