3
$\begingroup$

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...enter image description here with 360.000=F7*G15+F8*H15 and 440.000=G7*G15+G8*H15.

Then, I set up the Solver in this way… enter image description here

…but I obtain: enter image description here

Where I wrong? Is this normal?

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

Instead, the solution of the problem is (in all likelihood) the following: enter image description here

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?

$\endgroup$
  • $\begingroup$ Welcome to OR SE. $\endgroup$ – prubin Feb 27 at 16:46
  • $\begingroup$ 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? $\endgroup$ – A.Omidi Feb 29 at 6:57
  • $\begingroup$ 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? $\endgroup$ – prubin Feb 29 at 15:19
  • $\begingroup$ @prubin Thanks for your answer. Please, see my second edit (with my spreadsheet). $\endgroup$ – Francesco Totti Mar 2 at 8:49
  • $\begingroup$ It's a bit hard to compare your solution to the instructors; the units appear to be different. In any case, it would be helpful to have your actual spreadsheet file, in case there is an error in a cell that we cannot see. You could perhaps put the file in the cloud somewhere and post the link here. $\endgroup$ – prubin Mar 2 at 22:05
6
$\begingroup$

What you are looking for is the Solver component of Excel. If you are willing and able to use plugins, I can recommend OpenSolver. In addition to a more powerful solver (which you won't need -- your problem is quite small), it adds some "bells and whistles" including a model visualization component.

$\endgroup$
  • $\begingroup$ Thanks for your answer. Please, see my edit, I tried to use the Solver like you said. $\endgroup$ – Francesco Totti Feb 28 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.