the basic solver.

linsuant 

I asked the AI...here's the result. Haven't tried it... so try it and please give feedback.

To solve this problem with Excel, you can use the Solver add-in to find the optimal production mix that maximizes profit while satisfying the constraints.

Here's how you can set up the optimization model using Solver in Excel for Mac:

1. Create a new workbook and enter the following data in cells A1 to E6:

1. In cell F2, enter the formula =B2*D2 to calculate the profit contribution for producing one unit of Aqua.
2. Similarly, enter the formulas =B3*D3 and =B4*D4 in cells F3 and F4, respectively, to calculate the profit contribution for Hydro and Typhoon.
3. In cell F6, enter the formula =SUM(F2:F4) to calculate the total profit contribution.
4. In cells G2 to G4, enter the corresponding number of units of each product to produce.
5. In cells G5 to I5, enter the following formulas to calculate the total number of pumps, labor hours, and tubing feet required, respectively:

=SUMPRODUCT(G2:G4,$C$2:$C$4)

=SUMPRODUCT(G2:G4,$D$2:$D$4)

=SUMPRODUCT(G2:G4,$E$2:$E$4)

1. In cells G6 to I6, enter the corresponding limit values for pumps, labor hours, and tubing feet, respectively.
2. Click on the Data tab in the Excel ribbon and select Solver from the Analysis group.
3. In the Solver Parameters dialog box, set the following options:
• Set the Set Objective field to F6 (the total profit contribution).
• Set the To field to Max (to maximize the objective).
• In the By Changing Variable Cells field, enter G2:G4 (the number of units to produce).
• In the Constraints field, click on Add to add the following constraints:
• Set the Cell Reference to G5 and the Relation to <= and the Value to 200 (the maximum number of available pumps).
• Set the Cell Reference to H5 and the Relation to <= and the Value to 1566 (the maximum number of available labor hours).
• Set the Cell Reference to I5 and the Relation to <= and the Value to 2880 (the maximum length of available tubing).
• Click on OK to close the Add Constraint dialog box.
4. Click on the Solve button in the Solver Parameters dialog box to find the optimal solution.
5. If Solver finds a feasible solution, click on OK to close the Solver Results dialog box and view the optimal solution in cells G2:G4.

That's it! Solver should find the optimal production mix that maximizes the total profit contribution subject to the available resources.

NikolinoDE

I know I don't know anything (Socrates)

linsuant 

I use named formulas within 365.  The total profit and calculated resources consumed were calculated using

totalProfit
= SUM(quantity*profit)

consumed
= BYROW(resources, LAMBDA(r, SUM(r*quantity)))

Solver optimised the profit with the settings