🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com ⏭ Watch next - playlist with all my free tutorial videos on how to use Excel: th-cam.com/play/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY.html
This is an amazing training session. Step-by-step, well designed, well delivered. Incredible way to spend 23:33 minutes of your time to really learn something useful. Thank YOU Kevin Stratvert
As usual Kevin, in the most simplest process you have delivered on Goal Seek and Solver. Brilliant!! And you made it very practical with the Cookie company example.!! Incredibly amazing!!
Very meticulous way of explaining my friend,Keep it up.i come back to your videos all the time whenever I have problems on EXcel.Thanks for helping me out!
Awesome Video. Going to play with your sample file to see how to include Day Old Cookies (i.e. current "acceptable" inventory) to see how I can maintain a full Product selection for your company - including the Fortune Cookie customer desires. Suspect that if money is the only goal - will need to reduce the variety of Cookies offered. I REALLY APPRECIATE your Video's. Not only the content but your voice and diction. I'm hearing impaired and your one of the very few presenters that I NEVER have any trouble understanding the audio or the content your presenting. The sample files and the hyperlinks to sections of the video are OUTSTANDING. THANKS a Million (Cookies). ;-]
Thanks Kevin, that was sweet. May I suggest you do a sensitivity analysis of a project. Take investment, project implementation, cash flows, discounts , profits, & determine: Rates of return, payouts, etc
Hi Kevin, great video first of all. Very clear explanation and concise. I'm just wondering though - what if there's multiple solution to the problem? Is there any way to display this?
Great material Kevin. QUESTION: When recreating your exact initial SOLVER example; Why do I keep getting a message saying "CELL REQUIRES A FORMULA," when a formula is clearly there? CONFUSING AND FRUSTRATING. Thank you.
Thanks for video. Is there a way to do goal seeker automatically? how to say, to be real time. when other values in the sheet change, goal seeker update itself automatically.
Kevin, if financial analysis including accounting is not your strength, you should partner with someone who is, and as a team create these excellent Excel tutorials on how to use Excel advanced tools in the field of finance and accounting. Many people, including your subscribers, are intimidated by the number crunching involved to do budgeting, something that they and everyone should do as part of their adult life. Having a working budget is important to keep oneself financially solvent. I think for a noble goal as a TH-camr, you should expand to help people better their lives using powerful tools like Excel. Using running a fictitious cookie company is fine and dandy, but you can become a more successful content provider here with real life applications. You certainly have the natural and hard-earned talent as an explainer of technical topics with easy-to-follow lessons. Perhaps it’s time to up the ante to more practical real-world lessons?
Love it. Will you do a "How to use" the "Szenario Manager" ? it would be very interesting; if you can come up with a good use case :-) BR from Switzerland
Hi Aldo, I found your video very easy to understand and I have immediately subscribed to your channel. However, I was not able to solve my simple problem. I need to calculate what my revenue should be in a range of 4 years in order to arrive at a net profit of $3,000,000, given some non-linear fixed costs. I am trying to use the solver function because I need to know the value of my revenue for each year. When I run the solver function it says that "Solver can not find a point for which all Constraints are satisfied.", but I did not put any constraints. I also tried to enter the constraints for my fixed non-linear costs, but I got the same error message. What am I doing wrong? Thanks, Paolo
Hi Kevin, Thanks for loading such helpful videos. Could you please solve one problem - How to reduce the time frame - this annoying windows 10 pop volume control panel stays on screen. That comes on the top left hand corner. It just stays on for soooooo long. This is a big nuisance. It want the pop up to come but only stay for short time. Can you plz help with this. Thanks
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 great video. How do you make solver update automatically when you change a value that is used in the equation, not an input value that you are asking to calculate but a constant that you set manually. How you can get all of the solutions visible that will fulfill the constraints?
Very useful video thank you I have a question, what's the difference between evolutionary and GRG non-linear with multistart optiion? Form what I understood they do the same thing.
Kevin- im looking to use solver for a specific headcount optimization problem at work, let me set the stage and perhaps you can help. I have 41 stores (column A store ID), traffic (column b), FTE budget (column c) and traffic per fte (c/d). the goal is to say, given a finite budget (lets say 400 for simplicity, aka the SUM of all stores budget), we want to optimize traffic per FTE (the calc in E at the store level by saying no store had more than 900 swings per fte). is this possible to say, hey move budget numbers around (keeping the 400 total budget) to solve for a range of calcs?
I hope you found a solution already, if not try these steps hope it helps. To solve your requirement using Solver in Microsoft Excel. Here's how you can do it: 1. Enter the data for the 41 stores in columns A, B, and C. 2. In column D, calculate the traffic per FTE for each store by dividing the traffic by the FTE budget. 3. In column E, create a binary variable for each store, where a value of 1 indicates that the store's traffic per FTE is greater than 900, and a value of 0 indicates that it is less than or equal to 900. 4. In another cell, calculate the total budget by summing the FTE budget for all stores. 5. In yet another cell, calculate the total number of stores that exceed the 900 traffic per FTE threshold. 6. Select the Solver add-in in Excel and set up the Solver Parameters as follows: Set the Set Objective cell to the cell that calculates the total number of stores exceeding the threshold. Set the To Value to 0, as you want all stores to have traffic per FTE less than or equal to 900. Set the By Changing cells to the range of FTE budget cells for all stores. Select the GRG Nonlinear method as the Solving Method. Check the box for "Assume Linear Model." Check the box for "Assume Non-Negative." Check the box for "Use Derivatives for Gradients." 7. Click the Solve button to run the Solver. 8. The Solver will adjust the FTE budget for each store to minimize the number of stores exceeding the 900 traffic per FTE threshold, while keeping the total budget equal to 400. The result will be a new set of FTE budgets that optimizes traffic per FTE.
Hey Kevin, Great simplex example for how to use Solver! ;-) Please note that there’s (a lot) more to the underlying solvers algorithms than what you showed. Especially the Simplex (LP - Linear Programming) method is more intricate than your straight line suggests. BTW: it was invented by an American (USAF) back in 1947, so you should be a little proud about it. :-) Also, only Simplex is guaranteed to find the global optimum solution. That’s one of the beautiful things about LP. The requirements for it’s use: all equations (objective and constraints) need to be linear in the variables. FYI: all your examples were linear. Then again, truth be told: “Life is a Constrained Optimisation Problem”, but it’s non-linear and often not even smooth. That’s why life chose the Evolutionary Solver. ...Just think about that one for a minute and let it sink in. :-) See you on the next one! :-)
Hello Kevin, I always turn to you when there is something I am struggling to solve. I am trying to produce a solution for scheduling a workforce when they have different skills and different work hours and different departments. Can you point me in the direction to achieve this. I rather think that solver is the answer but don't know how. Any help would be gratefully received,
00:09 Excel goal seek allows you to solve for an individual variable. 02:19 Using Excel Solver & Goal Seek to solve business questions 04:20 Using Excel Solver and Goal Seek to find the break-even point 06:21 To become a cookie millionaire, one needs to sell 404,000 cookies. 08:13 Solver is an add-in for Microsoft Excel which allows you to solve for multiple variables and constraints 10:17 Excel Solver allows for duplicating the actions performed in Goal Seek. 12:07 Solver can be used to find optimal answers and maximize profits in complex scenarios. 14:10 Using Excel Solver to find the optimum combination of cookies to make for maximum profit. 16:06 Maximize profit by finding variables that lead to maximum profit. 17:59 Selecting cookie quantities based on total demand and constraints 19:46 Excel Solver offers different models for finding optimum values based on the data type. 21:44 Select GRG Non-Linear as the solver option
🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
⏭ Watch next - playlist with all my free tutorial videos on how to use Excel: th-cam.com/play/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY.html
My CIS 112 class had me to watch this video but I ended up fell in love with your TH-cam channel thank so much for your shares
Can you suggest how to make solver run for all the rows using loop?
👏🏻👏🏻👏🏻
This is an amazing training session.
Step-by-step, well designed, well delivered.
Incredible way to spend 23:33 minutes of your time to really learn something useful.
Thank YOU Kevin Stratvert
As usual Kevin, in the most simplest process you have delivered on Goal Seek and Solver. Brilliant!! And you made it very practical with the Cookie company example.!! Incredibly amazing!!
you are simply brilliant, the humor is on point. Thank you
I like the simple approach you use Kevin. Excellent explanation of the different solver options.
Kevin thank you so much, have learned alot from your tutorial, so am able to teacher my students computer packages confidently. Good work Kevin
thanks!!
i actually was solving situations like these ... the manual way! Watch me impress my boss tomorrow. :)
Very meticulous way of explaining my friend,Keep it up.i come back to your videos all the time whenever I have problems on EXcel.Thanks for helping me out!
Awesome Video. Going to play with your sample file to see how to include Day Old Cookies (i.e. current "acceptable" inventory) to see how I can maintain a full Product selection for your company - including the Fortune Cookie customer desires. Suspect that if money is the only goal - will need to reduce the variety of Cookies offered.
I REALLY APPRECIATE your Video's. Not only the content but your voice and diction. I'm hearing impaired and your one of the very few presenters that I NEVER have any trouble understanding the audio or the content your presenting. The sample files and the hyperlinks to sections of the video are OUTSTANDING.
THANKS a Million (Cookies). ;-]
A very friendly way of presenting Solver. Understood well. Thanks !
Thanks Kevin, that was sweet.
May I suggest you do a sensitivity analysis of a project. Take investment, project implementation, cash flows, discounts , profits, & determine: Rates of return, payouts, etc
Getting an intuitive understanding of how changes impact complex calculations is very difficult in practice. This would make a great video.
Thanks Kevin. Really before I don't what is Goal Seek & Solver in Microsoft Excel but you've explained it. Hats off Kevin :)
You weren't kidding about evolutionary taking a while, great introduction as always.
Thank you for your clear and concise explanation.
Haven't heard about these functions until now! Great!
Thank you Kevin for this excellent lesson tutorial!
I don’t know man, I have achieved a lot of your videos Kevin and this one is also for the books 😂. Thanks for all you do.
Kevin, the man we didn't want, but needed
Affflack
Veey Nice .You explained it in a very simple manner..Thank You Very much
Thank you, Kevin. Great lesson.
Once again Great Explanation from Kevin.
A pretty clear explanation. Tks a lot!
Say what??? I had no idea that Goal Seek or Solver even existed in Excel. Yet another Kevin video I need to bookmark for reference. Thanks Kevin!
Thanks Kevin! Very clear explanation! I didn't know about these features in Excel.
Very helpful Kevin, thank you mate!
Love that intro though! That transition from the intro to you telling *"Hello Everyone, Kevin Here"*. That's just AMAZING after all!
It's just a damn intro
Thanks for this, simple o follow and understand
Falling in love with your videos!
Thanks for detailed explanation. Time to practise. ☺
Had no idea this existed. Fabulous.
Thanks for the teaching!
for the first time I have understood the goal seek and solver. In my mathematics class we used to call id linear programing an used simplex algorithim
good examples, clearly explained.. all good.👍
thanks for that.
Good clear explanation. Thanks.
Thank you, King!
Thank you... Now I'm actually craving cookies haha
Beautiful video. Thank you.
The free version of solver comes in Excel limits the number of variables that it will take. So have this constraint in mind when you plan to use it.
Correct: 200 variables and 100 constraints.
After that you’ll have to pay up.
Optimisation Solvers are big business!
Are there any "PREMIUM" Solver version? If yes, where find???
Nice touch with the new intro.
This is incredible!
ilove you man, you really help people
Thanks Kevin.
Very nice tutorial.
Well Done!! Thanks!!
Many thanks very helpful
I literally started eating cookies first thing in the morning because of this video
Thanks in a million.
Thank you Kevin ..
Woah nice intro, giving me that retro feel.
Hi Kevin, great video first of all. Very clear explanation and concise. I'm just wondering though - what if there's multiple solution to the problem? Is there any way to display this?
Great material Kevin.
QUESTION: When recreating your exact initial SOLVER example; Why do I keep getting a message saying "CELL REQUIRES A FORMULA," when a formula is clearly there?
CONFUSING AND FRUSTRATING.
Thank you.
Thank you
congratulation..you just solved how to add solver..hahaha..anyway, nice video kevin..
thanks man
Very ncie and informative
Good video. Thank you! Can I activate the solver from the VBA, after I created it?
Well explained
Thanks for video. Is there a way to do goal seeker automatically? how to say, to be real time. when other values in the sheet change, goal seeker update itself automatically.
Superb!!
There's another similar JS add-in called "Analytic Solver" which is easy to find in add-in store: Excel->Insert->Get add-in
Kevin, if financial analysis including accounting is not your strength, you should partner with someone who is, and as a team create these excellent Excel tutorials on how to use Excel advanced tools in the field of finance and accounting. Many people, including your subscribers, are intimidated by the number crunching involved to do budgeting, something that they and everyone should do as part of their adult life. Having a working budget is important to keep oneself financially solvent. I think for a noble goal as a TH-camr, you should expand to help people better their lives using powerful tools like Excel. Using running a fictitious cookie company is fine and dandy, but you can become a more successful content provider here with real life applications. You certainly have the natural and hard-earned talent as an explainer of technical topics with easy-to-follow lessons. Perhaps it’s time to up the ante to more practical real-world lessons?
Wow Kevin Thanks
18:40 " I don't wanna back any negative quantity cookies....unless I'm starting up a weight loss program...." OMG LOL I'm dead.
First nice video
When is the coding video coming pls tell
Love it.
Will you do a "How to use" the "Szenario Manager" ?
it would be very interesting; if you can come up with a good use case :-)
BR from Switzerland
Can you make solver tutorial with case study using bin and dif constraint?
I dont understand 😢
Mmm! Kevin Cookies, I want some, where can I order? Fun videos as always. I love Excel, so much Algebra math help built in.
Hi Aldo, I found your video very easy to understand and I have immediately subscribed to your channel. However, I was not able to solve my simple problem. I need to calculate what my revenue should be in a range of 4 years in order to arrive at a net profit of $3,000,000, given some non-linear fixed costs. I am trying to use the solver function because I need to know the value of my revenue for each year. When I run the solver function it says that "Solver can not find a point for which all Constraints are satisfied.", but I did not put any constraints. I also tried to enter the constraints for my fixed non-linear costs, but I got the same error message. What am I doing wrong? Thanks, Paolo
Hi Kevin, Thanks for loading such helpful videos.
Could you please solve one problem - How to reduce the time frame - this annoying windows 10 pop volume control panel stays on screen. That comes on the top left hand corner. It just stays on for soooooo long. This is a big nuisance. It want the pop up to come but only stay for short time. Can you plz help with this. Thanks
Nice one.
Thank you sir, question is There a way to copy and paste The solver calculation to other spread sheet , without needing to repeat ALL The process?
Hello Kevin, First, many thanks for the brill videos. Second will you be covering anything from the dark side (Macs)... databasing?
Can you make a video for how to get weighted average contribution margin using solver?
and btw, it was a greattttt video.thank you
Amazing !!!!!
Please make videos about microsoft Child account ..plzzz
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 great video. How do you make solver update automatically when you change a value that is used in the equation, not an input value that you are asking to calculate but a constant that you set manually. How you can get all of the solutions visible that will fulfill the constraints?
Very useful video thank you
I have a question, what's the difference between evolutionary and GRG non-linear with multistart optiion? Form what I understood they do the same thing.
Bro love from pakistan
Kevin- im looking to use solver for a specific headcount optimization problem at work, let me set the stage and perhaps you can help. I have 41 stores (column A store ID), traffic (column b), FTE budget (column c) and traffic per fte (c/d). the goal is to say, given a finite budget (lets say 400 for simplicity, aka the SUM of all stores budget), we want to optimize traffic per FTE (the calc in E at the store level by saying no store had more than 900 swings per fte). is this possible to say, hey move budget numbers around (keeping the 400 total budget) to solve for a range of calcs?
I hope you found a solution already, if not try these steps hope it helps.
To solve your requirement using Solver in Microsoft Excel. Here's how you can do it:
1. Enter the data for the 41 stores in columns A, B, and C.
2. In column D, calculate the traffic per FTE for each store by dividing the traffic by the FTE budget.
3. In column E, create a binary variable for each store, where a value of 1 indicates that the store's traffic per FTE is greater than 900, and a value of 0 indicates that it is less than or equal to 900.
4. In another cell, calculate the total budget by summing the FTE budget for all stores.
5. In yet another cell, calculate the total number of stores that exceed the 900 traffic per FTE threshold.
6. Select the Solver add-in in Excel and set up the Solver Parameters as follows:
Set the Set Objective cell to the cell that calculates the total number of stores exceeding the threshold.
Set the To Value to 0, as you want all stores to have traffic per FTE less than or equal to 900.
Set the By Changing cells to the range of FTE budget cells for all stores.
Select the GRG Nonlinear method as the Solving Method.
Check the box for "Assume Linear Model."
Check the box for "Assume Non-Negative."
Check the box for "Use Derivatives for Gradients."
7. Click the Solve button to run the Solver.
8. The Solver will adjust the FTE budget for each store to minimize the number of stores exceeding the 900 traffic per FTE threshold, while keeping the total budget equal to 400. The result will be a new set of FTE budgets that optimizes traffic per FTE.
Hey Kevin,
Great simplex example for how to use Solver! ;-)
Please note that there’s (a lot) more to the underlying solvers algorithms than what you showed.
Especially the Simplex (LP - Linear Programming) method is more intricate than your straight line suggests.
BTW: it was invented by an American (USAF) back in 1947, so you should be a little proud about it. :-)
Also, only Simplex is guaranteed to find the global optimum solution. That’s one of the beautiful things about LP.
The requirements for it’s use: all equations (objective and constraints) need to be linear in the variables.
FYI: all your examples were linear.
Then again, truth be told: “Life is a Constrained Optimisation Problem”, but it’s non-linear and often not even smooth.
That’s why life chose the Evolutionary Solver.
...Just think about that one for a minute and let it sink in. :-)
See you on the next one! :-)
what did I just read.
can you give example for non linear variable that similar to this case sir? :)
How Can I download this sample for test in Excel??
Thanks you
well , can i use this tool to solve sudoko or i need vba code ??
Hello Kevin, I always turn to you when there is something I am struggling to solve. I am trying to produce a solution for scheduling a workforce when they have different skills and different work hours and different departments. Can you point me in the direction to achieve this. I rather think that solver is the answer but don't know how. Any help would be gratefully received,
Best
👍 is there an equal for Google sheets?
What if my data tool does not have "what if analysis"
😂😂😂"I know these cookies are very distracting"
00:09 Excel goal seek allows you to solve for an individual variable.
02:19 Using Excel Solver & Goal Seek to solve business questions
04:20 Using Excel Solver and Goal Seek to find the break-even point
06:21 To become a cookie millionaire, one needs to sell 404,000 cookies.
08:13 Solver is an add-in for Microsoft Excel which allows you to solve for multiple variables and constraints
10:17 Excel Solver allows for duplicating the actions performed in Goal Seek.
12:07 Solver can be used to find optimal answers and maximize profits in complex scenarios.
14:10 Using Excel Solver to find the optimum combination of cookies to make for maximum profit.
16:06 Maximize profit by finding variables that lead to maximum profit.
17:59 Selecting cookie quantities based on total demand and constraints
19:46 Excel Solver offers different models for finding optimum values based on the data type.
21:44 Select GRG Non-Linear as the solver option
How do you figure out the demand?
Can you add notes to Fortune Cookies that say "Life is happy when you eat chocolate chip cookies"
👍
Kevin make a tutorial on Python programming
Plz make a video on ransomware. i have in my computer plz tell me how can i recover my data
You mean I could have cheated in Algebra class using Excel to solve the “train” problem?
Hello handsome.
Second
one random question. Do you actually like baking cookies? xD
I like eating them the most 😁
First
Ddddddddddon