Linear Programming (LP) Optimization with Excel Solver

#excel #solver #linearprogramming
Please SUBSCRIBE:
ua-cam.com/users/subscription_...
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

[@88spaces]

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

[@MattMacarty]

Thank you. Glad it helped.

[@ASB-cf2ke]

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

[@MattMacarty]

Thanks. Glad it helped.

[@CT-mc3sy]

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

[@MattMacarty]

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]

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]

Glad it helped.

[@laurenwood2702]

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

[@MattMacarty]

Glad it helped

[@CamilleEssick]

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]

Thank you. Glad it helped.

[@ravneethanda8228]

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

[@MattMacarty]

Glad it helped!

[@goonhead3791]

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

[@MattMacarty]

Glad it helped. c

[@matthewh4377]

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]

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

[@soulmanjerome07]

@@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]

lol 😂 for real

[@oneandonlylfs8406]

THANK YOU! This helped me finally understand Solver.

[@MattMacarty]

Thanks. Glad it helped.

[@evridikson]

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

[@MattMacarty]

Glad it helped

[@arwinderkaur4411]

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

[@MattMacarty]

Glad it helped!

[@soumyadeepchatterjee2189]

Explained clearly. Thank You, Matt!

[@MattMacarty]

Great it helped

[@zainabhashimi]

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

[@MattMacarty]

Thanks. Glad it helped

[@brandycurtisrichards6946]

Great video! Thank you so much for the explanation.

[@MattMacarty]

Thank you. Glad it helped

[@8sevenstudiosphotography469]

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]

Thanks. Glad it helped.

[@jemimamagak]

You are incredible thank you for your help 🙏🤗

[@MattMacarty]

Glad it helped

[@reinssz]

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]

Glad it helped

[@HungTran-fj8hg]

Saved my life homie. Much love.

[@MattMacarty]

Glad it helped

[@portaltaker]

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

[@MattMacarty]

Glad it helped

[@kushikachhabra8021]

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]

Glad it helped!

[@williamhylen327]

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

[@MattMacarty]

Thanks. Glad it helped.

[@jmac2500]

Very helpful and straight forward - thank you!

[@MattMacarty]

Glad it helped.

[@mohammadrezaghasemi6792]

Thank you Matt. It was very helpful for me 🙏

[@MattMacarty]

Glad it helped

[@GSZ818]

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

[@MattMacarty]

Thanks. Glad it helped.

[@mariamelendez8615]

Thank you for making this video!

[@MattMacarty]

Glad it helped

[@RamandeepSingh-kk4zt]

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

[@MattMacarty]

You are welcome. Glad it helped.

[@jrels]

Thorough and clear explanation. THANKS so much :)

[@MattMacarty]

Glad it helped

[@AbdoulayeDiallo-xu4hx]

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

[@MattMacarty]

hank you. Glad it helped.

[@Light-vu6ws]

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

[@MattMacarty]

Thanks. Glad it helped

[@AniMax_cjs]

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

[@MattMacarty]

Thanks. Glad it helped.

[@laksyy]

Very clear and concise tutorial thank you!

[@MattMacarty]

Thank you. Glad it helped.

[@RandlemanProductions]

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

[@MattMacarty]

Glad it helped!

[@Tsunade64]

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

[@MattMacarty]

Thanks. Glad it helped.

[@nirtheart]

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]

Glad it helped.

[@oussamadeziri4806]

Thanks for the help! Definitely useful stuff

[@MattMacarty]

Glad it helped

[@aashishphatate7003]

Thank you! Video was very helpful!! :)

[@MattMacarty]

Glad it helped

[@kyle3818]

Video really helped me! Thank you!

[@MattMacarty]

Thanks. Glad it helped

[@Dret4288]

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]

Thanks very much. Glad it helped.

[@umarwaqas649]

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

[@MattMacarty]

Glad it helped.

[@nayaroni]

So helpful, thank you so much Matt!

[@MattMacarty]

Glad it helped.

[@desipuspitasari7079]

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

[@MattMacarty]

Thanks. Glad it helped.

[@jimparsons6803]

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]

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.

[@akashbehl3862]

Thanks, really helpful!

[@MattMacarty]

Glad it helped.

[@erickodhiambo3783]

Very clear and easily understood.
Thank you

[@MattMacarty]

Glad it helped

[@Philantrope]

Thank you - very well explained!

[@MattMacarty]

Glad it helped.

[@powellnine1477]

Thank you very much for your UA-cam video. I’ve subscribed to your channel.

[@MattMacarty]

Thanks! Glad it helped

[@CaptainMoroni9]

Very easy to understand, thank you!

[@MattMacarty]

Glad it helped

[@karlrupe8264]

Thank you! This helped a lot

[@MattMacarty]

Glad it helped!

[@mariavelasco4422]

Thank you! Very helpful

[@MattMacarty]

Glad it helped

