The Skimmer Boat Company manufactures three kinds of molded fiberglass recreational boats--a bass fishing boat, a ski boat, and a speedboat. The profit for a bass boat is $20,500, the profit for a ski boat is $12,000, and the profit for a speedboat is $22,300. The company believes it will sell more bass boats than the other two boats combined but no more than twice as many. The ski boat is its standard production mode, and bass boats and speedboats are modifications. The company has production capacity to manufacture 210 standard (ski-type) boats; however, a bass boat requires 1.3 times the standard production capacity, and a speedboat requires 1.5 times the normal production capacity. In addition, only 160 of the high-powered engines that are installed in the bass boats and 2 of those installed in the speedboats are available. The company wants to know how many boats of each type to produce to maximize profit. Formulate and solve an integer programming model for this problem.

Please provide screenshots of excel used to solve the problem and show formulas used. Also provide a screenshot of the solver parameters showing constraints.

Optimal solution:

Bass fishing boat = 110

Ski boat = 31

Speedboat = 24

Optimal Total Profit = $3,162,200

Step-by-step explanation

The decision variables (highlighted in orange color cells) are the number of Bass Fishing Boats (x), Skiboats (y), and Speedboats (z) to be produced

The objective function (highlighted in yellow color cell) is to maximize the total profit

Total Profit = Number of Bass Fishing Boats * Unit Profit for Bass Fishing Boat + Number of Skiboats * Unit Profit for Skiboat + Number of Speedboats * Unit Profit for Speedboat

Total Profit = x * 20500 + y * 12000 + z * 22300

The constraints are:

i) Total Production Time utilized should be less than or equal to the total production time available

x * 1.3 + y * 1 + z * 1.5 ≤ 210

ii) Number of high-powered engines utilized should be less than or equal to the high-powered engines available

x * 1 + y * 0 + z * 2 ≤ 60

iii) Number of bass boats produced should be more than or equal to the combined total number of ski boats and speedboats produced

x ≥ (y + z)

iv) Number of bass boats produced should be less than or equal to the twice the combined total number of ski boats and speedboats produced

x ≤ 2 * (y + z)

v) All the decision variables will take integral values

Please refer to the following Excel Solver snapshots:

The Solver Parameters and constraints are as follows:

The optimal solution is as follows:

Optimal solution:

Bass fishing boat = 110

Ski boat = 31

Speedboat = 24

Optimal Total Profit = $3,162,200

Please see the attached file for the complete solution