Topic #4: Equivalent Monetary Value (Quantitative Analysis)

Now we will be tackling on Quantitative Analysis. There is a concept called Equivalent Monetary Value. We will be creating a computer program that will automatically solve the EMV of a given decision tree problem. To start, lets declare every variables we will be using:

    Dim payoffs() As Single

    Dim prob() As Single

    Dim alt As Integer

    Dim son As Integer

    Dim emv() As Single

    Dim emvfinal As Single

    Dim emvalt() As String

    Dim emvcount As Integer

    Dim response As Single

    Dim dummy As Integer

   

    Dim altname() As String

    Dim sonname() As String

   

    Dim indexalt As Integer

    Dim indexson As Integer

    Dim indexcount As Integer

Please Note every variable as we go along with the program.

We will be making a program that will ask the user to input the number of alternatives and states of nature. We will just add the following codes

    alt = Application.InputBox(prompt:="how many alternatives?", Default:=0)

    son = Application.InputBox(prompt:="how many states of nature?", Default:=0)

Next is we need to redimension the following variables:

    ReDim payoffs(1 To alt, 1 To son) As Single

    ReDim prob(1 To son) As Single

    ReDim altname(1 To alt) As String

    ReDim sonname(1 To son) As String

    ReDim emv(1 To alt) As Single

Next is that we will input the names of the alternatives, states of nature, probability and payoffs. We will just use the following codes to do so:

    For indexalt = 1 To alt

        altname(indexalt) = Application.InputBox(prompt:="enter name of alternative " & indexalt & ".", Default:=indexalt, Type:=2)

    Next

   

    For indexson = 1 To son

        sonname(indexson) = Application.InputBox(prompt:="enter name of state of nature " & indexson & ".", Default:=indexson, Type:=2)

    Next

   

    For indexson = 1 To son

        prob(indexson) = Application.InputBox(prompt:="enter the probability of " & sonname(indexson) & " state of nature.", Default:=0)

    Next

   

    For indexalt = 1 To alt

        For indexson = 1 To son

            payoffs(indexalt, indexson) = Application.InputBox(prompt:="enter payoff of " & altname(indexalt) & " in " & sonname(indexson) & " state of nature.", Default:=0)

        Next

    Next

Next, we will now be solving for the EMV of each alternative. We will just follow the formula for EMV. Here is the code:

    For indexalt = 1 To alt

        emv(indexalt) = 0

        For indexson = 1 To son

            emv(indexalt) = emv(indexalt) + payoffs(indexalt, indexson) * prob(indexson)

        Next

    Next

After solving for the EMV of each alternatives, we will be choosing for the alternative with the greateast EMV. We just have to use the code below:

    emvfinal = emv(1)

    For indexalt = 1 To alt

        If emvfinal < emv(indexalt) Then

            emvfinal = emv(indexalt)

        End If

    Next

There is a problem if there would be multiple alternatives that we can choose with regards to the highest EMV. To fix that problem, i modified the code to make it more flexible for multiple alternatives. Here’s the code:

    emvcount = 0

    For indexalt = 1 To alt

        If emvfinal = emv(indexalt) Then

            emvcount = emvcount + 1

        End If

    Next

   

    ReDim emvalt(1 To emvcount)

   

    dummy = 1

    For indexcount = 1 To emvcount

        For indexalt = dummy To alt

            If emvfinal = emv(indexalt) Then

                    emvalt(indexcount) = altname(indexalt)

                    dummy = indexalt + 1

                    Exit For

            End If

        Next

    Next

Lastly, put the code below for the results output:

    If emvcount = 1 Then

        response = MsgBox(prompt:="choose " & emvalt(1) & " with an EMV of " & emvfinal & ".", Buttons:=vbOKOnly)

    End If

    If emvcount > 1 Then

        For indexcount = 1 To emvcount

            response = MsgBox(prompt:="choose the alternatives: " & emvalt(indexcount) & ".", Buttons:=vbOKOnly)

        Next

        response = MsgBox(prompt:="with an EMV of " & emvfinal & ".", Buttons:=vbOKOnly)

    End If