LBB_1 REV

BACKGROUND
- Discount Strategies
- Case Study
INPUT DATA
- DATA INSPECTION
- DATA CLEANSING AND COERTIONS
DATA EXPLANATION
DATA MANIPULATION
BUSINESS RECOMENDATION

BACKGROUND

Discount Strategies

Discount strategies

Plan before you offer discounts
Understand how discounting affects profit margins and sales targets
Consider the discount
Benefit discount to profit

Case Study

ABC.com is a Marketplace that loves to give out amazing deals. They have various discount on every category and They want their customer get amazing discount and expect increasing Quantity of sold. ABC.com works for statisfy their members. They want to make a fast moving products so that ABC.com members will have more quantity sold and ABC.com still profitable. This will be a win situation for both.

INPUT DATA

retail <- read.csv("data_input/retail.csv")

Chunk Commentary :

read data retail and put to retail table

DATA INSPECTION

str(retail)

## 'data.frame':    9994 obs. of  15 variables:
##  $ Row.ID      : int  1 2 3 4 5 6 7 8 9 10 ...
##  $ Order.ID    : Factor w/ 5009 levels "CA-2014-100006",..: 2501 2501 2297 4373 4373 202 202 202 202 202 ...
##  $ Order.Date  : Factor w/ 1237 levels "1/1/17","1/10/14",..: 305 305 836 94 94 922 922 922 922 922 ...
##  $ Ship.Date   : Factor w/ 1334 levels "1/1/15","1/1/16",..: 220 220 907 129 129 897 897 897 897 897 ...
##  $ Ship.Mode   : Factor w/ 4 levels "First Class",..: 3 3 3 4 4 4 4 4 4 4 ...
##  $ Customer.ID : Factor w/ 793 levels "AA-10315","AA-10375",..: 144 144 240 706 706 89 89 89 89 89 ...
##  $ Segment     : Factor w/ 3 levels "Consumer","Corporate",..: 1 1 2 1 1 1 1 1 1 1 ...
##  $ Product.ID  : Factor w/ 1862 levels "FUR-BO-10000112",..: 13 56 947 320 1317 186 563 1762 795 438 ...
##  $ Category    : Factor w/ 3 levels "Furniture","Office Supplies",..: 1 1 2 1 2 1 2 3 2 2 ...
##  $ Sub.Category: Factor w/ 17 levels "Accessories",..: 5 6 11 17 15 10 3 14 4 2 ...
##  $ Product.Name: Factor w/ 1850 levels "\"While you Were Out\" Message Book, One Form per Page",..: 387 833 1440 367 574 570 1137 1099 535 295 ...
##  $ Sales       : num  262 731.9 14.6 957.6 22.4 ...
##  $ Quantity    : int  2 3 2 5 2 7 4 6 3 5 ...
##  $ Discount    : num  0 0 0 0.45 0.2 0 0 0.2 0.2 0 ...
##  $ Profit      : num  41.91 219.58 6.87 -383.03 2.52 ...

Chunk Commentary : check retail table structure

From our inspection we can conclude :

Retail data contain 9994 of rows and 15 of coloumns
Each of column name :
- Row.ID
- Order.ID
- Order.Date
- Ship.Date
- Customer.ID
- Segment
- Product.ID
- Category
- Sub.Category
- Product.Name
- Sales
- Quantity
- Discount
- Profit

DATA CLEANSING AND COERTIONS

retail$Row.ID <- as.character(retail$Row.ID)
retail$Order.ID<- as.character(retail$Order.ID)
retail$Order.Date<- as.Date(retail$Order.Date, "%m/%d/%y")
retail$Ship.Date <- as.Date(retail$Ship.Date, "%m/%d/%y")
retail$Customer.ID<-as.character(retail$Customer.ID)
retail$Product.ID<- as.character(retail$Product.ID)
retail$Product.Name<-as.character(retail$Product.Name)

Chunk Commentary :

change some data type in retail table

str(retail)

