Question

John owns a bike shop. Most of John's bicycle sales are customer orders, however, he also stocks bicycles for walk in customers. He stocks 3 types of bicycles - road racing, cross-country, and mountain. He sells road racing bikes for $1,800, cross-country bikes for $2,100 and mountain bikes for $1,200.

- A road-racing bike costs $1,100, a cross-country bike costs $1,600, and a mountain bike costs $850. 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

- John wants to know how many bikes of each type he should stock in order to maximize profit

- Write out the model and solve using excel

Answer 1

Let,

R = No. of road-racing bikes
C = No. of cross-country bikes
M = No. of mountain bikes

Max Z = total profit = (1800 - 1100) R + (2100 - 1600) C + (1200 - 850) M = 700 R + 500 C + 350 M

Subject to,

1100 R + 1600 C + 850 M <= 12000 (budget)

8 R + 12 C + 18 M <= 120 (assembly hrs.)

R + C + M <= 20 (space)

M >= 2*(R + C) (ratio)

R, C, M >= 0, Integers

Excel model

Solver inputs:

Solution (Answer report)

Objective Cell (Max)
	Cell	Name	Original Value	Final Value
	$F$4	Profit	0	3150
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$C$3	Value of R	0	2	Integer
	$D$3	Value of C	0	0	Integer
	$E$3	Value of M	0	5	Integer