Assignment 4 (Total points = 17) 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 - road-racing, cross-country, and mountain. A road-racing bike costs $1,300, a cross- country bike costs $1,800, and a mountain bike costs $1000. He sells road-racing bikes for $1,800, cross-country bikes for $2,100, and mountain bikes for $1,200. He has $13,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 25 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. Solve the problem using excel solver and then apply sensitivity analysis to answer the following questions. a. Should John Hoke try to increase his budget for purchasing bikes, increase space to stock bikes, or increase labor hours to assemble bikes? Why? b. If John were to hire an additional worker for 40 hours at $10 per hour, how much additional profit would he make, if any? c. If John were to purchase a cheaper cross-country bike for $1,200 and sell it for $2,000, would this affect the original solution? Instructions: As mentioned above, this assignment focuses on the concept of sensitivity analysis. Note the terms dual price, shadow price and unit worth of resource have the same meaning. Solve the problem using excel solver. Provide the answer to sub-questions (a) - (c) using information that is part of the sensitivity report and not by resolving the problem and by generating multiple answer reports. If you resolve the problem to derive answers to questions (a) - () then you will lose points as it nullifies the purpose of this assignment and value the concept of sensitivity analysis brings when using linear programming technique. Please provide an appropriate and thorough justification for each of your answers using information that is part of the sensitivity report. Since the question involves the use of excel, please submit your excel file and relevant reports. Assignment 4 (Total points = 17) 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 - road-racing, cross-country, and mountain. A road-racing bike costs $1,300, a cross- country bike costs $1,800, and a mountain bike costs $1000. He sells road-racing bikes for $1,800, cross-country bikes for $2,100, and mountain bikes for $1,200. He has $13,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 25 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. Solve the problem using excel solver and then apply sensitivity analysis to answer the following questions. a. Should John Hoke try to increase his budget for purchasing bikes, increase space to stock bikes, or increase labor hours to assemble bikes? Why? b. If John were to hire an additional worker for 40 hours at $10 per hour, how much additional profit would he make, if any? c. If John were to purchase a cheaper cross-country bike for $1,200 and sell it for $2,000, would this affect the original solution? Instructions: As mentioned above, this assignment focuses on the concept of sensitivity analysis. Note the terms dual price, shadow price and unit worth of resource have the same meaning. Solve the problem using excel solver. Provide the answer to sub-questions (a) - (c) using information that is part of the sensitivity report and not by resolving the problem and by generating multiple answer reports. If you resolve the problem to derive answers to questions (a) - () then you will lose points as it nullifies the purpose of this assignment and value the concept of sensitivity analysis brings when using linear programming technique. Please provide an appropriate and thorough justification for each of your answers using information that is part of the sensitivity report. Since the question involves the use of excel, please submit your excel file and relevant reports