I need you to complete an excel homework assignment for my Data Analysis for Business Class. I will share the excel file my professor wants us to complete. It has 10 problems each with accompanying data sets used for the specific problem ie.(Problem #16, Data#16). I will also attach the professor's slides which can be used to help with what the answers to the questions should look like.
PowerPoint Presentation ITEC 210 DATA ANALYSIS FOR BUSINESS Normal Probability Distribution and Confidence Intervals Prof. Itir KARAESMEN AYDIN 1 Outline and Learning Outcomes In this presentation, you will learn Normal distribution functions in Excel to compute probabilities. Functions and tools in Excel to compute confidence intervals for a population mean. Functions and tools in Excel to compute confidence intervals for a population proportion. NOTE: This presentation does not show you *how* the work is done on Excel. 2 Topic: Normal Distribution and Probability Calculations 3 Learning Objective After completing these examples, you will be able to Compute probabilities for a random variable that is standard normal distributed. Compute probabilities for a random variable that has a general normal distribution. Compute the value of the normal-distributed random variable corresponding to a given cumulative probability. 4 Continuous Random Variables A quantitative variable that takes any value on one or more intervals on the real line. 5 0 X X 0 Continuous Random Variables A quantitative variable that takes any value on one or more intervals on the real line. We cannot compute the probability of the variable taking a precise value, but we can compute the probability of the variable lying within a given range. 6 0 X X 0 What is the probability that X takes values in a given interval? Normal Distribution The density function f(x) determines the shape of the distribution for a continuous random variable. The normal distribution is symmetric (skewness=0). 7 x f(x) Normal Distribution Normal Probability Density Function The mean and standard deviation are unique. The distribution is symmetric around the mean. Mean = Median = Mode for normal distribution. = mean = standard deviation = 3.14159 e = 2.71828 Normal Distribution The probability that X takes values between X1 and X2 is the same as the area under the density function between X1 and X2 in the graph below: 9 x x1 x2 f(x) Normal Distribution The probability that X takes values greater than X1 is the same as the area under the density function to the right of X1. 10 x x1 f(x) Normal Distribution The probability that X takes values smaller than X1 is the same as the area under the density function to the left of X1. 11 x x1 f(x) Normal Distribution The probability that X takes values smaller than X1 or greater than X2 is the same as the area under the density function to the left of X1 plus the area to right of X2. 12 x x1 f(x) x2 Mean and Standard Deviation Mean can be positive, zero, or negative. Mean () is the center value. Standard deviation () affects the variation or spread around the mean. 13 x =-10 =+10 =15 =25 Normal Distribution P(- < x="">< +="" )="1" =="" total="" area="" under="" the="" density="" function="" p(x="" ≤="" )="0.5" =="" the="" area="" to="" the="" left="" of="" ="" p(x="" ≥="" )="0.5" =="" the="" area="" to="" the="" right="" of="" ="" 14="" x="" f(x)="" ="" the="" empirical="" rule="" 15="" x="" m–3s="" m–s="" m–2s="" m+s="" m+2s="" m+3s="" m="" 68.3%="" 95.4%="" 99.7%="" standard="" normal="" distribution="" the="" normal="" distribution="" with="" ="0" and="" ="1" is="" called="" the="" standard="" normal="" distribution.="" we="" use="" the="" letter="" z="" for="" the="" random="" variable="" that="" is="" standard="" normal="" distributed="" 16="" z="" f(x)="" 0="" excel="" functions="" normsdist(cutoff)="" norm.s.dist(cutoff,true)="" norm.s.dist(cutoff,1)="" these="" functions="" calculate="" p(z="" ≤="" cutoff).="" these="" functions="" always="" compute="" the="" “cumulative”="" probability:="" the="" function="" computes="" the="" “left="" tail”="" of="" the="" distribution,="" i.e.,="" the="" area="" under="" the="" probability="" density="" curve="" to="" the="" left="" of="" a="" given="" a="" cutoff="" value.="" the="" output="" is="" a="" numerical="" value="" between="" 0="" and="" 1.="" 17="" excel="" exercise="" #16="" cutoff="" value="" is="" 0.9.="" what="" is="" the="" probability="" that="" the="" observed="" values="" of="" the="" random="" variable="" z="" will="" be="" less="" than="" or="" equal="" to="" 0.9?="" p(z="" ≤="" 0.9)="NORM.S.DIST(0.9,TRUE)=0.8159" 18="" z="" 0.9="" f(x)="" excel="" exercise="" #16="" (cont’d)="" cutoff="" value="" is="" -0.2.="" what="" is="" the="" probability="" that="" the="" observed="" values="" of="" the="" random="" variable="" z="" will="" be="" higher="" than="" or="" equal="" to="" -0.2?="" p(z="" ≥="" -0.2)="1" -="" p(z="" ≤="" -0.2)="1-" norm.s.dist(-0.2,true)="1" -="" 0.4207="0.5793." 19="" -0.2="" f(x)="" z="" excel="" exercise="" #16="" (cont’d)="" p(-0.2="" ≤="" z="" ≤="" 0.9)="NORM.S.DIST(0.9,TRUE)" -="" norm.s.dist(-0.2,true)="0.8159" –="" 0.4207="0.3952" 20="" 0.9="" f(x)="" -0.2="" z="" excel="" functions="" (cont’d)="" normsinv(probability)="" norm.s.inv(probability)="" these="" functions="" return="" the="" value="" of="" c="" such="" that="" p(z="" ≤="" c)="probability." the="" probability="" value="" is="" known.="" c="" is="" the="" unknown.="" 21="" excel="" exercise="" #16="" (cont’d)="" z="" is="" standard="" normal="" distributed.="" the="" probability="" of="" observing="" values="" below="" c="" is="" 0.20.="" find="" c.="" 22="" f(x)="" c="?" z="" c="NORM.S.INV(0.2)" =="" -0.84162="" excel="" exercise="" #16="" (cont’d)="" z="" is="" standard="" normal="" distributed.="" the="" probability="" of="" observing="" values="" above="" c="" is="" 0.75.="" find="" c.="" 23="" f(x)="" c="?" z="" c="NORM.S.INV(1-0.75)" =="" norm.s.inv(0.25)="-0.6745" excel="" exercise="" #17="" use="" excel="" to="" compute="" the="" following="" for="" a="" standard="" normal="" distributed="" random="" variable="" z.="" p(z="" ≤="" -0.3)="?" p(z="" ≤="" 0.3)="?" p(z="" ≥="" -0.3)="?" p(z="" ≥="" 0.3)="?" p(-0.3="" ≤="" z="" ≤="" 0.3)="?" p(0.3="" ≤="" z="" ≤="" 1.0)="?" find="" c="" such="" that="" p(z="" ≤="" c)="0.88." find="" c="" such="" that="" p(z="" ≤="" c)="0.3." find="" c="" for="" the="" 10%="" upper="" tail:="" p(z="">C)=0.1. 24 #17 – Excel functions 25 General Normal Distribution A random variable has the “standard” normal distribution only when its mean is 0 and its standard deviation is 1. Excel can calculate probabilities or cutoff values for random variables that are more “general” with means different from 0 and/or standard deviations different from 1. 26 Excel Functions for General Normal Distribution NORMDIST(cutoff,,, TRUE) NORM.DIST(cutoff, ,,TRUE) NORM.DIST(cutoff, ,,1) These functions calculate P(X ≤ cutoff) for a random variable X that is Normal distributed with a known mean value and a known standard deviation value . The output is a numerical value between 0 and 1. 27 Excel Functions for General Normal Distribution NORMINV(probability, ,) NORM.INV(probability, ,) These functions return the value of C such that P(X ≤ C) = probability for a random variable X that is normal distributed with a known mean value and a known standard deviation value . The probability value is known. C is the unknown. 28 Excel Exercise #18 Use Excel to compute the following for a normal-distributed random variable X with =10 and =25. P(X ≤ 15)=? P(X ≤ -5)=? P(X ≥ -10)=? P(X ≥ 10)=? P(-5 ≤ X ≤ 10)=? P(10 ≤ X ≤ 15)=? Find C such that P(X ≤ C)=0.88. Find C such that P(X ≤ C)=0.3. Find C for the 10% upper tail: P(X>C)=0.1. 29 #18 – Excel functions 30 Excel Exercise #19 An electronics retailer has only 20 webcams left in stock. The retailer places a new order with their supplier. The shipment arrives from the supplier in 10 days. The demand for the webcams is known to be normal distributed. The mean demand for webcams in the next 10 days is expected to be 18 and the standard deviation is 3. 31 #19 (cont’d) a) What is the probability that the retailer will sell out the webcam inventory within the next 10 days? Let X be the random demand. The retailer sells out when demand ≥ stock level. P(X ≥ Stock level) = P(X ≥ 20) = 1 - NORM.DIST(20,18,3,TRUE) = 1 – 0.7475 = 0.2525. 32 #19 (cont’d) b) What is the probability that the retailer will have 6 or more units remaining in stock when the next shipment arrives? The retailer has 6 or more units remaining in stock, if (stock level – demand) ≥ 6. P(stock level – demand ≥ 6) P(demand ≤ stock level-6) = P(X ≤ 20-6) = P(X ≤ 14) = NORM.DIST(14,18,3,TRUE) = 0.0912 33 #19 (cont’d) c) If the retailer had ordered the webcams earlier (i.e. before the stock level dropped to 20 units), then the risk of a stockout would have been lower. What is the correct “reorder point” (the stock level at the time the retailer contacts the supplier for a new shipment) if the retailer wants the probability of a stock out until the next shipment arrives to be at or below 5%? The retailer has a stock out if demand ≥ stock level. P(demand ≥ stock level) ≤ 0.05 P(X ≥ cutoff) = 0.05 = NORMINV(1-0.05,18,3) = 22.93 23 units or more are needed to keep stock out chance below 5%. 34 Confidence Interval for The Population Proportion 35 Learning Objective After completing these examples, you will be able to Compute confidence intervals for a population proportion given sample proportions. Compute confidence intervals for a population proportion given a sample data. 36 Sample vs. Population It is hard to obtain information about the entire population. We can take random, unbiased samples. Each sample may have a different sample proportion. How good is the information we obtain from a sample in representing the population? Is there a relationship between the sample information and the population information? 37 Sample vs. Population Proportion p: population proportion Example: p is the fraction of all Twitter accounts with zero tweets. n: sample size : sample proportion Example: Given a sample of n Twitter accounts, is the fraction of Twitter accounts with zero tweets in this sample. 38 Confidence Interval for Population Proportions Use the following information the sample proportion , the sample size (n), a significance level of α which gives us 100(1-α)% confidence to compute the confidence interval [Lower limit of the CI, Upper limit of the CI]. We will be 100(1-α)% confident that the unknown population proportion is contained in this interval, i.e., we will compute the interval using a method that is successful in giving correct results 95% of the time. 39 Distribution of Sample Proportions 40 p Each bubble represents the value of one sample proportion. 1 0 0 1 There are 36 sample proportions represented in this picture. The statistical distribution of