Use excel solver

Applied Technology, Inc. (ATI), produces bicycle frames using two fiberglass materials that improve the strength-to-weight ratio of the frames. The cost of the standard grade material is $7.50 per yard and the cost of the professional grade material is $9.00 per yard. The standard and professional grade materials contact different amounts of fiberglass, carbon fiber and Kevlar as shown in the table below:

	Standard Grade	Professional Grade
Fiberglass	84%	58%
Carbon Fiber	10%	30%
Kevlar	6%	12%

ATI signed a contract with a bicycle manufacturer to produce a new frame with carbon fiber content of at least 20% and a Kevlar content of not greater than 10%. To meet the required weight specification, a total of 30 yards of material must be used for each frame.

1. Formulate a linear program to determine the number of yards of each grade of material that ATI should use in each frame to minimize total cost. Define the decision variables and indicate the purpose of each constraint.
2. Use the graphical solution procedure to determine the feasible region. What is the optimal solution?
3. The distributor of the fiberglass material is currently overstocked with professional grade material. To reduce inventory, the distributor offered ATI the opportunity to purchase the professional grade for $8 per yard. Will the optimal solution change?
4. Suppose the distributor further lowers the price of the professional grade material to $7.40 per yard. Will the optimal solution change? What effect would an even lower price for the professional grade material have on the optimal solution? Explain.