Powered By Blogger

Friday, August 24, 2012

How Can We Get the Power Set of a Set by Using of Excel?


The purpose of this article is to obtain all subsets of a set by using of Classical or Fuzzy set theory and a VB code written by Myrna Larson. The power set has many applications in the fields of engineering such as control valves in piping and process engineering, control keys in electrical engineering, field data analysis in geophysics (does not different among the arrangements of Wenner, Schlumberger or Polygon - Net in resistivity test or Geoelectric investigation) and data analysis of NDT tests.
And so we can find the applications of power set in financial management, strategic management and risk management. For instance, I would like to refer you 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 where I stated: “....To change Rp(i) and probabilities simultaneously into above limited range, we should obtain all Permutations without Repetition by using of VB codes in excel...”
The concept of a set is the collection of the numbers or elements where we consider capital letters for them such as A, B, C,...
For example, set A has 4 elements as follows:
A = {1,2,3,4}
If all elements of set B are included in set A, we can say that set B is a subset of set A.
If B = {2,4}, set B is a subset of set A.
The power set of set A is a set such as C which is contained all subsets of set A even empty set and set A as follows:
C = {{}, {1}, {2}, {3}, {4}, {1,2}, {1,3}, {1,4}, {2,3}, {2,4}, {3,4}, {1,2,3}, {1,2,4}, {1,3,4}, {2,3,4}, {1,2,3,4}}
As we see, the total elements of set C is equal to (2^n) where “n” is all elements of set A.
How can we get the power set of a set by using of excel?
Methodology
Assume that set A is a subset of Universe set U. In the reference with Characteristic function (membership function) in set theory which is the base of Fuzzy set theory, if each element of set A is a member of set U, we will assign 1 as membership function otherwise 0 (zero) as follows:








Where:  x is member of set U.
Example:
If we have:  A = {1,,3,7,9} and   U = {1,2,3,4,5,6,7,8,9}
By using of membership function, we can write set A as follows:
A = {(1,1), (2,0), (3,1), (4,0), (5,0), (6,0), (7,1),(8,0), (9,1)}
According to above mentioned, there is very important rule that we should consider when we want to use from excel. This note is below cited:
Rule: If we have not set U, we have to consider 1 or 0 for each element of set A.
Let me start step by step this method by using of excel:
Ø  Type “c” into cell A1 in your spreadsheet
Ø  Type into cell A2 the number of elements in your set
Ø   Regarding to above rule, for each element of your set, you should type 0 and 1 respectively into cell A3, A4, A5, A6 and so on. For example, if your set has 5 elements, you should fill cells as follows:

A1 = c
A2 = 5
A3 = 0
A4 = 1
A5 = 0
A6 = 1
A7 = 0
A8 = 1
A9 = 0
A10 = 1
A11 = 0
A12 = 1



Ø  Now, we can run macro from VB code written by Myrna Larson which is as follows:
Option Explicit

 'Written by Myrna Larson - Microsoft Excel MVP
Dim vAllItems As Variant
Dim Buffer() As String
Dim BufferPtr As Long
Dim Results As Worksheet


Sub ListPermutations()
    Dim Rng As Range
    Dim PopSize As Integer
    Dim SetSize As Integer
    Dim Which As String
    Dim N As Double
    Const BufferSize As Long = 4096
    
    
    Set Rng = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
    
    PopSize = Rng.Cells.Count - 1
    If PopSize < 1 Then Goto DataError
    
    SetSize = Rng.Cells(2).Value
    If SetSize > PopSize Then Goto DataError
    
    Which = UCaseS(Rng.Cells(1).Value)
    Select Case Which
    Case "C"
        N = Application.WorksheetFunction.Combin(PopSize, SetSize)
    Case "P"
        N = Application.WorksheetFunction.Permut(PopSize, SetSize)
    Case Else
        Goto DataError
    End Select
    If N > Cells.CountLarge Then Goto DataError 
    
    Application.ScreenUpdating = False
    
    Set Results = Worksheets.Add
    
    vAllItems = Rng.Offset(2, 0).Resize(PopSize).Value
    Redim Buffer(1 To BufferSize) As String
    BufferPtr = 0
    
    If Which = "C" Then
        AddCombination PopSize, SetSize
    Else
        AddPermutation PopSize, SetSize
    End If
    vAllItems = 0
    
    Application.ScreenUpdating = True
    Exit Sub
    