[@gavinjdm]

Very helpful! Thank you

[@MattMacarty]

Glad it helped.

[@SuperMegamalik]

Excellent Matt

[@MattMacarty]

Thanks, glad it helped.

[@onyemaugoh4813]

Thank you so much this was super helpful

[@MattMacarty]

Glad it helped!

[@Faz527]

Wow! well explained. Thanks.

[@MattMacarty]

Thank you. 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

[@VedikaKhandelwal]

LIFESAVER THANK YOU SO MUCH

[@MattMacarty]

Glad it helped

[@s108t]

thanks Matt, you're awesome

[@MattMacarty]

Thanks. Glad it helped.

[@meatbeater5425]

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

[@MattMacarty]

Glad it helped

[@adolfom9091]

You are the man!

[@MattMacarty]

Thanks. Glad it helped

[@satviksoni8499]

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

[@MattMacarty]

Glad it helped.

[@ltrof]

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

[@MattMacarty]

Thanks. Glad it helped

[@roardinoson7]

Thanks Matt - it was help in college

[@MattMacarty]

Glad it helped.

[@edwardsparkes2513]

Thank you!

[@MattMacarty]

Glad it helped

[@davidszabo9302]

Thank you so much!!!

[@MattMacarty]

Glad it helped

[@henrymsipa2191]

very helpful, thank you very much for sharing

[@MattMacarty]

Thanks. Glad it helped

[@rishondanapier1445]

Thank you so much.

[@MattMacarty]

Glad it helped

[@nomnom914]

thanks, gonna use this to find probable missing amounts

[@MattMacarty]

Glad it helped

[@alifikri9824]

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

[@MattMacarty]

Glad it was helpful!

[@surik.5481]

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

[@RV22]

Thank you!!!

[@MattMacarty]

Glad it helped

[@TerryBowl]

perfect thanks!

[@MattMacarty]

Glad it helped

[@siddharthGupta632]

Thank you very much. :-)

[@MattMacarty]

Glad it helped.

[@milesdeichler9700]

Thank you so much

[@MattMacarty]

Glad it helped

[@zizo8737]

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

[@MattMacarty]

Thanks. Glad it helped

[@shambhabchaki5408]

thank you!!!!

[@MattMacarty]

Glad it helped

[@rozthepuffer1037]

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]

Glad it helped.

[@shikharsingh1540]

Awesome

[@MattMacarty]

Glad it helped

[@iWillRun_]

My Professor shared this Video with in class :)

[@MattMacarty]

Thanks, hop it helped.

[@iWillRun_]

@@MattMacarty Thankyou too :)

[@Icey_tata]

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

[@MattMacarty]

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

[@faadumomataan5104]

thanks

[@MattMacarty]

Glad it helped

[@mansigupta927]

THANKYOUUUUUU

[@MattMacarty]

Glad it helped

[@stephenip3259]

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]

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.

[@minseokkim7097]

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

[@MattMacarty]

Thanks. Yes please feel free to share.

[@mrmoe5272]

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

[@MattMacarty]

Glad it helped

[@becajaja5217]

Thank you #Matt Macarty

[@MattMacarty]

Glad it helped.

[@mrfoxviiska]

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

[@MattMacarty]

Thank you. Glad it helped.

[@calliebless6894]

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]

So you need side constraints. This example has something like what you are talking about: ua-cam.com/video/C_v0rlpTEmc/v-deo.html

[@mookaclement2270]

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

[@MattMacarty]

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

[@kayodeolakunleoluwaseyi4273]

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

[@MattMacarty]

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

[@LiLi-ul8jy]

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]

Glad it helped

[@leecurcio6132]

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]

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.

[@jamieharvie9827]

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]

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

[@houssainchaer8335]

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

[@MattMacarty]

HI. Take a look at this one: ua-cam.com/video/sI5X2kJA_4k/v-deo.html

[@houssainchaer8335]

@@MattMacarty can we solve it with simpleLP ?

[@MattMacarty]

@@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]

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]

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

[@hakankosebas2085]

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

[@MattMacarty]

You can only have one objective function.

[@merinasaha3669]

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

[@MattMacarty]

Hi. I have posted in the video description: ua-cam.com/video/6xa1x_Iqjzg/v-deo.html

[@indianarchangel]

Hi Marty, do you have this as a certificate course on Udemy?

[@MattMacarty]

No, sorry.

[@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

[@mdaroza]

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]

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

[@mdaroza]

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

[@MattMacarty]

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

[@mdaroza]

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

[@1rasha]

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

[@MattMacarty]

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

[@bintalkuds]

how did you do the absolute referencing

[@MattMacarty]

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

[@troooooper100]

what course do you learn this in? operations management?

[@MattMacarty]

There used to be a course called management science or operations research. Now it may be covered in OM.

[@troooooper100]

@@MattMacarty cool it will help google stuff