# Iterative calculus with Excel

I have this model: \begin{align}\max&\quad\small{(0.2(1.07)^{-1}+0.2(1.07)^{-2}+0.9(1.07)^{-3})x_A+0.4(1.07)^{-1}+0.5(1.07)^{-2}+0.3(1.07)^{-3})x_B}\\&\quad 0.2x_{A}+0.4x_{B}\geq300\,000 \\&\quad0.2x_{A}+0.5x_{B}\geq400\,000\\&\quad x_{A}+x_{B}=1\,000\,000 \\&\quad x_{A},x_{B}\geq0\end{align}

I have to find $$x_{A}$$ and $$x_{B}$$ with Excel. I'm inexperienced: how do I set up the program to use the iterative calculus? Thanks in advance for any help!

EDIT:

After downloading the add-on, I clicked on Solver but how have I to conduct the objective? And the constraints? I tried setting an hypothetical distribution of total investment (1.000.000), that is 200.000 and 800.000... with 360.000=F7*G15+F8*H15 and 440.000=G7*G15+G8*H15.

Then, I set up the Solver in this way…

…but I obtain:

Where I wrong? Is this normal?

EDIT 2: The early spreadsheet is in my first edit. Then, applying the Solver, I obtain:

Instead, the solution of the problem is (in all likelihood) the following:

What I don't understand is how the professor has obtained that particular distribution of the investment (200.000 and 800.000). When I use the Solver I obtain 0 and 800.000, like you can see. It was just a coincidence that I tried, immediately, with the same distribution of the solution (200.000 and 800.000). The point is that I should have gotten the distribution concerned applying the Solver, so I don't understand where I did wrong in the setting up. @ Prubin, are you really sure that the setting of the Solver is correct?

Moreover, I see now that 200.000 and 800.000 is not the distribution that meet the aim. In fact, for this distribution we have a total revenue of $$1.063.607$$, while by imposing for example 220.000 and 780.000 we obtain $$1.064.423>1.063.607$$. Shouldn'te be the Solver to find, iteratively, the best possible distribution?

• Welcome to OR SE. Feb 27 '20 at 16:46
• To add @prubin mentioned, you can use solver studio instead of OpenSolver. it has many features to use such as modelling language and etc. Also, if you are interested to see iterative tableau on your problem, this or this links might be useful. Would you see some useful youtube videos on what you want? Feb 29 '20 at 6:57
• I see nothing wrong in your input to the solver (assuming I'm guess the translations of the Italian prompts correctly). Perhaps you could post your spreadsheet here for others to look at? Feb 29 '20 at 15:19