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 $$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. Please show how you worked it out$/$reasoning why in your answers: $($a$)$ Formulate a linear programming model for this problem. $($c$)$ How much would the profit per cross$-$country bike have to be for the profit$-$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 afford to pay them? $($f$)$ If John were to hire an additional worker for $30$ hours at $$10$ per hour, how much additional profit 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? $($h$)$ If per unit profit 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, labour and storage constraints? Is there any relationship between slacks and shadow prices? Microsoft Excel $16\mathrm{.}0$ Answer Report Worksheet: $[$example$.$xls$]$Bike Problem Report Created: $30/09/20191$:$13$:$59$ PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: $0\mathrm{.}047$ Seconds. Iterations: $2$ Subproblems: $0$ Solver Options Max Time $100$ sec$,$ Iterations $100,$ Precision $0\mathrm{.}000001$ Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance $5\%,$ Solve Without Integer Constraints, Assume NonNegative Objective Cell $($Max$)$ Original Value Cell Name Final Value $B$$6$ Objective Function Value $36003600$ Variable Cells Original Value Cell Name Final Value Integer Decision Variables Value Road $33$ $B$$5$ $C$$5$ $D$$5$ Decision Variables Value Cross$-$country Contin Contin $00$ Decision Variables Value Mountain $66$ Contin Constraints Cell Slack $F$$10$ $F$$11$ $F$$12$ $F$$13$ Name Capital Constraint Labor Hours Constraint Storage Constraint Past Sales Cell Value Formula Status $9000$ $F$$10<=$$G$$10$ Not Binding $120$ $F$$11<=$$G$$11$ Binding $9$ $F$$12<=$$G$$12$ Not Binding $0$ $F$$13<=$$G$$13$ Binding $30000110$Microsoft Excel $16\mathrm{.}0$ Sensitivity Report Worksheet: $[$example$.$xls$]$Bike Problem Report Created: $30/09/20191$:$13$:$59$ PM Variable Cells Final Reduced Objective Coefficient Cell Name Value Cost $30600$ $B$$5$ $C$$5$ $D$$5$ Decision Variables Value Road Decision Variables Value Cross$-$country Decision Variables Value Mountain Allowable Allowable Increase Decrease $1$E$+30290\mathrm{.}90909093201$E$+309006000-32040030060$ Constraints Final Shadow Constraint Allowable Allowable Value Price R$.$H$.$ Side Increase Decrease $0120003000$ Cell Name $F$$10$ Capital Constraint $F$$11$ Labor Hours Constraint $F$$12$ Storage Constraint $F$$13$ Past Sales $90001209301$E$+30401$E$+301202012001101800107\mathrm{.}5$