need a solver model and answers to question 1

Question 1) Priority Level 1 Goal: The hours of employment generated in economically disadvantaged areas should be at least 29,250, Priority Level 2 Goal: The total amount of hazardous waste generated annually by the suppliers should be at most 18,020 pounds. Priority Level 3 Goal. The desired annual purchase cost savings must be at least 558,000 Answer the following questions in your P3 Excel spreadsheet in Cells R1, R2 R3, R4, and R5 after solving the P3 model: a) How many units should the buyer purchase from each supplier? b) How much will total annual purchase cost savings equal? c) For each of the three goals, you must state whether you met the target for the goal exactly were over the target along with the amount over), or were under the target (along with the amount under) As shown in Table 1 below, these criteria focus on the triple bottom line of economic, environmental and social performance objectives. Table 1. Estimated Supplier Performance on the Criteria Criterion 1) Hours of Employment Supplier Supplier Supplier Supplier Supplier Supplier Supplier 1 2 3 4 5 6 5.10 3.42 3.10 1.82 0.92 1.38 5.50 per Unit Generated 2) Hazardous 2.60 2.02 2.15 1.72 2.27 1.58 2.40 Waste per Unit (lbs) $9.05 3) Purchase $9.77 $6.10 $7.12 $8125 $215 SSMO Cost Savings per Unit This table shows estimated supplier performance for all suppliers on the aree rewa considered An explanation of the supplier selection criteria follows. 1 Social Performance Criterion: Hours of employment per unit generated in an economically disadvantaged area based on where each supplier is located. 2. Environmental Criterion: Hazardous waste per unit generated by each supplier s process (stated in pounds) during the production of the material 3. Economic Criterion: Purchase cost savings per unit relative to the cost per unit from the current supplier As shown in Table 1 below these criteria focus on the triple bottom line of economic environmental and social performance objectives The case described below analyzes the selection of new suppliers to replace a current supplier of a hazardous material used in the manufacture of a company's product. The buying company must purchase exactly 7.400 units total per year of a similar, but less hazardous, material that performs the same function as the current materialthis is a hard constraint and not a goal constraint. Each supplier has hard limits on its capacity: Supplier 1 can provide at most 2.299 units Supplier 2 can provide at most 1975 units: Supplier 3 can provide at most 2,448 units: Supplier 4 can provide at most 2015 units: Supplier 5 can provide at most 925 units; Supplier 6 can provide at most 810 units; Supplier 7 can provide at most 2,500 units. These limits on supplier capacity are hard capacity constraints not goal constraints. You will need a constraint for each supplier