In this workout. We are asked to complete the blank shaded cells to estimate the expected return of each asset class in a reverse optimization framework, that could be then used to apply our own views to in a black littmann model.

We are told to assume that returns are consistent with a cap end model and the risk free rate of return is 2.5% If we look at the information, we're given we have the absolute Market sizes for each asset class so we can add those up to get the overall Global Market size. And then from that we can derive the weights that we have for each asset class so we can take the market size for each asset class and divide this by the overall total Market size, which we can lock on to and then copy that down for each of the remaining for asset classes.

The sum total of his weights will give us the 100% waiting that we have in the portfolio.

When we move on to the next column, we're now looking at the beta for each asset class relative to the global market now here, we've made some estimates for four of these asset categories, but we have left one out the US Equity asset class.

We've got to be a little bit careful here because we do need the overall Global Market beta.

to be one so the beta in relation to itself is one and as a result, we do need to make sure that the individual asset class beaters do derive for us and overall beta of one.

Now the way that we can solve for this overall beta is using a sum product function.

Well, we multiply together the weights.

For each asset class has in the overall Global portfolio by that relevant pieces.

If we do this, we'll notice that we don't get through to a beta of one but we can use the goal seek function to solve for the value of the US Equity beta such that we get an overall Global Market beta of one.

Down here in cell e16. We need to use the goal seek function. So if we go to the data Tab and then what if analysis and then goal seek We now need to set cell e16 to be equal to the value of 1 by changing cell.

e 13 and then if we use the goal seek function, this will set for us the US Equity beta needing to be 1.5. So that overall we get a global market beta of one.

We can now use this data to solve for the expected return of each asset class.

The market risk premium is going to be the overall expected return of our Global Market portfolio their 5.5% minus the risk free return of 2.5% that we have and that's going to be consistent across every asset class and then we can solve for the expected return of each asset class using cap m So we take the risk-free rate of return.

And I'll look onto that and we add to that the beta fit each asset class multiplied by the market risk approvement.

Which will allow us to solve for the expected return of each asset class.

This is the process of reverse optimization.

Which identifies for us the expected return of each asset class? Which can then be used in a black Littman process to be adjusted based on our expectations relative to these expected returns derived from reverse optimization.