Week 10 notes March 14 Class Statistics concepts and excel  Mean=average value of a set of observations o =AVERAGE(data values)  Median=the middle value of a set of observations o =MEDIAN(data values)  Standard deviation= measure the spread of data (how far data points are from mean) o Measured in the same units as data values o =STDEV(data values) o In finance: standard deviation of return measures risk o Flawed measure of risk bc:  SD is very sensitive to extreme values (skew data if have outliers)  Measures deviation on both sides of meanthe risk is the spread in a negative direction but the spread in the positive direction is good  Better measure of risk= probability of loss=p(loss)  Variance=(standard deviation) 2 o =VAR(data points)  Random variable=variable used to represent a random event o Discrete random variable=2 distinct outcomes/categories o Continuous random variable=can have decimal values/on a range (aka temp, weight)  Histogram=use for discrete variable distribution o Y axis=frequency and x-axis=variable  Continuous variable distribution=a curve (continuous line) o Y-axis=probability density and x-axis=variable o Uniform distribution=horizontal line; equal probability that will take on any value; each value in range is equally likely o Normal Distribution=highest probability in center and probability decreases as farther away from center; mean and median in the same place  68% of data within 1 standard deviation  95% data within 2 standard deviation  99.7% data within 3 standard deviation  Normal Distribution Probabilities o Probability the x is greater/less than some value=total are to left or right of value o =NORM.DIST(value, mean, SD, 1)  1 is a binary code; 1=tell excel looking for area; 0=looking for height (**always use 1 in this course) o Can only find area to the left for area to the right= 1-total area to the left  Because entire area under the curve is 1 (100% of probabilities) o Area between 2 values=area to left of larger value-area to the left of smaller value  Ex. =NORM.DIST(25..)-NORM.DIST(15…) Inverse normal function o =NORM.INV(percentage, mean, SD) o Use when know the probability and trying to find value  Z-score o (=Mean-value)/SD o Excel =STANDARDIZE(x, mean, SD) o Finding z-score is called normalization/standardization March 16 Class NetConnect NetConnect, an internet service provider, plans to launch a joint marketing campaign with a cellular phone company to offer a cellular phone to existing NetConnect household customers. NetConnect does not know which of its specific household customers already have a cellular phone. However, they do have information about the internet usage of their customers. The Marketing department at NetConnect has determined that the number of hours online per month for homes with a cellular phone is normally distributed with mean 32 hours and standard deviation 7 hours. For homes without a cellular phone, the number of hours online per month is normally distributed with mean 27 hours and standard deviation 5 hours. NetConnect will send marketing material to all their household customers spending less than x hours online per month with x to be determined. (a) What should x be to ensure that 80% of homes without a cellphone get the mailing  Draw normal distribution w/ mean of 27 and SD of 5 hours  Trying to find x value given the percentage  X=NORM.INV(0.80, 27, 5) in excel=31.2 hours  Conclusion: send to all houses that spend 31.2 hours online or less (b) With x chosen as in (a), what percentage of homes with a cell phone will also get the mailing Show the area corresponding to your answer on a rough sketch of a normal distribution.  Draw normal distribution w/ mean of 32 and SD of 7  Trying to find percentage from x value  =NORM.DIST(31.2, 32, 7, 1)=0.45  45% of customers will get mailing (c) The Marketing Department at NetConnect estimates that there are 1,250,000 NetConnect household customers in total. It costs $1 per mailing. With x chosen as in (a) the total cost of the mailing will be $912,500. What is the number of households without a cellphone  Total number of homes that get mailing=$912,500/$1 per mailing=912,500 homes  Let X be the number of homes without a cellphone  (0.8)X + (0.45)(1,250,000-x)=912,500 o Homes with cellphone=1,250,000-x o Homes without cellphone=x o Multiply percentage of homes from each group with total number of homes and set equal to the total amount of mailings o .35X + 562,500=912,500 o 0.35X = 350,000 o X = 1,000,000 Tulane Culinary Society The Tulane Culinary Society sells cupcakes on the LBC lawn. The daily demand for cupcakes has a mean of 150 and standard deviation of 20. The society makes 175 fresh cupcakes every day. Unsold cupcakes are given to a community food kitchen. What is the probability that on any day 10 or more cupcakes will be given to the community food kitchen Draw a rough sketch of a normal distribution to illustrate your answer.  Draw SN curve with mean at 150 and SD of 20  175 cupcakes in stock  What is the probability that 10 or more cupcakes are given to the food kitchen  Find area to the left of 165 In Stock Demand # cupcakes given to food kitchen 175 180 0 175 175 0 175 174 1 175 165 10  165 or less cupcakes demand then will have 10 or more given to food kitchen  =NORM.DIST(165, 150, 20, 1)