Measuring and Analyzing Business Process
Author - Jesse Normand
kpi <- read.csv('KPI_Scenario.csv')
score <- read.csv('Scorecard_Project.csv')
df <- read.csv('sales_control.csv')
df=read.csv('sales_data.csv')Overview
In this report I will demonstrate how to measure and quantify a business process by :
Key Performance Indicator
Score Card
Process Behavior Charts
Process Simulation
The tools we will be using to conduct this measurement and analysis will be:
R
Excel
Libraries Used in This Project
library(qcc)
library(tidyverse)
library(dplyr)
library(formattable)
library(DT)Business Operations Scenario
In this scenario we have a sales department manager that had reached out to me letting me know that their KPI for # of Widgets Sold is in the red. The KPI is a weekly frequency, and the sales manager is concerned with the effect it will have on the company. We also have a score card set up for the sales department to ensure the our business strategies are being adhered to.
Lets start by looking at the KPI we set up for the sales department.
KPI for Sales Department
Reviewing the KPI dashboard we set up using Flexdashboard, we confirm that the sales for Widgets (1) are indeed not meeting the target threshold of 215.
Flex Dashboard
Below is a snippet of code showing how we were able to take a modify and format a table to utilize it as a dashboard element.We built a function to color the cells according to our KPI logic.
#Create a function for color cell argument
custom_color_tile <- function (x , x_Target = 75) {
formatter("span",
style = x ~ style(display = "block",
padding = "0 4px",
`color` = "white",
`border-radius` = "4px",
`background-color` = ifelse(x >= x_Target, "green", "red")))
}#More formatting including icon set
formattable(kpi,
align = c("l",rep("r", NCOL(kpi) - 1)),
list(`KPI` = formatter("span", style = ~ style(color = "grey", font.weight = "bold")),
area(col = 2, row = 1) ~ custom_color_tile(x_Target = 75),
area(col = 2, row = 2) ~ custom_color_tile(x_Target= 175000),
area(col = 2, row = 3) ~ custom_color_tile(x_Target= .23),
`Target.Achieved` = formatter("span",
x ~ icontext(ifelse(x > 0, "ok", "remove"),
ifelse(x > 0, "Yes", "No")),
style = x ~ style(color = ifelse(x < 0, "red", "green")))))| KPI | Actual | Target | Target.Achieved | Comments |
|---|---|---|---|---|
| Weekly Widgets Sold | 188 | 215 | No | Analyzing missed target results |
| Monthly Revenue | 200,000 | 175,000 | Yes | Revenue is up this month but still within our control limits |
| Profit Margin | 0.24 | 0.23 | Yes | Margin is consistent |
Process Behavior Analysis
Before jumping to any conclusions or hasty reactions as a result of the missed sales target, lets gather some additional context and insight through a behavior chart analysis.
We will run our process behavior chart, also known as control charts, in R. I will explain what we did through the code.
We created a data frame and read our sales data into the frame.
Created an object and extracted a subset of our data into a value.
Next we created a list from value “a”.
Utilizing the ‘qcc’ package, we set up our process behavior environment and created a chart for analysis.
Lets take a look at the chart and results below.
#Control chart function with new data for analysis
a=df$Units
lst=list(a)
qcc(a[1:28], type = "xbar.one", newdata = a[29:30])
Process Behavior Chart Analysis
We see that we have had a nice consistent process in the sales department, and the fluctuations in sales are within control, and what we would expect to be caused by normal random noise. However, it is clear that we have a violating behavior point which coincides with the KPI results we are analyzing.
This one point outside of the limit does not verify a trend yet, hence we have zero violating runs; however, it does point to an existing out of control scenario.
One measurement outside of the limits typically points to a defined input such as human error, a missed step in a process, a mistake in QC. These types of inputs can be identified and resolved before a trend is validated.
With this new discovery insight, lets continue to drill down and take a look at the sales department score card to see if any steps in the business process have been unaccounted for.
Scorecard
Well here we go! While it is encouraging to see most of the targets have been met in the sales department, we have a glaring issue with the prescribed sales training sessions. We have a young and ambitious sales team; however, due to their junior level experience we knew consistent training sessions would ensure the growth needed to achieve our sales goals.
Three missed training sessions is a broken process, and at this point could be our correlating input with the missed sales goals for this month.
####Create a function for color cell argument
custom_color_tile <- function (x , x_Target = 75) {
formatter("span",
style = x ~ style(display = "block",
padding = "0 4px",
`color` = "white",
`border-radius` = "4px",
`background-color` = ifelse(x >= x_Target, "green", "red")))
}#Change values
score[3, 2] = '90'
score[4, 1] = 'Closed'
#Use our function to add color conditioning based on argument.
formattable(score,
align = c("l",rep("r", NCOL(score) - 1)),
list(`KPI` = formatter("span", style = ~ style(color = "grey", font.weight = "bold")),
area(col = 2, row = 1) ~ custom_color_tile(x_Target = 20),
area(col = 2, row = 2) ~ custom_color_tile(x_Target= 30),
area(col = 2, row = 3) ~ custom_color_tile(x_Target= 84),
area(col = 2, row = 4) ~ custom_color_tile(x_Target= 50),
area(col = 2, row = 5) ~ custom_color_tile(x_Target= 4),
`Target.Achieved` = formatter("span",
x ~ icontext(ifelse(x > 0, "ok", "remove"), ifelse(x > 0, "Yes", "No")),
style = x ~ style(color = ifelse(x > 0, "green", "red")))))| KPI | Actual | Target |
|---|---|---|
| Prospects identified | 25 | 20 |
| Proposals pending | 34 | 30 |
| Number of calls made | 90 | 84 |
| Closed | 55 | 50 |
| MonthlyTraining Sessions | 1 | 4 |
Monte Carlo Simulation
Before speaking with management about our findings and summary, lets run a revenue and profit simulation based on the new expected revenue as a result of the missed sales target for this month.
The information gained in this simulation will allow us to provide additional context and guidance to our stakeholders so that rational decisions and preparations can be made accordingly.
What we find after running our profit simulation is that we are now at an 8% probability risk of running a loss for this month.
The box plot provides additional transparency and confirms our profit variance runs below zero dollars.
results = NULL
#Set up simulation model
{revenue = runif(500,155000, 182500)
fixed_cost = runif(500,52000,57000)
variable_cost = runif(500,100000,105000)
results <- data.frame(revenue, fixed_cost,variable_cost)
}
#Calculate and add in profit column into data set. Compute risk loss score.
results$profit <- with(results, revenue - fixed_cost - variable_cost)
x <- sum(results$profit < 0) / 500 *100
message("Risk Loss Score Percent: ", x)## Risk Loss Score Percent: 6.8 summary(results$profit)## Min. 1st Qu. Median Mean 3rd Qu. Max.
## -5677 4677 11436 11671 18796 28512#boxplot to analyze distribution
ggplot(results, aes(x = 1:500, y = results$profit)) +
geom_boxplot()
Business Analysis Conclusion
In summary we:
Captured measurable data through our KPI dashboard and scorecard.
Utilized a process behavior chart to confirm we had a special cause in our business process that needed to be analyzed.
Discovered that our training process target was not met, and concluded the lack of sales training had a direct effect on our sales objectives.
We ran a profit simulation through 500 trials and concluded that we now have an 8% risk loss probability due to the missed sales target.