Linear Programming (LP) Optimization with Excel Solver

Рет қаралды 628,129

7 жыл бұрын

#excel #solver #linearprogramming
Please SUBSCRIBE:
kzfaq.info_...
alphabench.com/data/linear-pro...
Please watch: "Linear Programming Optimization Transportation Problem Excel Solver"
• How to Setup & Solve L...
A tablet computer manufacturer offers two models of its product, the Tablet Pro and the Tablet Mini. The Tablet Pro requires 1 chipset, 15 electronic components and 6 hours of labor and returns a profit of $182. The Tablet Mini requires 1 chipset, 9hours of labor and 10 electronic components and returns a profit of $139. Monthly resources are limited to 1,000 chipsets, 7,000 labor hours and 14,000 electronic components. The tablet manufacturer is interested in maximizing monthly profit. What product mix achieves maximal profit?
Overview of formulating linear programming models and using Solver to find an optimal solution. Includes discussion of sensitivity reports and important terminology.
Demonstration of classic two product profit maximization formulation.
Spreadsheet used in the video can be downloaded from:
alphabench.com/resources.html

Пікірлер: 249

@88spaces 4 жыл бұрын

Matt, thank you for taking the time to produce this video. It has helped me a lot.

@MattMacarty 4 жыл бұрын

Thank you. Glad it helped.

@CT-mc3sy 4 жыл бұрын

Thank you!!! So much better than my prof at explaining, especially since you use much clearer terms.

@MattMacarty 4 жыл бұрын

Thanks. Glad it helped.

@justinfay3011 Жыл бұрын

@@MattMacarty Do you help with homework at a reasonable $/hr rate? The model I have to create is very complicated!

@user-ru2ry7rm3p 5 жыл бұрын

Thank you for posting this! you explained it in a easy but clear way. Now, I know how to solve my assignment problem. Thank you!

@MattMacarty 5 жыл бұрын

Glad it helped.

@oneandonlylfs8406 5 жыл бұрын

THANK YOU! This helped me finally understand Solver.

@MattMacarty 5 жыл бұрын

Thanks. Glad it helped.

@ASB-cf2ke 6 жыл бұрын

easy way to learn ,good work to the developers of the video

@MattMacarty 6 жыл бұрын

Thanks. Glad it helped.

@CamilleEssick 5 жыл бұрын

I think I watched about 5 videos before I found one that has the best example on what to do. Thanks!!! Short and Simple!!

@MattMacarty 5 жыл бұрын

Thank you. Glad it helped.

@laurenwood2702 5 жыл бұрын

Easy to understand and so much easier than doing the simplex method by hand 🙏🙌 Thank you!

@MattMacarty 5 жыл бұрын

Glad it helped

@evridikson 4 жыл бұрын

Saved me a lot of time and stress, can't thank you enough!!

@MattMacarty 4 жыл бұрын

Glad it helped

@ravneethanda8228 3 жыл бұрын

Thank you so much. I was struggling to solve LP with excel solver and your video helped me a lot.

@MattMacarty 3 жыл бұрын

Glad it helped!

@soumyadeepchatterjee2189 2 жыл бұрын

Explained clearly. Thank You, Matt!

@MattMacarty 2 жыл бұрын

Great it helped

@goonhead3791 5 жыл бұрын

Man thank you I wish I could give you my money instead of my professors. I’ll repay you one day

@MattMacarty 5 жыл бұрын

Glad it helped. c

@matthewh4377 3 жыл бұрын

Thank you Matt! Ive spent hours trying to interpret my professor's videos and you taught this in 20 mins! My university needs to fire my professor and give me a refund as well! - Thanks dude! Im sending your link to the class

@belladonnagammidge8572 3 жыл бұрын

I got help from thechinahacks financially .They got me a transfer of $37,000 just yesterday .

@soulmanjerome07 3 жыл бұрын

@@MattMacarty This is a great review on how to use solver. Any other ones? I second Goon Head's comment. My professors focused on the results but not the Answer Report, Sensitivity Report, and Limits Report.

@yonathantadesse3385 2 жыл бұрын

lol 😂 for real

@arwinderkaur4411 3 жыл бұрын

Hi Matt! Thanks for your video, this really helped me to find the LP for my problem.

@MattMacarty 3 жыл бұрын

Glad it helped!

@Dret4288 5 жыл бұрын

Let me tell you. You definitely aleviated a headache and got me out of a tough spot. You explained and showed me this in a manner that was easy to pick up thanks a ton.

@MattMacarty 5 жыл бұрын

Thanks very much. Glad it helped.

@zainabhashimi 6 жыл бұрын

