A company manufactures four types of bicycles: standard, cruiser, sport, and off-road. Over the coming month, they need assistance in determining how many of each bicycles to manufacture to maximize their profits. 

Bicycles have to go through four industrial processes in order to be manufactured: stamping, welding, assembly, and inspection. In addition, there are storage limits as all of the produced items must be stored in the warehouse. Finally, the company has determined the minimum number of each bicycle they have to make to satisfy customers demand. 

The following tables contains all of the relevant data for each bicycle type including the amount of time required in each department (in hours), the storage requirement (in sq.ft), and the profit per unit along with the minimum number of bikes required.

Bicycles	Stamping	Welding	Assembly	Inspection	Storage Space
Standard	1.3	2.3	2.1	1.5	4.2
Cruiser	1.9	3.1	2.4	2.3	5.6
Sport	3.5	3.3	3.3	3.2	5.0
Off-road	4.2	4.0	4.5	4.0	6.2
Limits	8000	8000	8000	8000	30000
Bicycles	Min Sales	Profit
Standard	50	115
Cruiser	50	310
Sport	100	400
Off-road	100	225

A company wants to determine how many of each bicycle should be manufactured given all of the limitations such that they achieve the maximum possible profit. Solve the linear optimization problem in Excel to determine the optimal production plan (do not require your decision variables to be integers at this point).

 

Upload your solution.  Please make sure to scroll down to see all the questions related to this problem.

 20. Based on your solution, the optimal number of Standard Bicycles to manufacture is

21. Based on your solution, the optimal number of Cruiser Bicycles to manufacture is

22. Based on your solution, the Final Value of the objective function is

23. Based on your solution, the total time needed in Assembly department for your optimal solution is

 

24. Based on the Sensitivity report generated for your solved model, changing cell Profit for each Sport Bicycle from $400 to $500 will cause the Final Value of the objective function to increase by

  

25. Based on the Sensitivity report for your solved model, changing Profit for each Cruiser Bike from $310 to $380 will cause the Final Value of the objective function to increase by

 

26. Based on the Sensitivity report for your solved model, increasing the time available for Inspection from 8,000 to 8,500 hours will cause the cause the Final Value of the objective function to increase by

  

27.Based on the Sensitivity report for your solved model, changing the time available for Stamping from 8,000 to 8,500 hours will cause the cause the Final Value of the objective function to increase by

  

28. Based on the Sensitivity report for your solved model, changing the time available for Welding from 8,000 to 7,500 hours will cause the cause the Final Value of the objective function to decrease by

 

Answer rating: 100% (QA)

Here are the stepbystep workings for each question 20 Based on the optimal solution shown in the Exc... View the full answer