The Cullowhee Bicycle Shop (CBS) stocks three types of bicycle – road, cross-country, and mountain. CBS buys these bikes from a supplier to sell. A road bike costs $1200, a cross-country cost $1400, and a mountain bike costs $900. CBS can spend up to $12,500 a month to restock their inventory. They sell road bike for $1900, a cross-country for $1800, and a mountain bike for $1200. Each bike must be assembled after they are delivered to the shop. The road bike takes 8 hours to assemble, the cross-country takes 12 hours to assemble, and the mountain bike takes 16 hours to assemble. The employees have 120 hours of available time per month to assemble the bikes. The store has room to store 15 new bike purchases per month. CBS sells more road than cross-country bikes, so they want to purchase twice as many road bikes as cross-country bikes. CBS purchase at least 2 bikes of each type every month. Assume they can sell each bike they purchase from the supplier, how many of each bike should they purchase to maximize their profit? (Hint: Profit = Revenue – Cost). Formulate and solve this problem in Excel.

a) What is the maximum profit based on your optimal solution (the value of the objective function)?

b) How many road bikes should be purchased each month based on your optimal solution?

c) How many cross-country bikes should be purchased each month based on your optimal solution?

d) How many mountain bikes should be purchased each month based on your optimal solution?

e) Identify the binding constraints based on your optimal solution?

f) Identify the slack constraints based on your optimal solution?

g) Identify the surplus constraints based on your optimal solution?