H02.1

It is equally likely that annual unit sales for Widgetco’s widgets will be low or high. If sales are low (60,000 units), the company can sell the product for $10 per unit. If sales are high (100,000 units), a competitor will enter, and Widgetco can sell the product for only $8 per unit. The variable cost per unit has a 25% chance of being $6, a 50% chance of being $7.50, and a 25% chance of being $9. Annual fixed costs are $30,000. Use simulation to estimate Widgetco’s expected annual profit.

Sales (units) 60,000 100,000
Probability 50% 50%
Price $10.00 $8.00
Var Cost per unit $6.00 $7.50 $9.00
Probability 25% 50% 25%
  1. Set up a simulation to estimate annual Profit.
  2. Run 1000 replications of your simulation.
  3. Calculate the average profit and a 95% confidence interval for profit.

Input Libraries that will be used

library(stats)
library(ggplot2)

Input Variables

These will be used in following calculations

SalesDist <- c(60000, 100000)
SalesProbDist <- c(0.5, 0.5)
PriceDist <- c(10, 8)

VCDist <- c( 6.00, 7.50, 9.00 )
VCProbDist <- c(0.25, 0.50, 0.25)

conFixedCost <- 30000

Creating a dataset

Created a simulated dataset of 1000 sales transactions, each with associated sales figures, a binary pricing strategy, variable costs, and calculates the resulting profit for each transaction.

df21 <- data.frame(sales=sample(SalesDist, 1000, replace = TRUE,prob= SalesProbDist))
  # Creating the Initial Data Frame
df21 <- transform(df21, price = ifelse(sales==60000, 10, 8))
  #Adding a Price Column Based on Sales
df21 <- cbind(df21, VarCost = sample(VCDist, 1000, replace = TRUE,prob= VCProbDist))
  #Adding a Variable Cost Column
df21 <- cbind(df21, Profit = df21$sales*(df21$price-df21$VarCost)-conFixedCost)
  #Calculating and Adding a Profit Column

Analyze average profit results

sales price VarCost Profit
Min. : 60000 Min. : 8.000 Min. :6.000 Min. :-130000
1st Qu.: 60000 1st Qu.: 8.000 1st Qu.:6.000 1st Qu.: 20000
Median :100000 Median : 8.000 Median :7.500 Median : 120000
Mean : 80040 Mean : 8.998 Mean :7.506 Mean : 69060
3rd Qu.:100000 3rd Qu.:10.000 3rd Qu.:9.000 3rd Qu.: 170000
Max. :100000 Max. :10.000 Max. :9.000 Max. : 210000

Box plot for average profit results

Box plot for analyzing Sales vs Profits

Shows us that average profits seem to be higher for 60000

Calcullating confidence intervals for expected average profit.

This code performs a t-test to evaluate whether the sample mean of Profit is significantly different from zero, and then extracts the lower and upper bounds of the confidence interval around the sample mean.

x <- t.test(df21$Profit)
x$conf.int[1]
## [1] 62681.37
x$conf.int[2]
## [1] 75438.63
   # by default, 95% confidence is used unless specified otherwise

Lower Bound = $61,073.09 Upper Bound = $73,846.91

H02.2

Bottleco produces six-packs of soda cans. Each can is supposed to contain at least 12 ounces of soda. If the total weight in a six-pack is under 72 ounces, Bottleco is fined $100 and receives no sales revenue for the six-pack. Each six-pack sells for $3.00. It costs Bottleco $0.02 per ounce of soda put in the cans. Bottleco can control the target fill rate of its soda-filling machines. The amount each can actually fills is normally distributed around the target setting with a standard deviation of 0.10 ounces. What target fill rate maximizes expected profit per six-pack?

  1. Setup a simulation to model producing a 6pk and selling it.
  2. Replicate your model 1000 times.
  3. What target fill rate would you recommend within 0.05 ounce)?
  4. Graph your results (profit vs fill rates).

Install Libraries

library(ggplot2)

Set up variables

conSixPackCans <- 6 # cans
conMin6PckWt <- 72 # ounces
conFine <- 100 # dollars if under 72 oz/pack
conCost <- 0.02 # per ounce soda used
conPrice <- 3.00 # per six-pack
conFillSD <- 0.10 # ounces

Create Fill Rate Sequence and Initialize Data Frames

fillRate <- seq(12.05,12.50,0.05)
df22anames <- c()

df22a <- data.frame(1:1000)

Use loop to simulate fill rate and Set data frames

for (f in fillRate) {
  ttl6pkWt <- c()
  set.seed(123)
  for (i in 1:1000) {
    sixIndCans <- rnorm(6,mean=f, sd=conFillSD)
    ttl6pkWt <- append(ttl6pkWt,sum(sixIndCans))
  }
  df22a <- cbind(df22a, ttl6pkWt)
  df22anames <- c(df22anames,paste0("fr",f))
}
df22a <- df22a[,-1]
colnames(df22a) <- df22anames

Calculate Profit Loss

This loop calculates the profit loss for each fill rate. With this we can see that fill rate 12.5 is expected to return the least amount of loss.

df22b <- data.frame(matrix(ncol = ncol(df22a), nrow = nrow(df22a)))

for (i in 1:ncol(df22a))
  for (j in 1:nrow(df22a)) {
    if (df22a[j,i] < conMin6PckWt) {
      df22b[j,i] <- 0 - conFine - df22a[j,i]*conCost
    } else {
      df22b[j,i] <- conPrice - df22a[j,i]*conCost
    }
  }
colnames(df22b) <- df22anames

Analyze output from the loop

This output shows 12.15 being the better choice because it has the 3rd highest profit while not having a tremendous loss.

library(knitr)
kable(summary(df22b))
fr12.05 fr12.1 fr12.15 fr12.2 fr12.25 fr12.3 fr12.35 fr12.4 fr12.45 fr12.5
Min. :-101.440 Min. :-101.440 Min. :1.530 Min. :1.524 Min. :1.518 Min. :1.512 Min. :1.506 Min. :1.500 Min. :1.494 Min. :1.488
1st Qu.: 1.549 1st Qu.: 1.545 1st Qu.:1.539 1st Qu.:1.533 1st Qu.:1.527 1st Qu.:1.521 1st Qu.:1.515 1st Qu.:1.509 1st Qu.:1.503 1st Qu.:1.497
Median : 1.553 Median : 1.548 Median :1.542 Median :1.536 Median :1.530 Median :1.524 Median :1.518 Median :1.512 Median :1.506 Median :1.500
Mean : -9.158 Mean : 1.136 Mean :1.542 Mean :1.536 Mean :1.530 Mean :1.524 Mean :1.518 Mean :1.512 Mean :1.506 Mean :1.500
3rd Qu.: 1.556 3rd Qu.: 1.551 3rd Qu.:1.545 3rd Qu.:1.539 3rd Qu.:1.533 3rd Qu.:1.527 3rd Qu.:1.521 3rd Qu.:1.515 3rd Qu.:1.509 3rd Qu.:1.503
Max. : 1.560 Max. : 1.560 Max. :1.555 Max. :1.549 Max. :1.543 Max. :1.537 Max. :1.531 Max. :1.525 Max. :1.519 Max. :1.513

Bar chart showing mean profit or loss by fill rate