DATA ANYLIC SIS IN ADVANCE EXCEL INTERNSHIP REPORT

DATA ANYLIC
SIS IN ADVANCE EXCEL INTERNSHIP REPORT

1. Coffee Sales Analysis
HARSHA ADARSHI

2. Project Overview
Welcome to the Coffee Shop Sales Data Analysis project repository! This project showcases an in-depth data analysis of
coffee shop sales using advanced Excel techniques. The analysis provides insights into customer information, product details,
and valuable business metrics
.
Key Objective
1. Customer Data Compilation
We used advanced Excel functions to seamlessly compile essential customer details:
•Names: Extracted customer names using innovative methods.
•Emails: Derived customer email addresses using Excel's capabilities.
•Countries: Extracted customer countries through strategic Excel techniques.
2. Product Data Extraction
Our analysis involved extracting crucial product details:
•Coffee Type: Extracted coffee types using advanced Excel techniques.
•Roast Type: Derived roast types for each product.
•Size: Extracted product sizes using Excel's advanced functionalities.
•Unit Price: Obtained unit prices leveraging Excel's capabilities.

3. INTRODUCTION
By analysing sales data across weeks, months, or years, we can help businesses identify high performing products
and buying habits which enables them to make informed decisions on inventory management and marketing
strategies for enhanced overall sales performance. The sales analysis for four (4) coffee types –Robusta, Arabica,
Excelsa, liberica–from 2019 to 2022 offers a detailed examination of consumer preferences associated with each
coffee variant. Using STAR technique, this report will highlight all the necessary steps taken and provide insights
and possible recommendations to improve sales for high and low performing products.
Business Problem
A coffee shop with franchise across 3 countries in Uk, Ireland and America wants to track their data across their
branches and understand how each product performs in different countries.
SITUATION
–spotlight the total sales trends over time for each coffee type and provide insights into the ever evolving market demand.
–Analyzegeographical preferences, outlining the total sales by country to identify regional variances and potential growth
opportunities.
–highlight key customers who have consistently contributed to the highest sales volume, shedding light on valuable
consumer relationships.

4. Data Cleaning, Preparation and Transformation
1.The complete data set needed is contained in 3 different tables.
2.we’ll Clean and prepare the data set; formatting the header row & removing duplicates. A new table was
formed to merge the other tables by using XLOOKUP to populate the new columns, formatting date and size
columns, using conditional formatting to check for nulls and handle them appropriately.
3.Creating new columns to calculate coffee sales and another column to for customers with loyalty cards
4.Pivot Tables and charts to analyzethe total sales, top cutomersand total sales by country and coffee type
to identify top performing products and top country with the highest market share.
5.Visualize key performance indicators for sales, top customers and countries. Create timeline and sliders for
an interactive dashboard to view all key performance indicators in real time.
DATASET
Data set contains 3 tables of customer data, product data and sales data containing these columns respectively;
Orders table:Customer ID, Product ID, Quantity
Customer table:Customer ID, Customer Name, Email, Phone Number, Address Line 1, City, Country, Postcode, Loyalty
Card
Product table:Product ID, Coffee Type, Roast Type, Size, Unit Price,Priceper 100g, Profit.
ACTION