DataError:
    If N = 0 Then
        Which = "Enter your data in a vertical range of at least 4 cells. " _
        & String$(2, 10) _
        & "Top cell must contain the letter C or P, 2nd cell is the number " _
        & "of items in a subset, the cells below are the values from which " _
        & "the subset is to be chosen."
        
    Else
        Which = "This requires " & Format$(N, "#,##0") & _
        " cells, more than are available on the worksheet!"
    End If
    MsgBox Which, vbOKOnly, "DATA ERROR"
    Exit Sub
End Sub

Private Sub AddPermutation(Optional PopSize As Integer = 0, _
    Optional SetSize As Integer = 0, _
    Optional NextMember As Integer = 0)
    
    Static iPopSize As Integer
    Static iSetSize As Integer
    Static SetMembers() As Integer
    Static Used() As Integer
    Dim i As Integer
    
    If PopSize <> 0 Then
        iPopSize = PopSize
        iSetSize = SetSize
        Redim SetMembers(1 To iSetSize) As Integer
        Redim Used(1 To iPopSize) As Integer
        NextMember = 1
    End If
    
    For i = 1 To iPopSize
        If Used(i) = 0 Then
            SetMembers(NextMember) = i
            If NextMember <> iSetSize Then
                Used(i) = True
                AddPermutation , , NextMember + 1
                Used(i) = False
            Else
                SavePermutation SetMembers()
            End If
        End If
    Next i
    
    If NextMember = 1 Then
        SavePermutation SetMembers(), True
        Erase SetMembers
        Erase Used
    End If
    
End Sub 'AddPermutation

Private Sub AddCombination(Optional PopSize As Integer = 0, _
    Optional SetSize As Integer = 0, _
    Optional NextMember As Integer = 0, _
    Optional NextItem As Integer = 0)
    
    Static iPopSize As Integer
    Static iSetSize As Integer
    Static SetMembers() As Integer
    Dim i As Integer
    
    If PopSize <> 0 Then
        iPopSize = PopSize
        iSetSize = SetSize
        Redim SetMembers(1 To iSetSize) As Integer
        NextMember = 1
        NextItem = 1
    End If
    
    For i = NextItem To iPopSize
        SetMembers(NextMember) = i
        If NextMember <> iSetSize Then
            AddCombination , , NextMember + 1, i + 1
        Else
            SavePermutation SetMembers()
        End If
    Next i
    
    If NextMember = 1 Then
        SavePermutation SetMembers(), True
        Erase SetMembers
    End If
    
End Sub 'AddCombination

Private Sub SavePermutation(ItemsChosen() As Integer, _
    Optional FlushBuffer As Boolean = False)
    
    Dim i As Integer, sValue As String
    Static RowNum As Long, ColNum As Long
    
    If RowNum = 0 Then RowNum = 1
    If ColNum = 0 Then ColNum = 1
    
    If FlushBuffer = True Or BufferPtr = UBound(Buffer()) Then
        If BufferPtr > 0 Then
            If (RowNum + BufferPtr - 1) > Rows.Count Then
                RowNum = 0
                ColNum = ColNum + 1
                If ColNum > 256 Then Exit Sub
            End If
            
            Results.Cells(RowNum, ColNum).Resize(BufferPtr, 1).Value _
            = Application.WorksheetFunction.Transpose(Buffer())
            RowNum = RowNum + BufferPtr
        End If
        
        BufferPtr = 0
        If FlushBuffer = True Then
            Erase Buffer
            RowNum = 1
            ColNum = 0
            Exit Sub
        Else
            Redim Buffer(1 To UBound(Buffer))
        End If
        
    End If
    
     'construct the next set
    For i = 1 To UBound(ItemsChosen)
        sValue = sValue & ", " & vAllItems(ItemsChosen(i), 1)
    Next i
    
     'and save it in the buffer
    BufferPtr = BufferPtr + 1
    Buffer(BufferPtr) = Mid$(sValue, 3)
