Question

Jon Hoke owns a bicycle shop. He stocks three types of bicycles: road-racing, cross-country

and mountain. A road-racing bike costs $1,200, a cross-country bike costs $1,700 and a

mountain bike costs $900. He sells road-racing bikes for $1,800, cross-country bikes for $2,100

and mountain bikes for $1,200. He has $12,000 available this month to purchase bikes. Each

bike must be assembled: a road-racing bike requires 8 hours to assemble, a cross-country bike

requires 12 hours and a mountain bike requires 16 hours. He estimates that he and his

employees have 120 hours available to assemble bikes. He has enough space in his store to

order 20 bikes this month. Based on past sales, John wants to stock at least twice as many

mountain bikes as the other two combined because mountain bikes sell better. Formulate

(develop the objective function and constraints) a linear programming model for this problem

where the Jon’s objective is to maximize total profits. Generate the solution using Excel Solver.

Please include solver explanation (step by step)

Answer 1

Let,

x1 = Number of road-racing bicycles to be assembled
x2 = Number of cross-country bicycles to be assembled
x3 = Number of mountain bicycles to be assembled

Objective function = max (1800-1200)x1+(2100-1700)x2+(1200-900)x3 = Max 600x1+400x2+300x3

Subject to,

1200x1+1700x2+900x3 <= 12000 (budget)
8x1+12x2+16x3 <= 120 (Assembly time)
x1+x2+x3 <= 20 (Space)
x3 >= 2(x1+x2) (Marketing: mountain >= twice of other bikes)
-2x1-2x2+x3 >= 0

x1,x2,x3 >= 0 (Non-negativity constraint)

Solving in solver we get,

x1 = Number of road-racing bicycles to be assembled = 3
x2 = Number of cross-country bicycles to be assembled = 0
x3 = Number of mountain bicycles to be assembled = 6

maximized profit = $3600

Solver screenshot

Solver formula

Explanation:

1. Add decision variables and constraints as shown in the screenshot

2. Add objective function equation in cell I3 as shown in the solver formula image

3. Add all the formulas likewise just as shown in the solver formula image

4. Next click on Data at excel top menu -> Solver -> and add constraints as mentioned in the below image

Select Simplex LP as I have selected in the above image and click on Solver to get the answer you can see value populating at B3,C3,D3 and I3