5. transaction_idtransaction_dateMonth Name Day Name transaction_timeHourstore_locationtransaction_qtyunit_priceTotal Pricestore_idproduct_idproduct_category
114301 01-06-2023 June Thursday 11:33:2911 Astoria 1 3 3 3 45 Tea
115405 02-06-2023 June Friday 11:18:2411 Astoria 1 3 3 3 45 Tea
115478 02-06-2023 June Friday 12:02:4512 Astoria 1 3 3 3 45 Tea
116288 02-06-2023 June Friday 19:39:4719 Astoria 1 3 3 3 45 Tea
116714 03-06-2023 June Saturday 12:24:5712 Astoria 1 3 3 3 45 Tea
116739 03-06-2023 June Saturday 12:44:1712 Astoria 1 3 3 3 45 Tea
116853 03-06-2023 June Saturday 13:58:1413 Astoria 1 3 3 3 45 Tea
117389 03-06-2023 June Saturday 18:57:2918 Astoria 1 3 3 3 45 Tea
118056 04-06-2023 June Sunday 14:31:4314 Astoria 1 3 3 3 45 Tea
118281 04-06-2023 June Sunday 16:30:3316 Astoria 1 3 3 3 45 Tea
118428 04-06-2023 June Sunday 17:53:3017 Astoria 1 3 3 3 45 Tea
118913 05-06-2023 June Monday 12:01:0312 Astoria 1 3 3 3 45 Tea
119196 05-06-2023 June Monday 14:19:5914 Astoria 1 3 3 3 45 Tea
119240 05-06-2023 June Monday 14:42:1614 Astoria 1 3 3 3 45 Tea
119351 05-06-2023 June Monday 15:51:3515 Astoria 1 3 3 3 45 Tea
119444 05-06-2023 June Monday 16:52:4016 Astoria 1 3 3 3 45 Tea
119692 05-06-2023 June Monday 19:51:0919 Astoria 1 3 3 3 45 Tea
121279 07-06-2023 June Wednesday 10:16:4010 Astoria 1 3 3 3 45 Tea
121416 07-06-2023 June Wednesday 10:59:1910 Astoria 1 3 3 3 45 Tea
121493 07-06-2023 June Wednesday 12:01:1812 Astoria 1 3 3 3 45 Tea
121656 07-06-2023 June Wednesday 15:05:4715 Astoria 1 3 3 3 45 Tea
121709 07-06-2023 June Wednesday 16:17:2516 Astoria 1 3 3 3 45 Tea
121876 07-06-2023 June Wednesday 19:56:3719 Astoria 1 3 3 3 45 Tea
122518 08-06-2023 June Thursday 11:09:4711 Astoria 1 3 3 3 45 Tea
122622 08-06-2023 June Thursday 12:09:5712 Astoria 1 3 3 3 45 Tea
122818 08-06-2023 June Thursday 15:00:4015 Astoria 1 3 3 3 45 Tea
122954 08-06-2023 June Thursday 16:58:4716 Astoria 1 3 3 3 45 Tea
123152 08-06-2023 June Thursday 19:54:5619 Astoria 1 3 3 3 45 Tea
123465 09-06-2023 June Friday 08:39:358 Astoria 1 3 3 3 45 Tea

6. Data Cleaning & Pre processing
Asa data analysis project , my first aim was collect the raw data . I has succesfully collect raw data from maven analytics online which link is given.
After succesfull admire the raw data first of all i saw& understand the data . The raw data was too much complex and consistingtoo much duplicated vales and
unnesseary thing . So My 1st major challenge was clean the data . so i cleaned & re -arranged the Raw data using NAVIGATOR to ready for analysis. Which sort
process are mentioned.
1. conditional colm add for product_detail for colm name --colm detail .... Lg-Large, Sm-Small, Rg-Regular.
2. In product detail replace values using transform tool Rg, Lg, Sm.
3.In product detail remove white spaces of text using Transform-format-trim.text.
4.Add custom colm of Total price Add colm-custom colm-Total price [unit price]*[Transaction_quantity].
5.In transaction time colm remove date using transform-extract-extract text after Delimator & Giving [Space] in delimator.
6. From transaction_date exract & add colm for month,day. and From trans_time extract& add hours.
Operations.
After Cleaning& rearranging the raw data . i performed various operations . some of that mentioned below.
1. File-operation-customised ribbon-add developer. for adding developer option.
2.developer-colm Add-ins--Microsoft power pivot for excel, add new sheet& add pivot table from data model insert -pivot table-from data model.
hours-rows, transaction.id-values.
3. change sum of transaction_id into total transaction_id from values setting.
4. Perform many more operations for ready to further analysis.
After perform these operations I analysis this coffee sales data on behalf of some straight forward questions. some of
these mentioned below.

7. DATA CLEANING, PREPARATION and TRANSFORMATION.
•Loaded the dataset in Excel spreadsheets and renamed the table “coffee sales analysis”, scanned for
duplicate rows in all tables which the data set was void off. Null values existed in the “Email” column but seeing
that it wasntas important to our analysis or affect it in anyway, we decided they’d be left blank.
•During data transformation, the columns from other tables were merged using XLOOKUP.
Customer name:XLOOKUP(orders!C2,customers!$A$1:$A$1001,customers!$B$1:$B$1001,,0)
Email: XLOOKUP(C2,customers!$A$1:$A$1001,customers!$C$1:$C$1001,,0))
The null values in the email column returned as “0
To correct this we used to IF(XLOOKUP) Function to return a blank value for all the affected cells
IF(XLOOKUP(C2,customers!$A$1:$A$1001,customers!$C$1:$C$1001,,0)=0,””,XLOOKUP(C2,customers!$A$1:$A$1001,cust
omers!$C$1:$C$1001,,0))
Country:XLOOKUP(orders!C2,customers!$A$1:$A$1001,customers!$G$1:$G$1001,,0)
Coffee type; rob,exc,lib,ara:XLOOKUP(D2,products!$A$1:$A$49,products!$B$1:$B$49,,0)
Roast type; M,L,D: XLOOKUP(D2,products!$A$1:$A$49,products!$C$1:$C$49,,0)

