This Monte Carlo Simulation model is based on a wonderful Turnover Model that calculates the costs of staff leaving an organization and that of replacing them.

The model was developed by Fahim Fahim Lashkaria, the Content Manager and Partner of ExcelDataPro. It is detailed on this page site:

https://exceldatapro.com/employee-turnover-cost-calculator/

It is available for free download.

Click Here to download the Excel workbook for the model as well as a workbook containing animation of the BetaPert and its inverse.

I adapted the model developed by Fahim to include some more input variables to be simulated. The Excel workbook contains credits to Excel DataPro. It contains a static model for the calculation of the total cost of Turnover.

On the STATIC Turnover Cost worksheet, a static model is shown as per Fahim’s general structure. This is followed by the simulated model, a Results and a Parameters worksheet.

The cells follow the color code used in the two Monte Carlo Simulation books:

Yellow: constants or fixed values

Green: input variables to be randomly extracted from some distribution

White: cells that are calculated using values in other cells.

All cells have their formulas shown next to them.

The static and simulated models are broken down into 8 sections:

A) Contains some parameters to be used in the model. Not all of them are in Section A as some are indicated in Sections G and H.

B) Cost to cover the vacant position

C) Cost to fill the post (up to joining)

D) Cost of training and onboarding the new recruit

E) Cost of lost productivity due to “freshness” of new recruit

F) Overall turnover calculations. The cell to be monitored is the Estimated Turnover Cost per Year in D38.

G) Contains general constants such as workdays per year, work hours per day, etc.

H) Contains general parameters to be used in the final estimate. All of these are to be randomized.

The Simulated Turnover Cost Worksheet

This worksheet is identical to the Static model with all the green cells replaced by RAND() based functions.

The green cells in the Static model are now replaced by light color cells showing the distributions used:

Blue cells use the BetaPert distribution

Orange cells use the Normal distribution

Green cells use the Uniform distribution.

The parameters for all these cells are found in the PRM worksheet.

Note that we use 5 and 2 for alpha and beta respectively as we wish the BetaPert distribution to be skewed to the right. This is because in many cases, the estimates above the mode are fewer than those below the mode. Lower blocks in the PRM worksheet give examples of the 3 distributions.

The Simulated Turnover Cost worksheet includes the output results using WHAT IF. Starting in Cell B42, we have 2000 values of the estimated cost of turnover.

The Results Worksheet

These values are copy pasted as “values” in the RESULTS worksheet.

The rest of the calculations follow the general procedures used in the two Monte Carlo Simulation books.