H01.1

A small supermarket is trying to determine how many copies of People magazine they should order each week They believe their Demand for People is governed by the following discrete random variable:

Demand 15 20 25 30 35
Probability 10% 20% 30% 25% 15%

The supermarket pays $1.00 for each copy of People and sells it for $1.95. Each unsold copy can be returned for $0.50.

  1. How many copies of People should the store order?
  2. What’s the average profit at that order level?

Input Data

The input data includes the probability distribution for Demand and the constants for magazine cost, selling price, and liquidation for excess. The Average Demand is 25.6 = ‘r’

  DemandDist <- c(15, 20, 25, 30, 35)
  ProbDist <- c(0.10, 0.20, 0.30, 0.25, 0.15)
  # sum(DemandDist*ProbDist)
  
  conCost <- 1
  conPrice <- 1.95
  conLiq <- 0.50

[1] 25.75

Initialize Calculations

Create a sample size 100 from the distribution in the table above. I then create a dataframe with one column of 100 samples of Demand. Finally establish levels of order quantity to analyze.

Demand <- sample(DemandDist,100,prob = ProbDist, replace=TRUE)
df11 <- data.frame(Demand)
OrderQty <- DemandDist

Calculations for profit vs order quantities

For each order quantity investigated, this loop will calculate profit for 100 samples of Demand and add the results to the dataframe.

for (ord in OrderQty) {
  p <- c()
  for (d in Demand) {
    unitSales <- min(ord, d)
    excUnits <- max(ord-d,0)
    Profit <- conPrice*unitSales - conCost*ord + conLiq*excUnits
    p <- append(p,Profit)
  }
  df11 <-cbind(df11,p)
}
colnames(df11) <- c("Demand", "oq15","oq20","oq25","oq30","oq35")

Analyze results

The average profit is maximized at order quantity of 30. When order quantity is at 15, profit is always at $14.50.

Average Profit Results
Demand oq15 oq20 oq25 oq30 oq35
Min. :15.00 Min. :14.25 Min. :11.75 Min. : 9.25 Min. : 6.75 Min. : 4.25
1st Qu.:20.00 1st Qu.:14.25 1st Qu.:19.00 1st Qu.:16.50 1st Qu.:14.00 1st Qu.:11.50
Median :27.50 Median :14.25 Median :19.00 Median :23.75 Median :24.88 Median :22.38
Mean :26.85 Mean :14.25 Mean :18.35 Mean :20.92 Mean :22.05 Mean :21.43
3rd Qu.:35.00 3rd Qu.:14.25 3rd Qu.:19.00 3rd Qu.:23.75 3rd Qu.:28.50 3rd Qu.:33.25
Max. :35.00 Max. :14.25 Max. :19.00 Max. :23.75 Max. :28.50 Max. :33.25

As you can see from the boxplot, as order quantity increases so does the variation between minimum and maximum profit.

H01.2

A GMC dealer believes that demand for Envoys will be normally distributed with a mean of 200 and standard deviation of 30. His cost of receiving an Envoy is $25,000, and he sells an Envoy for $40,000. Half of all the Envoys not sold at full price can be sold for $30,000. He is considering ordering 200, 220, 240, 260, 280, or 300 Envoys. How many should he order?

Inputing our data and setting our dataframe

DemandMean <- 200
DemandSD <- 30

CostDlr <- 25000
SellPrice <- 40000
LiqProp <- 0.50
LiqPrice <- 30000

OrderQty <- c(seq(200,300,by=20))
#  instead of listing the order quantities you can 
#  list the first and last number in the range and increase by 20
df12b <- data.frame()

Caculaiting the Demand, Amount sold, Excess Amount, and Profit

We are using a loop in order to calculate the amounts for each order quantity while running the simulation 500 times

We then place the observations in a data frame that holds the quantities we want to observe

for (ord in OrderQty) {
  set.seed(123)
  df12a <- data.frame(Demand = round(rnorm(500,mean=DemandMean,sd=DemandSD),digits = 0))
  df12a$OrderQty <- ord
  df12a <- transform(df12a, SoldFP = pmin(df12a$Demand, df12a$OrderQty))
  df12a <- transform(df12a, Excess = pmax(df12a$Demand - df12a$OrderQty, 0))
  df12a <- transform(df12a, Profit = SoldFP*SellPrice +
                     round(LiqProp*Excess*LiqPrice,digits = 0) -
                     ord*CostDlr)
  df12b <- rbind(df12b, df12a)
}

Analyze loop observations

Order Quantity Observations
Demand OrderQty SoldFP Excess Profit
Min. :120 Min. :200 Min. :120.0 Min. : 0.000 Min. :-2700000
1st Qu.:183 1st Qu.:220 1st Qu.:183.0 1st Qu.: 0.000 1st Qu.: 780000
Median :201 Median :250 Median :200.0 Median : 0.000 Median : 1780000
Mean :201 Mean :250 Mean :198.0 Mean : 3.071 Mean : 1714425
3rd Qu.:221 3rd Qu.:280 3rd Qu.:215.2 3rd Qu.: 0.000 3rd Qu.: 2780000
Max. :297 Max. :300 Max. :297.0 Max. :97.000 Max. : 4455000

Computing summary statisics for Profit of each order.

Understanding the distribution and central tendency of Profit across different order quantities. We will see insights into sales performance, profitability, and potential outliers.

df12b$OrderQty <- as.factor(df12b$OrderQty)
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
df12b %>%
  group_by(OrderQty) %>%
  summarise(min = min(Profit),
            Q1 = quantile(Profit,0.25),
            median = quantile(Profit,0.50),
            mean = mean(Profit),
            Q3 = quantile(Profit,0.75),
            max = max(Profit)
            )
## # A tibble: 6 × 7
##   OrderQty      min      Q1  median    mean      Q3     max
##   <fct>       <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
## 1 200       -200000 2320000 3015000 2738750 3315000 4455000
## 2 220       -700000 1820000 2540000 2423900 3315000 4455000
## 3 240      -1200000 1320000 2040000 2006900 2840000 4455000
## 4 260      -1700000  820000 1540000 1535450 2340000 4455000
## 5 280      -2200000  320000 1040000 1040350 1840000 4455000
## 6 300      -2700000 -180000  540000  541200 1340000 4380000

Analyze our findings using a box plot

Box plot shows that our average profit decreases as our order quantity increases. Therefore 200 Envoys should be ordered. This is much easeir to understand than just looking at the dataframe