Iowa Liquor

Alcoholic beverage division sales analytics

​

Our team

Introduction

Putu Bagia Artha

Nafisha Belva Sakinah

Krizan Wijayakusumah

Executive Summaries

• Liquor revenue is less than liquor budgeted by Iowa ABD
• The number of liquor ordered in 2021 by Iowa ABD is ~31M bottles or equal to $262M, but the number of bottles sold is only ~22M bottles or equal to $258M (only 71% sold) which does not reach the BEP (Break Even Point).
• This condition creates problem because Iowa ABD cannot achieve profit in the given year, or at least it reaches the BEP for the liquor sales.
• From the data, we can see that Iowa ABD needs to increase sales at least ~2% by selling the unsold bottle, so that Iowa ABD can reduce the amount of unsold bottle from 2021.
• Suggestion should be given to avoid the same condition next year
• We suggest that until the end of the year 2022, liquor target sales should be increase up to 5% from 2021 sales, so that Iowa ABD can achieve profit in the given year.
• We suggest that to increase the number of liquor sales, Iowa ABD should create an event in a selected month or run a campaign such as online sales, DTC, and new packaging to increase customer interest.

Background

01

Objective and scope definition

02

Data Source

04

Data Analysis

05

Table of Contents

Business Question And Methodology

03

Insights and recommendation

06

Introduction

ABD overview

Problem background

Background

01.

Iowa Alcoholic Beverages Division (ABD)

Overview

BACKGROUND

According to NABCA, National Alcohol Beverage Control Association, Iowa became one of the first Control states in 1934. The state regulates the traffic in alcoholic liquors for the protection of the welfare, health, peace, morals, and safety of the people of the state. Also, the state provides its E license liquor sales data monthly which contains more than 20M transactions records since 2012. Class "E" liquor license (LE) allows for the sale of alcoholic liquor for off-premises consumption in original unopened containers. In other words, this license is designed for customers who purchase alcohol and then take it somewhere else.

​

Problems Background

BACKGROUND

• Iowa spent $262 million to import liquor demand requirements by stores in Iowa.
• In 2021, Iowa only made $258M in revenue.
• Iowa can only order liquors from vendors in packs, not per bottle.
• Of 31M bottles ordered by Iowa ABD, only 71% of bottles were sold to the stores in 2021.

02. Objective and Scope

Main Objective

Objective and scope

• Giving recommendations about overstocked liquors sales for 2022.

• Advising on how to sell at least 2% of the unsold bottle.

​

• Giving recommendations on how to increase sales by 5% next year.

​

Scope Definition

Objective and scope

• This report is created to display liquors sales insights to Iowa ABD within the period January 2021 – January 2022.

​

• This report includes the Iowa liquor sales from January 2021 to January 2022.

​

• This dataset is about sales between the Iowa Alcoholic Beverages Division and stores across Iowa county.

​

• We assume that the budget for liquor purchases is the same every year.
• We assume that the liquor purchased in year must be sold completely at the end of the year

Business Questions And Methodology

03.

Business Questions

How to decrease the number of monthly unsold bottle by 2% in the next year.

How to increase total sales by 5% next year.

​

Methodology

Brainstorming

Determine what topic and find the related dataset

Data Cleaning

Data Analysis

Data Visualization

Insights and

Recommendation

Clean the raw data using python

Analyze the data using python and tableau

Visualize the findings and insights using tableau

Generated insights and recommendation for the business problem

04. Data Source

​

​

Iowa Liquor Sales (Jan 2021-Jan 2022)

​

https://www.kaggle.com/gabrielramos87/iowa-sales-liquor-jan-2021jan-2022

This dataset contains the spirits purchase information of Iowa Class “E” liquor licensees by product and date of purchase from January 2021 to January 2022. The dataset contains of ~ 3M rows and 24 columns.

Data Analysis

05.

Monthly REVENUE Iowa ALCOHOLIC BEVERAGES DIVISION

Data Analysis

Total Revenue in 2021 is 86M dollars.

​

The months that generate the Highest Revenue are March, June, and December. The Lowest Revenue occurred in July 2021 and January 2022.

​

Number of sold and unsold bottles per category

Data Analysis

• The total Unsold Bottles is 29% of the total bottles. The total unsold bottles indicates 1/3 of the total bottles sold.
• The top 3 Best Selling Bottle based on categories are Whiskey, Vodka, and Cordial/Liqueurs.

​

Profit Margin

Data Analysis

The Average profit margin for all categories is around 33%. There was an Increase in Unsold Bottles when the profit margin got significant for certain categories.

Top 8 and bottom 5 bottle category

Data Analysis

The highest type of liquor sales is Vodka (American Vodka). The second popular type is Whiskey (Whiskey Liqueur, Canadian Whiskies, Straight Borboun Whiskies).

Monthly DiffERENCE Sold and unsold bottle

Data Analysis

Monthly bottle sold shows a downtrend from the beginning of the year to the end of the year 2021.

The most significant percentage difference between the total sold and unsold bottles is June (11%). There is a high demand in June so it can be used as a good sales month. While March and December were relatively higher, the unsold level for those months was relatively high. So that promos can be given in both months by considering the amount of liquor ordered.

​

MAP of AVERAGE BOttle sold per County

Data Analysis

South Iowa is the area that has a higher average liquor consumption than North Iowa.

The Highest Bottle Sold are in Scott county. And the Lowest Bottle Sold are in Keokuk county.

​

Insights and

Recommendation

06.

Insights

Insights and recommendation

Iowa ABD’s revenue does not cover the overall expense to buy liquor (deficit of $3,6M).

Iowa ABD’s unsold bottle reached a staggering ~29% of the overall liquor bought.

Iowa ABD’s only achieve ~71% sales from the overall liquor bought.

There is a downtrend in bottle sold from the beginning of 2021 to the beginning of 2022

Downtrend in bottle sold and profit showing that innovation is required to boost more sales in the near future

01.

04.

02.

05.

03.

Recommendation

Insights and recommendation

Increase the number of bottle sold at least 2% per month to achieve BEP by giving promo on selected item (liquor that has more that 44% profit margin).

We suggest that we should focused our sales on the most preferable liqueurs such as whiskey, Vodka, Cordials/liqueurs in Iowa.

​

Increase the bottle sold target from 71% in 2021 to 75% until the end of 2022 to gain profit.

Innovation like ordering new packaging type, improving online sales, and DTC (direct-to-customer) shipping can increase the number of liquor sales.

(source: goekos.com)

​

Increase the sales by running a campaign in several counties (Scott, Pottawatta, Montgomery) that have high average bottles sold.

Create an event or campaign in March, June, and December that has high profit to optimize the sales of liqueurs.

01.

04.

02.

05.

03.

06.

THANK YOU

Credits

Appendices

Data

Visualization

Script

CREDITS: This presentation template was created by Slidesgo, including icons by Flaticon and infographics & images by Freepik