End Sub 'SavePermutation


Ø  Since we have some repeated cells, we should use Data Tab – Data Tools – Remove Duplicates
Ø  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
Ø  If the elements of your set are the numbers, you can multiply all subsets of 0 and 1 to numbers to return codes to numbers.
Ø  If the elements of your set are letters (no numbers), you can use from above step plus Vlookup formula in excel.
Here, I have brought an example as follows:
If we have set A = {a,b,c,d,e,f,}, the power set will generated as follows:
c
6
0
1
0
1
0
1
0
1
0
1
0
1










































As we can see, the power set of set A has 64 sets (2^6).
Each row (subset) can be considered as a scenario to analyze data.
If you want to analyze your data by using What – If – Analysis Tool in excel, Scenario analysis is limited to 32 rows. Therefore, the best tool is Solver – add.
To be continued….




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.”
  

Wednesday, July 11, 2012

Application of Pascal’s Triangular Plus Monte Carlo Analysis to Design a Strategic Plan


Following to article of “Fuzzy Delphi Method to Design a Strategic Plan” posted on link: http://emfps.blogspot.com/2012/02/fuzzy-delphi-method-to-design-strategic.html, the purpose of this article is to utilize a new simulation model to design the strategic plan instead of FDM where I had already depicted this simulation model in article of “Application of Pascal’s Triangular Plus Monte Carlo Analysis to Appraise the Wisdom of Crowds” on link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_08.html. The most important finding is to show the advantages of this simulation model to design the strategic plan in which I have compared the results with FDM.
The template of this article is the same “Fuzzy Delphi Method to Design a Strategic Plan”.

Methodology

As you remember, I started an example of Driving Forces (DF) in article of “Fuzzy Delphi Method to Design a Strategic Plan” where I chose 14 experts to respond my survey questionnaire about the ranking of driving forces issues as follows:
   
DF
   P
    M
   O
DF1
1
5.79
10
DF2
2
6.36
10
DF3
1
6.36
10
DF4
1
6.43
10
DF5
1
6.14
10
DF6
1
6.29
10
DF7
2
6.14
10
DF8
1
6.29
10
DF9
1
6.07
10
DF10
1
6.29
10
DF11
2
6.86
10
DF12
1
6.36
10
DF13
2
6.00
9
DF14
2
6.43
10


Now, I am willing to use this simulation model step by step instead of FDM as follows:
I1 I look at the minimum and maximum of the ranking responses by experts for each driving force. In this case, I do not need to repeat data collection from experts just like to Delphi Method because the experts should only answer one number. In fact, this method do not need to have Linguistic Values such as Pessimistic = P, Most likely = M, and Optimistic = O. Therefore, the accuracy of the responses will increase only by one time data collection.

I2 I assume that I have collected the data from 50, 100 and 200 experts so that I expand the numbers into a limited range of the ranking by increase of the experts where real number of the experts is 14. As the matter of fact, I can say you that it can be the similar the repetitions in Delphi Method.  
I3 I utilize the simulation model mentioned in article of “Application of Pascal’s Triangular Plus Monte Carlo Analysis to Appraise the Wisdom of Crowds” on link: 


http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_08.html.

(Please read carefully it before the continuation of this article)

Discussion and Data Analysis

Referring to the example included in article of “Fuzzy Delphi Method to Design a Strategic Plan”, you can see that there are three ranges of the ranking responded by the experts below cited:

Xmin = 1, Xmax = 10
 Ymin = 2, Ymax = 10
Zmin = 2, Zmax = 9

