Excel: Using Solver for Decision Analysis
1h 53mIntermediate2022-11-10
Authors
Curt Frye
President of Technology and Society, Incorporated
Course details
You can use Excel's Solver function to find optimal solutions to a wide range of problems, from simple to complex. In this course, Excel power user Curt Frye shows you how, with a wide range of potential challenges. Curt explains the basics of working with Solver and gives you a chance to create a Solver model. He steps through how to tune investment portfolios, then goes over the specific approach that you need to use Solver for resource placement analysis. Plus, Curt covers the process of decision tree analysis in Excel.
Skills covered
Decision-MakingSpreadsheetsMicrosoft ExcelProfessional DevelopmentLeadership and ManagementBusiness Software and ToolsMicrosoftDeep Dive (X:Y)
Concepts
Introduction
- Optimize your analysis in Excel
- What you should know before starting
Working with Solver
- Find target values using Goal Seek
- Introduce linear, nonlinear, and evolutionary programming
- Install the Solver add-in on Windows
- Organize a worksheet for use in Solver
- Find a solution using Solver
- Challenge - Create a Solver model
- Solution - Create a Solver model
Tuning Investment Portfolios
- Introduce the problem
- Organize the worksheet
- Create objective and control formulas
- Create and run the Solver model
- Experiment with different constraints
- Challenge - Tune an investment portfolio
- Solution - Tune an investment portfolio
Optimizing Resource Placement
- Introduce the problem
- Organize the worksheet
- Map store and depot locations
- Create objective and control formulas
- Create and run the Solver model
- Extend the model by adding population density
- Challenge - Optimize resource placement
- Solution - Optimize resource placement
Defining Decision Trees
- Introduce the problem
- Organize the worksheet
- Calculate the probability of reaching a node
- Calculate the expected value for the tree
- Challenge - Define a decision tree
- Solution - Define a decision tree
Conclusion
- Additional resources