EMFPS: How Can We Get the Power Set of a Set by Using of Excel? (The Case Analysis of Big Data)

Following to my previous article posted on link: http://emfps.blogspot.com/2012/08/emfps-how-can-we-get-power-set-of-set.html, let me tell you one of the applications of this method in Risk Management.  In this article, I am willing to solve a case in which the template is referred to my article of “EMFPS: Efficient Portfolio of Assets (The Optimization for Risk, Return and Probability)” posted on link: 

http://emfps.blogspot.com/2011/10/emfps-efficient-portfolio-of-assets.html.

The Case: To Find Minimum Risk for Portfolio of Assets in Variety of the Outcomes

If we have had a range of probability for our outcomes where there is not the same probability assigned to the outcomes, how can we calculate minimum risk for the portfolio of assets? What are the probability distributions assigned to the expected portfolio return in which we will have minimum risk?

Let me start the solution of this case by using of the example stated in article of “EMFPS: Efficient Portfolio of Assets (The Optimization for Risk, Return and Probability)” posted on link: 
http://emfps.blogspot.com/2011/10/emfps-efficient-portfolio-of-assets.html as follows:

Referring to step (3), I define the range of probability for six outcomes equal to 10% to 21%. 

10% ≤ Pr ≤ 21%

The solution is step by step below cited:

Ø  On the spreadsheet of excel, we should type:

A1 = p

A2 = 6

A3 = 0.1, A4, = 0.11, A5 = 0.12, A6 = 0.13, A7 = 0.14, A8 = 0.15, A9 = 0.16, A10 = 0.17

A11 = 0.18, A12 = 0.19, A13 = 0.2, A14 = 0.21

Ø  Using from VB code written by Myrna Larson mentioned in article of  “EMFPS: How Can We Get the Power Set of a Set by Using of Excel?” posted on link: http://emfps.blogspot.com/2012/08/emfps-how-can-we-get-power-set-of-set.html

Ø  We will have 665280 cells filled with different permutations (please check it by formula = PERMUT (12,6) on excel)

               Ø  Due to each subsets is into one cell, to split the elements of subsets into other cells, we should utilize      Data Tab – Data Tools – Text to Columns

Ø  Using from formula = =IF(SUM(A2:F2)=1,A2,"") on excel to find all combinations of probabilities which are equal to 1or 100%. Copy and paste this formula for all 665280 cells.

Ø  Using from formula: Rv= SUM [Rp(i) * P(i)] to find the expected value of portfolio returns. Copy and paste this formula for all 665280 cells.

Ø  Using from formula: Qr = {SUM [(Rp(i) – Rv)^2) * P(i)]}^0.5 to find Standard Deviation of the expected portfolio returns. Copy and paste this formula for all 665280 cells.

Ø   Using from formula: CV = Qr / Rv to find Coefficient of Variation. Copy and paste this formula for all 665280 cells.

Now, we will be able to find the minimum Qr and CV for the range of 10% ≤ Pr ≤ 21% as follows:

Qr(min)	0.1993867
CV(min)	0.0144341

There are 12 combinations of the probability distributions (in the range of 10% to 21%) assigned to the outcomes which show us the minimum risk as follows:

	C1	C2	C3	C4	C5	C6
Outcome (1)	0.21	0.21	0.21	0.21	0.21	0.21
Outcome (2)	0.18	0.18	0.18	0.18	0.19	0.19
Outcome (3)	0.19	0.19	0.2	0.2	0.18	0.18
Outcome (4)	0.2	0.2	0.19	0.19	0.2	0.2
Outcome (5)	0.1	0.12	0.1	0.12	0.1	0.12
Outcome (6)	0.12	0.1	0.12	0.1	0.12	0.1

C7     C8    C9         C10      C11     C12 Outcome (1)                   0.21 0.21 0.21 0.21 0.21 0.21 Outcome (2)                0.19 0.19 0.2 0.2 0.2 0.2 Outcome (3)                    0.2 0.2 0.18 0.18 0.19 0.19 Outcome (4)                0.18 0.18 0.19 0.19 0.18 0.18 Outcome (5)                  0.1 0.12 0.1 0.12 0.1 0.12 Outcome (6)   0.12 0.1 0.12 0.1 0.12 0.1

(C1 to C12 are all 12 combinations)

What do you think about using of my previous simulation model mentioned in article of “Application of Pascal’s Triangular plus Monte Carlo Analysis to Find the Least Squares Fitting for a Limited Area” posted on link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_23.html?

I have already calculated the minimum risk by using of this simulation model. The final results are as follows:

CV(min)	0.014946785
Qr(min)	0.206693106

As you can see, the error is approximately equal to 3.6%.

How can we decrease the error by using of this simulation model?

Definitely we will be able to decrease the error by increasing the calculation iteration.

Note:  “All spreadsheets and calculation notes are available. The people, who are interested in having my spreadsheets of this method as a template for further practice, do not hesitate to ask me by sending an email to: soleimani_gh@hotmail.com or call me on my cellphone: +989109250225. Please be informed these spreadsheets are not free of charge.”