By using of this simulation model, the final results for 50, 100, and 200 experts will be as follows:

50 Experts:

Xmin= 1 to Xmax = 10
Average
6.107455
STDEV
0.140204999
Ave(STD)
0.14474197
CV
0.023699228
Ymin= 2 to Ymax = 10
Average
6.537685818
STDEV
0.1316774
Ave(STD)
0.132140879
CV
0.02021218
Zmin= 2 to Zmax = 9
Average
5.975219182
STDEV
0.113890746
Ave(STD)
0.11444645
CV
0.019153515

100 Experts:

Xmin= 1 to Xmax = 10
Average
6.033304225
STDEV
0.111575614
Ave(STD)
0.106823434
CV
0.017705627
Ymin= 2 to Ymax = 10
Average
6.4742536
STDEV
0.096731927
Ave(STD)
0.096762967
CV
0.01494581
Zmin= 2 to Zmax = 9
Average
5.915929675
STDEV
0.079107522
Ave(STD)
0.083170539
CV
0.014058744

200 Experts:

Xmin= 1 to Xmax = 10
Average
5.884592
STDEV
0.05611
Ave(STD)
0.053924
CV
0.009164
Ymin= 2 to Ymax = 10
Average
6.340549
STDEV
0.05075
Ave(STD)
0.049573
CV
0.007818
Zmin= 2 to Zmax = 9
Average
5.79915
STDEV
0.042235
Ave(STD)
0.041204
CV
0.007105

Regarding to the threshold as alpha-cut which was equal to 6 (alpha-cut = 6), the appropriate driving forces for responses of 50, 100, and 200 experts are as follows:

 The appropriate driving forces 50 Experts:

All driving forces will be considered by the experts as the most impact factors except (D13) which is “Regulatory influences government policies changes”.

 The appropriate driving forces 100 Experts:

All driving forces will be considered by the experts as the most impact factors except (D13) which is “Regulatory influences government policies changes”.

The appropriate driving forces 200 Experts:

In this case, the appropriate driving forces are:

DF2:  Increasing globalization

DF7:  Marketing innovation

DF11:  Growing buyer preferences for differentiated products instead of standardized commodity product (or for a more standardized product instead of strongly differentiated products)
DF14:  Changing societal concerns, attitudes, and life styles
As you can see, the final results for 200 experts are the similar to the final results of the article of “Delphi Method to Design a Strategic Plan” posted on link: http://emfps.blogspot.com/2012/02/fuzzy-delphi-method-to-design-strategic.html

Conclusion

Finally, let me tell you about the advantages and disadvantage of this simulation model in comparing with FDM as follows:

Advantages:  

Ø  To delete Linguistic Values such as Pessimistic = P, Most likely = M, and
Optimistic = O
Ø  To eliminate the repetition of the experts’ responses
Ø  To increase the accuracy of the responses
Ø  To save the cost and the time

Disadvantage:

In the reference with above item (I1), I have still doubt about the increase the accuracy of the responses collected only in the first round because it is possible, in the second or third round, the range of the ranking will increase. This simulation model is very sensitive to be changed the minimum and maximum of the ranking.  

 
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.”


Wednesday, July 4, 2012

Application of Pascal’s Triangular plus Monte Carlo Analysis to Find the Least Squares Fitting for a Limited Area: The Case of Constant – Growth (Gordon) Model


Following to article of “Application of Pascal’s Triangular plus Monte Carlo Analysis to Find the Least Squares Fitting for a Limited Area” on link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_23.html, some professional people asked me about the application of the Least Squares Fitting for a Limited Area. In this article, I am willing to bring a simple example which is the case of constant – growth model.

The Case of Constant – Growth (Gordon) Model

