Maximize Profit by Optimizing Production Using Excel Solver
HTML-код
- Опубликовано: 29 июн 2024
- A video showing a simple business problem that you can solve using Excel's Solver add-in. This particular example is focused on optimizing the production mix of Mice, Keyboards, and USB Hubs. However, there are many analogous problems in business, such as choosing Lean Six Sigma projects or Choosing IT enhancements that maximize return on investment (ROI).
This video shows the basics of using the Excel Solver add-in and also demonstrates some of the best practices in organizing your spreadsheet.
Here is a link to the spreadsheet used in the example: www.dropbox.com/s/fp2854wsj8y...
Easily the best example of how to use solver. Thank you, made this case in CPA much easier
Love it! Thanks for the feedback.
Thanks so much for sharing your knowledge, and so articulately. I had a problem to solve and watching your video I figured it out in less than 10 minutes! Great work! Thanks again!
Glad it helped!
Mark, you are very good! Easy and simple explanations. Thanks.
Glad you like them! Thanks for watching!
Thank you for keeping it complete and concise. I've been watching other ones, and they don't explain the step-by-step (and Microsoft makes 'WONDERFUL' tutorials -_-). Thank you.
Thanks Sean! Let me know of other topics you'd like to see ... if you have them.
@Dwayne Maverick, thank you for sharing... Would anyone else like to share, before we close the meeting?
Wow, you made something that seems complicated so easy! Thank you very much!
My pleasure. Thanks for watching!
Really useful! Thanks Sr! Now I do have a wide vision about solving complex problems!
Awesome! Thanks for watching!
Awesome video! Solver can be complex, but you did a good job at making it very understandable
Thanks dexter!
Thanks for the short and clear explanation. My Docent spent 3 hours about that topic and you can summarize it in 10min :)
You're very welcome! Glad it was helpful and concise!
Great video, looking forward to your new channel!
Thanks! Please feel free to suggest topics for me to cover. Thanks again for watching!
This is it, and you nailed it. Thanks a lot .
You're welcome! Glad you found it helpful!
Thank you are the best I have seen so far.. very clear indeed. ..
Thanks so much!
Thanks. Very well done and clear.
My pleasure. Thank you for watching!
I've been using Excel since it's ZeroDay. Today I learned something new! Thanks!
Well, maybe not so fast. It seems to me that the only way to increase profits is to increase the price, or change the output rate of the machine; because the 1st step (operator) isn't going to change his hourly pay, nor is he likely to work faster. One could look at Labor calculations and their costs as "Process 1" and Manufacturing expenses and their time as "Process 2"; which is what I'm doing. Setting up the Solver is quite trickier than what you did (and I followed) because if you change the variables Process 1 or 2 or constraints; wonky things happen. For example, the constraint of hours can be met by the first item; and the second item shows a negative hours (which is impossible in real life). I'd encourage the next example to have hourly wages (such as minimum wages or typical manufacturing wages which around here are $14.00/hour) and then make this Solver work. In any event, I am having quite the challenge trying to change the variables of production then getting the Solver to find me Demand for the 3 products that achieve maximization of profits. --Regardless, this is something new for me to learn more about.
Yes, solver takes some practice adding constraints. It also greatly depends upon which version of Excel you have, your "epsilon" (smallest step you take), the way you choose your constraints (E.g., do you choose to make your values integers or floating point), potentially the initial conditions you use ... you may find a local, but not global maximum.
Certainly the topic of optimization is far more complex than can be covered in 10 minutes. However, I have found as a teacher of math to mostly adults, that many of the tangential issues that only can be addressed by the learner taking his/her time to "bang his/her head" for a while and buckle down to actually do what professional mathematicians/statisticians/engineers do (which is mostly making and correcting errors) get solved much better when the learner solves a simple problem or two.
Truth is, you can do a ton of things with Solver - optimize schedules, maximize ROI of various investments, minimize travel time, etc. But there are many "tricks" to learn along the way. It's fun! Treat it that way. A don't worry about making a mistake. The computer will forgive you and let you try again! :)
This was so helpful! Thank you!
Glad it was helpful for you!
Awesome. Thank you for the guide
Glad you enjoyed it!
Sir, thank you so much. You saved my life.
Glad to help!!
Thank you very much for such amasing presentation.
My pleasure.
This video was fantastic!
Thanks!
Very good , you explain it better than my teacher
Thanks James!
Thanks! Really good explanation.
Glad it was helpful!
Thank you! You are great!
Thanks Kristina!!
I love your guitar videos, and since I am an ops analyst in real life, this is a pretty cool tutorial. I think the fact I watched a 10 minute video on Excel makes me a nerd . How about one on practical applications of Pivot Tables?
Keep up the good work!
Hi Kenneth. Thanks! Pivot tables? Only the best thing Excel has ever done!! Sure, I'll do some Pivot Table videos. I'm planning to launch another channel (Math/Stat/Six Sigma/Continuous Improvement) shortly, and I'll publish a bunch of these types of videos. I'll give you a heads-up when I do. Thanks for the GREAT suggestion!
Very cool!
Thank you sir ! you're really life saver
You are most welcome
Excellent.
Thank you!
great video, helped a lot!
Glad it helped!
Amazing video sir! Please make a video on Solver table as well. Lovely explanation skills. Thank You!
Hi Priyesh. Thank you so much. I will certainly follow up on that video. Thanks for watching and for your kind comment and suggestion!
Thank you very informative
Glad it was helpful!
The video was very helpful sir. It would be better if you could do even more examples using solver
Thank you. I will do some more examples certainly. Thanks for watching!
Excellent video. One critique though: i think it's technically "Monthly QUANTITY Demanded." The word "Demand" refers to the whole demand function, so it's not a number, unless it's perfectly elastic (a horizontal line).
Okay, thanks.
You are awesome bro
Thank you so much 😀
Thanks govindu
You're welcome.
Is this a class that you teach? I'm in a Data Analytics course that's following problems from the same book Microsoft excel 2019 Data analysis and business modeling, is there any resource you would recommend for the explanations to the problems section? Also this vid really helped me out thanks.
Hi. I used to teach a class that featured these sorts of problems, and I may go back to teaching it again some day. I'm familiar with the book you mention (Wayne Winston I believe), but with a much earlier version - maybe 2004 or something like that. Good book. I seem to remember in those days it came with spreadsheet examples, though very little to no explanation of them. I wish I knew of another resource I could tell you about, but I don't know of one. Sorry about that.
Nice one
Thanks
You're welcome. Thanks for watching!
Mark Zabel my pleasure
you are a "mouse" as the example above, but "GENIUS!!!!!" brand
This is great, can we apply this on my stocks portfolio, where i have Qunatity, & Bought price, to maximize the overall Profit
Yes!
I have a problem where I need to find the minimiun cost for new hires and layoffs in a company, anyone has an example?
i am doing a practice set and some of my questions after I input my constraints, solver would not change the values of my changing cells.
Without knowing more about your specific situation, I'd recommend:
1. Make sure Solver is working. Put in a simple equation like a2 = 10, b2 = 5 and c2=a2-b2. Then "solve c2" by setting c2 as target (target of 0) by changing b2. No constraints. Solver should change b2 to 10.
2. If step 1 works, check your options on the Solver. Sometimes Solver defaults to some things that aren't desirable - like forcing solutions to be integer values or non- negative.
3. Assuming all is okay in steps 2 and 2, check your constraints very carefully. Delete them all and test each of them out 1 by 1 until they all work as expected.
If your step 1 fails, you might not have Solver installed properly. Check the general options in Excel first. If all seems okay, you may need to reinstall Solver. Good luck.
The only thing I am not quite understanding is that if your production costs per unit are so much cheaper than your labour cost per unit, then you think that you could get a higher profit from lower costs by reaching the production constraint first instead of the labour constraint. Wait, on second look, I realize that while what i say might be true in the real world, there are actually no labour costs or production costs in this model. Only hours. The cost is factored into profit per unit but we don't really see how that cost is allocated between labour and production costs . And certainly factoring the cost of labour and production would change the profit function. But yeah, I guess this tutorial was just trying to give a simple example. Could always play around with it by adding cost per unit to labour and production, or cost per hour, and try a few more models with solver. If it turned out capital costs were cheaper, than I guess you WOULD hit the capital constraint before labour constraint, but i suppose thats just not factored in
Labor is included into unit cost here, as it usually is. If you want to split it into "production" and "labor", that's doable too, but it leads to a different mathematical optimization problem.
Hello Mark, thanks for the video. But I am having trouble with solver. I did just the same as you described with the same sheets and constrains. But it keeps popping a solution, where one product is zero. So all the production goes to the second one. Could you please help me out with this? Thanks!
Not clear what the issue is without knowing a lot more about what you've got. But 3 things to look for:
1. Make sure your constraint references are correct in Excel. Easy to miss one or pick an incorrect cell.
2. Check the "Options" in the solver. Make sure integer constraints are not ignored.
3. Double-check your constraints as defined in the Excel workbook.
You can also download the example spreadsheet to see *exactly* what I did. ruclips.net/user/redirect?v=rbZww2eCzo4&event=video_description&q=https%3A%2F%2Fwww.dropbox.com%2Fs%2Ffp2854wsj8yvd46%2FComputerPeripheralOptimization_Solver%25231.xlsx%3Fdl%3D0&redir_token=QUFFLUhqa3FnXzM2d0x5b3puakpEcHBhZ3puOGRjakh1d3xBQ3Jtc0tuN3M3NTdyUUFZSFBYV0JINkRDWjRrc3NiUXpJVENZWmpua2kwRHk0NTc1SEh6UGZPcHJLSy00TkgzMjN4SUo5aVRfbHpkSndPZGNjYTIxdHRtTkM5bHBXS3lWZkd3cHpZRld4VVcwbUZMSTBiYk9PQQ%3D%3D
@@MarkZabel Dear god!!!!! You have saved me from the misery! Thank you so much! God blesses you !
@@NuJorkJB So glad it helped!
How would you add the constraint "If demand of mice is exceeded, reduce the total profit by 20%"?
Good question.
In one sense, no constraints would change. It's not a constraint, it's a calculation you'd need to put into the calculation of profit. The target is still the same and the constraints are the same, but the penalty for exceeding mouse demand is higher. It may not affect the optimization at all.
However, though the optimization would likely be the same, but the risks may not be optimized that way. So as a business decision, you may choose to manufacture more mice to avoid risk. One thing that basic optimization analysis doesn't account for is uncertainty.
Again, great question!
I want to introduce 20 more new products. So I discontinued some unprofitable products. So using existing production capacity I worked out with solver. It gives answer that I need to produce only 2 products out of 20 products (product mix) to maximize the profit. Is this correct answer?
Hi. I'm not sure whether this is a textbook problem or a real business problem. If I know that it would help.
I don't understand the context here. 20 new, but you're discontinuing "some" unprofitable products ... nothing about "profitable products" that exist. Do they exist? Are you discontinuing them too? At the end you allude to 2 out of 20, but there are clearly more than 20 products in question in your problem setup. A little more precise specification to the problem would help.
All of the above notwithstanding, "the right answer" is probably not what you're looking for here. If you ran a pilot to see which of 20 new products would be most profitable it would be easy to see which ones were. No solver is required. If you want to maximize profit (given, say, production constraints and market constraints) you very well could come up with a strategy to introduce only 2 of the 20 products.
So a study in optimization could be helpful, but in business you'd definitely want to go further than that, because these are new products. You'd want to seriously ask whether you captured robust enough constraints, you'd want to look at hedging strategies, you'd want to seriously question the experimental groups and control groups in your pilot. Launching products is serious business.
What is the difference between labor time (hours) per unit and production time (hours) per unit?
Production time is the time a machine is available. For example, if a plant is open from 8 am to 8 pm that might be 12 hours for that day.
Labor time is the amount of people-time you have available. Say you have 2 people who work from 8 am to 4 pm with 1 hour break each day. That would be 7 + 7 = 14 hours of available labor.
I hope that helps.
nice
Thanks!
Good
Thanks
how did you come up with labor/unit(hour) .45 and time .04?
It is assumed we know those numbers. (stated at 0:23) In a business situation, you would measure them; either through a time study (i.e., going out to production and timing it) or you got it from the accounting department or you got it from someone in operations who usually go through great pains to get those measurements.
@@MarkZabel thanks for your response...
Hi I want to set alarm and display message box when the paticular time of the day occur in my excel workbook to remind me do something on that time.Can u please help me to write some code in vba to perform this..thanks in advance....🤝
Hi. I'd be happy to help. I develop spreadsheets for hire, so you can email me for rates (mark@markzabel.com)
I need Solver to accept formulas in the Variable Cells Line so that it can select only certain cells within a range. Is this possible? If not, is there an alternate method to achieve my goal?
Hi. I don't think Solver can do that, however ... the solution I usually use is a combination of two things you can do in the spreadsheet itself.
1. Create a matrix of 1's and 0's that matches your area and have the product be the selection you want.
2. Sum up the columns or rows of the resultant matrix to create your objective function that you wish to optimize.
Then Solver is varying the 1's and 0's, but the resulting product is as if you're turning on and off various cells.
The context is often something like "find the optimal set of projects to do given capacity and budget constraints - maximize potential ROI" or "find the minimal set of nursing to fully staff needed patient intake each day of the week given constraints on the number of hours and/or days in a row a nurse can work ... or certain nurses cannot work weekends", etc.
Hope that helps.
@@MarkZabel In my case I leave the Objective cell blank. The Variable Cells are T2:T9, Constraints are C2:C3=I2:I3. The thing is that I don't want all the cells within the whole range of T2:T9 selected to act as Variable Cells for Solver - just the ones that have a "tick" in the checkbox next to them.
Hopefully that makes more sense on my end.
@@theapexpredator157 Not having an objective cell doesn't make sense to me. You have to have *something* that you're trying to drive to a MAX, MIN or TARGET, right? Otherwise what are you solving? In Operations Research that is usually called the "objective".
The matrix of 1's and 0's will act as your "tick marks".
@@MarkZabel The objective in my case is just to meet the constraints that I put in. For my purposes, having something inputted into the objective line is really just not necessary at all. It may not make sense, but it's really the case for me.
The matrix of 1's and 0's sounds interesting, but I'm not grasping it exactly and how it could be used to help my case.
Thanks for the help btw!
@@theapexpredator157 I see. I think I have a solution for you. It involves the 1s and 0s ... so here's the idea: each 1,0 pair acts as a switch (1=ON, 2=OFF). That way you're essentially turning on or off cells. Which gets me to what I think will solve it for you.
1. For each constraint create (In Excel, not Solver) a cell that checks if the constraint is met. And you set TRUE = 1, FALSE = 0. So lets say you had a constraint that A2 > 10. in another cell , say C2, you create the formula: =if(A2>7, 1,0). You continue this for each constraint, using whatever logic in Excel you need. Multiple cells? No problem!
2. You sum up each constraint and make that your objective function. Suppose you have 10 constraints and your 1/0 cells go from C2 to C11. Then you could make, say, C12 = Sum(C2:C11). When all constraints are met, C12 = 10. When C12 < 10, by definition not all the constraints are met.
3. Set C12 as your target cell and set the target to 10. (or however many constraints you have).
I think that will work for you. Good luck! Interesting problem!!
hello.....i hv some question to ask u(A factory plan to produce two types of production based on three production line A, B, C. every production of type 1 takes 1 hour on line A and 3 hours on line B. Every production of type 2 takes 2 hours on line B and 2 hours on line C. Line A, B, C provides 4 hours, 18 hours and 12 hours for production respectively. Every production 1 brings the profit 300$ and every production 2 brings 500$. What is the production schedule to obtain the maximal profit? )......on this case how to solve it?
You need to understand/determine/specify the costs of each production process (A, B, and C) and whether labor and/or time is constrained throughout any of the processes A, B, and C. For example, if the cost of labor in process A is 10 times that of B or C producing Product Type 2 exclusively may be most profitable. Your problem is under-specified. If it's a real problem in business, you'll need to understand costs and constraints on your core processes A, B, and C. (I.e., labor, materials, facilities, etc.)
@@MarkZabel thz u..🙏
@@MarkZabel It is not about My business, It is My exam😥😥😥
@@phoutthalavongnathy6783 Okay. You should examine the question carefully to see the assumptions made. The problem as you stated it to me was under-specified. Ask the teacher for clarification if you're unsure.
can you please explain the T MOTAD model
Hi Nazneen. I don't know much about it. My understanding is that MOTAD models mainly differ from more typical models in that they minimize ABSOLUTE DEVIATIONS rather than LEAST SQUARES DEVIATIONS. Least squares models have some very math-friendly properties and that's a main reason they're so popular and powerful. It makes sense to me that now, because looking at absolute values is easy to do on computer, MAD models would be re-investigated, as they will have different properties. (Convergence, goodness of fit, etc.) Beyond that I don't know anything about MOTAD models.
Mark Zabel
Thank you for your response
I cannot find solver in add-in ,how can I tackle this?
Hi. Please tell me what version of Excel you are using. I may be able to help once I know that.
thanks!I have solved the problem
Cool!
Sorry but don't understand the difference between Time/Unit (hours) and Labour / Unit (Hours) ?
No worries. Labor/unit is the average time a unit (1 mouse, 1 keyboard or 1 USB Hub) takes in people-time. For example, a USB Hub takes 12 minutes of people-time on average. It doesn't matter how many people are involved in making the USB hub. That 12 minutes is the total of their times in making that 1 USB Hub.
Time/Unit is the amount of production time 1 mouse, 1 keyboard, or 1 USB Hub takes. That's actual time in a day. Manufacturing plants have a limited number of lines and potentially 24 hours a day open to them. But you cannot have 25 hours in a day. For example, each mouse in the example takes up 2.4 minutes of production time (0.04 hours). There are a total of 1700 hours of production time available in a month for all products.
The point is that labor and time are different operating levers for businesses. This is a manufacturing example, so maybe it is unfamiliar. So maybe consider a process of baking cakes. A cake may require a certain amount of labor (beating eggs, adding ingredients, piping icing, etc.) but it also requires time to put into an oven to bake. If you have 10 bakers, but only have 2 ovens you would have a lot of labor time available (bakers), but not that much production time (oven capacity).
I can't access the spreadsheet document in the link
It's still there, and the link works.
I don't get it all these videos about playing the guitar when one about Excel. I need more videos on Excel but not videos about guitars😉
ewan thomas Okay. More Excel ... Got it! :)
I suck at optimization problems....
Hmmm, it's all about the setup of the objective function and constraints. I hope this helped a little bit.
+Mark Zabel +Mark Zabel It's cool, I am taking calculus right now and we don't use excel, everything is hand written, we can't even use graphing calculators either. This is a nice tutorial though, so thanks!
Take care , you said seventeen thousands but it's one thousand seven hundred
Thanks! I think I said "seventeen hundred" which is a colloquial way of saying 1700. (I.e., 1700 = 17 * 100) But yes, it's a bit of an odd way of saying "one thousand seven hundred".
@@MarkZabel thanks man ..new info
Hiii I’m really stuck on a similar problem like this it would be great if you could help me out!
Do you have a question?
Mark Zabel hi yes is there a way I could possibly email you the problem
Helloooo...
Please let me know!!
@@heeralgajjar298 Hi. If you don't ask the question, I don't know whether I can help you. So please ask.
I cant stand these things theyre soooo boring but i need to learn them for school :c
Thank you Sir, for the presentation. But you speak too fast and your actions are too fast. If you wish your video to be watced by more international people , you must speak and act in slow motion.
Thanks for the feedback.