Module 1 Exam
Jason Suchanek
10/11/2021
Introduction
This analysis looks at the liquor sales for the great state of Iowa. The state is concerned about recent reports of increased drunkenness resulting from the COVID-19 pandemic. To analyze this claim, we can look at liquor purchases in the three largest cities of the state: Des Moines, Cedar Rapids and Davenport. This analysis looks at data from the state Department of Commerce recording every liquor store wholesale purchase from January 2015 to September 2021.
Since liquor is regulated closely in the state of Iowa we are able to analyze many different variables such as the cost the state pays to the vendor for alcohol, and the cost the state charges to their in state stores for the alcohol that they purchased from the vendor. A list of the terms is provided in this analysis later on.
This analysis will help an individual better understand liquor consumption/sales in the state by examining the type of liquor being purchased by the state, the margins of the state purchase of the product and the store retail cost, as well as other comparisons.
Terms Definition
| Variables in Dataset | Variable Type | Explanation |
|---|---|---|
| Date | Date | This is the date of the order |
| Invoice/Item Number | Character | Invoice and Line number associated with the liquor order Provides a unique identifier for the individual liquor products. |
| Store Number/ Name | Character/Number | Name of the store who order the liquor and the number assigned to that store. |
| City | Character | City where the store who ordered the liquor is located in |
| Category Name | Character | Category of the liquor ordered |
| Vendor Name | Character | The vendor name of the company for the brand of liquor ordered |
| Item Description | Character | Description of the individual liquor product ordered |
| Pack | Numeric | The number of bottles in a case for the liquor ordered |
| Bottle Volume | Numeric | Volume of each liquor bottle ordered in milliliters |
| State Bottle Cost | Numeric | The amount that Alcoholic Beverages Division paid for each bottle of liquor ordered |
| State Bottle Retail | Numeric | The amount the store paid for each bottle of liquor ordered |
| Bottles Sold | Numeric | The amount of bottles of liquor ordered by the store |
First Visualization
Below shows a table that breaks down by category name the amount of bottles sold by each store. The table can be filtered to see the high and low categories for Bottles Sold by store. The most popular category for bottles sold is American Vodkas.
Below are 5 small summary tables highlighting interesting points about our data.
This shows the number of stores in each city who purchased alcohol from the state of Iowa.
## # A tibble: 3 x 2
## City `Store Count`
## <chr> <int>
## 1 Des Moines 68663
## 2 Cedar Rapids 52039
## 3 Davenport 33786Below is a small summary table for the average and standard deviation in regards to the amount the store purchased from the state of Iowa.
## # A tibble: 1 x 2
## AVG STDEV
## <dbl> <dbl>
## 1 15.2 14.6Below shows the frequency of bottle volume classification from most common to least
## # A tibble: 5 x 2
## `Bottle Volume Classification` Frequency
## <chr> <int>
## 1 376 - 750 75168
## 2 <=375 39543
## 3 1251 - 2000 23519
## 4 751 - 1250 15214
## 5 > 2000 1044Below are the top 10 stores across all 3 cities that have made the most purchases for liquor to the state.
## # A tibble: 10 x 2
## `Store Name` `Number of Purchases`
## <chr> <int>
## 1 Hy-Vee #3 / BDI / Des Moines 6691
## 2 Central City 2 5828
## 3 Central City Liquor, Inc. 4752
## 4 Hy-Vee #7 / Cedar Rapids 3692
## 5 Hy-Vee Food Store #5 / Cedar Rapids 3598
## 6 Benz Distributing 3537
## 7 Hy-Vee Food Store #3 / Cedar Rapids 3223
## 8 Hy-Vee Food Store / Fleur / DSM 3145
## 9 Hy-Vee Food Store #1 / Cedar Rapids 2385
## 10 Hy-Vee Food Store #3 / Des Moines 2360Below are the top 10 products that are purchased by the stores from the state.
## # A tibble: 10 x 2
## `Item Description` `Number of times purchased`
## <chr> <int>
## 1 Black Velvet 3074
## 2 Hawkeye Vodka 3007
## 3 Hennessy VS 2380
## 4 Fireball Cinnamon Whiskey 2189
## 5 Titos Handmade Vodka 2027
## 6 Five Star 1431
## 7 Crown Royal Regal Apple 1382
## 8 Mccormick Vodka Pet 1334
## 9 Fireball Cinnamon Whiskey Mini Dispenser 1332
## 10 Jim Beam 1324Illustrations
Let’s look at a histogram in regard to the distribution of retail transaction price
Now let’s take a look at the how the total transaction sales value per year has changed from 2015-2021
Below is the relationship between the type of liquor and the variance in retail price
Below is a visual of the variance in retail price per liter for each bottle volume classification
Below is an illustration for the relationship between retail price, number of bottles sold, and the type of liquor purchased.
Analysis Questions
How has the popularity of Vodka, Whiskey, and Rum, Tequila and Brandy varied over the years?
Above is a data table broken down by year for the 3 types (Vodka, Whiskey, Rum), to see their popularity over the years. This datatable can be filtered to see the highest/lowest amount of sales for the specific type of liquor.
When did the state of Iowa transition from recording invoice numbers in the form “S00000000000” to the form “INV-00000000000?”
Above table shows us that the state of Iowa stopped recording invoice numbers in the form “S00000000000” after 2016. Iowa then started recording invoice numbers in the form “INV-00000000000”
Do Iowans appear to prefer imported or domestic liquors?
From a year by year breakdown it is clear that Iowans prefer Domestic liquors in an overwhelming fashion.
Which weekday is the hottest for alcohol sales?
## wday Number of Sales
## 1: Sun 67
## 2: Mon 43295
## 3: Tue 29469
## 4: Wed 27977
## 5: Thu 35787
## 6: Fri 17133
## 7: Sat 760We see that Monday is the most popular day for alcohol sales. Other mid week days are also popular, with Friday and the weekend being low.
Which vendor sells the least expensive liquor on average?
The above bar chart shows the 10 Vendors that (on average) sell the least expensive liquor.
Additional Analysis
## # A tibble: 7 x 2
## Type Margin
## <chr> <dbl>
## 1 Brandy 0.501
## 2 Gin 0.501
## 3 Liqueur 0.501
## 4 Rum 0.500
## 5 Tequila 0.500
## 6 Vodka 0.501
## 7 Whiskey 0.501The above summary table dives into the margins for each type of alcohol. We quite clearly see that all types of margins are relatively the same at 0.5. This 0.5 value tells us that the ABD is making about 50% more than what they purchased from the vendor.
Looking at the above visual, we 2020 has the highest amount for bottles sold compared to any other year prior. Though the increase is small, the 2021 year of year change is higher than any other year over year change. As we know COVID 19 impacted individuals in 2020, which can be seen as a factor in regard to the higher amount of bottles being sold in that year. (2021 is YTD and is not the full year)
Where to open stores?
It would be wise to open stores in the city of Cedar Rapids. If we filter the table by bottles sold the area codes of 52402 and 52404 have purchased the most bottles from the state. Also these area codes in Cedar Rapid have an average retail bottle cost 14.90, which is below the average cost for state bottle retail of 15.24.This area would be a very good place to open up a new store.
What products to sell?
In regard to the 50 products that would be sold, It would be nice to keep costs low. Therefore, it would be smart to sell the 50 products in which there is the lowest margin between the state bottle cost and the state bottle retail. These 50 products would be the ones to be sold in the boutique liquor store in Cedar Rapids (specifically in zip codes 52402 and 52404).
Final Analysis
Lets dive into to see if there is any difference between the margin of the state bottle cost and the state retail cost when it comes to liquor that is imported and liquor that is domestic. This analysis will look at the top 10 vendors that the state of Iowa purchases from the most in both domestic products and imported products. This analysis is looking through the lens of the state of Iowa. The higher the margin the better for the state.
First let’s look at the Domestic margins for the top 10 vendors used by the state of Iowa.
We see that the highest margins for the top vendors are just over 6 dollars (difference between the average state bottle cost and the state bottle retail).
Now lets look at the margins for Imported liquor to see if there is a difference.
We see that almost half of the top 10 vendors have margins of 9 dollars or over. This tells us that the state of Iowa is making a larger profit when it sells imports to it’s stores. However, it is up to the consumer (citizens of the state of Iowa) to set the demand for imported liquor, otherwise stores would not purchase from the state. Currently, imported liquor makes up just over 2% of all liquor purchased by the state of Iowa.
Conclusion
This analysis looked at the liquor sales for the great state of Iowa’s 3 biggest cities (Des Moines, Davenport, and Cedar Rapids). As mentioned in the introduction the state was concerned about recent reports of increased drunkenness resulting from the COVID-19 pandemic. While 2020 did have the most bottles sold compared to years 2015 - 2019, the amount of bottles sold was increasing every year.