Notebook to accompany article on Practical Business Python
In [1]:
import pandas as pd
import numpy as np
import seaborn as sns
In [2]:
sns.set_style('whitegrid')
In [3]:
# Define the variables for the Percent to target based on historical results
avg = 1
std_dev = .1
num_reps = 500
num_simulations = 1000
In [4]:
# Show an example of calculating the percent to target
pct_to_target = np.random.normal(avg, std_dev, num_reps).round(2)
In [5]:
pct_to_target[0:10]
Out[5]:
In [6]:
# Another example for the sales target distribution
sales_target_values = [75_000, 100_000, 200_000, 300_000, 400_000, 500_000]
sales_target_prob = [.3, .3, .2, .1, .05, .05]
sales_target = np.random.choice(sales_target_values, num_reps, p=sales_target_prob)
In [7]:
sales_target[0:10]
Out[7]:
In [8]:
# Show how to create the dataframe
df = pd.DataFrame(index=range(num_reps), data={'Pct_To_Target': pct_to_target,
'Sales_Target': sales_target})
df.head()
Out[8]:
In [9]:
# Simple histogram to confirm distribution looks as expected
df['Pct_To_Target'].plot(kind='hist', title='Historical % to Target Distribution')
Out[9]:
In [10]:
# Look at the sales target distribution
df['Sales_Target'].plot(kind='hist', title='Historical Sales Target Distribution')
Out[10]:
In [11]:
# Back into the actual sales amount
df['Sales'] = df['Pct_To_Target'] * df['Sales_Target']
In [12]:
def calc_commission_rate(x):
""" Return the commission rate based on the table:
0-90% = 2%
91-99% = 3%
>= 100 = 4%
"""
if x <= .90:
return .02
if x <= .99:
return .03
else:
return .04
In [13]:
df['Commission_Rate'] = df['Pct_To_Target'].apply(calc_commission_rate)
In [14]:
df.head()
Out[14]:
In [15]:
# Calculate the commissions
df['Commission_Amount'] = df['Commission_Rate'] * df['Sales']
In [16]:
df.head()
Out[16]:
In [17]:
print(df['Sales'].sum(), df['Commission_Amount'].sum(), df['Sales_Target'].sum())
In [18]:
df.describe()
Out[18]:
In [19]:
# Define a list to keep all the results from each simulation that we want to analyze
all_stats = []
# Loop through many simulations
for i in range(num_simulations):
# Choose random inputs for the sales targets and percent to target
sales_target = np.random.choice(sales_target_values, num_reps, p=sales_target_prob)
pct_to_target = np.random.normal(avg, std_dev, num_reps).round(2)
# Build the dataframe based on the inputs and number of reps
df = pd.DataFrame(index=range(num_reps), data={'Pct_To_Target': pct_to_target,
'Sales_Target': sales_target})
# Back into the sales number using the percent to target rate
df['Sales'] = df['Pct_To_Target'] * df['Sales_Target']
# Determine the commissions rate and calculate it
df['Commission_Rate'] = df['Pct_To_Target'].apply(calc_commission_rate)
df['Commission_Amount'] = df['Commission_Rate'] * df['Sales']
# We want to track sales,commission amounts and sales targets over all the simulations
all_stats.append([df['Sales'].sum().round(0),
df['Commission_Amount'].sum().round(0),
df['Sales_Target'].sum().round(0)])
In [20]:
results_df = pd.DataFrame.from_records(all_stats, columns=['Sales', 'Commission_Amount', 'Sales_Target'])
In [21]:
results_df.describe().round(0).style.format('{:,}')
Out[21]:
In [22]:
results_df['Commission_Amount'].plot(kind='hist', title="Total Commission Amount")
Out[22]:
In [23]:
results_df['Sales'].plot(kind='hist')
Out[23]: