Linear Programming (LP) Optimization with Excel Solver
HTML-код
- Опубликовано: 30 сен 2024
- #excel #solver #linearprogramming
Please SUBSCRIBE:
www.youtube.co...
alphabench.com/...
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/...
Man thank you I wish I could give you my money instead of my professors. I’ll repay you one day
Glad it helped. c
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
I got help from thechinahacks financially .They got me a transfer of $37,000 just yesterday .
@@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.
lol 😂 for real
Thank you so much this was super helpful
Glad it helped!
Thank you!!! So much better than my prof at explaining, especially since you use much clearer terms.
Thanks. Glad it helped.
@@MattMacarty Do you help with homework at a reasonable $/hr rate? The model I have to create is very complicated!
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.
They still teach that in school, but without the slide rule.
@@MattMacarty we would just use our Ti-84 and a pencil and paper to solve these in my operations management class.
easy way to learn ,good work to the developers of the video
Thanks. Glad it helped.
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
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!
Thanks. Glad it helped.
Using this in a business merger and acquisition deal in the Midwest. Its a maximization problem of how much capital to allocate to acquisition 1 vs acquisition 2. Its tricky in that the constraints are sensative to changes and are somewhat subjective. For example, we are using return on investment (ROI) as our maximization goal - similar to the "profits goal" in the video. However, the characteristics used as inputs on ROI have some nuances - i.e, capitalization rates, time horizon, standard deviation of cash flows, industry betas and overall risk profiles. Nevertheless, it does provide a methodology to analyze capital allocations to different investments.
I saw the other comments about how Linear Programming is taught at the university level. I experienced the same issue at both the undergraduate and graduate level. It was the most time intensive class I took and most of the learning I received was from my own investigation as the Prof didn't seem to care much about what we absorbed or was so far advanced that he had a difficult time bringing down to an introductory level. Stay with it though - its good real world usage your getting.
Thanks for your comment
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
Glad it helped.
Thank you so much. I was struggling to solve LP with excel solver and your video helped me a lot.
Glad it helped!
Matt, thank you for taking the time to produce this video. It has helped me a lot.
Thank you. Glad it helped.
Great video! Thank you so much for the explanation.
Thank you. 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.
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.
can you do it with multiple objective? and can we pick one thing from list with multiple objective problem with excel solver?
You can only have one objective function.
You are incredible thank you for your help 🙏🤗
Glad it helped
Please say in hindi.
So sorry. That's beyond my capabilities.
Thanks. You teach better than my prof. who teach in the most famous university in my country.... #youtuisbetterteacherthanteacherinclass
Thank you. Glad it helped.
Hello Matt, what keys did you press for you to change (B4:C4) to ($B$4:$C$4)??
It's F4 (function4) in Windows, command + T in MAC
Thank you very much for your RUclips video. I’ve subscribed to your channel.
Thanks! Glad it helped
I don’t understand why professors make so difficult huhhh thanksss a lot man❤
Thanks. Glad it helped
it was easy to learn and certainly very helpful to me. Thank you so much for making this video!
Thanks. Glad it helped
How to avoid decimal number. I want natural number but i keep getting decimal numbers
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.
Can you give me the same example (question, issue) Because I want to solve it on my own?
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
hi- to define if i exceeding daily usage what would be my constraints?
I think you will just have to compare actual usage with "optimal" usage
I think I watched about 5 videos before I found one that has the best example on what to do. Thanks!!! Short and Simple!!
Thank you. Glad it helped.
Extremely good video. I was pulling my hair in class because my teacher was confusing everyone. Thank you!
Thank 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!!!
Glad it helped!
hello! This is so good my MBA stats prof links this video to show us a different way to learn this.
I did have a question! is it possible to get a region of feasibility and/or a graph of such using this method?
Thanks. Yes you can at least easily plot the results of a data table with profit as the objective and varying production levels. This should result in a line graph or you can plot as an area chart.
This sensitivity report is not clear. Can you make a detailed video on its analysis please
You mean you can't see the report? You can changethe settings to HD and that should clear it up.
thanks, gonna use this to find probable missing amounts
Glad it helped
man you are literally a god thank you so much !!!!!
Thanks. Glad it helped
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?
I may be missing something, but this sounds like a simple algebra problem
@@MattMacarty I'd like to know if Solver is smart enough to calculate that I need 6 tires as opposed to 8.
It should if you set the objective to minimize instead of maximize.
@@MattMacartyI think this is where I’m struggling with, is to set up the constraints.
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!.
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! This helped me finally understand Solver.
Thanks. Glad it helped.
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?
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.
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
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
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
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.
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!
So you need side constraints. This example has something like what you are talking about: ruclips.net/video/C_v0rlpTEmc/видео.html
Wow man, you are a LEGEND; the way you explain.... just wow. I wish I could trade you with my professor.
hank 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!
Glad it helped.
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.
Glad it helped
so so so helpfull, gonna submit my semester project with t your spreadsheet. thank you so muchh
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!
Glad it helped
Wow! well explained. Thanks.
Thank you. Glad it helped.
Saved me for my Quantitative Analytics module! My lecturer explained this so badly, thank you!
Glad it helped
Easy to understand and so much easier than doing the simplex method by hand 🙏🙌 Thank you!
Glad it helped
Thorough and clear explanation. THANKS so much :)
Glad it helped
LIFESAVER THANK YOU SO MUCH
Glad it helped
Thank you! Very helpful
Glad it helped
Better than my professor explanation that took 6 classes and I did not understand a thing
Glad it helped
Wonderful video... Can we use excel solver to solve goal programming?
Yes, you may also be able to simply use goal seek
You are a god among men. This is the best explanation of this I have heard. Thank you.
Thanks. Glad it helped.
You're awesome for replying to every comment. Good video!
Thanks. 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. :)
Glad it helped!
This is the best explanation I've seen so far💯 ,Thank you Sir☺️
You are welcome. Glad it helped.
You saved my ass. I have an assignment on this and I had no idea before I watched this video.
Glad it helped
thanks Matt, you're awesome
Thanks. Glad it helped.
Hi Marty, do you have this as a certificate course on Udemy?
No, sorry.
Thank you! This helped a lot
Glad it helped!
Saved my life homie. Much love.
Glad it helped
Can you articulate the example in a paragraph or give the link of the example?
Hi. I have posted in the video description: ruclips.net/video/6xa1x_Iqjzg/видео.html
very helpful, thank you very much for sharing
Thanks. Glad it helped
Very clear and easily understood.
Thank you
Glad it helped
how can i add a constraint to disable the use of decimal?
You can solve as an integer problem, but it's likely better to just decrease decimals in Excel
@@MattMacarty found it, its in the constraints button, set the operator to int
@@nomnom914 Yes
So helpful, thank you so much Matt!
Glad it helped.
how did you do the absolute referencing
In windows it's F4. Mac is command + T
Your channel it s very helpful 👏👏
I just want to ask How we can use linear programming for asset allocation
HI. Take a look at this one: ruclips.net/video/sI5X2kJA_4k/видео.html
@@MattMacarty can we solve it with simpleLP ?
@@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.
im student from indonesia, thank youu this video really helpful :)
Glad it was helpful!
sen türk değil misin şimdi? ali fikri türkçe ad değil mi?
Thank you - very well explained!
Glad it helped.
Thank you! Awesome job btw, and very clearly and thoroughly explained :)
Thanks. Glad it helped.
Thanks, really helpful!
Glad it helped.
Explained clearly. Thank You, Matt!
Great it helped
what course do you learn this in? operations management?
There used to be a course called management science or operations research. Now it may be covered in OM.
@@MattMacarty cool it will help google stuff
you're really a good tutor. thank you so much. this helped a lot.
Thanks. Glad it helped.
Why is my Objective coefficient different in my sensitivity analysis than it is in my LP model?
Did you select Simplex method before solving? Did you change it in the spreadsheet after solving?
@@MattMacarty I re-did the entire sheet and resolved and am still having the same issue. The Decision variables are correct and the objective value is correct. I just can't execute the sensitivity analysis because all of the objective coefficients are incorrect. This specific problem is unique in that there are some routes that can not be used - I think maybe that has something to do with it - although I was able to execute a sensitivity analysis with an earlier version of the spreadsheet. Thanks for the help also!!
@@baselineiggy Maybe download my spreadsheet and strat from there: alphabench.com/data/linear-programming-optimization.html
@@MattMacarty Thankyou
Hi Matt! Thanks for your video, this really helped me to find the LP for my problem.
Glad it helped!
Thank you for making this video!
Glad it helped
Thank you #Matt Macarty
Glad it helped.
Thank you so much.
Glad it helped
perfect thanks!
Glad it helped
Thank you so much, really helped a lot!! Awesome work
Thanks. Glad it helped.
Thank you so much!!!
Glad it helped
Thank you. Well explained. Great Job (thumbs up).
Glad it helped.
Thank you so much. Can I share this youtube with my students?
Thanks. Yes please feel free to share.
Very helpful! Thank you.
Glad it helped
Thank you! Video was very helpful!! :)
Glad it helped
Very helpful and straight forward - thank you!
Glad it helped.
Very easy to understand, thank you!
Glad it helped
Saved me a lot of time and stress, can't thank you enough!!
Glad it helped
thank you!!!!
Glad it helped
THANKYOUUUUUU
Glad it helped
Thank you so much!! Now I understand a lot!
Thanks. Glad it helped.
My Professor shared this Video with in class :)
Thanks, hop it helped.
@@MattMacarty Thankyou too :)
Solver 101
Thanks
how did you make b4 and c4 constant??
These are changing cells for the model. If you change the parameters such as number of labor hours, etc. you will get a different solution.
I think I misunderstood your question. In the SUMPRODUCT function to make a freence absolute you can either hit F4 in Windows or Coomand+T in MAC
Thanks... it has worked perfectly
Thank you Matt. It was very helpful for me 🙏
Glad it helped
Awesome
Glad it helped
Thanks for the help! Definitely useful stuff
Glad it helped
Thanks Matt - it was help in college
Glad it helped.
Very clear and concise tutorial thank you!
Thank you. Glad it helped.