Finance modelling
Project description
1. Briefly explain the concepts of expected return, standard deviation, and correlation (in the context of share prices), and discuss their importance in portfolio management. (Issues you should consider here are the relationship between risk and return, and how portfolio risk can be reduced through diversification)<br
2. Create a spreadsheet model to find efficient portfolios of shares, and derive the efficient frontier. Give a full explanation of your modelling procedures and the results that you obtain.
3. Introduce a risk-free asset into the model and find the optimal portfolio of shares. Discuss what happens to this portfolio as the risk-free rate of return changes.
4. Introduce a risk aversion factor into the model and discuss its effect upon the optimal complete portfolio.
5. Discuss the limitations of this approach to portfolio selection.
As well as a spreadsheet file, you need to submit a report (in Word) explaining all five parts of the answer. Credit will be given for demonstrating that you have understood the procedures and the results.
Main results for simulation outputs:
1.NPV
Suggests that the investment is likely to have a negative NPV (74.8% of the simulated NPV’s were negative).
The expected (mean) NPV is negative at around -£38 thousand, suggesting that the investment is not worthwhile.
2. Number of competitors in the market after 5 years:
It is likely that there will be at least 3 competitors in the market after 5 years. The chances of there being 2 or less are around 20%.
(This graph also validates that the model is producing ‘sensible’ results in that the minimum is zero and the maximum is 5.)
Example of results for simulation inputs:
1. Market size in Year 1
The sampled values (histogram) are very close to the theoretical distribution (solid line)
This can also be shown for the other inputs e.g. market growth each year. number of competitors entering market each year.
Simulation Sensitivities
Performed By: Richard
Date: 20 April 2009 16:50:05
Rank For B51 Cell Name Sheet1!B51
NET PRESENT VALUE
Correlation Coeff. Sheet1!B53
INTERNAL RATE OF RETURN
Correlation Coeff.
#1 B6 Market size (year 1) 0.698 0.687
#2 C29 Number of competitors entering market this year / 1 -0.464 -0.463
#3 D29 Number of competitors entering market this year / 2 -0.207 -0.214
#4 E29 Number of competitors entering market this year / 3 -0.05 -0.057
#5 B8 Market growth (second year) 0.212 0.216
#6 F29 Number of competitors entering market this year / 4 0.095 0.093
#7 B9 Market growth (third year) 0.216 0.221
#8 B10 Market growth (fourth year) 0.245 0.25
#9 B11 Market growth (fifth year) 0.177 0.182
– G29 Number of competitors entering market this year / 5 n/a n/a
This table shows that the input which correlates most strongly with NPV is the market size in Year 1 (r = 0.698), followed by the number of competitors who enter the market in Year 1 (r = -0.464). These inputs can be said to have the biggest effect upon the NPV of the investment.
Simulation Scenarios
@RISK Scenario Analysis
Performed By: Richard
Date: 20 April 2009 16:55:12
Inputs in Scenario For B51 >90% Cell Name Sheet1!B51
NET PRESENT VALUE
Actual Sheet1!B51
NET PRESENT VALUE
Actual Sheet1!B51
NET PRESENT VALUE
Actual
>75% <25% >90%
#1 B6 Market size (year 1) 66765.89 56994.52 68264.46
#2 E29 Number of competitors entering market this year / 3 – – 0
#3 D29 Number of competitors entering market this year / 2 0 – 0
#4 C29 Number of competitors entering market this year / 1 0 2 0
#5 B8 Market growth (second year) – – 0.06346761
#6 B10 Market growth (fourth year) – – 0.06198987
#7 B9 Market growth (third year) – – 0.06016324
– G29 Number of competitors entering market this year / 5 – – –
– B11 Market growth (fifth year) – – –
– F29 Number of competitors entering market this year / 4 – – –
The right-hand column depicts a scenario in which the NPV obtained is in the top 10% of the simulated results. The market size in Year 1 needs to be over 68,000 and this needs to be coupled with high market growth in years 2, 3, and 4 (at least 6% each year) and no competitors entering the market for the first 3 years.
The column third from the right is a bit less optimistic and looks at a scenario that would give an NPV in the top 25% of the results. This requires a market in year 1 of at least 66765 customers and no competitors entering the market in the first 2 years. This particular scenario has no minimum requirement for market growth.
Further areas for discussion:
1. Explanation of the modelling process and what the model is actually doing.
2. Discussion of the model limitations.
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