Integer Programming: Budget Allocation with Excel Solver (Knapsack Problem)
Vložit
- čas přidán 13. 06. 2020
- Enjoyed this content & want to support my channel? You can get the spreadsheet I build in the video or buy me a coffee! Links below:
Buy me a coffee: www.buymeacoffee.com/tallysyunes
Buy complete spreadsheet: www.buymeacoffee.com/tallysyu...
Buy me an item from my wishlist: www.buymeacoffee.com/tallysyu...
Given a limited resource (e.g. a budget) and a collection of activities, each of which consumes a portion of the resource and provides some benefit, choose which activities to perform so that the total benefit is maximized without exceeding the budget. This problem captures several real-life situations in which the "budget" can be money, time, space, weight, etc. In the literature, this problem is known as the knapsack problem.
Such a helpful video, thanks so much!
You're welcome!
Good teacher... I understood it from here
Thanks to much for this powerful video
You are very welcome!
Very help full video, thank you!
I want to ask a question,
Do you have any explanation on how to effectively determine which facilities should be developed, when they should be developed and at what capacity in order to achieve minimum system costs?
Thanks in advance
You're welcome. This video may have what you are looking for: czcams.com/video/eWJl4H-DwWE/video.html
@@TallysYunes thank you so much for your prompt reply, sir.
Obrigada pela disponibilização dessa aula, tenho um pouco de dificuldade em entender os assuntos de programação inteira, mas consegui entender bem este assunto com o seu vídeo.
Oi Eloisa. Obrigado pela sua audiência! Fico feliz em saber que meu vídeo lhe ajudou.
Very insightful. How will will determine if the problem is binary or integer?
If you have decisions of the type yes/no, true/false, do/not do, etc., then you need binary variables. You also need them if your problem includes logical constraints of the type: if this then that, or if this then not that, etc. If you have decisions that are quantities (how much/how many) and these quantities need to be whole-valued (number of people, number of cars, etc.), then you need integer variables. It's common for problems to need both types of these variables together.
@@TallysYunes Thank you very much
@@TallysYunes thankyou sir for explaining!
How do I set a limit on how many items it can pick?
Actually don't worry I think I worked it out! (:
Did you have some formulas in grey cells?? I am not sure
No. The cells are painted gray just to highlight them and differentiate them from the others. Those are the variables in the problem, i.e. the solution to the question. It is the Solver add-in that calculates those values for us.
Hello can I ask for the graphical solution of this problem?
Pls send it to my email solielrabago@gmail.com
Problems with more than 3 variables cannot be solved graphically.
🎯 Key Takeaways for quick navigation:
00:00 *📚 Introduction to Integer Programming in Excel*
- Overview of transitioning from linear to integer programming, focusing on integral or whole number variables and binary variables for decision-making.
- Introduction to binary variables for yes/no decisions and their common applications.
- Preview of utilizing binary and whole number variables in Excel models.
02:08 *🎯 Budget Allocation Problem: The Knapsack Problem*
- Explanation of the budget allocation problem as a practical application of integer programming, referring to the historical knapsack problem.
- Setting the scenario for applying a budget allocation strategy using binary variables in advertising channel selections.
- Detailing the process of mapping out the problem's constraints and objectives using a hypothetical business case.
06:05 *💻 Setting up the Excel Model for Integer Programming*
- Instructions on creating the Excel model for the problem, including data input, defining binary variables, and formulating the objective and constraints.
- Demonstrating the setup of formulas for total exposure and total expense based on binary decisions.
- Guidance on using Excel Solver for maximizing exposure within the budget constraint by adjusting Solver parameters for binary variables.
11:07 *🔄 Different Applications of the Knapsack Problem*
- Describing the versatility of the knapsack problem by illustrating various real-life applications beyond budget allocation, such as camping gear selection and space mission preparation.
- Highlighting the adaptability of the problem structure to different contexts, including time management, space utilization, and auditing projects.
- Introduction to extending the binary variable model to accommodate logical conditions in subsequent scenarios.
Made with HARPA AI
Sir, I don't quite understand the solver, I have a stock and have 3 feature (for example A, B, C features) of it, I want to find optimum stock that staisfy maximizing A and minimizing B and C, how can I set objective and contraints? I couldn't find same type of problem solution on youtube, please help me. I am not sure how I can set the objective and constraints.
In the situation you describe, you have a multi-objective optimization problem. One way of approaching it is to find a target or goal value for each objective, turn them into soft constraints, and use Goal Programming as I describe in this video: czcams.com/video/p4rDNJXwx2M/video.html I don't have a video on this specific topic yet, but it's in the works. It's usually not possible to find a solution that achieves the best value for all objectives, so you end up with a collection of solutions that don't dominate each other; a kind of Pareto frontier of sorts.
@@TallysYunes thank you for your kind responds, I watched the video and I will do same problem myself to fully understand, but as my understanding my problem is like picking stock within a list with multiple max min objective function, I am not academic or expreinced guy so I couldn't fit solution in the referenced video to my problem, a video tutorial and multi objective problems explanation would be appreciated, thank you for your respond though
Yes, it's not obvious how to connect goal programming with multi-objective optimization without a specific video on the topic.
Hi, how do you limit the number of picks it can show but can still cover the budget?
To limit the number of picks you add a constraint that says SUM(gray cells)