## 'data.frame':    9994 obs. of  15 variables:
##  $ Row.ID      : chr  "1" "2" "3" "4" ...
##  $ Order.ID    : chr  "CA-2016-152156" "CA-2016-152156" "CA-2016-138688" "US-2015-108966" ...
##  $ Order.Date  : Date, format: "2016-11-08" "2016-11-08" ...
##  $ Ship.Date   : Date, format: "2016-11-11" "2016-11-11" ...
##  $ Ship.Mode   : Factor w/ 4 levels "First Class",..: 3 3 3 4 4 4 4 4 4 4 ...
##  $ Customer.ID : chr  "CG-12520" "CG-12520" "DV-13045" "SO-20335" ...
##  $ Segment     : Factor w/ 3 levels "Consumer","Corporate",..: 1 1 2 1 1 1 1 1 1 1 ...
##  $ Product.ID  : chr  "FUR-BO-10001798" "FUR-CH-10000454" "OFF-LA-10000240" "FUR-TA-10000577" ...
##  $ Category    : Factor w/ 3 levels "Furniture","Office Supplies",..: 1 1 2 1 2 1 2 3 2 2 ...
##  $ Sub.Category: Factor w/ 17 levels "Accessories",..: 5 6 11 17 15 10 3 14 4 2 ...
##  $ Product.Name: chr  "Bush Somerset Collection Bookcase" "Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back" "Self-Adhesive Address Labels for Typewriters by Universal" "Bretford CR4500 Series Slim Rectangular Table" ...
##  $ Sales       : num  262 731.9 14.6 957.6 22.4 ...
##  $ Quantity    : int  2 3 2 5 2 7 4 6 3 5 ...
##  $ Discount    : num  0 0 0 0.45 0.2 0 0 0.2 0.2 0 ...
##  $ Profit      : num  41.91 219.58 6.87 -383.03 2.52 ...

Chunk Commentary :

check new structure in retail table

Check Missing Value

colSums(is.na(retail))

##       Row.ID     Order.ID   Order.Date    Ship.Date    Ship.Mode  Customer.ID 
##            0            0            0            0            0            0 
##      Segment   Product.ID     Category Sub.Category Product.Name        Sales 
##            0            0            0            0            0            0 
##     Quantity     Discount       Profit 
##            0            0            0

Chunk Commentary :

no missing value in retail table

retail <- retail %>% 
  select(-Row.ID, -Order.ID)

Chunk Commentary :

we erase two column, Row.ID and Order.ID because we dont use it.

DATA EXPLANATION

retail <- retail %>% 
  mutate(PL = ifelse(Profit>= 0, "PROFIT","LOSS"))
table(retail$PL)

## 
##   LOSS PROFIT 
##   1871   8123

Chunk Commentary :

total sku that loss in profit 1871
total sku that get profit 8123

retail.profit <- retail %>% 
  filter(PL == "PROFIT") %>%
  summarize(sum_profit = sum(Profit))

retail.loss <-  retail %>% 
  filter(PL == "LOSS") %>%
  summarize(sum_loss = sum(Profit))
  
x = retail.profit$sum_profit + retail.loss$sum_loss
x

## [1] 286397

Chunk Commentary :

so from this data we get Profit 286.397 if we combine sku with loss and profit

DATA MANIPULATION

retail <- retail %>% 
  mutate(pos_profit = ifelse(Profit< 0, "negative","positive"))

Chunk Commentary :

to mutate new column fill it with negative profit or positive profit refer to profit or loss

retail_profit <- retail %>% 
  select(Sales, Profit,pos_profit, Segment,Category, Sub.Category,Discount) %>% 
  filter(Discount > 0) %>% 
  filter(pos_profit == "positive") %>% 
  mutate(COGS = Sales - Profit, a = 1- Discount, mftdisc = Profit / Sales,sales_bfr = Sales/a) %>% 
  mutate(sales_bfr = formatC(sales_bfr, digits = 4, format = "f"))

head(retail_profit)

