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

CHECK TWO FILES...


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 outcomeProbabilityMarket returnSq dev from mean Rapid Expansion0.2023.0%0.00884 Moderate Expansion0.2018.0%0.00194 No Growth0.2015.0%0.00020 Moderate Contraction0.209.0%0.00212 Serious Contraction0.203.0%0.01124 Summary measures of return Mean13.6% Variance0.004860.00486Quick alternative formula Std Dev7.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 algebraically 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$15TRUE'Market'!$A$15FALSEFALSETRUETRUE Simulation Simulating market returns Summary statistics from simulation below Average return12.9% Std Dev of returns7.4% Exact values from previous sheet (for comparison) Average return13.6% Std Dev of returns7.0% SimulationLookup table Random #Simulated market returnCum ProbReturn 0.8668393.0%023.0% 0.41886315.0%0.2018.0% 0.08001923.0%0.4015.0% 0.9512023.0%0.609.0% 0.22614318.0%0.803.0% 0.8830313.0% 0.47521515.0% 0.8993033.0% 0.7575689.0% 0.7114749.0% 0.53434615.0% 0.26480618.0% 0.9112983.0% 0.7604989.0% 0.9273673.0% 0.9821993.0% 0.53649815.0% 0.38713518.0% 0.11964523.0% 0.6208529.0% 0.24838518.0% 0.21098018.0% 0.49992215.0% 0.8748553.0% 0.6972219.0% 0.9510183.0% 0.09828123.0% 0.9412533.0% 0.6634209.0% 0.6474169.0% 0.47835315.0% 0.42932715.0% 0.51999115.0% 0.7824309.0% 0.8119123.0% 0.05572423.0% 0.8051183.0% 0.17942523.0% 0.8476873.0% 0.57650915.0% 0.37301418.0% 0.9751213.0% 0.55528515.0% 0.6911869.0% 0.9693873.0% 0.7780399.0% 0.17908523.0% 0.51415915.0% 0.07660823.0% 0.00775723.0% 0.9015173.0% 0.35511018.0% 0.6467089.0% 0.9178453.0% 0.42142915.0% 0.55049815.0% 0.42944715.0% 0.35514918.0% 0.33014018.0% 0.7442939.0% 0.54025015.0% 0.03739423.0% 0.39229918.0% 0.26510218.0% 0.6934639.0% 0.7217459.0% 0.6716799.0% 0.8841533.0% 0.9078493.0% 0.42123015.0% 0.11130523.0% 0.43658215.0% 0.08313123.0% 0.54147815.0% 0.33351918.0% 0.8763293.0% 0.8387473.0% 0.8058593.0% 0.8052463.0% 0.8867013.0% 0.25654218.0% 0.59287915.0% 0.6500169.0% 0.6165519.0% 0.06743323.0% 0.6503229.0% 0.05600923.0% 0.52182015.0% 0.9072053.0% 0.02103723.0% 0.23038418.0% 0.7786839.0% 0.34821118.0% 0.15995423.0% 0.8882633.0% 0.7413729.0% 0.57320515.0% 0.26461118.0% 0.06399823.0% 0.8741543.0% 0.18986123.0% 0.28713118.0% 0.6496499.0% 0.24308318.0% 0.19062223.0% 0.37771818.0% 0.49554315.0% 0.9232073.0% 0.9389283.0% 0.9925323.0% 0.04770423.0% 0.6699579.0% 0.25700618.0% 0.6885229.0% 0.33502918.0% 0.00454123.0% 0.09849023.0% 0.15258723.0% 0.24052018.0% 0.8555313.0% 0.22233118.0% 0.9343973.0% 0.8574273.0% 0.15155023.0% 0.32469118.0% 0.9866513.0% 0.46861215.0% 0.8963833.0% 0.28666418.0% 0.7265819.0% 0.39333618.0% 0.10842623.0% 0.8612843.0% 0.34096618.0% 0.42753715.0% 0.19427323.0% 0.9058753.0% 0.17490923.0% 0.8144453.0% 0.6973479.0% 0.6080749.0% 0.8296043.0% 0.19945023.0% 0.29785718.0% 0.9311723.0% 0.6818799.0% 0.6680609.0% 0.7295059.0% 0.9912533.0% 0.24234518.0% 0.9789133.0% 0.44409315.0% 0.10436723.0% 0.06830023.0% 0.51001215.0% 0.27179818.0% 0.37078218.0% 0.32966418.0% 0.6779559.0% 0.9938323.0% 0.6445589.0% 0.9237143.0% 0.12969423.0% 0.6158979.0% 0.8733413.0% 0.30759018.0% 0.8249153.0% 0.9684803.0% 0.7582999.0% 0.8467173.0% 0.47574015.0% 0.9917803.0% 0.18217523.0% 0.24981518.0% 0.37793418.0% 0.7697319.0% 0.06715923.0% 0.6160319.0% 0.50851615.0% 0.43346515.0% 0.7294329.0% 0.01854923.0% 0.9786153.0% 0.07573123.0% 0.6116019.0% 0.7467389.0% 0.8444763.0% 0.07537123.0% 0.49839715.0% 0.9233883.0% 0.09796323.0% 0.14788623.0% 0.11223423.0% 0.8840353.0% 0.19610223.0% 0.19577823.0% 0.9387423.0% 0.19524623.0% 0.59855615.0% 0.34891718.0% 0.01336523.0% 0.7890009.0% 0.01652323.0% 0.09094423.0% 0.36827118.0% 0.8847653.0% 0.8868683.0% 0.13497923.0% 0.47179715.0% 0.49477115.0% 0.47119915.0% 0.12601423.0% 0.9883963.0% 0.27880918.0% 0.9832933.0% 0.9777773.0% 0.8372533.0% 0.8602433.0% 0.6991799.0% 0.7468459.0% 0.8433483.0% 0.26109418.0% 0.9904273.0% 0.12878023.0% 0.39572918.0% 0.11861823.0% 0.34897418.0% 0.54537815.0% 0.42443715.0% 0.02148623.0% 0.15550723.0% 0.7562519.0% 0.8761653.0% 0.7453869.0% 0.07796923.0% 0.45656615.0% 0.37905018.0% 0.9356143.0% 0.7060879.0% 0.9439843.0% 0.7121819.0% 0.48909915.0% 0.31328618.0% 0.00448123.0% 0.23586418.0% 0.8780963.0% 0.8637353.0% 0.9657473.0% 0.15010423.0% 0.00488223.0% 0.54357015.0% 0.51290615.0% 0.9248173.0% 0.9641353.0% 0.18687223.0% 0.6957539.0% 0.9976633.0% 0.6734439.0% 0.8770203.0% 0.9839263.0% 0.01042223.0% 0.6717159.0% 0.27578518.0% 0.26852018.0% 0.8539183.0% 0.38404818.0% 0.7183139.0% 0.39655918.0% 0.9130073.0% 0.01805523.0% 0.13212823.0% 0.11407623.0% 0.57418515.0% 0.8712683.0% 0.8286713.0% 0.04044723.0% 0.47693715.0% 0.06253223.0% 0.00327223.0% 0.57634015.0% 0.9344263.0% 0.52347215.0% 0.9820163.0% 0.8618553.0% 0.38947418.0% 0.19299923.0% 0.32587518.0% 0.9513543.0% 0.25840618.0% 0.58312715.0% 0.59628515.0% 0.40295015.0% 0.24081218.0% 0.49583515.0% 0.34526118.0% 0.16455523.0% 0.9179933.0% 0.8737313.0% 0.53535815.0% 0.55343215.0% 0.8596893.0% 0.42680115.0% 0.32299918.0% 0.23386918.0% 0.6653759.0% 0.7008759.0% 0.47644515.0% 0.41617315.0% 0.7799689.0% 0.8347633.0% 0.15583923.0% 0.33675318.0% 0.47720715.0% 0.9363333.0% 0.01716323.0% 0.8645883.0% 0.35662618.0% 0.8208833.0% 0.15210523.0% 0.53182715.0% 0.58205915.0% 0.6806569.0% 0.44451715.0% 0.12279023.0% 0.29481618.0% 0.7922799.0% 0.7116379.0% 0.28298318.0% 0.49963815.0% 0.24908518.0% 0.52384015.0% 0.54342515.0% 0.25384518.0% 0.36266618.0% 0.6583889.0% 0.55173915.0% 0.7242679.0% 0.9127313.0% 0.52234615.0% 0.7542739.0% 0.29893218.0% 0.9669893.0% 0.19366623.0% 0.45451215.0% 0.6530399.0% 0.59037015.0% 0.9984763.0% 0.7522279.0% 0.9857623.0% 0.45336615.0% 0.30301018.0% 0.16838623.0% 0.6955199.0% 0.9139843.0% 0.35433818.0% 0.9126163.0% 0.8337253.0% 0.7348229.0% 0.53547015.0% 0.23392818.0% 0.57020915.0% 0.50140815.0% 0.59695315.0% 0.01123023.0% 0.6045729.0% 0.10076423.0% 0.6464859.0% 0.7460179.0% 0.7048519.0% 0.6107559.0% 0.02907823.0% 0.17848123.0% 0.7980249.0% 0.9493893.0% 0.9731033.0% 0.07328123.0% 0.17469523.0% 0.8152413.0% 0.8654483.0% 0.9580683.0% 0.9967533.0% 0.47896715.0% 0.55230115.0% 0.8333433.0% 0.6227489.0% 0.22733818.0% 0.9173913.0% 0.56848415.0% 0.02954223.0% 0.19400623.0% 0.6247989.0% 0.01064423.0% 0.6839249.0% 0.36240018.0% 0.42176215.0% 0.6226219.0% 0.50810515.0% 0.8586853.0% 0.22388718.0% 0.08968323.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