Create objective and control formulas

Keyboard Shortcuts

Now that you have organized your worksheet and added the data used in your analysis, you can define the summary formulas that will calculate the output of a given portfolio’s overall beta and expected return.

- [Instructor] Now that you have organized your worksheet,…and added the data used in the analysis,…you can define the summary formulas…that will calculate the output of a given portfolio's…overall beta and expected return.…In this movie I will use the investments_03 workbook.…You can find that sample file in the chapter two folder…of the exercise files collection.…For a quick review of what we've done,…I have a set of eight funds labeled very creatively…Fund1 through Fund8.…

Each of them has a beta.…Which is the amount that each particular fund moves…relative to the market.…For Fund1 with a beta of 1.66,…if the market went up by 10 points,…Fund1 would go up by 16.6.…The bad news of course is that if the market went down…by 10 points…then that same fund would go down by 16.6 points.…The average return over the years has been 6.62%.…

We see similar data for funds two through eight.…The target beta,…which will be the blended average of the investments…that we split between these eight funds is 1.3.…That's in cell C12.…

Resume Transcript Auto-Scroll

Author

Released

5/17/2017

Solver—a Microsoft Office Excel add-in—can help you analyze your data more efficiently. In this course, learn how to leverage Solver to find optimal solutions to problems with multiple constraints. Curt Frye explains how to install Solver, organize worksheets to make the data and summary operations clear, and find a solution using Solver. He also takes you through how to optimize investment portfolios by setting rules that limit the risk you're willing to take on, as well as how to change a model's parameters to analyze various scenarios. Plus, Curt shares how to define decision trees, represent a tree in an Excel table, and calculate the probability of reaching a node in your decision tree.

LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.