##      Sales  Profit pos_profit  Segment        Category Sub.Category Discount
## 1   22.368  2.5164   positive Consumer Office Supplies      Storage      0.2
## 2  907.152 90.7152   positive Consumer      Technology       Phones      0.2
## 3   18.504  5.7825   positive Consumer Office Supplies      Binders      0.2
## 4 1706.184 85.3092   positive Consumer       Furniture       Tables      0.2
## 5  911.424 68.3568   positive Consumer      Technology       Phones      0.2
## 6   15.552  5.4432   positive Consumer Office Supplies        Paper      0.2
##        COGS   a mftdisc sales_bfr
## 1   19.8516 0.8  0.1125   27.9600
## 2  816.4368 0.8  0.1000 1133.9400
## 3   12.7215 0.8  0.3125   23.1300
## 4 1620.8748 0.8  0.0500 2132.7300
## 5  843.0672 0.8  0.0750 1139.2800
## 6   10.1088 0.8  0.3500   19.4400

Chunk Commentary :

this chunk find a profit sku after discount company gift and adding some column adding some column to gain some information:
cogs = cost of good sold
mftdisc = margin after discount
sales_bfr = sales before discount

retail_loss <- retail %>% 
  select(Sales, Profit,pos_profit, Segment,Category, Sub.Category,Discount) %>% 
  filter(Discount > 0) %>% 
  filter(pos_profit == "negative") %>% 
  mutate(hpp = Sales - Profit, a = 1- Discount, mftdisc = Profit / Sales,sales_bfr = Sales/a) %>% 
  mutate(sales_bfr = formatC(sales_bfr, digits = 4, format = "f"))

head(retail_loss)

##       Sales     Profit pos_profit     Segment        Category Sub.Category
## 1  957.5775  -383.0310   negative    Consumer       Furniture       Tables
## 2   68.8100  -123.8580   negative Home Office Office Supplies   Appliances
## 3    2.5440    -3.8160   negative Home Office Office Supplies      Binders
## 4   71.3720    -1.0196   negative    Consumer       Furniture       Chairs
## 5 3083.4300 -1665.0522   negative    Consumer       Furniture    Bookcases
## 6    9.6180    -7.0532   negative    Consumer Office Supplies      Binders
##   Discount       hpp    a     mftdisc sales_bfr
## 1     0.45 1340.6085 0.55 -0.40000000 1741.0500
## 2     0.80  192.6680 0.20 -1.80000000  344.0500
## 3     0.80    6.3600 0.20 -1.50000000   12.7200
## 4     0.30   72.3916 0.70 -0.01428571  101.9600
## 5     0.50 4748.4822 0.50 -0.54000000 6166.8600
## 6     0.70   16.6712 0.30 -0.73333333   32.0600

Chunk Commentary :

this chunk find a loss in profit sku after discount company gift and adding some column

adding some column to gain some information:

cogs = cost of good sold
mftdisc = margin after discount
sales_bfr = sales before discount

max_profit <- max(retail_profit$Profit)
retail_profit1 <- retail_profit %>% 
  select(Sales, Profit,pos_profit, Segment,Category,mftdisc, Sub.Category,Discount) %>% 
  filter(Profit == max_profit)

retail_profit1

##      Sales   Profit pos_profit     Segment   Category mftdisc Sub.Category
## 1 11199.97 3919.989   positive Home Office Technology    0.35      Copiers
##   Discount
## 1      0.2

Chunk Commentary :

Technolgy/copiers has a maximum profit with discount 0.2

max_loss <- min(retail_loss$Profit)
retail_loss1 <- retail_loss %>% 
  select(Sales, Profit,pos_profit, Segment,Category,mftdisc, Sub.Category,Discount) %>% 
  filter(Profit == max_loss)

retail_loss1

##      Sales    Profit pos_profit  Segment   Category   mftdisc Sub.Category
## 1 4499.985 -6599.978   negative Consumer Technology -1.466667     Machines
##   Discount
## 1      0.7

Chunk Commentary :

Technology/Machine has minimum profit with discount 0.7

BUSINESS RECOMENDATION

some information we after ABC.com:

so from this data we get Profit 286.397 despite some SKU got loss
find cost of good sold
find margin after discount
find loss or profit
find category with profit or loss sku

The discount must be less than the initial margin if you want to keep making a profit, by adding Cost of good sold information obtained from Sales - Profit, find out the initial margin by adding this margin in the dataframe that can be used as a benchmark in this company to place a discount in the upcoming discount program. From this data it can be seen that there is a negative profit because the Discount is greater than the initial margin.