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. How do I set up the program to use the iterative calculus?
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=F7G15+F8H15 and 440.000=G7G15+G8H15.
Then, I set up the Solver in this way…
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?