############################
# STEP 1: Create Data Frame
############################
retail <- data.frame(
BillID = c(501,502,503,504,505,506),
CustomerName = c("Aman","Riya","Karan","Neha","Rohit","Simran"),
Gender = c("Male","Female","Male","Female","Male","Female"),
Membership = c("Gold","Silver","Gold","None","Silver","Gold"),
Category = c("Grocery","Electronics","Clothing","Grocery","Electronics","Clothing"),
Quantity = c(10,1,3,8,2,5),
CostPrice = c(50,20000,800,60,18000,700),
SellingPrice = c(70,25000,1000,80,22000,900),
Discount = c(100,2000,150,50,500,300),
DeliveryType = c("Home","Store","Home","Store","Home","Home"),
stringsAsFactors = FALSE
)
############################
# STEP 2: Calculation Columns
############################
retail$GrossRevenue <- retail$Quantity * retail$SellingPrice
retail$TotalCost <- retail$Quantity * retail$CostPrice
retail$NetRevenue <- retail$GrossRevenue - retail$Discount
retail$Profit <- retail$NetRevenue - retail$TotalCost
############################
# STEP 3: Multi-Condition Analysis
############################
# 1. Gold + Profit > 5000 + Home
gold_highprofit_home <- subset(retail,
Membership=="Gold" &
Profit>5000 &
DeliveryType=="Home")
# 2. Loss making transactions
loss_transactions <- subset(retail, Profit < 0)
# 3. Electronics Qty>=2, Discount>1000, Profit>0
electronics_condition <- subset(retail,
Category=="Electronics" &
Quantity>=2 &
Discount>1000 &
Profit>0)
# 4. Premium customers
premium_customers <- subset(retail,
NetRevenue>20000 |
Membership=="Gold")
############################
# STEP 4: Logical Classification
############################
# Profit Category
retail$ProfitCategory <- ifelse(retail$Profit>10000,"High Profit",
ifelse(retail$Profit>0,"Moderate Profit","Loss"))
# Risk Flag
retail$DiscountRatio <- retail$Discount / retail$GrossRevenue
retail$RiskFlag <- ifelse(retail$DiscountRatio>0.20 |
retail$Profit<0,
"Risky","Safe")
############################
# STEP 5: Aggregated Analysis
############################
# Total Profit by Membership & Category
total_profit_group <- aggregate(Profit ~ Membership + Category,
data=retail, sum)
# Average Profit by Gender & DeliveryType
avg_profit_group <- aggregate(Profit ~ Gender + DeliveryType,
data=retail, mean)
############################
# STEP 6: KPI Calculations
############################
retail$ProfitMargin <- (retail$Profit / retail$NetRevenue) * 100
high_margin_orders <- subset(retail,
ProfitMargin>30 &
NetRevenue>20000)
############################
# STEP 7: Sorting
############################
retail_sorted <- retail[order(-retail$Profit,
-retail$NetRevenue),]
############################
# STEP 8: Scenario Questions
############################
# 1
scenario1 <- subset(retail,
Membership=="Gold" &
DeliveryType=="Home" &
Profit>8000)
# 2
scenario2 <- subset(retail,
(Discount/GrossRevenue)>0.15 &
Category=="Electronics")
# 3
scenario3 <- subset(retail,
Gender=="Female" &
(Profit>5000 | Category=="Grocery"))
# 4
scenario4 <- subset(retail,
Profit<0 |
DiscountRatio>0.25)
# 5 Top 3 Profitable
top3 <- head(retail[order(-retail$Profit),],3)
# 6 Compare Avg Profit Gold vs Silver
compare_avg <- aggregate(Profit ~ Membership,
data=retail[retail$Membership %in% c("Gold","Silver"),],
mean)
# 7 TotalCost > NetRevenue
cost_exceeds <- subset(retail,
TotalCost > NetRevenue)
# 8 Highest Total Profit Membership
profit_by_member <- aggregate(Profit ~ Membership,
data=retail, sum)
highest_profit_member <- profit_by_member[which.max(profit_by_member$Profit),]
# 9 Delivery Type Lowest Avg Profit
avg_delivery <- aggregate(Profit ~ DeliveryType,
data=retail, mean)
lowest_delivery_profit <- avg_delivery[which.min(avg_delivery$Profit),]
# 10 High Revenue Low Margin
high_rev_low_margin <- subset(retail,
NetRevenue>20000 &
ProfitMargin<20)
############################
# View Final Data
############################
retail
## BillID CustomerName Gender Membership Category Quantity CostPrice
## 1 501 Aman Male Gold Grocery 10 50
## 2 502 Riya Female Silver Electronics 1 20000
## 3 503 Karan Male Gold Clothing 3 800
## 4 504 Neha Female None Grocery 8 60
## 5 505 Rohit Male Silver Electronics 2 18000
## 6 506 Simran Female Gold Clothing 5 700
## SellingPrice Discount DeliveryType GrossRevenue TotalCost NetRevenue Profit
## 1 70 100 Home 700 500 600 100
## 2 25000 2000 Store 25000 20000 23000 3000
## 3 1000 150 Home 3000 2400 2850 450
## 4 80 50 Store 640 480 590 110
## 5 22000 500 Home 44000 36000 43500 7500
## 6 900 300 Home 4500 3500 4200 700
## ProfitCategory DiscountRatio RiskFlag ProfitMargin
## 1 Moderate Profit 0.14285714 Safe 16.66667
## 2 Moderate Profit 0.08000000 Safe 13.04348
## 3 Moderate Profit 0.05000000 Safe 15.78947
## 4 Moderate Profit 0.07812500 Safe 18.64407
## 5 Moderate Profit 0.01136364 Safe 17.24138
## 6 Moderate Profit 0.06666667 Safe 16.66667
print(retail)
## BillID CustomerName Gender Membership Category Quantity CostPrice
## 1 501 Aman Male Gold Grocery 10 50
## 2 502 Riya Female Silver Electronics 1 20000
## 3 503 Karan Male Gold Clothing 3 800
## 4 504 Neha Female None Grocery 8 60
## 5 505 Rohit Male Silver Electronics 2 18000
## 6 506 Simran Female Gold Clothing 5 700
## SellingPrice Discount DeliveryType GrossRevenue TotalCost NetRevenue Profit
## 1 70 100 Home 700 500 600 100
## 2 25000 2000 Store 25000 20000 23000 3000
## 3 1000 150 Home 3000 2400 2850 450
## 4 80 50 Store 640 480 590 110
## 5 22000 500 Home 44000 36000 43500 7500
## 6 900 300 Home 4500 3500 4200 700
## ProfitCategory DiscountRatio RiskFlag ProfitMargin
## 1 Moderate Profit 0.14285714 Safe 16.66667
## 2 Moderate Profit 0.08000000 Safe 13.04348
## 3 Moderate Profit 0.05000000 Safe 15.78947
## 4 Moderate Profit 0.07812500 Safe 18.64407
## 5 Moderate Profit 0.01136364 Safe 17.24138
## 6 Moderate Profit 0.06666667 Safe 16.66667