it was easy to learn and certainly very helpful to me. Thank you so much for making this video!

@MattMacarty 6 жыл бұрын

Thanks. Glad it helped

@brandycurtisrichards6946 4 жыл бұрын

Great video! Thank you so much for the explanation.

@MattMacarty 4 жыл бұрын

Thank you. Glad it helped

@HungTran-fj8hg 5 жыл бұрын

Saved my life homie. Much love.

@MattMacarty 5 жыл бұрын

Glad it helped

@portaltaker 2 жыл бұрын

Saved me for my Quantitative Analytics module! My lecturer explained this so badly, thank you!

@MattMacarty 2 жыл бұрын

Glad it helped

@jmac2500 4 жыл бұрын

Very helpful and straight forward - thank you!

@MattMacarty 4 жыл бұрын

Glad it helped.

@RandlemanProductions 7 ай бұрын

Thank you so much!!!! I had so much trouble with understanding this and you explained it perfectly. I now understand this. THANK YOU!!!

@MattMacarty 7 ай бұрын

Glad it helped!

@laksyy 5 жыл бұрын

Very clear and concise tutorial thank you!

@MattMacarty 5 жыл бұрын

Thank you. Glad it helped.

@jemimamagak 5 жыл бұрын

You are incredible thank you for your help 🙏🤗

@MattMacarty 4 жыл бұрын

Glad it helped

@reinssz 2 жыл бұрын

Thank you so much for this video. It really helps a lot for my upcoming report with this kind of topic. Thanks a bunch!

@MattMacarty 2 жыл бұрын

Glad it helped

@8sevenstudiosphotography469 3 жыл бұрын

It baffles me that Matt made this problem so easy to understand. Why can’t my Professor break it down like this? Thanks Matt!

@MattMacarty 3 жыл бұрын

Thanks. Glad it helped.

@AniMax_cjs 3 жыл бұрын

you're really a good tutor. thank you so much. this helped a lot.

@MattMacarty 3 жыл бұрын

Thanks. Glad it helped.

@mariamelendez8615 3 жыл бұрын

Thank you for making this video!

@MattMacarty 3 жыл бұрын

Glad it helped

@Tsunade64 6 жыл бұрын

Thank you! Awesome job btw, and very clearly and thoroughly explained :)

@MattMacarty 6 жыл бұрын

Thanks. Glad it helped.

@jrels Жыл бұрын

Thorough and clear explanation. THANKS so much :)

@MattMacarty Жыл бұрын

Glad it helped

@mohammadrezaghasemi6792 5 жыл бұрын

Thank you Matt. It was very helpful for me 🙏

@MattMacarty 5 жыл бұрын

Glad it helped

@nayaroni Жыл бұрын

So helpful, thank you so much Matt!

@MattMacarty Жыл бұрын

Glad it helped.

@kyle3818 4 жыл бұрын

Video really helped me! Thank you!

@MattMacarty 4 жыл бұрын

Thanks. Glad it helped

@oussamadeziri4806 5 жыл бұрын

Thanks for the help! Definitely useful stuff

@MattMacarty 5 жыл бұрын

Glad it helped

@williamhylen327 2 жыл бұрын

You are a god among men. This is the best explanation of this I have heard. Thank you.

@MattMacarty 2 жыл бұрын

Thanks. Glad it helped.

@kushikachhabra8021 3 жыл бұрын

Great video. My professor took 2 hours to teach this method and i did not understand a thing. whereas you made me understand it in 10 mins. :)

@MattMacarty 3 жыл бұрын

Glad it helped!

@CaptainMoroni9 5 жыл бұрын

Very easy to understand, thank you!

@MattMacarty 5 жыл бұрын

Glad it helped

@desipuspitasari7079 5 жыл бұрын

Thank you so much!! Now I understand a lot!

@MattMacarty 5 жыл бұрын

Thanks. Glad it helped.

@Light-vu6ws 2 жыл бұрын

You're awesome for replying to every comment. Good video!

@MattMacarty 2 жыл бұрын

Thanks. Glad it helped

@nirtheart 3 жыл бұрын

Edit: I found it! I had to go to Data/Solver and then go through the set-up there and the "Limits Report" showed up! Thank you so much for the video again! Thank you for the insightful video! Only thing I was unable to do on my own when following your video was to find the "Limits Report" which didn't show up anywhere in my Excel document. I'm using the newest versions of things, so it probably is just somewhere I don't know where to look for it

@MattMacarty 3 жыл бұрын

Glad it helped.

@GSZ818 6 жыл бұрын

Thank you so much, really helped a lot!! Awesome work

@MattMacarty 6 жыл бұрын

