Question

John Hoke owns Hoke’s Spokes, a bicycle shop. Most of John’s bicycle sales are customer orders. However, he also stocks bicycles for walk-in customers. He stocks three types of bicycles: 1) road bike, 2) e-bike and 3) mountain bike. From his overseas supplier, the cost to obtain each type of bike are: road bike $1,200, e-bike $1,700 and mountain bike $900. He sells road bikes for $1,800, e-bikes for $2,100 and mountain bikes for $1,200. His profit is the difference between sales price and cost. He has $12,000 available this month to import bikes. Each bike must be assembled; a road bike requires 8 hours to assemble, an e-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 display space in his store for 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.

(a) Formulate a linear programming model for this problem and write it down in the space here:

(b) Solve the linear programming model formulated in (a) using “solver” tool in Excel, making sure to request an answer report and a sensitivity report (attach these reports to the Word file). What is the optimal number of each type of bike to import? Road bike ____ e-bike ____ Mountain bike _____

(c) Should John Hoke try to increase his budget for purchasing bikes, or is it better for him to rent extra space to stock more bikes or is it better to increase labour hours to assemble bikes? Why? John is better to increase the assembly hours he has available

(d) By how much would the profit margin (in $) have to rise on an e-bike before it would be optimal to include at least one e-bike in John’s import order? Explain your answer.

(e) Suppose that John’s employees went on strike and demand an increase of $5 per hour. Can he afford to pay them? Explain your answer.

Answer 1

Ans (a) : Formulating a LP model for John: Let us consider

r, e, and m denote number of road bike, e-bike, and mountain bikes to order this month, respectively.

Max Z = 600r + 400e +300m

Budget constraint : 900m+1700e+1200r ≤ 12000

Assembly time constraint : 16m+12e+8r ≤ 120

Space constraint : r + e+ m ≤ 20

Marketing constraint : m-2e-2r ≥ 0

And say   m,e,r ≥ 0

Ans(b) : Error Uploading excel file. Therefore here I'm presenting the excel sheet view using solver tool,

Bicycles Unit profit	Road-Bike 600	E-bike 400	Mountain Bike 300			Profit 3600
Opt values	3	0	6
Constraints				LHS		RHS
Budget	1200	1700	900	9000	<	12000
Assembly	8	12	16	120	<	120
Space	1	1	1	9	<	20
Marketing	-2	-2	1	0	>	0

Adjustable Cells
Cell     Name	Final Value	Reduced Cost	Objective Coefficient	Allowable Increase	Allowable Decrease
$B$3 Opt values Road-bike	3	0	600	1E+30	290.909
$C$3 Opt values E-bike	0	-320	400	320	1E+30
$D$3 Opt values Mountain bike	6	0	300	900	600
Constraints
	Final	Shadow	Constraint	Allowable	Allowable
Cell     Name	Value	Price	R.H. Side	Increase	Decrease
$E$5 Budget LHS	9000	0	12000	1E+30	3000
$E$6 Assembly LHS	120	30	120	40	120
$E$7 Space LHS	9	0	20	1E+30	11
$E$8 Marketing LHS	0	-180	0	7.5	10

Ans(c). Firstly John should make an increment in Labour hours, as we know that this constraint is of binding nature and possess a plus (+) shadow price behaviour.

Ans(d). The profit of $400 margin have to rise on an e-bike before it would be optimal to include at least one e-bike in John’s import order as for the e-bike the net profit per unit is $400, and initially the order sizes of the bikes are 3 road bikes, zero e bikes and 6 mountain bikes.