As an example, I would like to refer you to my article of “Case Analysis of GAINESBORO MACHINE TOOLS CORPORATION (CON): A New Financial Simulation Model” on link: http://emfps.blogspot.com/2012/04/case-analysis-of-gainesboro-machine.html. The case issue is: How can we cope with the Constant- Growth Model as our dividend policy and our decision making?
Lawrence J. Gitman (2009) in his book of “Principles of Managerial Finance (Twelfth edition)” stated:
“The most widely cited dividend valuation approach, the constant – growth model, assumes that dividends will grow at a constant rate, but a rate that is less than the required return.”
If we simplify the equation of the constant – growth model, we will have below formula:
P0 = D1 / (rs – g)
Where:
P0 = value of common stock
D1 = the most recent dividend per – share
rs = required return on common stock ( In this case, I consider it as the Cost of Capital (WACC) for GAINESBORO MACHINE TOOLS CORPORATION)
g = the constant – growth rate
If we multiply all outstanding shares to above equation, the formula shows us the enterprise value of Gainesboro.
We can re-write above formula as follows:
WACC = (D1/ EV) + g
Where:
WACC = Gainesboro’s Cost of Capital
D1 = total dividend projected in 2005
EV = enterprise value in accordance with financial model mentioned on link: http://emfps.blogspot.com/2012/04/case-analysis-of-gainesboro-machine.html
In the reference with the article of “Application of Pascal’s Triangular plus Monte Carlo Analysis to Find the Least Squares Fitting for a Limited Area” on link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_23.html, we can define the area as follows:
WACC = y
 D1/ EV = b
g = x
m = 1
Then we have:
y = x + b
Referring to the article of “Case Analysis of GAINESBORO MACHINE TOOLS CORPORATION (CON): A New Financial Simulation Model” posted on link: http://emfps.blogspot.com/2012/04/case-analysis-of-gainesboro-machine.html, I obtained the range for “WACC” and “g” as follows:
4% <= WACC <= 11%
And
0 <= g <= 3%
It means that, our definition for area is:
0.04 <= y <= 0.11
0 <= x <=3
Now, I try to calculate “b” as the Least Squares Fitting for a Limited Area by using the application of Pascal’s Triangular and Monte Carlo Simulation (referred to link: http://emfps.blogspot.com/2012/05/application-of-pascals-triangular-plus_23.html) below cited:



As we can see:
D1/ EV = 0.06
Since the range of “g” is between 0 and 3%, WACC should be always equal or more than 6% and equal or less than 9%. Therefore we have:
WACC = g + 0.06, If and only if 6% < = WACC < = 9%
In this case, I have made four scenarios which will cover all results.
Now, I am ready to use from Financial Model posted on link: http://emfps.blogspot.com/2012/04/case-analysis-of-gainesboro-machine.html :
How can we work with this financial model for this example?
1) I added the item D1/ EV on my spreadsheet (financial model)
2) I used from sensitivity analysis for D1 as independent variable and  D1/ EV as dependent variable and I obtained D1 for each scenario for D1/ EV = 0.06
3) I added D1 for 2005 year as dividend payout and formula = D1 * (1+g) for 2006 to 2011.
The final results of each scenario are as follows:

Scenario (1)
WACC = 6%
g = 0%




Scenario (2)

WACC = 7%
g = 1%























Scenario (3)

WACC = 8%
g = 2%



Scenario (4)

WACC = 9%
g = 3%




As we can see, the range of changes for D1 and current share price (P0) in the all scenarios are negligible.

Conclusion

The final answer to the issue of this case is that the Constant – Growth (Gordon) Model as a dividend policy and decision making for Gainesboro should be completely rejected, if the range of “WACC” is between 4% to11percent and so the range of “g” is between 0 to3percent. Because by using of Constant – Growth (Gordon) Model, expected share price will go down under current share price that the average is equal to $29.15 for 2004 year.

Let me start another case as the new example as follows:

The Case of Terminal Value

One of the most crucial problems to use the discounted cash flow analysis is to find the appropriate the Terminal Value because this methodology is very sensitive to the Terminal Value Growth Rate and the firm’s Cost of Capital. The formula for terminal value is as follows:
Terminal value = [FCFn * (1+g)] / (WACC – g)


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.”

To be continued……..