Thanks. Glad it helped.

@erickodhiambo3783 2 жыл бұрын

Very clear and easily understood. Thank you

@MattMacarty 2 жыл бұрын

Glad it helped

@RamandeepSingh-kk4zt 2 жыл бұрын

This is the best explanation I've seen so far💯 ,Thank you Sir☺️

@MattMacarty 2 жыл бұрын

You are welcome. Glad it helped.

@felagato5860 2 жыл бұрын

thanks Matt, you're awesome

@MattMacarty 2 жыл бұрын

Thanks. Glad it helped.

@karlrupe8264 Жыл бұрын

Thank you! This helped a lot

@MattMacarty Жыл бұрын

Glad it helped!

@powellnine1477 3 жыл бұрын

Thank you very much for your KZfaq video. I’ve subscribed to your channel.

@MattMacarty 3 жыл бұрын

Thanks! Glad it helped

@Philantrope 3 жыл бұрын

Thank you - very well explained!

@MattMacarty 3 жыл бұрын

Glad it helped.

@umarwaqas649 6 жыл бұрын

Thank you. Well explained. Great Job (thumbs up).

@MattMacarty 6 жыл бұрын

Glad it helped.

@AbdoulayeDiallo-xu4hx 4 жыл бұрын

Wow man, you are a LEGEND; the way you explain.... just wow. I wish I could trade you with my professor.

@MattMacarty 4 жыл бұрын

hank you. Glad it helped.

@aashishphatate7003 4 жыл бұрын

Thank you! Video was very helpful!! :)

@MattMacarty 4 жыл бұрын

Glad it helped

@akashbehl3862 5 жыл бұрын

Thanks, really helpful!

@MattMacarty 5 жыл бұрын

Glad it helped.

@meatbeater5425 4 жыл бұрын

You saved my ass. I have an assignment on this and I had no idea before I watched this video.

@MattMacarty 4 жыл бұрын

Glad it helped

@Faz527 Жыл бұрын

Wow! well explained. Thanks.

@MattMacarty Жыл бұрын

Thank you. Glad it helped.

@mariavelasco4422 Жыл бұрын

Thank you! Very helpful

@MattMacarty Жыл бұрын

Glad it helped

@VedikaKhandelwal 2 жыл бұрын

LIFESAVER THANK YOU SO MUCH

@MattMacarty 2 жыл бұрын

Glad it helped

@SuperMegamalik 6 жыл бұрын

Excellent Matt

@MattMacarty 6 жыл бұрын

Thanks, glad it helped.

@gavinjdm 5 жыл бұрын

Very helpful! Thank you

@MattMacarty 5 жыл бұрын

Glad it helped.

@jimparsons6803 2 жыл бұрын

Interesting. ... going to school in the early 70s, we did all of this by hand... with the use of a slide rule to estimate the answer and then draw a couple of graphs by hand to zero in on the better answer. Them were the days!! Enjoyed the clip.

@MattMacarty 2 жыл бұрын

They still teach that in school, but without the slide rule.

@jamesquaranto8606 Жыл бұрын

@@MattMacarty we would just use our Ti-84 and a pencil and paper to solve these in my operations management class.

@ltrof Жыл бұрын

man you are literally a god thank you so much !!!!!

@MattMacarty Жыл бұрын

Thanks. Glad it helped

@melo4246 Жыл бұрын

Extremely good video. I was pulling my hair in class because my teacher was confusing everyone. Thank you!

@MattMacarty Жыл бұрын

Thank you. Glad it helped

@onyemaugoh4813 Жыл бұрын

Thank you so much this was super helpful

@MattMacarty Жыл бұрын

Glad it helped!

@nomnom914 Жыл бұрын

thanks, gonna use this to find probable missing amounts

@MattMacarty Жыл бұрын

Glad it helped

@satviksoni8499 3 жыл бұрын

so so so helpfull, gonna submit my semester project with t your spreadsheet. thank you so muchh

@MattMacarty 3 жыл бұрын

Glad it helped.

@roardinoson7 5 жыл бұрын

Thanks Matt - it was help in college

@MattMacarty 5 жыл бұрын

Glad it helped.

@adolfom9091 6 жыл бұрын

You are the man!

@MattMacarty 6 жыл бұрын

Thanks. Glad it helped

@davidszabo9302 4 жыл бұрын

Thank you so much!!!

@MattMacarty Жыл бұрын

Glad it helped

@rishondanapier1445 Жыл бұрын

Thank you so much.

@MattMacarty Жыл бұрын

Glad it helped

@edwardsparkes2513 6 жыл бұрын

Thank you!

@MattMacarty 6 жыл бұрын

