Linear Programming Using Excel Solver. Olympic Bike Co$.$ is introducing two

new lightweight bicycle frames, the Deluxe and the Professional, to be made from special

aluminum and steel alloys. The anticipated unit profits are $$10$ for the Deluxe and $$15$ for the

Professional. A supplier delivers $100$ pounds of the aluminum alloy and $80$ pounds of the of the

steel alloy weekly. The number of pounds of each alloy need per frame is summarized in the

following table:

Aluminum Alloy Steel Alloy

Deluxe $23$

Professional $42$

There is an additional constraint in that the number of Deluxe frames produced must be greater

than or equal to the number of Professional frames produced. The objective is to maximize total

profit subject to the limits on the availability of aluminum and steel and the additional production

constraint. Please answer the following questions. Note that the answers for all the questions

should be solely based on the Answer Report and Sensitivity Report produced in part $($a$)$ below.

$($a$)(10$ points$)$ Solve the linear programming problem using an Excel spreadsheet. Produce

the Answer Report and Sensitivity Report from Excel Solver.

$($b$)(10$ points$)$ What is the optimal number of Deluxe frames and Professional frames

produced? What is the optimal profit?

$($c$)(10$ points$)$ Suppose the unit profit on the Deluxe frames is increased to $$20$ per frame.

Does the optimal solution change? What is the value of the objective function in this

case? Explain your answers.

$($d$)(10$ points$)$ Suppose the unit profit on Deluxe frames is decreased to $$6$ per frame. Does

the optimal solution change? Explain your answer.

$($e$)(10$ points$)$ What is the maximum amount the company pay for an extra pound of

aluminum? What is the maximum amount the company should pay for $3$ extra pounds of

steel? Explain your answers.

$($f$)(10$ points$)$ If the company is forced to produce $1$ more Deluxe frame over and above the

number of Professional frames produced, how much would total profit change? Explain

your answer.