Iterative calculus with Excel - N° 2

Question

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?

Answer 1

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.