Glad it helped

@zizo8737 7 ай бұрын

I don’t understand why professors make so difficult huhhh thanksss a lot man❤

@MattMacarty 7 ай бұрын

Thanks. Glad it helped

@henrymsipa2191 4 жыл бұрын

very helpful, thank you very much for sharing

@MattMacarty 4 жыл бұрын

Thanks. Glad it helped

@siddharthGupta632 6 жыл бұрын

Thank you very much. :-)

@MattMacarty 6 жыл бұрын

Glad it helped.

@TerryBowl 9 ай бұрын

perfect thanks!

@MattMacarty 9 ай бұрын

Glad it helped

@milesdeichler9700 4 жыл бұрын

Thank you so much

@MattMacarty 4 жыл бұрын

Glad it helped

@alifikri9824 3 жыл бұрын

im student from indonesia, thank youu this video really helpful :)

@MattMacarty 3 жыл бұрын

Glad it was helpful!

@surik.5481 3 жыл бұрын

sen türk değil misin şimdi? ali fikri türkçe ad değil mi?

@RV22 4 жыл бұрын

Thank you!!!

@MattMacarty 4 жыл бұрын

Glad it helped

@shambhabchaki5408 2 жыл бұрын

thank you!!!!

@MattMacarty 2 жыл бұрын

Glad it helped

@faadumomataan5104 6 жыл бұрын

thanks

@MattMacarty 6 жыл бұрын

Glad it helped

@shikharsingh1540 3 жыл бұрын

Awesome

@MattMacarty 3 жыл бұрын

Glad it helped

@becajaja5217 3 жыл бұрын

Thank you #Matt Macarty

@MattMacarty 3 жыл бұрын

Glad it helped.

@iWillRun_ 5 жыл бұрын

My Professor shared this Video with in class :)

@MattMacarty 5 жыл бұрын

Thanks, hop it helped.

@iWillRun_ 5 жыл бұрын

@@MattMacarty Thankyou too :)

@mansigupta927 10 ай бұрын

THANKYOUUUUUU

@MattMacarty 10 ай бұрын

Glad it helped

@mrmoe5272 3 жыл бұрын

Better than my professor explanation that took 6 classes and I did not understand a thing

@MattMacarty 3 жыл бұрын

Glad it helped

@rozthepuffer1037 2 жыл бұрын

My professor literally gave us a link to this video instead of just teaching it himself, I wish I can just take back the money I gave to my professor and give it to you instead. Have my like and sub cuz idk how else to repay u back

@MattMacarty 2 жыл бұрын

Glad it helped.

@minseokkim7097 5 жыл бұрын

Thank you so much. Can I share this youtube with my students?

@MattMacarty 5 жыл бұрын

Thanks. Yes please feel free to share.

@stephenip3259 4 жыл бұрын

Back in the days, we used simplex method, and I wrote C program to solve the opimization... now there is an excel solver.... People have forgotten the importance of solving problems on their own.

@MattMacarty 4 жыл бұрын

Agreed If I were in a classroom I would have started by solving with Simplex and then moved onto Solver. Most courses still cover with a graphical example so you can see what's going on but as soon as you move past a couple of variables pretty much everyone is using a solver. I was actually considering showing Simplex with a python program but I haven't gotten to it yet.

@mrfoxviiska 3 жыл бұрын

Thanks. You teach better than my prof. who teach in the most famous university in my country.... #youtuisbetterteacherthanteacherinclass

@MattMacarty 3 жыл бұрын

Thank you. Glad it helped.

@calliebless6894 4 жыл бұрын

How do you do a weight using your format? I have a problem that I set up just like you did, but one product cannot account for more than 50% of units produced and another has to account for at least 20%. Thanks for your help!

@MattMacarty 4 жыл бұрын

So you need side constraints. This example has something like what you are talking about: kzfaq.info/get/bejne/ecWmY9Wd1bqpnZc.html

@LiLi-ul8jy 5 жыл бұрын

Do you have an example on linear programming to minimize interest payment? Example whether to get bank loan at the beginning of the year or monthly loan or combination of both types of loan. I can’t seem to find any examples online. Hope you can show an example. Thank you.

@MattMacarty 4 жыл бұрын

Glad it helped

@Icey_tata 5 жыл бұрын

hi- to define if i exceeding daily usage what would be my constraints?

@MattMacarty 5 жыл бұрын

I think you will just have to compare actual usage with "optimal" usage

@1rasha 2 жыл бұрын

This sensitivity report is not clear. Can you make a detailed video on its analysis please

@MattMacarty 2 жыл бұрын

You mean you can't see the report? You can changethe settings to HD and that should clear it up.