8. UsingIFFunction
Coffee type
name:=IF(I2=”Rob”,”Robusta”,IF(I2=”Exc”,”Excelsa”,IF(I2=”Lib”,”Liberica”,IF(I2=”Ara”,”Arabica”,””)))) to expand
the coffee type name
Roast type name:
=IF(K2=”M”,”Medium”,IF(K2=”L”,”Light”,IF(K2=”D”,”Dark”)))
With the quantity ordered and unit price provided in the Order and Product table respectively, calculating the
metric for Sales, the formula quantity multiplied by Unit price was utilised and used to populate anewSales
Column.

9. Transactions[Day Name]Transactions[transaction_time]Transactions[Hour]Transactions[store_location]Transactions[transaction_qty]Transactions[unit_price]Transactions[Total Price]
Thursday 11:33:29 11Astoria 1 3 3
Friday 11:18:24 11Astoria 1 3 3
Friday 12:02:45 12Astoria 1 3 3
Friday 19:39:47 19Astoria 1 3 3
Saturday 12:24:57 12Astoria 1 3 3
Saturday 12:44:17 12Astoria 1 3 3
Saturday 13:58:14 13Astoria 1 3 3
Saturday 18:57:29 18Astoria 1 3 3
Sunday 14:31:43 14Astoria 1 3 3
Sunday 16:30:33 16Astoria 1 3 3
Sunday 17:53:30 17Astoria 1 3 3
Monday 12:01:03 12Astoria 1 3 3
Monday 14:19:59 14Astoria 1 3 3
Monday 14:42:16 14Astoria 1 3 3
Monday 15:51:35 15Astoria 1 3 3
Monday 16:52:40 16Astoria 1 3 3
Monday 19:51:09 19Astoria 1 3 3
Wednesday 10:16:40 10Astoria 1 3 3
Wednesday 10:59:19 10Astoria 1 3 3
Wednesday 12:01:18 12Astoria 1 3 3
Wednesday 15:05:47 15Astoria 1 3 3
Wednesday 16:17:25 16Astoria 1 3 3
Wednesday 19:56:37 19Astoria 1 3 3
Thursday 11:09:47 11Astoria 1 3 3
Thursday 12:09:57 12Astoria 1 3 3
Thursday 15:00:40 15Astoria 1 3 3
Thursday 16:58:47 16Astoria 1 3 3
Dataset after cleaning and transformation

10. Pivot Tables and Charts.
•The Sum of sales by producttype
We want to get the sales by each coffee type for each
year. The order dates were provided in the data set and
using pivot tables, we are able to distinguish by years and
months giving the sales data for each coffee type in the
individual months and grouped by years
Product type Sum of Total Price
Brazilian ₹ 37,746.50
Ethiopia ₹ 42,304.10
Jamaican Coffee River ₹ 38,781.15
Latte ₹ 36,369.75
Sustainably Grown Organic ₹ 39,065.10
Grand Total ₹ 1,94,266.60
GAUTAM KUMAR
ADARSHI:
Where data is too long so
we take top 5 rows using
value filter
91406.2
47932
77081.95
70034.6
72416
Total price by product type
Barista Espresso
Brewed Black tea
Brewed Chai tea
Gourmet brewed
coffee
Hot chocolate
33000
34000
35000
36000
37000
38000
39000
40000
41000
42000
43000
BrazilianEthiopiaJamaican
Coffee
River
LatteSustainably
Grown
Organic
Total price of top 5 product details
Total

