Lab 3 Part 1
You will need to use the Standardizing_Finished Excel sheet on the course module page.
In this Lab Activity, you are going to complete a transformation for four possible predictor variables, while examining the distribution of the variables. These four variables are predictors that will be used to model some outcome variable at a later time. This week, we are simply making sure we can all transform data to the Standard Z-Score.
Step 1: Get the file,
Step 2: Review the method for finding the Z-Score Distribution on the example sheet.
Step 3: Conduct a similar transformation for the predictor variables on the adjacent sheet (called Lab 3.1 data).
Of course, We will go over this lab activity in our weekly meet-up. Being able to transform data into distributions is a powerful and important skill in analytics and data science. So, this week, let's apply this to our probability distribution work. Also, there might be some challenges with the distributions themselves and the assumptions. As you write up your lab, think through what those might be -- and think about how to communicate the impact on the results.
Market
Mean, variance, and standard deviation of the market return
Economic outcome Probability Market return Sq dev from mean
Rapid Expansion 0.20 23.0% 0.00884
Moderate Expansion 0.20 18.0% 0.00194
No Growth 0.20 15.0% 0.00020
Moderate Contraction 0.20 9.0% 0.00212
Serious Contraction 0.20 3.0% 0.01124
Summary measures of return
Mean 13.6%
Variance 0.00486 0.00486 Quick alternative formula
Std Dev 7.0% 7.0%
This is a template for finding the mean and variance of any discrete distribution. First, take SUMPRODUCT of values and probabilities to get the mean. Second, calculate squared deviations from the mean. Finally, take SUMPRODUCT of squared deviations and probabilities to get the variance, and take its square root to get the standard deviation.
There is an alternative to calculating the variance you might prefer. It is easy to show alge
aically that variance is the mean of the squares minus the square of the mean. See the formula in cell C12 for this alternative approach, where the squared deviations in column D aren't needed.
The advantage of using squared deviations is that they indicate more directly what variance is all about.
MarketSI
1000 'Market'!$B$15 TRUE 'Market'!$A$15 FALSE FALSE TRUE TRUE
Simulation
Simulating market returns
Summary statistics from simulation below
Average return 12.9%
Std Dev of returns 7.4%
Exact values from previous sheet (for comparison)
Average return 13.6%
Std Dev of returns 7.0%
Simulation Lookup table
Random # Simulated market return Cum Prob Return
XXXXXXXXXX 3.0% 0 23.0%
XXXXXXXXXX 15.0% 0.20 18.0%
XXXXXXXXXX 23.0% 0.40 15.0%
XXXXXXXXXX 3.0% 0.60 9.0%
XXXXXXXXXX 18.0% 0.80 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 23.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 9.0%
XXXXXXXXXX 15.0%
XXXXXXXXXX 3.0%
XXXXXXXXXX 18.0%
XXXXXXXXXX 23.0%
This simulation illustrates how you can sample values from a discrete distribution, as you will do in the later simulation chapters (15 and 16). The trick is to use Excel's RAND function, along with a lookup table. If you simulate enough such values and then take their average and standard deviation (as in Chapter 2 with the AVERAGE and STDEV.S functions), these summary measures should be very close to the mean and standard deviation from the previous sheet.
Note: Using the lookup table to generate the random returns in column B is one approach. Another is to use the discrete_ function from the DADM_Tools add-in, as you will learn in Chapter 15.
1
2
3
4
5
6
A
B
Mean, variance, and standard deviation of the market return
Economic outcome
Probability
Rapid Expansion
0.20
Moderate Expansion
0.20
No Growth
0.20