Exercise: Sales Forecast using MCM

Background

Often, Monte Carlo simulation can come in handy to calculate risk or evaluate investments in projects. This is a simple demonstration.

Exercise

The following provides the breakdown of profit made by a business unit. All metrics are measured in daily basis.

Profit = Income - Expenses

    Income = Sales (S) * Profit Margin per Sale (M)
    
        M assumes an uniform dist. from $350 to $400
    
        S = Number of Leads (L) * Conversion Rate (R)
    
             L assumes an uniform dist. with from 3000 to 4000
             
             R assumes a normal dist. with mean of 4% and sd of 0.5%
        
    Expenses = Fixed Overhead (H) + Total Cost of the Leads (C)
    
        C = Cost Per Lead (Cpl) * Number of Leads (L)
        
            Cpl assumes an uniform dist. from $8 to $10
    
        H assumes a constant of $20000
        

In summary,

Profit = Leads * Conversion Rate * Profit Margin per Sale - (Cost per Lead * Leads + Fixed Overhead)

Profit Forecast Model

An oversimplified daily profit forecast model,

If we set a profitability goal of $100,000 a month, what is the probability that we achieve that? How about the probability that we lose money?

## [1] "Probability of hitting goal is 4%"

## [1] "Probability of incurring losses is 22%"

We can also plot the cumulative probability for clearer visualization.

Update Model

What if we further assume that cost per lead and conversion rate are correlated?

## [1] "Probability of hitting goal is 21%"

## [1] "Probability of incurring losses is 36%"

Sensitivity Analysis

What if we are offered an option to increase our leads at the cost of fixed overheads increase?

## [1] "Probability of hitting goal is 18%"

## [1] "Probability of incurring losses is 51%"

Finding Optimal

What is the maximum cost per lead we can accept if we wish to cover our probability of losses at X%?

## [1] "Maximum cost per lead allowed to reduce risk down to 0.05 is $9.4"