Linear Programming (LP) Optimization with Excel Solver
Ғылым және технология
#excel #solver #linearprogramming
Please SUBSCRIBE:
kzread.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
Matt, thank you for taking the time to produce this video. It has helped me a lot.
@MattMacarty
4 жыл бұрын
Thank you. Glad it helped.
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!
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.
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.
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.
easy way to learn ,good work to the developers of the video
@MattMacarty
6 жыл бұрын
Thanks. Glad it helped.
Easy to understand and so much easier than doing the simplex method by hand 🙏🙌 Thank you!
@MattMacarty
5 жыл бұрын
Glad it helped
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!
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
4 жыл бұрын
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
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.
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
4 жыл бұрын
Glad it helped.
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.
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
Saved me for my Quantitative Analytics module! My lecturer explained this so badly, thank you!
@MattMacarty
2 жыл бұрын
Glad it helped
You are a god among men. This is the best explanation of this I have heard. Thank you.
@MattMacarty
2 жыл бұрын
Thanks. Glad it helped.
THANK YOU! This helped me finally understand Solver.
@MattMacarty
5 жыл бұрын
Thanks. Glad it helped.
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.
Thank you so much!!!! I had so much trouble with understanding this and you explained it perfectly. I now understand this. THANK YOU!!!
@MattMacarty
8 ай бұрын
Glad it helped!
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!
Hi Matt! Thanks for your video, this really helped me to find the LP for my problem.
@MattMacarty
3 жыл бұрын
Glad it helped!
Saved me a lot of time and stress, can't thank you enough!!
@MattMacarty
4 жыл бұрын
Glad it helped
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
Explained clearly. Thank You, Matt!
@MattMacarty
2 жыл бұрын
Great it helped
This is the best explanation I've seen so far💯 ,Thank you Sir☺️
@MattMacarty
2 жыл бұрын
You are welcome. Glad it helped.
Great video! Thank you so much for the explanation.
@MattMacarty
4 жыл бұрын
Thank you. Glad it helped
Extremely good video. I was pulling my hair in class because my teacher was confusing everyone. Thank you!
@MattMacarty
Жыл бұрын
Thank you. Glad it helped
Thank you very much for your KZread video. I’ve subscribed to your channel.
@MattMacarty
3 жыл бұрын
Thanks! Glad it helped
Thorough and clear explanation. THANKS so much :)
@MattMacarty
Жыл бұрын
Glad it helped
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
Thank you! Awesome job btw, and very clearly and thoroughly explained :)
@MattMacarty
6 жыл бұрын
Thanks. Glad it helped.
You're awesome for replying to every comment. Good video!
@MattMacarty
2 жыл бұрын
Thanks. Glad it helped
Very helpful and straight forward - thank you!
@MattMacarty
4 жыл бұрын
Glad it helped.
you're really a good tutor. thank you so much. this helped a lot.
@MattMacarty
3 жыл бұрын
Thanks. Glad it helped.
You are incredible thank you for your help 🙏🤗
@MattMacarty
4 жыл бұрын
Glad it helped
Saved my life homie. Much love.
@MattMacarty
5 жыл бұрын
Glad it helped
Very clear and concise tutorial thank you!
@MattMacarty
5 жыл бұрын
Thank you. Glad it helped.
Thank you Matt. It was very helpful for me 🙏
@MattMacarty
5 жыл бұрын
Glad it helped
Thank you for making this video!
@MattMacarty
3 жыл бұрын
Glad it helped
So helpful, thank you so much Matt!
@MattMacarty
Жыл бұрын
Glad it helped.
Thank you so much, really helped a lot!! Awesome work
@MattMacarty
6 жыл бұрын
Thanks. Glad it helped.
I don’t understand why professors make so difficult huhhh thanksss a lot man❤
@MattMacarty
8 ай бұрын
Thanks. Glad it helped
so so so helpfull, gonna submit my semester project with t your spreadsheet. thank you so muchh
@MattMacarty
3 жыл бұрын
Glad it helped.
Thanks for the help! Definitely useful stuff
@MattMacarty
5 жыл бұрын
Glad it helped
Thank you. Well explained. Great Job (thumbs up).
@MattMacarty
6 жыл бұрын
Glad it helped.
Very clear and easily understood. Thank you
@MattMacarty
3 жыл бұрын
Glad it helped
Thank you so much!! Now I understand a lot!
@MattMacarty
5 жыл бұрын
Thanks. Glad it helped.
Thank you! Video was very helpful!! :)
@MattMacarty
4 жыл бұрын
Glad it helped
Video really helped me! Thank you!
@MattMacarty
4 жыл бұрын
Thanks. Glad it helped
Thank you! This helped a lot
@MattMacarty
Жыл бұрын
Glad it helped!
thanks, gonna use this to find probable missing amounts
@MattMacarty
Жыл бұрын
Glad it helped
Very easy to understand, thank you!
@MattMacarty
5 жыл бұрын
Glad it helped
Thank you so much this was super helpful
@MattMacarty
Жыл бұрын
Glad it helped!
Thank you - very well explained!
@MattMacarty
3 жыл бұрын
Glad it helped.
Thank you! Very helpful
@MattMacarty
Жыл бұрын
Glad it helped
Excellent Matt
@MattMacarty
6 жыл бұрын
Thanks, glad it helped.
Wow! well explained. Thanks.
@MattMacarty
Жыл бұрын
Thank you. Glad it helped.
thanks Matt, you're awesome
@MattMacarty
2 жыл бұрын
Thanks. Glad it helped.
Thanks, really helpful!
@MattMacarty
5 жыл бұрын
Glad it helped.
man you are literally a god thank you so much !!!!!
@MattMacarty
Жыл бұрын
Thanks. Glad it helped
Very helpful! Thank you.
@MattMacarty
3 жыл бұрын
Glad it helped
LIFESAVER THANK YOU SO MUCH
@MattMacarty
2 жыл бұрын
Glad it helped
Thanks Matt - it was help in college
@MattMacarty
5 жыл бұрын
Glad it helped.
You are the man!
@MattMacarty
6 жыл бұрын
Thanks. Glad it helped
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?
very helpful, thank you very much for sharing
@MattMacarty
4 жыл бұрын
Thanks. Glad it helped
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.
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.
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.
Thank you so much. Can I share this youtube with my students?
@MattMacarty
5 жыл бұрын
Thanks. Yes please feel free to share.
Thank you so much!!!
@MattMacarty
Жыл бұрын
Glad it helped
My Professor shared this Video with in class :)
@MattMacarty
5 жыл бұрын
Thanks, hop it helped.
@iWillRun_
5 жыл бұрын
@@MattMacarty Thankyou too :)
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
Better than my professor explanation that took 6 classes and I did not understand a thing
@MattMacarty
3 жыл бұрын
Glad it helped
Thank you so much.
@MattMacarty
Жыл бұрын
Glad it helped
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: kzread.info/dash/bejne/dZOqktSloLerncY.html
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
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
Thank you very much. :-)
@MattMacarty
6 жыл бұрын
Glad it helped.
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.
Thank you!
@MattMacarty
7 жыл бұрын
Glad it helped
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
perfect thanks!
@MattMacarty
9 ай бұрын
Glad it helped
Wonderful video... Can we use excel solver to solve goal programming?
@MattMacarty
3 жыл бұрын
Yes, you may also be able to simply use goal seek
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
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
6 ай бұрын
I may be missing something, but this sounds like a simple algebra problem
@mdaroza
6 ай бұрын
@@MattMacarty I'd like to know if Solver is smart enough to calculate that I need 6 tires as opposed to 8.
@MattMacarty
6 ай бұрын
It should if you set the objective to minimize instead of maximize.
@mdaroza
6 ай бұрын
@@MattMacartyI think this is where I’m struggling with, is to set up the constraints.
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.
can you do it with multiple objective? and can we pick one thing from list with multiple objective problem with excel solver?
@MattMacarty
11 ай бұрын
You can only have one objective function.
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: kzread.info/dash/bejne/pX1pupSkeqTFZM4.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.
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
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: kzread.info/dash/bejne/aKyVk9qYedTQqso.html
Thank you so much
@MattMacarty
4 жыл бұрын
Glad it helped
Thank you!!!
@MattMacarty
5 жыл бұрын
Glad it helped
thank you!!!!
@MattMacarty
2 жыл бұрын
Glad it helped
When you start doing the SUMPRODUCT part you lose me. It doesn't show how you copied it down and I do not get the $ as you do. I only get the B3:C3 part and there is no explanation on the video as to how you did that since the cell you had already formatted
@MattMacarty
5 жыл бұрын
Hi to add absolute referencing ($), if in Windows press F4. If in MAC press command + T. To copy the formula down position your mouse over the lower right corner of a cell and then click and drag or double-click.
Awesome
@MattMacarty
3 жыл бұрын
Glad it helped
THANKYOUUUUUU
@MattMacarty
11 ай бұрын
Glad it helped
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
@nomnom914
Жыл бұрын
@@MattMacarty found it, its in the constraints button, set the operator to int
@MattMacarty
Жыл бұрын
@@nomnom914 Yes
how did you do the absolute referencing
@MattMacarty
3 жыл бұрын
In windows it's F4. Mac is command + T
Thank you #Matt Macarty
@MattMacarty
3 жыл бұрын
Glad it helped.
How to avoid decimal number. I want natural number but i keep getting decimal numbers
@MattMacarty
3 жыл бұрын
Yes it happens. Probably the easiest thing is to hide the decimals. You could also force integer values by setting int as a constraint on the changing cells.