ECON154 - STATISTICAL FOUNDATIONS OF BUSINESS ANALYTICS
Group Project in MS Excel
[100 marks]
Instructions
1. You will submit two files as part of your group project – a report in 1,000 words (MS Word document) and a supplementary data file (MS Excel file).
2. Please make sure to label the question number in your data file for each constructed table when submitting the supplementary data file. You may put each question’s tables and charts on a new sheet for better communication.
3. Unmarked tables and charts in the supplementary file will not be graded.
4. Wherever possible, describe the chart or table in your own words.
5. The University of Liverpool’s academic integrity policy applies.
6. Late submissions will be subject to the University of Liverpool’s late submissions policy.
Study Context
In November 2014, the city of Berkeley in California implemented a tax on sugar-sweetened beverages (SSB) sellers, to discourage SSB consumption due to health issues like diabetes and obesity. The tax of one cent per fluid ounce meant that if retailers raised their prices to exactly counter the effects of the tax, a $1 can of soda (12 oz) would now cost $1.12. But did sellers respond this way? In this project, we will make before-and-after comparisons using data on sugar beverages to learn about the effects of the sugar tax. To do so, we compare the outcomes of two groups, both before and after the policy took effect:
· The treatment group: those who were affected by the policy
· The control group: those who were not affected by the policy.
Before-and-after comparisons of retail prices
We will look at price data from the treatment group (stores in Berkeley) to see what happened to the price of sugary and non-sugary beverages after the tax.
a) Download the ‘Dataset Project Sugar Tax’ Excel dataset.
b) The first tab of the Excel file contains the data dictionary. Make sure you read the data description column carefully, and check that each variable is in the Data tab.
We will compare the variable price per ounce in US$ cents (price_per_oz_c). We will look at what happened to prices in the two treatment groups before the tax (time = DEC2014) and after the tax (time = JUN2015):
· treatment group one: large supermarkets (store_type = 1)
· treatment group two: pharmacies (store_type = 3).
Before doing this analysis, we will use summary measures to see how many observations are in the treatment and control group, and how the two groups differ across some variables of interest. For example, if there are very few observations in a group, we might be concerned about the precision of our estimates and will need to interpret our results considering this fact.
You may use Excel’s PivotTable option to make frequency tables containing the summary measures that we are interested in. The tables should be in a different tab to the data (either all in the same tab, or in separate tabs).
Question 1 [15 marks]
Create the following tables:
a) A frequency table showing the number (count) of store observations (store type) in December 2014 and June 2015, with ‘store type’ as the row variable and ‘time period’ as the column variable. For each store type, is the number of observations similar in each time period? [5 marks]
b) Two frequency tables showing the number of taxed and non-taxed beverages in (i) December 2014 and (ii) June 2015, with ‘store type’ as the row variable and ‘taxed’ as the column variable. (‘Taxed’ equals 1 if the sugar tax applied to that product, and 0 if the tax did not apply). For each store type, is the number of taxed and non-taxed beverages similar? [5 marks]
c) A frequency table showing the number of each product type (type), with ‘product type’ as the row variable and ‘time period’ as the column variables. Which product types have the highest number of observations, and which have the lowest number of observations? Why might some products have more observations than others? [5 marks]
Question 2 [15 marks]
Next, we are interested in comparing the mean price of taxed and non-taxed beverages, before and after the tax.
Calculate and compare conditional means:
a) Create a table similar to Figure 1, showing the average price per ounce (in cents) for taxed and non-taxed beverages separately, with ‘store type’ as the row variable, and ‘taxed’ and ‘time’ as the column variables. Make sure to only include non-supplementary products (supp = 0). Make sure to keep only store types 1 and 3 in the table. [5 marks]
b) Without doing any calculations, summarize any differences or general patterns between December 2014 and June 2015 that you find in the table. [5 marks]
c) Would we be able to assess the effect of sugar taxes on product prices by comparing the average price of non-taxed goods with that of taxed goods in any given period? Why or why not? [5 marks]
|
|
Non-taxed
|
Taxed
|
|
Store type
|
Dec 2014
|
Jun 2015
|
Dec 2014
|
Jun 2015
|
|
1
|
|
|
|
|
|
3
|
|
|
|
|
Figure 1. The average price of taxed and non-taxed beverages, according to time period and store type.
Question 3 [20 marks]
To make a before-and-after comparison, we will make a chart to show the change in prices for each store type. Using your table from Question 2:
a) Calculate the change in the mean price after the tax (price in June 2015 minus price in December 2014) for taxed and non-taxed beverages, by store type. [10 marks]
b) Using the values you calculated in Question 3(a), plot a column chart to show this information (as done in Figure 2 below) with store type on the horizontal axis and price change on the vertical axis. Label each axis and data series appropriately. [10 marks]
Figure 2. the change in average beverage prices before and after the tax by store type for taxed and non-taxed beverages.
Question 4 [10 marks]
To assess whether the difference in mean prices before and after the tax could have happened by chance due to the samples chosen (and there are no differences in the population means), we calculate the p-value. Let the p-value be 0.04 for large supermarkets and 0.65 for pharmacies in this case.
Based on these p-values and your chart from Question 3, what can you conclude about the difference in means? [10 marks]
Question 5 [40 marks]
Follow these data manoeuvre instructions for the next set of questions [5 marks]:
· Delete supp=1 observations (1162 observations left for Dec 2014 and June 2015).
· Create a new variable labelled time_code with two values 0 and 1, such that time_code=0 if time=DEC2014 and time_code=1 if time=JUN2015.
· Create a new variable labelled time_tax with two values 0 and 1, such that time_tax=1 if both time_code=1 and taxed=1, time_tax=0 otherwise. See the table below for specific values:
|
Time_code
|
Taxed
|
Time_tax
|
|
0
|
0
|
0
|
|
0
|
1
|
0
|
|
1
|
0
|
0
|
|
1
|
1
|
1
|
Now we regress beverage prices on the dummy variables ‘taxed’, ‘time_code’, and the interaction of the two dummies – time_tax.
a) Run the following regression in excel: use ‘price_per_oz_c’ as the dependent variable and three independent variables - ‘taxed’, ‘time_code’, and ‘time_tax’. Report the regression table. [10 marks]
b) Develop the estimated regression equation. [5 marks]
c) Test for a significant relationship for all three independent variables. Use α = 0.05. [5 marks]
d) Did the estimated regression equation provide a good fit? Explain. [5 marks]
e) Do you believe the above estimated regression provides a good prediction/explanation of the impact of sugar tax on beverage prices? Why or why not? Think about a thought experiment you would do to test the impact of the sugar tax on beverage prices. [10 marks]