Breweries
"Profit or Perish .... There are only two ways to make money: Increase sales or decrease cost" ~ Fred deLuca
Understanding the strengths of the business is key to maintaining and increasing profit.
Background:
A brewery has recorded data for a duration of three years, analysis is done to aid better decision making in order to maximize profit and reduce loss to the lowest minimum.
Business Questions to Answer
-
Within the space of the last three years, what was the profit worth of the breweries, inclusive of the anglophone and the francophone territories?
-
Compare the total profit between these two territories in order for the territory manager
-
Country that generated the highest profit in 2019
-
The year with the year with the highest profit.
-
Which month in the three years were the least profit generated?
-
What brand had the minimum profit in the month of December 2018?
-
A Comparison of the profit in percentage for each of the month in 2019
-
What particular brand generated the highest profit in Senegal?
-
Country with the highest consumption of beer.
Using Structured Query Language (SQL) and Microsoft Excel each business questions is answered and a final analytics report is presented
Business Case 1
SELECT
SUM(Profit) AS total_profit
FROM
international_breweries
Business Case 2
SELECT
SUM(PROFIT)
FROM
(SELECT
*,
CASE
WHEN Countries = 'Ghana' THEN 'Anglophone'
WHEN Countries = 'Nigeria' THEN 'Anglophone'
WHEN Countries = 'Togo' THEN 'Francophone'
WHEN Countries = 'Benin' THEN 'Francophone'
WHEN Countries = 'Senegal' THEN 'Francophone'
END
AS country_language
FROM
International_breweries) S
WHERE
country_language = 'Anglophone';
Business Case 3
SELECT
Countries,
SUM(Profit) AS Total_Profit
FROM
international_breweries_main
WHERE
Years = 2019
GROUP BY
Countries
ORDER BY
Total_Profit
Business Case 4
SELECT
Years,
SUM(Profit) AS Total_Profit
FROM
international_breweries_main
GROUP BY
Years
ORDER BY
Total_Profit DESC
Business Case 5
SELECT
Months,
SUM(Profit) AS Total_Profit
FROM
international_breweries_main
GROUP BY
Months
ORDER BY
Total_Profit DESC
Business Case 6
SELECT
Brands,
SUM(Profit) AS Total_Profit
FROM
international_breweries_main
WHERE
Years = 2018 AND Months = 'December'
GROUP BY
brands
Business Case 7
SELECT
Months,
SUM(Profit) AS total_profit,
((SUM(Profit)/30020250)*100) AS Percentage_Profit
FROM
international_breweries_main
WHERE
Years=2019
GROUP BY
Months
Business Case 8
SELECT
Brands,
SUM(PROFIT) AS Total_Profit
FROM
international_breweries_main
WHERE
Countries = 'Senegal'
GROUP BY
Brands
Business Case 9
SELECT
COUNTRIES,
SUM(QUANTITY) as Beer_quantity
FROM
(SELECT
*,
CASE WHEN BRANDS = 'trophy' THEN 'beer'
WHEN Brands = 'budweiser' THEN 'beer'
WHEN Brands = 'hero' THEN 'beer'
WHEN Brands = 'castle lite' THEN 'beer'
WHEN Brands = 'eagle lager' THEN 'beer'
WHEN Brands = 'beta malt' THEN 'malt'
WHEN Brands = 'grand malt' THEN 'malt'
END as category
FROM
international_breweries_main) C
WHERE
category = 'beer'
GROUP BY
Countries
Contact
I'm always looking for new and exciting opportunities in which i can utilize this skills to better decision making. Let's connect.