@leecurcio6132 5 жыл бұрын

Hi. I have a question. I created a macro (VBA) that emulates the solver by clicking on a button. The problem is that sometime I have to click 3-4 times before getting the last results. Do you know how to avoid this and getting on solution with one click?

@MattMacarty 5 жыл бұрын

Hi. This is really difficult to diagnose, but one thing to consider is that usually there are many feasible solutions, but only one optimal solution. it sounds like maybe your script is stopping once it finds a feasible solution and then on subsequent runs it gets closer and closer to the optimal. Another common problem is in the modeling step. It's pretty easy to design your model in such a way that it is no longer linear. When this happens you sometimes end up with "local" optima rather than global optima.

@merinasaha3669 5 жыл бұрын

Can you articulate the example in a paragraph or give the link of the example?

@MattMacarty 5 жыл бұрын

Hi. I have posted in the video description: kzfaq.info/get/bejne/bN6RZNuQrtfOqps.html

@mookaclement2270 2 жыл бұрын

Hello Matt, what keys did you press for you to change (B4:C4) to ($B$4:$C$4)??

@MattMacarty 2 жыл бұрын

It's F4 (function4) in Windows, command + T in MAC

@NinOo794 Жыл бұрын

Can you give me the same example (question, issue) Because I want to solve it on my own?

@MattMacarty Жыл бұрын

Hi. I am not sure what you mean. If you want to download the spreadsheet I used follow this link: alphabench.com/data/linear-programming-optimization.html

@kayodeolakunleoluwaseyi4273 3 жыл бұрын

Wonderful video... Can we use excel solver to solve goal programming?

@MattMacarty 3 жыл бұрын

Yes, you may also be able to simply use goal seek

@houssainchaer8335 3 жыл бұрын

Your channel it s very helpful 👏👏 I just want to ask How we can use linear programming for asset allocation

@MattMacarty 3 жыл бұрын

HI. Take a look at this one: kzfaq.info/get/bejne/qa9li5Wcr6fDZJ8.html

@houssainchaer8335 3 жыл бұрын

@@MattMacarty can we solve it with simpleLP ?

@MattMacarty 3 жыл бұрын

@@houssainchaer8335 Well whatever we do is based on a forecasted return and volatility. There are many ways to formulate the problem, my video describes one of them where we maximize Sharpe ratio.

@nathaniellmolinaa5188 3 жыл бұрын

when i add subj to constraints it says unequal number of cell ref and constraints, however they are equal, how do i fix this? thanks great video btw, just encountered some technical difficulties

@MattMacarty 3 жыл бұрын

Thanks. It's probably telling you that you have selected different dimensions for one of the ranges, so maybe three cells for one and two for the other or something similar. You can download the spreadsheet here: alphabench.com/data/linear-programming-optimization.html

@jamieharvie9827 3 жыл бұрын

Hi! I am a little confused about 5:37 for the total profit function. Can you explain what you did? I tried sum product for the number to make * used numbers and got VALUE!.

@MattMacarty 3 жыл бұрын

This may mean you have selected the wrong range for one of the arguments or ranges of different sizes. You can download my spreadsheet here: alphabench.com/data/linear-programming-optimization.html

@hakankosebas2085 11 ай бұрын

can you do it with multiple objective? and can we pick one thing from list with multiple objective problem with excel solver?

@MattMacarty 10 ай бұрын

You can only have one objective function.

@bintalkuds 3 жыл бұрын

how did you do the absolute referencing

@MattMacarty 3 жыл бұрын

In windows it's F4. Mac is command + T

@komalyadav528 6 жыл бұрын

How to form all reports?

@MattMacarty 6 жыл бұрын

This is an option after a solution has been found. Watch the video starting at 9:10

@mdaroza 6 ай бұрын

I'm wondering if you can help set up the following question in Solver: I'm travelling 15000km. Each tire has travel a max distance of 10000km (assume I start with a set of 4 tires). What is the min number of tires I need to use to complete the trip?

@MattMacarty 5 ай бұрын

I may be missing something, but this sounds like a simple algebra problem

@mdaroza 5 ай бұрын

@@MattMacarty I'd like to know if Solver is smart enough to calculate that I need 6 tires as opposed to 8.

@MattMacarty 5 ай бұрын

It should if you set the objective to minimize instead of maximize.

@mdaroza 5 ай бұрын

@@MattMacartyI think this is where I’m struggling with, is to set up the constraints.

@nomnom914 Жыл бұрын

how can i add a constraint to disable the use of decimal?

@MattMacarty Жыл бұрын

You can solve as an integer problem, but it's likely better to just decrease decimals in Excel