Check Your Understanding 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-racing, 2) cross-country and 3) mountain. A road racing bike costs $1,200, a cross-country bikes cost 31.100 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-cormny 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. (a) Formulate a linear programming mode] for this problem. (b) Solve the linear programming model formulated in (a) using \"solver" tool in Excel. Your solutions shouid be buying 3 road-racing bikes, 0 cross-country bike and 6 mountain bikes. Use the attached answer and sensitivity reports to answer the following questions. (c) How much would the prot per cross-country bike have to be for the prot maximizing solution for John to purchase any of cross-country bikes? (d) Should John Hoke try to increase his budget for purchasing bikes, increase space to stock bikes or increase labor hours to assemble bikes? Why? (e) Suppose that John's employees went on strike and demand an increase of $ 5 per hour. Can he aorrl to pay them? (f) If John were to hire an additional worker for 30 hours at $10 per hour, how much additional prot would he make, if any? (g) If John were forced (for good reason) to cut down on either capital or labor hours (i.e. changing the RHS of those constraints), which one would you recommend? (11) prer unit prot for road-racing bike increases to $900, would the optimal solution change? How about the value of the objective function? (i) What are the slacks on the capital, labor and storage constraints? Is there any relationship between slacks and shadow prices? \fECONSZOS Check Your Understanding Topic 7 {Linear Programming) Microsoft Excel 16.0 Sensitivity Report Worksheet: [example.xls]Bike Problem Report Created: 3010912019 1:13:59 PM Variable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefcient Increase Decrease $B$5 Decision Variables Value Road 3 0 600 1E+30 2909090909 $C$5 Decision Variables Value Crosscountry 0 320 400 320 1E+30 $D$5 Decision Variables Value Mountain 6 0 300 900 600 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $F$10 CaQital Constraint 9000 0 12000 1E+30 3000 $F$11 Labor Hours Constraint 120 30 120 40 120 $F$12 Storage Constraint 9 0 20 1 E+30 1 1 $F$13 Past Sales 0 180 0 1D 7.5
