Modelling Uncertainty-: Monte Carlo simulations using Excel and Python

Project Objective-: Setting up a Monte Carlo simulation for calculating the distribution of EBITDA under varying assumptions for
1. COGS
2. Selling Price
3. Units/Volume
4. SG&A

Some background-: A typical business application is forecasting sales/margin and setting targets. Any such model would take as inputs the Units or volume, the expected COGS, the Selling price and the SG&A expense. However each of these quantities can and does vary. It is better to get a distribution of EBITDA rather than anchoring on a point estimate. Using a distribution you can also build confidence intervals.

Excel Model setup

The excel model is set up as follows. The first sheet is set up with the model parameters and an input cell for the number of simulations.

fig 1- Inputs for excel Model

• We will model the Selling price and the COGS as normal distributions, with the mean/average and the standard deviation as shown above
• The unit sales is modelled as a triangular distribution. A triangular distribution is a useful way to model variables that are skewed and cannot be modelled well using a normal distribution. In this example the most frequent value of sales (the Mode) is 2,000 with a low of 700 and a max of 6,000 units
• The SG&A variable is calculated as a percentage of revenue and set up to vary between 2–5%

fig 2-: Excel Model

The key parts of the excel model are shown here. The model shows the Units, the average selling price ,Revenue, COGS and the SG&A expenses. We use python to create distributions for

1. Units (Triangular distribution Min-700, Mode-2,000, Max-6,000)
2. Avg Selling price (Normal Distribution Mean-20 Std dev-2)
3. COGS (Normal Distribution Mean-14 Std Dev-1)
4. SG&A (Uniform Distribution Min- 2% of Revenue, Max- 5% of Revenue)

Python Script setup

Import Necessary Modules

import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
import numpy as np

We use xlwings for manipulating the data in excel and for using excel as front end for python output. Matplotlib is the visualization library and numpy arrays are what we use to create the various distributions for the variables

Open work book and connect to sheets

wb=xw.Book('Monte_carlo.xlsx')
sheet=wb.sheets[0]
sheet1=wb.sheets[1]

In this step we use xl wings to connect to the Monte Carlo workbook. We instantiate 2 sheet objects connecting to sheet[0] (model parameters) and sheet[1] the actual model.

Reading input values into python from the Model Parameters sheet

n_sims=sheet.range('B2').value
Sp_avg=sheet.range('B3').value
Sp_std=sheet.range('B4').value
COGS_avg=sheet.range('B6').value
COGS_std=sheet.range('B7').value
SG_A=sheet.range('B9').value
Mode_units=sheet.range('B14').value
Low_units=sheet.range('B15').value
High_units=sheet.range('B16').value
sims=int(n_sims)

In this step we go to the Model parameters sheet and read in

1. n_sims-: The number of simulations. By default set to 1000
2. Selling Price parameters-: Normally distributed. Can change the values in cells B3 and B4 to see changes in the
3. COGS-: Normally distributed. Values can be changed in cells B6 and B7
4. SG&A-: Uniformly distributed. cell B9
5. Sales units-: Triangular distribution. cells B14 to B16

Run simulation-:

fig3-: Run simulation

Key steps

1. Set up an empty numpy array with size equal to the sims parameter read in from the excel
2. Run a loop equal to the number of sims
3. Change the values for the parameters in the cell ranges using the distribution parameters read in the earlier step
4. Store the results of the iteration in the res or results array. This array will have a 1,000 values of EBITDA for different combinations of the input variables

Results-:

We can now plot the results array to get a distribution for the EBITDA

#Create a histogram of the resulting distribution
fig=plt.figure(figsize=(10,5))
plt.hist(res,bins=20)
plt.xlabel('Net Income',fontsize=14)
plt.ylabel('Number of sims',fontsize=14)
plt.title('Distribution of Net Income',fontsize=14)
plt.show()
plot=sheet.pictures.add(fig,update=True,name='Distribution')
plot.left=sheet.range('H3').left
plot.top=sheet.range('H3').top
#Calculate statistics for the EBITDA variable
sheet.range('K27').value=np.median(res)
sheet.range('K28').value=np.mean(res)
sheet.range('K29').value=np.std(res)

1. We create a figure of the appropriate size (width=10 and height=5)
2. We visualize the results as a histogram with 20 bins. We use the results array res created in the previous step.
3. We then calculate the appropriate statistics for the EBITDA distribution across 1,000 simulations

When we visualize the results we see that the EBITDA distribution is skewed with the mean value at $236K and the median at $221K. We can use the median value of $220K as the most likely estimate and set median+1SD as a stretch goal around $310K.

Links

Code at Github

https://github.com/gmehra123/PROJECTS/tree/master/Monte_Carlo