Problem 2 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 fixed set up costs and a 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 Fixed Cost Profit Standard 50 25000 115 Cruiser 0 50000 310 Sport 0 300000 400 Off road 100 100000 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 integer optimization problem in Excel to determine the optimal production plan (your decision variables must be integers at this point) Hint Do not forget to use Big Number concept for your forcing constraint 1 Based on the integer solution, the optimal number of Cruiser Bicycles to manufacture is Correct answer 2,414 2 Based on your integer solution, the optimal number of Standard Bicycles to manufacture is Correct answer 50 3 Based on your integer solution, the Final Value of the objective function is Correct answer $601,590 4 Based on your integer solution, if the profit for Sport Bicycles changes to $450, the Final Value of the objective function would change to Correct answer $676,750

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 16, 2024

Problem 2 A company manufactures four types of bicycles: standard, cruiser, sport, and off-road. Over the coming month, they need assistance in determining how many

Problem 2

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 fixed set up costs and a 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	Fixed Cost	Profit
Standard	50	25000	115
Cruiser	0	50000	310
Sport	0	300000	400
Off-road	100	100000	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 integer optimization problem in Excel to determine the optimal production plan (your decision variables must be integers at this point).

Hint: Do not forget to use Big Number concept for your forcing constraint.

1. Based on the integer solution, the optimal number of Cruiser Bicycles to manufacture is

Correct answer: 2,414

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

Correct answer: 50

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

Correct answer: $601,590

4. Based on your integer solution, if the profit for Sport Bicycles changes to $450, the Final Value of the objective function would change to

Correct answer: $676,750