PROBABILITY DISTRIBUTION : MARKET RETURN SCENARIOS FOR THE NATIONAL ECONOMY

 In this Business Analytics lecture, the Market Return.xlsx data is available at https://github.com/alcadelina/business-analytics-excel-data

As a Business Manager, you are concerned with the market return for the coming year, where the market return is defined as the percentage gain (or loss, if negative) over the year. You believe there are 5 possible scenarios for the national economy in the coming year:

    Scenario                                         Estimated Market Return    Probability

1. Rapid Expansion                           23%                                        0.12

2. Moderate Expansion                     18%                                        0.40

3. No Growth                                    15%                                        0.25

4. Moderate Contraction                    9%                                         0.15

5. Serious Contraction                       3%                                         0.08

The objective is to compute the Mean, Variance, and Standard Deviation of the probability distribution of the market return for the coming year using Excel and visualize the Simulation in Density using Tableau..

Solution for Excel.

To make the connection between the general notation, let X denote the market return for the coming year. Then each possible economic scenario leads to a possible value of X. 

Step 1. Mean Return. Calculate the mean return in cell B11 with the formula:

=SUMPRODUCT(C4:C8,B4:B8)

Step 2. Squared Deviations. to get ready to compute the variance, calculate the squared deviations from the mean by entering the formula

= (C4-Mean)^2

in cell D4 and copying it down through cell D8.

Step 3. Variance. Calculate the variance of the market return in cell B12 with the formula

= SUMPRODUCT(Sq_dev_from_mean,Probability)

=SUMPRODUCT(Market_return,Market_return,Probability)-Mean^2

Step 4. Standard Deviation. calculate the Standard deviation of the Market return in cell B13 with the formula

=SQRT(Variance)

Step 5. we will do Simulation. Create a 2nd worksheet and name it SIMULATION. Then copy the details below. Also copy the Average Return and Std.Dev return from the original sheet.

In your Look Up table copy the "Return" and do the Cum Probability

Step 6. On the "Simulation" type the formula of RAND as show below:

Step 7. Type the VLOOKUP Formula as shown below:

Step 8. On the "Summary statistics from simulation below" , type the Average formula as shown below:

and the Std. Dev of returns as shown below:

Below is the complete Excel Output.

In tableau, Visualize the Market return using density:

Arnel Lopez Cadeliña, MBA, REA, CFA (Investment Foundation Certificate)