3
$\begingroup$

In continuation with the previous post, I propose you another problem. I have this model: \begin{align}\min&\quad F\\\text{s.t.}&\quad F-(1150x_{B_{1}}+1000x_{B_{2}}+1350x_{B_{3}}-S_{1})=430\\&\quad (0,0875x_{B_{1}}+0,055x_{B_{2}}+0,1175x_{B_{3}})+1,04S_{1}-S_{2}=210\\&\quad (0,0875x_{B_{1}}+0,055x_{B_{2}}+0,1175x_{B_{3}})+1,04S_{2}-S_{3}=222\\ &\quad (0,0875x_{B_{1}}+0,055x_{B_{2}}+0,1175x_{B_{3}})+1,04S_{3}-S_{4}=231\\ &\quad (0,0875x_{B_{1}}+0,055x_{B_{2}}+0,1175x_{B_{3}})+1,04S_{4}-S_{5}=240\\ &\quad (1,0875x_{B_{1}}+0,055x_{B_{2}}+0,1175x_{B_{3}})+1,04S_{5}-S_{6}=195\\ &\quad (1,055x_{B_{2}}+0,1175x_{B_{3}})+1,04S_{6}-S_{7}=225\\ &\quad (1,1175x_{B_{3}})+1,04S_{7}-S_{8}=255\end{align}

Professor says only: "Solving the model with Excel we find... ":

OBJECTIVE FUNCTION VALUE    -->    1708.669    

VARIABLE        VALUE          REDUCED COST
     F      1708.668701          0.000000
    B1       147.734344          0.000000
    B2       190.845779          0.000000
    B3       201.342285          0.000000
    S1       646.116333          0.000000
    S2       509.226654          0.000000
    S3       354.861359          0.000000
    S4       185.321487          0.000000
    S5         0.000000          0.064025
    S6         0.000000          0.012614
    S7         0.000000          0.021318
    S8         0.000000          0.670839

Honestly, I really don't know where to put my hands, and in the manuals of Excel I can't find anything who can help me. Would you tell me where to start, at least?

$\endgroup$
  • $\begingroup$ Do you have any force to use excel to solve your problem? Would you try using an algebraic language like GAMS, AMPL or some Python based languages? They have some nice facilities to deal with your problem and to answer your questions. Also, you can easily write your problem and modifying. Almost all of them have a demo or academic license. $\endgroup$ – A.Omidi Mar 2 at 19:19
  • 1
    $\begingroup$ Just implemented as well and it seems incorrectly specified. On the other hand giving him a finished Excel sheet is not too helpful either? $\endgroup$ – CMichael Mar 2 at 21:49
4
$\begingroup$

To do linear optimization in Excel you should use the built-in Excel Solver. Here is a good tutorial for installation and first steps.

It should be straight-forward to incorporate your problem this way (with proper values see comments). After that you want to use sensitivity analysis to access reduced costs. Find a tutorial here.

| improve this answer | |
$\endgroup$

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.