1.Use the Resource Allocation tab in the Excel spreadsheet. There are 3 activities that utilize 3 resources as shown in the table (C8:E10). Each activity has a profit per unit that is shown in( C3:E3). Each resource has a maximum limit shown in (F8:F10).

1.Build a spreadsheet model that calculates the total profit across all the activities showing the amount of each resource used (Color code your cells (Blue, Yellow, Orange) and name your name your cells appropriately) (2 points)

2.Use the Solver to find the maximum profit and the amount of each activity utilized which represents the optimal solution(2 points)

3.Use the Sensitivity output from the Solver to answer the following questions:

a.Will the optimal solution change if you decrease the profit amount of activity 1 by $3 and increase the profit amount of activity 3 by $8 simultaneously? Prove your answer by showing that these changes represent less than 100% in total allowable % changes (1 point)

b.Use the Shadow prices to calculate the change in the total profit from the original solution by decreasing the amount of resource 1 by 1 unit, increasing the amount of resource 2 by 1 unit and increasing the amount of resource 3 by 4 units simultaneously. Prove that the shadow prices can be used by showing that the total allowable % changes represented are less than 100%. Lastly, show that the new profit amount that you calculated using the shadow prices is correct by rerunning the Solver using the new resource amounts after the changes above are completed. Did the optimal solution change with these resource changes? (1 point)