Using the Hypothesis
Test in Excel To Find
Out If Your Advertising
This article will explain how to use Excel to perform a Hypothesis Test to analyze an advertising campaign. Specifically, we will show how to use Excel to perform One-Tailed, Two-Sample, Paired Hypothesis Test of Mean to determine whether an advertising campaign improved really sales.
This Hypothesis Test tests the Null Hypothesis that the advertising campaign did not improve sales.
The advantages of statistical analysis in Excel to solve business statistics problems is that most problems can be solved in just one or two steps and there is no more need to look anything up on Normal Distribution tables.
Here is the problem:
Problem: Determine with 95% certainty whether an advertising campaign increased average daily sales to our large dealer network. Before and After samples of average daily sales were taken with at least 30 dealers.
Here is the Before and After data for all of the dealers:
Before we begin solving this problem, we need to know whether we are dealing with normally distributed data. If the data is not normally distributed, we have to use nonparametric statistical tests to solve this problem.
Always Test for Normality First
Normality tests should be performed on the before and after sales data. Both data sets must be normally distributed to perform the well-known hypothesis test that is based upon the underlying data being normally distributed. This blog has numerous articles about how to perform normality testing and nonparametric testing if the data is not normally distributed.
Determine What Type of Hypothesis
Test You Will Perform
1) Hypothesis Test of Mean or Proportion?
We know that this is a test of mean and not proportion because each individual sample taken can have a wide range of values: Any sales sample measurement from 90 to 250 is probably reasonable.
2) One or Two-Tailed Hypothesis Test?
We know that this is a one-tailed test because we are trying to determine if the "After Data" mean sales is larger than the "Before Data" mean sales, not whether the mean sales are merely different.
3) One or Two-Sample Hypothesis Test?
We know that two samples need to be taken because no data is initially available.
4) Paired or Unpaired Hypothesis Test?
This is paired data because each set of "Before" and "After" data came from the same object.
In this case, we are performing a One-Tailed, Two-Sample, Paired Hypothesis Test of Mean to determine whether an advertising campaign improved really sales. We will do this test in Excel. It is extremely important to establish the type of Hypothesis test. Each type of Hypothesis test uses a slightly (or very) different methodology and set of formulas.
In this case, the yellow-highlighted column represents the difference between the Before and After sample of each data pair. The Hypothesis test will be performed on that column of data.
Above is the data sample:
Paired Hypothesis Tests involve taking Before and After samples from the same large number (n is greater than 30) of objects and performing a Hypothesis test on the differences between the Before and After samples.
Initial Parameters Needed
Before we can begin the Hypothesis test, we need to calculate the following parameters of variable x:
Sample size - Use Excel function COUNT
Sample mean - Use Excel function AVERAGE
Sample standard deviation - Use Excel function STDEV
Sample standard error = (Sample standard deviation) / SQRT (Sample size)
We need to make the following calculations on the data that represents the difference between the Before and After data:
xavg = "Difference Data" sample average = 9.60
Sxavg = Sample standard error = 1.11
n = "Difference Data" Sample size = 30
α = Level of Significance = 0.05 because there is a 5% max chance of error allowed.
Therefore a 95% Level of Certainty Required
The Null Hypothesis normally states that both populations sampled are the same. If the mean sales from both the Before and After Data are the same, then their average difference = 0
For this one-tailed test, the Alternative Hypothesis states that the value of the distributed variable xavg is larger than the value of 0 stated in the Null Hypothesis,
Two-tailed test - (Value of variable) does not equal (Constant)
Step 2 - Map the Normal Curve
Step 3 - Map the Region of Certainty
b) p Value Test
Using the Hypothesis Test in Excel to Find Out If Your Advertising Worked