Using solver to maximize with results being normally distributed

[Kettie]

How can I optimization with solver in excel to maximize profit of 7 different inputs with the inputs being normally distributed?

[#2]

@Kettie 

To use Excel's Solver tool to maximize profit while considering normally distributed inputs, you will need to set up your model correctly. The Solver tool in Excel is used for optimization problems, and you can use it to find the optimal values for your input variables that maximize a target output, such as profit.

Here is a step-by-step guide on how to set up your model (without opening your file):

1. Data and Inputs: Organize your data in an Excel spreadsheet. Let's assume you have the following setup:
• Column A: Input Variables (X1, X2, ..., X7)
• Column B: Mean of the inputs
• Column C: Standard Deviation of the inputs
2. Formulate Profit Function: Define a cell where you will calculate the profit based on your inputs. Let us assume this cell is D2. In D2, you can use a formula to calculate the profit based on the input variables and any relevant coefficients. Your formula might look something like:

=A2*X1 + A3*X2 + A4*X3 + A5*X4 + A6*X5 + A7*X6 + A8*X7

Here, A2 to A8 are placeholders for coefficients related to each input variable.

1. Define the Objective Function: In another cell (E2 for example), calculate the negative of the profit. Since Solver minimizes, but you want to maximize, you will minimize the negative of your objective. So, in cell E2:

=-D2

1. Set Up Constraints: If you have any constraints on the input variables, define them. Constraints can be things like upper and lower limits on the inputs. You can set these up in separate cells, and then refer to these cells in the Solver setup.
2. Install and Open Solver: Solver might not be installed by default in Excel, so you might need to install it. You can usually find it in the "Add-Ins" section of Excel's options.
3. Solver Configuration:
• Open Solver from the "Data" tab.
• Set the "Objective" to the cell where you calculated the negative profit (E2).
• Choose "Max" for the objective, since you want to maximize profit.
• Set the "By Changing Variable Cells" to the input variable cells (X1 to X7, for example).
• Add any relevant constraints based on your model.
4. Solver Options: In the Solver options, you can choose the method that Solver should use to find the solution. For smooth and continuous problems like this, the default options usually work well.
5. Solver Parameters: You might want to set specific parameters like tolerance for convergence and maximum iterations. These depend on your problem and how accurate you want the solution to be.
6. Solve: Click the "Solve" button. If Solver finds a solution, it will adjust the input variables (X1 to X7) to maximize the calculated profit.

Please note that if you want to model inputs as normally distributed, you will need to consider how to incorporate randomness in your model. In this basic example, the inputs are treated as deterministic values. If you want to introduce randomness, you might need to use Excel's functions for generating random numbers from a normal distribution and then run the Solver multiple times to understand the distribution of outcomes. This adds a layer of complexity to the problem.

My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together.

My answers are voluntary and without guarantee!

Hope this will help you.

[#3]

The sample workbook provided and the data is good in the sense that I think everything is there that's needed. I was not sure what you wanted to use for the inputs and constraint(s). This kind of problem can be solved with Solver or a Lambda.