Analyze the plots and explain the conclusion about comparative amount of expected volatility by each stock, which we can make visually.

INTERMEDIATE CONCLUSION

14. Create the tables for the calculation of the semi-characteristics (we estimate earnings risk) for the Net return over month R_t.

Semi-characteristics (one-sided deviations)
Sample Semi-Variance SD Rt
Sample Semi-Standard Deviation Sσ Rt
Semi-Coefficient of variation SCV Rt

For this purpose to add columns to the main tables for each stock:

Deviation of the net return from the mean	ai– the indicator of the undesirable (risky) deviations
……	…….

15. Calculate:

o deviation of the Net return over month R_t from its expected value m_R

R _t – m _R

o a_i –“ counters ”, allows to include into the calculations only negative (risky) deviations:

o semi-Variance SD _R (only undesirable deviations):

& In the numerator of the formula use a function SUMPRODUCT.

o Semi-Standard deviation: SCV_R.

o Semi- Coefficient of variation: SCV_R = (Sσ _R / m _R ) · 100%.

Define them appropriate names.

Build the charts showing the dependence of the standard deviation from the expected profit rate for two-sided and one-sided deviations. Analyze the results.

For example:

Make the conclusion, if the net return over month distribution is symmetric or not and what outcome (positive or negative) is more probable.

& To automate the calculations use a function IF() using the following algorithm:

IF SCV_R = CV_R, then net return over month distribution is symmetric

IF SCV_R > CV_R, then net return over month distribution is not symmetric and more probable positive outcome

IF SCV_R < CV_R, then net return over month distribution is not symmetric and more probable negative outcome

In the base of the obtained results of quantitative risk analysis of stocks formulate the advice for decision maker, provides for possible automatically generate the answer for making a decision on the choice of type of stocks, that guarantee the best combination of the expected value of Net return over month and degree of risk.

ADVICE FOR DECISION MAKER

& To automate the calculations use a function IF() using the following algorithm:

IF m _R1 =m _R2 and σ _R1 < σ _R2, then choose stocks of the 1^st type

IF m _R1=m _R2 and σ _R2 < σ _R1, then choose stocks of the 2^nd type

IF m _R1>m _R2 and σ _R1 < σ _Rx2, it is logical to choose stocks of the 1^st type

(comment semi-characteristics)

IF m _R2>m _R1 and σ _Rx2 < σ _R1, it is logical to choose stocks of the 2^nd type

(comment semi-characteristics)

IF m _R1>m _R2 and σ _R1 > σ _R2 and v _R1 < v _R2, it is logical to choose stocks of the 1^st type

(comment semi-characteristics)

IF m _R2>m _R1 and σ _Rx2 >σ _R1 and v _R2 < v _R1, it is logical to choose stocks of the 2^nd type

(comment semi-characteristics)

otherwise – the choice is determined by the decision maker, but it is desirable to comment on the expected results.

Дата добавления: 2015-10-26