mathematical model and solve using MS-Excel Solver

Problem #2: Reclaiming Solid Wastes The SAVE-T COMPANY operates a reclamation center that collects four types of solid waste materials and treats them so that they can be amalgamated into a salable product. (Treating and amalgamating are separate processes.) Three different grades of this product can be made (see the first column of Table P2.1), depending upon the mix of the materials used. Although there is some flexibility in the mix for each grade, quality standards may specify the minimum or maximum amount allowed for the proportion of a material in the product grade. (This proportion is the weight of the materialexpressed as a percentage of the total weight for the product grade.) For each of the two higher grades, a fixed percentage is specified for one of the materials. These specifications are given in Table P2.1 along with the cost of amalgamation and the selling price for each grade. The reclamation center collects its solid waste materials from regular sources and so is normally able to maintain a steady rate for treating them. Table P2.2 gives the quantities available for collection and treatment each week, as well as the cost of treatment, for each type of material. The Save-It Co. is solely owned by Green Earth, an organization devoted to dealing with environmental issues, so Save-It's profits are used to help support Green Earth's activities. Green Earth has raised contributions and grants, amounting to $30,000 per week, to be used exclusively to cover the entire treatment cost for the solid waste materials. The board of directors of Green Earth has instructed the management of Save-It to divide this money among the materials in such a way that at least half of the amount available of each material is actually collected and treated. These additional restrictions are listed in Table P2.2. Within the restrictions specified in Tables P2.1 and P2.2, management wants to determine the amount of each product grade to produce and the exact mix of materials to be used for each grade. The objective is to maximize the net weekly profit (total sales income minus total amalgamation cost), exclusive of the fixed treatment cost of $30,000 per week that is being covered by gifts and grants. Table P2.1 Product data for Problem #2 Amalgamation Selling Price Specification Cost per Pound (5) per Pound (5) Grade A 3.00 8.50 Material 1: Not more than 30% of total Material 2: Not less than 40% of total Material 3: Not more than 50% of total Material 4: Exactly 20% of total B Material 1. Not more than 50% of total Material 2 Not less than 10% of total Material 4: Exactly 10% of total 2.50 7.00 Material : Not more than 70% of total 2.00 5.50 Table P2.2 Solid waste materials data for Problem #2 Pounds per Treatment Cost per Pound (5) Additional Restrictions 1. For each material, at least half of the Material Week Available 1 2 3 3,000 2,000 4,000 1,000 3.00 6.00 4.00 5.00 pounds per week available should be collected and treated 2. 530,000 per week should be used to treat these materials a. Formulate a linear programming model for it, b. Display the model on an MS-Excel spreadsheet, and c. Use MS-Excel Solver to solve this model by the simplex method