title

Excel Solver & Goal Seek Tutorial

description

In this step-by-step tutorial, learn how to use Goal Seek & Solver in Microsoft Excel. With Goal Seek, you can find the result you want by adjusting an input value. Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell subject to constraints, or limits, on the values of other formula cells on a worksheet.
👋 Additional resources:
- Sample files to follow along: https://1drv.ms/x/s!AmxrofZZlZ-whK4XsoXu2yIHzc6bhg?e=czyAOX
- Learn the fundamentals of Excel in just 2 hours: https://kevinstratvert.thinkific.com
⌚ Timestamps
0:00 Introduction
1:49 Goal Seek
8:26 Turn on Solver
9:56 Solver Simple Example
12:11 Solver Advanced Example
22:49 Wrap up
📃 Watch related playlists and videos
- Playlist with all my videos on Excel: https://youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY
🚩 Connect with me on social:
- LinkedIn: https://www.linkedin.com/in/kevinstratvert/
- Twitter: https://twitter.com/kevstrat
- Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818
🔔 Subscribe to my YouTube channel
https://www.youtube.com/user/kevlers?sub_confirmation=1
🎬 Want to watch again? Navigate back to my YouTube channel quickly
http://www.kevinstratvert.com
🛍 Support me with your Amazon purchases: https://amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#stratvert

detail