11. 4594
13428
1765417764
18545
9766
87088714893389799093
8745
7498
6092
603
0
2000
4000
6000
8000
10000
12000
14000
16000
18000
20000
67891011121314151617181920
Total price hourly
Total
Total price hourly:
HourSum of Total Price
6 ₹ 21,900.27
7 ₹ 63,526.47
8 ₹ 82,699.87
9 ₹ 85,169.53
10 ₹ 88,673.39
11 ₹ 46,319.14
12 ₹ 40,192.79
13 ₹ 40,367.45
14 ₹ 41,304.74
15 ₹ 41,733.10
16 ₹ 41,122.75
17 ₹ 40,134.31
18 ₹ 34,286.20
19 ₹ 28,446.68
20 ₹ 2,935.64
Grand Total ₹ 6,98,812.33
DaysSum of Total Price
Friday ₹ 1,01,373.00
Monday ₹ 1,01,677.28
Saturday ₹ 96,894.48
Sunday ₹ 98,330.31
Thursday ₹ 1,00,767.78
Tuesday ₹ 99,455.94
Wednesday ₹ 1,00,313.54
Grand Total ₹ 6,98,812.33
Sunday,
98330.31
Monday,
101677.28
Tuesday,
99455.94
Wednesday,
100313.54
Thursday,
100767.78
Friday,
101373
Saturday,
96894.48
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
0
20000
40000
60000
80000
100000
120000
140000
160000
180000
total billl by month
Total
MonthSum of Total Price
April ₹ 1,18,941.08
February ₹ 76,145.19
January ₹ 81,677.74
June ₹ 1,66,485.88
March ₹ 98,834.68
May ₹ 1,56,727.76
Grand Total ₹ 6,98,812.33
Price footfall over days
Price footfall over month

12. 32000
34000
36000
38000
40000
42000
44000
Total price of top 5 product details
Total
Product type Sum of Total Price
Brazilian ₹ 37,746.50
Ethiopia ₹ 42,304.10
Jamaican Coffee River ₹ 38,781.15
Latte ₹ 36,369.75
Sustainably Grown Organic ₹ 39,065.10
Grand Total ₹ 1,94,266.60
Bak
ery
Bran
ded
Coff
ee
Coff
ee
bea
ns
Drin
king
Cho
cola
te
Flav
ours
Loos
e
Tea
Pack
age
d
Cho
cola
te
Tea
Total823113603E+0400872418409112144082E+0
0
50000
100000
150000
200000
250000
300000
Total price by product category
Total
product categorySum of Total Price
Bakery ₹ 82,315.64
Branded ₹ 13,607.00
Coffee ₹ 2,69,952.45
Coffee beans ₹ 40,085.25
Drinking Chocolate ₹ 72,416.00
Flavours ₹ 8,408.80
Loose Tea ₹ 11,213.60
Packaged Chocolate ₹ 4,407.64
Tea ₹ 1,96,405.95
Grand Total ₹ 6,98,812.33
Large,
68540, 43%
Regular,
70186, 44%
Small,
21423, 13%
TRANSACTION_QUANTITY% VS
SIZE
Size count of transaction_qty
Large 68540
Regular 70186
Small 21423
Grand Total 160149
Total price by product type
Count of transaction over size
Total price by product category

13. store location count of transaction_qty
Astoria 214470
Hell's Kitchen 214470
Lower Manhattan 214470
Grand Total 214470
Store LocationSum of Total Price
Astoria ₹ 2,32,243.91
Hell's Kitchen ₹ 2,36,511.17
Lower Manhattan₹ 2,30,057.25
Grand Total ₹ 6,98,812.33
Astoria,
232243.91
Hell's Kitchen,
236511.17
Lower
Manhattan,
230057.25
Astoria
Hell's Kitchen
Lower Manhattan
460004700048000490005000051000
Astoria
Hell's Kitchen
Lower Manhattan
Footfall over various location
Total
Transaction by store location
Total price by store location
1.Here I found that Hell’s kitchen has highest sum of total price
2.Hell’s kitchen made most number of transaction

14. Full Dashboard

15. After perform these operations I analyse this coffee sales data on behalf of some straight
forward questions .some of these mentioned below.
1.How do sales vary by day of the week , hours of the days and by months?
2.Are these any peak times for sales activity?
3.What is the total sales revenue for each month?
4.How do sales vary across different location and stores?
5.What is the average price/order per person?
6.Which products are the best selling in terms of quantity and revenue?
7.How do sales vary by product category & size?

16. •Sales by country for each coffee type
To analyse what country has the biggest market for each
of the 4 coffee types we are analysing for. It becomes
evident that for each coffee type, America consumes the
most and generates more revenue than any other country
in the company’s market share.

17. •Best purchasing customer by sum of sales, what
country they are in and their favourite coffee type.
From the pivot table, the top customers having the most
coffee purchases not surprisingly come from America as
that is the country with the highest market share.

18. RESULTS and RECOMMENDATIONS
A total of 1001 coffee orders for different coffee types for 4
years across 3 countries were analysed and this
interactive dashboard consisting of a general timeline from
2019–2021, slicers and charts was created using pivot
charts