{'title': 'Excel Solver & Goal Seek Tutorial', 'heatmap': [{'end': 383.038, 'start': 321.992, 'weight': 0.743}, {'end': 1036.374, 'start': 986.513, 'weight': 0.723}, {'end': 1078.171, 'start': 1058.515, 'weight': 0.748}, {'end': 1414, 'start': 1399.86, 'weight': 1}], 'summary': "Learn to use excel's goal seek and solver features for data analysis, including examples like determining breakeven points and maximizing profit while considering constraints, as well as optimizing cookie baking for profit by leveraging solver to find the optimal combination and using evolutionary models for better results.", 'chapters': [{'end': 71.906, 'segs': [{'end': 47.111, 'src': 'embed', 'start': 3.663, 'weight': 0, 'content': [{'end': 5.105, 'text': 'Hi everyone, Kevin here.', 'start': 3.663, 'duration': 1.442}, {'end': 13.356, 'text': 'Today I want to show you how you can use both Goal Seek and also Solver in Microsoft Excel.', 'start': 5.285, 'duration': 8.071}, {'end': 17.862, 'text': 'These are two different tools that you can use with data analysis.', 'start': 13.997, 'duration': 3.865}, {'end': 24.689, 'text': 'So what can you actually do with them? Well, with Goal Seek, you can solve for an individual variable.', 'start': 18.242, 'duration': 6.447}, {'end': 25.711, 'text': 'What does that mean?', 'start': 25.05, 'duration': 0.661}, {'end': 33.799, 'text': "Well, let's say, at the Kevin Cookie Company, let's say, I want to figure out how many cookies do I have to bake on a daily basis to break even?", 'start': 26.051, 'duration': 7.748}, {'end': 36.802, 'text': 'I can use Goal Seek to solve for that.', 'start': 34.3, 'duration': 2.502}, {'end': 42.807, 'text': 'So what is Solver? It does everything that Goal Seek does, but it does even more.', 'start': 37.443, 'duration': 5.364}, {'end': 47.111, 'text': 'It solves for multiple variables and you can even add constraints.', 'start': 43.328, 'duration': 3.783}], 'summary': 'Learn to use goal seek and solver in excel for data analysis.', 'duration': 43.448, 'max_score': 3.663, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE3663.jpg'}], 'start': 3.663, 'title': 'Using excel for data analysis', 'summary': 'Discusses using goal seek and solver in microsoft excel, with examples such as determining the number of cookies needed to break even and maximizing profit while considering constraints.', 'chapters': [{'end': 71.906, 'start': 3.663, 'title': 'Excel: goal seek and solver', 'summary': 'Discusses how to use goal seek and solver in microsoft excel for data analysis, outlining their functionalities and providing examples such as using goal seek to determine the number of cookies needed to break even and utilizing solver to maximize profit while considering constraints.', 'duration': 68.243, 'highlights': ['Solver can solve for multiple variables and add constraints, allowing for the optimization of resources, such as determining the combination of cookies to maximize profit while considering production limitations.', 'Goal Seek can be used to solve for an individual variable, such as determining the quantity of cookies needed to break even at The Kevin Cookie Company.', 'The chapter provides practical examples of using Goal Seek and Solver in real-world scenarios, such as determining daily baking quantities and optimizing cookie production for profit maximization.', 'The speaker, Kevin, introduces the concept of using both Goal Seek and Solver in Microsoft Excel for data analysis, indicating their relevance in making informed business decisions.']}], 'duration': 68.243, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE3663.jpg', 'highlights': ['Solver can solve for multiple variables and add constraints, allowing for resource optimization.', 'Practical examples of using Goal Seek and Solver in real-world scenarios are provided.', 'Goal Seek can be used to determine the quantity of cookies needed to break even.', 'The chapter introduces the concept of using Goal Seek and Solver in Microsoft Excel for data analysis.']}, {'end': 340.342, 'segs': [{'end': 99.484, 'src': 'embed', 'start': 71.906, 'weight': 0, 'content': [{'end': 78.429, 'text': "we're going to walk through this step by step and I'll show you exactly how you can take advantage of these different tools.", 'start': 71.906, 'duration': 6.523}, {'end': 82.69, 'text': 'In fact, if you want to learn, I think the best way is to actually do it.', 'start': 78.829, 'duration': 3.861}, {'end': 86.992, 'text': "I've also included the sample sheet so you can follow along with this video.", 'start': 83.09, 'duration': 3.902}, {'end': 90.673, 'text': "Alright, well let's jump on the PC and let's start solving.", 'start': 87.592, 'duration': 3.081}, {'end': 93.096, 'text': 'Here I am now in Microsoft Excel.', 'start': 90.993, 'duration': 2.103}, {'end': 99.484, 'text': "And once again, if you want to follow along, I've included a link to this spreadsheet in the description of this video.", 'start': 93.196, 'duration': 6.288}], 'summary': 'Learn to utilize different tools in microsoft excel with practical demonstration and sample sheet provided.', 'duration': 27.578, 'max_score': 71.906, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE71906.jpg'}, {'end': 170.825, 'src': 'embed', 'start': 112.099, 'weight': 2, 'content': [{'end': 117.502, 'text': 'This is the easier of the two and this allows us to solve for one variable at a time.', 'start': 112.099, 'duration': 5.403}, {'end': 125.168, 'text': 'Now this might bring back some memories of algebra class back in middle school or in high school and hopefully those memories are all very positive.', 'start': 117.883, 'duration': 7.285}, {'end': 132.392, 'text': "To get started on the bottom, let's click into the worksheet called Goal Seek, and this will drop you on this page right here.", 'start': 125.908, 'duration': 6.484}, {'end': 135.795, 'text': "And we're going to focus most of our time over on the left hand side.", 'start': 132.573, 'duration': 3.222}, {'end': 138.196, 'text': 'I know these cookies are very distracting.', 'start': 136.255, 'duration': 1.941}, {'end': 142.919, 'text': "I probably shouldn't have put them in, but just try your hardest to look over to the left.", 'start': 138.396, 'duration': 4.523}, {'end': 145.981, 'text': 'This is subliminal messaging for the Kevin Cookie Company.', 'start': 143.4, 'duration': 2.581}, {'end': 149.224, 'text': "Hopefully by the end of this tutorial, you'll want to buy some cookies.", 'start': 146.021, 'duration': 3.203}, {'end': 157.051, 'text': 'Now, I have a few different business questions that I need to answer today and I have a hunch that Goal Seek can help me with this.', 'start': 149.964, 'duration': 7.087}, {'end': 158.232, 'text': 'Right up here.', 'start': 157.652, 'duration': 0.58}, {'end': 163.397, 'text': 'I want to know how many cookies do I need to sell to break even on a daily basis?', 'start': 158.232, 'duration': 5.165}, {'end': 170.825, 'text': 'And along with that, once I break even and once I hit that milestone, well, how many cookies do I need to sell to become a cookie millionaire?', 'start': 164.098, 'duration': 6.727}], 'summary': 'Using goal seek to solve for variables, with a focus on business questions and cookie sales.', 'duration': 58.726, 'max_score': 112.099, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE112099.jpg'}, {'end': 240.175, 'src': 'embed', 'start': 209.607, 'weight': 3, 'content': [{'end': 214.993, 'text': 'Of course, there are ingredients, there are different costs involved, and so my unit cost is $2.50 to make each cookie.', 'start': 209.607, 'duration': 5.386}, {'end': 221.099, 'text': "And also, it's not just the ingredients, but there are also a lot of overhead costs.", 'start': 217.176, 'duration': 3.923}, {'end': 223.241, 'text': 'For example, I have a commercial kitchen.', 'start': 221.159, 'duration': 2.082}, {'end': 224.402, 'text': 'I have an oven.', 'start': 223.261, 'duration': 1.141}, {'end': 229.645, 'text': 'These are all expensive, and so my fixed costs are $10,000.', 'start': 224.782, 'duration': 4.863}, {'end': 233.249, 'text': "So to calculate my profit, let's click down here into the bottom cell.", 'start': 229.646, 'duration': 3.603}, {'end': 240.175, 'text': "And here you can see that to calculate the profit, here I take the number of cookies sold times the unit price, and that's my revenue.", 'start': 233.609, 'duration': 6.566}], 'summary': 'Unit cost is $2.50 per cookie, fixed costs are $10,000, profit calculated by cookies sold x unit price.', 'duration': 30.568, 'max_score': 209.607, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE209607.jpg'}, {'end': 321.292, 'src': 'embed', 'start': 293.872, 'weight': 1, 'content': [{'end': 297.974, 'text': "Or what if I sell 5,000 cookies? Oh, there I've made some money.", 'start': 293.872, 'duration': 4.102}, {'end': 305.46, 'text': 'But what is the break even point? Now, I could just go around, type in different numbers until I find it, but that takes a really long time.', 'start': 298.435, 'duration': 7.025}, {'end': 310.123, 'text': 'Instead, we should rely on Microsoft Excel to figure this out for us.', 'start': 305.96, 'duration': 4.163}, {'end': 316.328, 'text': 'This is where we can use Goal Seek to figure out what is the number that brings us to the breakeven point.', 'start': 310.643, 'duration': 5.685}, {'end': 321.292, 'text': "To access Goal Seek, let's go up to the top tabs on top and click on data.", 'start': 316.728, 'duration': 4.564}], 'summary': 'Using goal seek in microsoft excel to find break-even point efficiently', 'duration': 27.42, 'max_score': 293.872, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE293872.jpg'}], 'start': 71.906, 'title': 'Using goal seek in excel', 'summary': "Walks through using microsoft excel's goal seek feature, including hands-on learning with a sample sheet, and discusses using goal seek to calculate breakeven points, becoming a millionaire, and multimillionaire based on revenue, unit cost, and fixed costs, showcasing the feature's efficiency.", 'chapters': [{'end': 149.224, 'start': 71.906, 'title': 'Microsoft excel goal seek tutorial', 'summary': "Walks through using microsoft excel's goal seek feature, emphasizing hands-on learning with a sample sheet provided, and suggests positive memory recall of the algebra class. it also includes a subliminal messaging for the kevin cookie company.", 'duration': 77.318, 'highlights': ['The chapter emphasizes hands-on learning by providing a sample sheet for viewers to follow along, enhancing retention and understanding.', 'It suggests positive memory recall of the algebra class, creating a relatable and positive learning experience.', 'The chapter includes subliminal messaging for the Kevin Cookie Company, subtly promoting the product to the viewers.']}, {'end': 340.342, 'start': 149.964, 'title': 'Using goal seek in excel', 'summary': 'Discusses using goal seek in microsoft excel to determine the number of cookies needed to break even, become a millionaire, and multimillionaire based on revenue, unit cost, and fixed costs, demonstrating the efficiency of the goal seek feature in calculating the breakeven point.', 'duration': 190.378, 'highlights': ['Goal Seek is used to determine the number of cookies needed to break even, become a millionaire, and multimillionaire based on revenue, unit cost, and fixed costs. Demonstrates the main purpose of using Goal Seek and the specific objectives of the calculations.', 'Using Goal Seek is more efficient than manually entering different numbers to find the breakeven point. Emphasizes the efficiency of Goal Seek in automating the process of finding the breakeven point, saving time and effort.', 'The revenue is $5 for each cookie sold, unit cost is $2.50, and fixed costs are $10,000. Provides key quantifiable data used in the calculations, including revenue, unit cost, and fixed costs.']}], 'duration': 268.436, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE71906.jpg', 'highlights': ['The chapter emphasizes hands-on learning by providing a sample sheet for viewers to follow along, enhancing retention and understanding.', 'Using Goal Seek is more efficient than manually entering different numbers to find the breakeven point. Emphasizes the efficiency of Goal Seek in automating the process of finding the breakeven point, saving time and effort.', 'Goal Seek is used to determine the number of cookies needed to break even, become a millionaire, and multimillionaire based on revenue, unit cost, and fixed costs. Demonstrates the main purpose of using Goal Seek and the specific objectives of the calculations.', 'The revenue is $5 for each cookie sold, unit cost is $2.50, and fixed costs are $10,000. Provides key quantifiable data used in the calculations, including revenue, unit cost, and fixed costs.', 'It suggests positive memory recall of the algebra class, creating a relatable and positive learning experience.', 'The chapter includes subliminal messaging for the Kevin Cookie Company, subtly promoting the product to the viewers.']}, {'end': 747.286, 'segs': [{'end': 389.861, 'src': 'embed', 'start': 361.19, 'weight': 0, 'content': [{'end': 363.973, 'text': "Well, the cell that we're going to change is how many cookies are sold.", 'start': 361.19, 'duration': 2.783}, {'end': 366.857, 'text': "So I'll click here and then let me go over and select cookies sold.", 'start': 364.174, 'duration': 2.683}, {'end': 369.861, 'text': 'So right now I have all of these defined.', 'start': 367.498, 'duration': 2.363}, {'end': 370.782, 'text': "Let's now click on OK.", 'start': 370.001, 'duration': 0.781}, {'end': 377.856, 'text': "And here Goal Seek now has gone through and it's calculated the optimum value.", 'start': 373.194, 'duration': 4.662}, {'end': 383.038, 'text': 'So here if I sell 4,000 cookies, my total profit will be zero.', 'start': 378.296, 'duration': 4.742}, {'end': 384.419, 'text': "That's a lot of work.", 'start': 383.458, 'duration': 0.961}, {'end': 389.861, 'text': "I have to make 4,000 cookies and I don't even make profit? Wow, I don't know if this is the best business.", 'start': 384.479, 'duration': 5.382}], 'summary': 'Using goal seek, the optimum value is 4,000 cookies for zero profit.', 'duration': 28.671, 'max_score': 361.19, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE361190.jpg'}, {'end': 471.316, 'src': 'embed', 'start': 442.709, 'weight': 1, 'content': [{'end': 447.774, 'text': "It'll go through now and calculate how many cookies I need to sell to make a million dollars of profit.", 'start': 442.709, 'duration': 5.065}, {'end': 449.876, 'text': 'And here it looks like I need to make 404,000 cookies.', 'start': 448.154, 'duration': 1.722}, {'end': 450.796, 'text': 'That is a lot of cookies.', 'start': 449.896, 'duration': 0.9}, {'end': 457.022, 'text': "But if that's what it's going to take, I'm up for the task.", 'start': 454.54, 'duration': 2.482}, {'end': 461.847, 'text': "And lastly, we can also calculate what it'll take to become a cookie multimillionaire.", 'start': 457.463, 'duration': 4.384}, {'end': 465.21, 'text': "Once again, let's go up to what if analysis, click into goal seek.", 'start': 462.247, 'duration': 2.963}, {'end': 471.316, 'text': "And just like we did before here, I'll set this cell down here and a multimillionaire is at least 2 million.", 'start': 465.691, 'duration': 5.625}], 'summary': 'To make a million dollars of profit, 404,000 cookies need to be sold; becoming a multimillionaire requires at least 2 million cookies.', 'duration': 28.607, 'max_score': 442.709, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE442709.jpg'}, {'end': 755.75, 'src': 'embed', 'start': 728.279, 'weight': 2, 'content': [{'end': 731.68, 'text': 'I was able to use Solver to do exactly what I did with Goal Seek.', 'start': 728.279, 'duration': 3.401}, {'end': 739.276, 'text': "Now, of course, why use something more complex when you have Goal Seek? Let's see a true example where Solver really comes in handy.", 'start': 732.608, 'duration': 6.668}, {'end': 743.882, 'text': "And for that, let's go down to the bottom worksheet called Solver Advanced and click into this.", 'start': 739.396, 'duration': 4.486}, {'end': 747.286, 'text': 'And wow, this looks a lot more advanced than the previous example.', 'start': 744.362, 'duration': 2.924}, {'end': 755.75, 'text': "Now this might start to look a little bit scary, but when you see how Solver works, it's actually pretty beautiful how it could let you find,", 'start': 748.226, 'duration': 7.524}], 'summary': 'Using solver for complex tasks, demonstrated in solver advanced worksheet.', 'duration': 27.471, 'max_score': 728.279, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE728279.jpg'}], 'start': 340.722, 'title': 'Break even and goal seek in excel', 'summary': 'Covers setting break-even points using goal seek, finding that selling 4,000 cookies results in zero profit, and explores using goal seek in excel to achieve a million-dollar profit. it also demonstrates using solver to calculate breakeven points and provides examples of its setup and comparison to goal seek.', 'chapters': [{'end': 402.592, 'start': 340.722, 'title': 'Break even analysis and goal seek', 'summary': 'Discusses setting a break-even point by using goal seek, where it is found that selling 4,000 cookies results in zero profit, prompting the consideration of becoming a cookie millionaire.', 'duration': 61.87, 'highlights': ['Using Goal Seek to calculate the break-even point by setting the profit to zero and changing the quantity of cookies sold, resulting in the determination that selling 4,000 cookies yields zero profit.', 'Expressing disappointment at the prospect of having to sell 4,000 cookies to break even and expressing determination to become a cookie millionaire.']}, {'end': 747.286, 'start': 403.433, 'title': 'Excel what-if analysis and goal seek', 'summary': 'Illustrates the use of goal seek in excel to determine the number of cookies needed to achieve a million-dollar and multimillion-dollar profit, as well as the use of solver to calculate breakeven points, with examples of setting up solver and comparing it to goal seek.', 'duration': 343.853, 'highlights': ['The use of Goal Seek to determine the number of cookies needed to achieve a million-dollar and multimillion-dollar profit Using Goal Seek, it was determined that 404,000 cookies are needed to make a million dollars of profit and 804,000 cookies to make $2 million.', 'Setting up Solver in Excel and comparing it to Goal Seek The process of setting up Solver in Excel was explained, including turning on the Solver add-in, entering solver parameters, and utilizing its capabilities to calculate breakeven points, with a comparison to the simpler Goal Seek tool.', 'The comparison between Solver and Goal Seek in terms of complexity and capabilities The comparison highlighted that Solver, while more complex than Goal Seek, offers added richness such as the ability to handle multiple variables and set constraints, providing more comprehensive solutions.']}], 'duration': 406.564, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE340722.jpg', 'highlights': ['Using Goal Seek to calculate the break-even point by setting the profit to zero and changing the quantity of cookies sold, resulting in the determination that selling 4,000 cookies yields zero profit.', 'The use of Goal Seek to determine the number of cookies needed to achieve a million-dollar and multimillion-dollar profit Using Goal Seek, it was determined that 404,000 cookies are needed to make a million dollars of profit and 804,000 cookies to make $2 million.', 'Setting up Solver in Excel and comparing it to Goal Seek The process of setting up Solver in Excel was explained, including turning on the Solver add-in, entering solver parameters, and utilizing its capabilities to calculate breakeven points, with a comparison to the simpler Goal Seek tool.']}, {'end': 916.629, 'segs': [{'end': 772.901, 'src': 'embed', 'start': 748.226, 'weight': 0, 'content': [{'end': 755.75, 'text': "Now this might start to look a little bit scary, but when you see how Solver works, it's actually pretty beautiful how it could let you find,", 'start': 748.226, 'duration': 7.524}, {'end': 757.632, 'text': 'or help you get to the optimal answer.', 'start': 755.75, 'duration': 1.882}, {'end': 761.434, 'text': 'So right here, I have another business question that I want to answer.', 'start': 758.292, 'duration': 3.142}, {'end': 766.817, 'text': 'I want to know what types of cookies should I bake to maximize my profit?', 'start': 761.874, 'duration': 4.943}, {'end': 772.901, 'text': 'Like I said in the intro, at The Kevin Cookie Company we have lots of different cookie types, all listed right here.', 'start': 767.417, 'duration': 5.484}], 'summary': 'Using solver to maximize profit by determining the optimal types of cookies to bake at the kevin cookie company.', 'duration': 24.675, 'max_score': 748.226, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE748226.jpg'}, {'end': 821.508, 'src': 'embed', 'start': 790.112, 'weight': 1, 'content': [{'end': 792.634, 'text': 'But along with that, I also have some constraints.', 'start': 790.112, 'duration': 2.522}, {'end': 794.555, 'text': 'I have the oven capacity constraint.', 'start': 792.734, 'duration': 1.821}, {'end': 796.897, 'text': 'I also have demand constraints too.', 'start': 795.015, 'duration': 1.882}, {'end': 802.119, 'text': "I can't just make all chocolate chip cookies because people only want so many of them.", 'start': 797.857, 'duration': 4.262}, {'end': 806.961, 'text': 'Also over here I can see how much profit I make per cookie.', 'start': 802.879, 'duration': 4.082}, {'end': 809.482, 'text': 'So some cookies are more profitable than others.', 'start': 807.021, 'duration': 2.461}, {'end': 814.104, 'text': 'In fact, chocolate chip cookies are a lot more profitable than fortune cookies.', 'start': 809.903, 'duration': 4.201}, {'end': 815.645, 'text': "They just don't have the same margins.", 'start': 814.144, 'duration': 1.501}, {'end': 821.508, 'text': 'And over here I can see the total profit per cookie type and also the total profit overall.', 'start': 816.165, 'duration': 5.343}], 'summary': 'Constraints include oven capacity and demand; chocolate chip cookies are more profitable.', 'duration': 31.396, 'max_score': 790.112, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE790112.jpg'}], 'start': 748.226, 'title': 'Optimizing cookie baking for profit', 'summary': 'Provides techniques to use solver to maximize profit by determining the optimal combination of cookies to bake, considering oven capacity, demand constraints, and varying profit margins for different cookie types.', 'chapters': [{'end': 916.629, 'start': 748.226, 'title': 'Optimizing cookie baking for profit', 'summary': 'Explains how to use solver to determine the optimal combination of cookies to bake in order to maximize profit, considering oven capacity, demand constraints, and varying profit margins for different cookie types.', 'duration': 168.403, 'highlights': ['Using Solver to determine the optimal combination of cookies to bake in order to maximize profit. The chapter emphasizes the use of Solver to calculate the best combination of cookies to bake, considering constraints such as oven capacity, demand limits, and profit margins.', 'Importance of considering both profit margins and demand constraints for different cookie types. The chapter highlights the need to balance profit margins with demand constraints when deciding how many of each cookie type to bake.', 'Illustrating the process of manually determining the optimal cookie combination before using Solver. The chapter explains the manual process of determining the optimal cookie combination based on profitability and demand before introducing the use of Solver for a more efficient solution.']}], 'duration': 168.403, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE748226.jpg', 'highlights': ['Using Solver to determine the optimal combination of cookies to maximize profit, considering constraints.', 'Balancing profit margins with demand constraints when deciding how many of each cookie type to bake.', 'Explaining the manual process of determining the optimal cookie combination before using Solver.']}, {'end': 1162.971, 'segs': [{'end': 958.089, 'src': 'embed', 'start': 917.109, 'weight': 0, 'content': [{'end': 920.97, 'text': "But let's say I had a lot more cookies or maybe the example had more constraints.", 'start': 917.109, 'duration': 3.861}, {'end': 924.091, 'text': 'It would become very difficult to do this manually.', 'start': 921.53, 'duration': 2.561}, {'end': 926.832, 'text': "So let's go to solver and see how it can help us.", 'start': 924.171, 'duration': 2.661}, {'end': 933.335, 'text': "Once again to access Solver, let's go up to the top tabs, click into data and all the way over.", 'start': 927.632, 'duration': 5.703}, {'end': 935.836, 'text': "on the right hand side of the ribbon, let's click on Solver.", 'start': 933.335, 'duration': 2.501}, {'end': 942.139, 'text': "This opens up Solver and now we're going to use more of the capabilities compared to the previous example.", 'start': 936.456, 'duration': 5.683}, {'end': 947.562, 'text': 'Here we can set the objective and I want to make as much profit as possible.', 'start': 942.62, 'duration': 4.942}, {'end': 949.103, 'text': "I'm a true capitalist here.", 'start': 947.682, 'duration': 1.421}, {'end': 955.046, 'text': "So let's move this over to the left hand side and for the objective, it's going to be this total profit sell.", 'start': 949.743, 'duration': 5.303}, {'end': 958.089, 'text': "Here, I'll click on this and then let me select this cell.", 'start': 955.666, 'duration': 2.423}], 'summary': 'Using solver to maximize profit by setting the objective as total profit sell.', 'duration': 40.98, 'max_score': 917.109, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE917109.jpg'}, {'end': 1043.04, 'src': 'heatmap', 'start': 977.29, 'weight': 4, 'content': [{'end': 984.553, 'text': 'So before we always changed one cell, but once again, one of the values of Solver is that I can change multiple variables.', 'start': 977.29, 'duration': 7.263}, {'end': 986.433, 'text': "So here I'll click over here.", 'start': 985.253, 'duration': 1.18}, {'end': 989.935, 'text': 'I want to figure out the combination of cookies that I should bake.', 'start': 986.513, 'duration': 3.422}, {'end': 993.936, 'text': "So here I'll select all of these different cells and then let's click on OK.", 'start': 990.015, 'duration': 3.921}, {'end': 996.397, 'text': 'Next, I want to add some constraints.', 'start': 994.336, 'duration': 2.061}, {'end': 1002.018, 'text': "Right here for the quantity that I bake, I don't want to bake any fractional cookies.", 'start': 997.097, 'duration': 4.921}, {'end': 1004.179, 'text': 'I need them all to be integers.', 'start': 1002.218, 'duration': 1.961}, {'end': 1006.319, 'text': "So that's going to be my first constraint.", 'start': 1004.539, 'duration': 1.78}, {'end': 1008.7, 'text': "Let's go over here and click on add.", 'start': 1006.82, 'duration': 1.88}, {'end': 1012.541, 'text': 'This opens up a prompt where I can add my first constraint.', 'start': 1009.08, 'duration': 3.461}, {'end': 1014.962, 'text': "And once again, I don't want any fractional cookies.", 'start': 1012.721, 'duration': 2.241}, {'end': 1017.602, 'text': "So I'm going to select all of these values here.", 'start': 1015.322, 'duration': 2.28}, {'end': 1019.483, 'text': "This is going to be how many cookies I'm baking.", 'start': 1017.682, 'duration': 1.801}, {'end': 1023.044, 'text': 'And right here in the dropdown list, I can select integer.', 'start': 1019.923, 'duration': 3.121}, {'end': 1026.346, 'text': 'My constraint is all of these have to be an integer.', 'start': 1023.604, 'duration': 2.742}, {'end': 1028.667, 'text': "Once again, they're not going to be fractional cookies.", 'start': 1026.527, 'duration': 2.14}, {'end': 1030.829, 'text': "Once I'm done, I'll click on add.", 'start': 1029.289, 'duration': 1.54}, {'end': 1036.374, 'text': "Next, I want to make sure that the cookies that I bake don't exceed my oven capacity.", 'start': 1031.23, 'duration': 5.144}, {'end': 1043.04, 'text': 'Right down here, I have a cell called total baked, and this is simply a sum of all of the cookies that I make.', 'start': 1037.055, 'duration': 5.985}], 'summary': 'Using solver, multiple variables can be changed to find optimal cookie combination, ensuring integer quantities and not exceeding oven capacity.', 'duration': 65.75, 'max_score': 977.29, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE977290.jpg'}, {'end': 1046.643, 'src': 'embed', 'start': 1017.682, 'weight': 1, 'content': [{'end': 1019.483, 'text': "This is going to be how many cookies I'm baking.", 'start': 1017.682, 'duration': 1.801}, {'end': 1023.044, 'text': 'And right here in the dropdown list, I can select integer.', 'start': 1019.923, 'duration': 3.121}, {'end': 1026.346, 'text': 'My constraint is all of these have to be an integer.', 'start': 1023.604, 'duration': 2.742}, {'end': 1028.667, 'text': "Once again, they're not going to be fractional cookies.", 'start': 1026.527, 'duration': 2.14}, {'end': 1030.829, 'text': "Once I'm done, I'll click on add.", 'start': 1029.289, 'duration': 1.54}, {'end': 1036.374, 'text': "Next, I want to make sure that the cookies that I bake don't exceed my oven capacity.", 'start': 1031.23, 'duration': 5.144}, {'end': 1043.04, 'text': 'Right down here, I have a cell called total baked, and this is simply a sum of all of the cookies that I make.', 'start': 1037.055, 'duration': 5.985}, {'end': 1046.643, 'text': 'I need to make sure that this does not exceed 15,000.', 'start': 1043.52, 'duration': 3.123}], 'summary': 'Baking integer cookies, ensuring total does not exceed 15,000.', 'duration': 28.961, 'max_score': 1017.682, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE1017682.jpg'}, {'end': 1086.935, 'src': 'heatmap', 'start': 1058.515, 'weight': 0.748, 'content': [{'end': 1062.62, 'text': "Then I'm going to select the equal sign and my constraint right here is going to be 15,000.", 'start': 1058.515, 'duration': 4.105}, {'end': 1063.561, 'text': "Next, let's click on add.", 'start': 1062.62, 'duration': 0.941}, {'end': 1068.946, 'text': 'And lastly, I want to add one more constraint.', 'start': 1066.164, 'duration': 2.782}, {'end': 1070.406, 'text': 'I want to make sure that,', 'start': 1069.386, 'duration': 1.02}, {'end': 1078.171, 'text': "however many cookies I decide to bake for each one of these cookie types doesn't exceed the overall demand for that cookie type.", 'start': 1070.406, 'duration': 7.765}, {'end': 1083.113, 'text': "So here I'm going to select all of these different cookie quantities that I'm going to bake,", 'start': 1078.771, 'duration': 4.342}, {'end': 1086.935, 'text': 'and I need it to be less than or equal to the total demand.', 'start': 1083.113, 'duration': 3.822}], 'summary': 'Setting constraints for baking cookies: 15,000 limit, ensuring demand is not exceeded.', 'duration': 28.42, 'max_score': 1058.515, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE1058515.jpg'}, {'end': 1148.468, 'src': 'embed', 'start': 1121.858, 'weight': 5, 'content': [{'end': 1126.82, 'text': "bake any negative quantities of cookies, unless, of course, maybe I'm starting up a weight loss program.", 'start': 1121.858, 'duration': 4.962}, {'end': 1132.022, 'text': "that might make sense, but for now I only want to make whole cookies, so let's leave that box checked.", 'start': 1126.82, 'duration': 5.202}, {'end': 1133.842, 'text': 'And down below.', 'start': 1132.762, 'duration': 1.08}, {'end': 1143.346, 'text': 'we have a few different methods for solving this and when I click on this drop down, we have GRG, nonlinear, simplex, LP and evolutionary.', 'start': 1133.842, 'duration': 9.504}, {'end': 1148.468, 'text': 'What are these and which one should you choose? Let me show a quick example of what these mean.', 'start': 1143.826, 'duration': 4.642}], 'summary': 'Choose grg, nonlinear, simplex, lp, or evolutionary methods for solving equations in excel.', 'duration': 26.61, 'max_score': 1121.858, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE1121858.jpg'}], 'start': 917.109, 'title': 'Using solver for profit maximization and baking optimization', 'summary': 'Covers using the solver tool to maximize profit by setting the objective as total profit sell and selecting cells to change, and also explains how to optimize the combination of cookies to bake, adding constraints for integer quantity, maximum oven capacity, and demand.', 'chapters': [{'end': 977.15, 'start': 917.109, 'title': 'Solver tool for maximizing profit', 'summary': 'Discusses using the solver tool to maximize profit by setting the objective as total profit sell and selecting cells to change, ultimately aiming to find variables leading to the maximum profit.', 'duration': 60.041, 'highlights': ['Using the Solver tool to set the objective as total profit sell and selecting cells to change in order to maximize profit.', 'Accessing the Solver tool by clicking into data and selecting it from the top tabs.', 'Exploring the capabilities of Solver to make decisions based on objectives, such as maximizing profit.']}, {'end': 1162.971, 'start': 977.29, 'title': 'Solver for baking cookies', 'summary': 'Explains how to use solver to optimize the combination of cookies to bake, adding constraints for integer quantity, maximum oven capacity, and demand, and choosing the appropriate solving method.', 'duration': 185.681, 'highlights': ['Using Solver to optimize the combination of cookies to bake Explains the primary purpose of using Solver for baking cookies', 'Adding constraints for integer quantity, maximum oven capacity, and demand Describes the process of adding constraints to ensure integer quantity, limit oven capacity, and meet demand', 'Choosing the appropriate solving method from options like GRG, nonlinear, simplex, LP, and evolutionary Discusses the different solving methods available and the significance of choosing the appropriate one']}], 'duration': 245.862, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE917109.jpg', 'highlights': ['Using the Solver tool to maximize profit by setting the objective as total profit sell and selecting cells to change', 'Adding constraints for integer quantity, maximum oven capacity, and demand', 'Exploring the capabilities of Solver to make decisions based on objectives, such as maximizing profit', 'Accessing the Solver tool by clicking into data and selecting it from the top tabs', 'Using Solver to optimize the combination of cookies to bake', 'Choosing the appropriate solving method from options like GRG, nonlinear, simplex, LP, and evolutionary']}, {'end': 1399.518, 'segs': [{'end': 1207.685, 'src': 'embed', 'start': 1180.096, 'weight': 0, 'content': [{'end': 1186.759, 'text': "As soon as it finds something that seems like a high point and it starts going down again, it'll select this as the best option.", 'start': 1180.096, 'duration': 6.663}, {'end': 1195.882, 'text': "However, as you go a little farther along, it might find another value that's even higher, and this is where you can use the evolutionary model.", 'start': 1187.159, 'duration': 8.723}, {'end': 1200.403, 'text': "It'll find the what they call the global maximum or the global min.", 'start': 1196.242, 'duration': 4.161}, {'end': 1207.685, 'text': 'However, the one downside with this approach is it also takes a lot more compute power and it takes a lot longer,', 'start': 1200.823, 'duration': 6.862}], 'summary': 'Algorithm selects high points, uses evolutionary model, but requires more compute power and time.', 'duration': 27.589, 'max_score': 1180.096, 'thumbnail': ''}, {'end': 1247.99, 'src': 'embed', 'start': 1222.068, 'weight': 2, 'content': [{'end': 1226.409, 'text': 'this is the best and quickest one to select to find the optimum value.', 'start': 1222.068, 'duration': 4.341}, {'end': 1231.013, 'text': 'So, now that we went back to school for a moment, which one should you actually choose?', 'start': 1226.869, 'duration': 4.144}, {'end': 1236.378, 'text': "Well, there's a reason that Microsoft Excel makes GRG nonlinear the default.", 'start': 1231.033, 'duration': 5.345}, {'end': 1241.603, 'text': "This one will not only solve nonlinear models, but it'll also solve linear models.", 'start': 1236.799, 'duration': 4.804}, {'end': 1245.327, 'text': "So even if you have this selected, it'll still solve for linear.", 'start': 1241.984, 'duration': 3.343}, {'end': 1247.99, 'text': "So it'll do what the simplex LP does.", 'start': 1245.407, 'duration': 2.583}], 'summary': "Microsoft excel's default grg nonlinear solver is the best and quickest for solving both linear and nonlinear models.", 'duration': 25.922, 'max_score': 1222.068, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE1222068.jpg'}, {'end': 1299.412, 'src': 'embed', 'start': 1267.722, 'weight': 3, 'content': [{'end': 1269.763, 'text': 'And yes, you have a lot more options.', 'start': 1267.722, 'duration': 2.041}, {'end': 1273.764, 'text': 'If you thought the previous screen was intimidating, this one is even more so.', 'start': 1269.943, 'duration': 3.821}, {'end': 1275.924, 'text': 'Here you have tons of different options.', 'start': 1274.124, 'duration': 1.8}, {'end': 1281.546, 'text': 'But here if we click into GRG nonlinear, you have the option to use multi-start.', 'start': 1276.344, 'duration': 5.202}, {'end': 1287.347, 'text': "So this way it'll search at multiple points, and so if there are, let's say, two humps or three humps,", 'start': 1281.966, 'duration': 5.381}, {'end': 1290.588, 'text': "chances are it'll find that optimum point that you're looking for.", 'start': 1287.347, 'duration': 3.241}, {'end': 1293.169, 'text': "So here I'll just set it to use multi-start.", 'start': 1290.948, 'duration': 2.221}, {'end': 1299.412, 'text': 'Also here for the evolutionary model, you can also configure this as well to maybe try to speed it up a little bit.', 'start': 1293.809, 'duration': 5.603}], 'summary': 'Options for grg nonlinear include multi-start for better optimization.', 'duration': 31.69, 'max_score': 1267.722, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE1267722.jpg'}, {'end': 1386.731, 'src': 'embed', 'start': 1363.117, 'weight': 4, 'content': [{'end': 1371.103, 'text': "but unfortunately they just don't make as much money and with limited oven capacity I need to focus on these to give me the highest amount of profit.", 'start': 1363.117, 'duration': 7.986}, {'end': 1376.726, 'text': "All right, well that's how you can use both Goal Seek and Solver in Microsoft Excel.", 'start': 1371.483, 'duration': 5.243}, {'end': 1381.588, 'text': 'If you were able to solve some of these cookie problems, please give this video a thumbs up.', 'start': 1377.106, 'duration': 4.482}, {'end': 1386.731, 'text': 'To see more videos like this in the future, make sure to hit that subscribe button.', 'start': 1382.129, 'duration': 4.602}], 'summary': 'Optimizing oven capacity for highest profit using goal seek and solver in excel.', 'duration': 23.614, 'max_score': 1363.117, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/UD9e-gQCQsE/pics/UD9e-gQCQsE1363117.jpg'}], 'start': 1162.971, 'title': 'Optimization techniques in evolutionary and excel models', 'summary': 'Covers the application of evolutionary models to find global extremum, emphasizing potential for better results. it also explains the use of goal seek and solver in excel, recommending grg nonlinear for both linear and nonlinear models with the option for multi-start for efficiency.', 'chapters': [{'end': 1200.403, 'start': 1162.971, 'title': 'Finding global extremum in evolutionary model', 'summary': 'Discusses the limitations of finding local extremum and introduces the concept of using the evolutionary model to find global extremum, which can potentially lead to better results and higher values.', 'duration': 37.432, 'highlights': ['The evolutionary model is used to find the global maximum or the global minimum, potentially leading to better results and higher values.', 'The downside of finding local extremum is that as it finds a point that seems like a high point and starts going down again, it will select this as the best option, potentially missing even higher values.']}, {'end': 1399.518, 'start': 1200.823, 'title': 'Using goal seek and solver in excel', 'summary': 'Explains how to use goal seek and solver in microsoft excel to solve optimization problems, recommending the use of grg nonlinear as the default option for solver due to its ability to solve both linear and nonlinear models, with the option to use multi-start for improved efficiency.', 'duration': 198.695, 'highlights': ["GRG nonlinear is recommended as the default option for Solver due to its ability to solve both linear and nonlinear models, providing a balance between efficiency and accuracy. GRG nonlinear is the default option in Microsoft Excel's Solver as it can solve both linear and nonlinear models, offering a balance between efficiency and accuracy.", "The use of multi-start option within GRG nonlinear can improve efficiency in finding the optimum point in cases with multiple humps, enhancing Solver's capability. The multi-start option within GRG nonlinear enables Solver to search at multiple points, enhancing its capability to find the optimum point, particularly in cases with multiple humps.", 'The chapter demonstrates the use of Goal Seek and Solver in Excel to determine the quantity to bake, optimizing the baking process for maximum profit. The chapter demonstrates using Goal Seek and Solver in Excel to optimize the baking process, determining the quantity to bake for maximum profit.']}], 'duration': 236.547, 'thumbnail': '', 'highlights': ['The evolutionary model is used to find the global maximum or the global minimum, potentially leading to better results and higher values.', 'The downside of finding local extremum is that as it finds a point that seems like a high point and starts going down again, it will select this as the best option, potentially missing even higher values.', 'GRG nonlinear is recommended as the default option for Solver due to its ability to solve both linear and nonlinear models, providing a balance between efficiency and accuracy.', "The use of multi-start option within GRG nonlinear can improve efficiency in finding the optimum point in cases with multiple humps, enhancing Solver's capability.", 'The chapter demonstrates using Goal Seek and Solver in Excel to optimize the baking process, determining the quantity to bake for maximum profit.']}], 'highlights': ['Solver can solve for multiple variables and add constraints, allowing for resource optimization.', 'Using Solver to determine the optimal combination of cookies to maximize profit, considering constraints.', 'Using the Solver tool to maximize profit by setting the objective as total profit sell and selecting cells to change', 'The evolutionary model is used to find the global maximum or the global minimum, potentially leading to better results and higher values.', 'The chapter demonstrates using Goal Seek and Solver in Excel to optimize the baking process, determining the quantity to bake for maximum profit.', 'Using Goal Seek is more efficient than manually entering different numbers to find the breakeven point. Emphasizes the efficiency of Goal Seek in automating the process of finding the breakeven point, saving time and effort.', 'The revenue is $5 for each cookie sold, unit cost is $2.50, and fixed costs are $10,000. Provides key quantifiable data used in the calculations, including revenue, unit cost, and fixed costs.', 'The use of Goal Seek to determine the number of cookies needed to achieve a million-dollar and multimillion-dollar profit Using Goal Seek, it was determined that 404,000 cookies are needed to make a million dollars of profit and 804,000 cookies to make $2 million.', 'The chapter introduces the concept of using Goal Seek and Solver in Microsoft Excel for data analysis.', 'The chapter emphasizes hands-on learning by providing a sample sheet for viewers to follow along, enhancing retention and understanding.', 'Using Goal Seek to calculate the break-even point by setting the profit to zero and changing the quantity of cookies sold, resulting in the determination that selling 4,000 cookies yields zero profit.', 'Setting up Solver in Excel and comparing it to Goal Seek The process of setting up Solver in Excel was explained, including turning on the Solver add-in, entering solver parameters, and utilizing its capabilities to calculate breakeven points, with a comparison to the simpler Goal Seek tool.', 'Adding constraints for integer quantity, maximum oven capacity, and demand', 'Accessing the Solver tool by clicking into data and selecting it from the top tabs', 'Choosing the appropriate solving method from options like GRG, nonlinear, simplex, LP, and evolutionary', 'The downside of finding local extremum is that as it finds a point that seems like a high point and starts going down again, it will select this as the best option, potentially missing even higher values.', 'GRG nonlinear is recommended as the default option for Solver due to its ability to solve both linear and nonlinear models, providing a balance between efficiency and accuracy.', "The use of multi-start option within GRG nonlinear can improve efficiency in finding the optimum point in cases with multiple humps, enhancing Solver's capability."]}