Portland Plant KPI Tracker

Portland KPI Report

Load required libraries

# Load required libraries
library(readxl)        
library(dplyr)         

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(ggplot2)       
library(tidyr)         
library(stringr)       
library(scales)        
library(gridExtra)     

## 
## Attaching package: 'gridExtra'

## The following object is masked from 'package:dplyr':
## 
##     combine

library(knitr)        
library(rmarkdown)    
library(lubridate)     

## 
## Attaching package: 'lubridate'

## The following objects are masked from 'package:base':
## 
##     date, intersect, setdiff, union

library(RColorBrewer)  

Load requiered excel data base

# Load the dataset
bd <-read_excel("/Users/sebastianespi/Downloads/DATA BASE sebastian - KPI portalnd report.xlsx")
bd

## # A tibble: 135 × 8
##     Week Name    Category   Acknowledgements `Late Ack >3days` `TTS < 10 days`
##    <dbl> <chr>   <chr>                 <dbl>             <dbl>           <dbl>
##  1    25 Team    Percentage               18                10              61
##  2    25 Ivey    Percentage               19                10              16
##  3    25 Corrina Percentage               16                89               4
##  4    26 Team    QTY                     319                30              56
##  5    26 Team    Percentage               17                 9               2
##  6    26 Ivey    QTY                     195                20              17
##  7    26 Ivey    Percentage               19                10               1
##  8    26 Corrina QTY                     124                10              39
##  9    26 Corrina Percentage               16                 8               3
## 10    27 Team    QTY                     348                30              56
## # ℹ 125 more rows
## # ℹ 2 more variables: `Invoice past due` <dbl>, `W-OTD (OOR)` <chr>

Exploratory Analisis of the data base

# Clean the dataset - your data is already in the right format
bd_clean <- bd %>%
  mutate(
    # Convert percentage columns to numeric (already clean numeric values)
    `Acknowledgements_%` = case_when(
      Category == "Percentage" ~ as.numeric(`Acknowledgements`),
      TRUE ~ NA_real_
    ),
    
    `Late Ack >3days_%` = case_when(
      Category == "Percentage" ~ as.numeric(`Late Ack >3days`),
      TRUE ~ NA_real_
    ),
    
    `TTS < 10 days_%` = case_when(
      Category == "Percentage" ~ as.numeric(`TTS < 10 days`),
      TRUE ~ NA_real_
    ),
    
    `Invoice past due_%` = case_when(
      Category == "Percentage" ~ as.numeric(`Invoice past due`),
      TRUE ~ NA_real_
    ),
    
    `W-OTD (OOR)_%` = case_when(
      Category == "Percentage" ~ as.numeric(`W-OTD (OOR)`),
      TRUE ~ NA_real_
    ),
    
    # Convert quantity columns to numeric
    `Acknowledgements_QTY` = case_when(
      Category == "QTY" ~ as.numeric(`Acknowledgements`),
      TRUE ~ NA_real_
    ),
    
    `Late Ack >3days_QTY` = case_when(
      Category == "QTY" ~ as.numeric(`Late Ack >3days`),
      TRUE ~ NA_real_
    ),
    
    `TTS < 10 days_QTY` = case_when(
      Category == "QTY" ~ as.numeric(`TTS < 10 days`),
      TRUE ~ NA_real_
    ),
    
    `Invoice past due_QTY` = case_when(
      Category == "QTY" ~ as.numeric(`Invoice past due`),
      TRUE ~ NA_real_
    ),
    
    # Parse W-OTD (OOR) quantity (numerator/denominator format)
    `W-OTD (OOR)_Numerator` = case_when(
      Category == "QTY" & str_detect(`W-OTD (OOR)`, "/") ~ as.numeric(str_extract(`W-OTD (OOR)`, "^[0-9]+")),
      TRUE ~ NA_real_
    ),
    `W-OTD (OOR)_Denominator` = case_when(
      Category == "QTY" & str_detect(`W-OTD (OOR)`, "/") ~ as.numeric(str_extract(`W-OTD (OOR)`, "[0-9]+$")),
      TRUE ~ NA_real_
    )
  )

## Warning: There was 1 warning in `mutate()`.
## ℹ In argument: `W-OTD (OOR)_% = case_when(...)`.
## Caused by warning:
## ! NAs introduced by coercion

# Separate data by category (QTY vs Percentage)
bd_percentages <- bd_clean %>% 
  filter(Category == "Percentage") %>%
  select(Week, Name, `Acknowledgements_%`, `Late Ack >3days_%`, `TTS < 10 days_%`, 
         `Invoice past due_%`, `W-OTD (OOR)_%`)

bd_quantities <- bd_clean %>% 
  filter(Category == "QTY") %>%
  select(Week, Name, `Acknowledgements_QTY`, `Late Ack >3days_QTY`, `TTS < 10 days_QTY`, 
         `Invoice past due_QTY`, `W-OTD (OOR)_Numerator`, `W-OTD (OOR)_Denominator`)

# Create color palette for consistent theming
team_colors <- c("Team" = "#2E86AB", "Ivey" = "#A23B72", "Corrina" = "#F18F01")

GRAPH 1: Acknowledgements Percentage Analysis

graph_ack_pct <- ggplot(bd_percentages, aes(x = Week, y = `Acknowledgements_%`, color = Name)) +
  geom_line(linewidth = 2, alpha = 0.8) +
  geom_point(size = 5) +
  scale_color_manual(values = team_colors) +
  scale_y_continuous(limits = c(0, 100), breaks = seq(0, 100, 10)) +
  labs(
    title = "Acknowledgements Rate Analysis by Team Member",
    subtitle = "Weekly acknowledgement percentage performance (Week 25-42)",
    x = "Week Number",
    y = "Acknowledgements Rate (%)",
    color = "Team Member",
    caption = "Higher percentages indicate better acknowledgement performance"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_ack_pct)

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_line()`).

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_point()`).

Acknowledgements Rate: This graph shows the efficiency in processing acknowledgements for each team member. Higher percentages indicate better performance in acknowledgement processing.

GRAPH 2: Late Acknowledgements (>3 days) Percentage Analysis

graph_late_pct <- ggplot(bd_percentages, aes(x = Week, y = `Late Ack >3days_%`, color = Name)) +
  geom_line(linewidth = 2, alpha = 0.8) +
  geom_point(size = 5) +
  scale_color_manual(values = team_colors) +
  scale_y_continuous(limits = c(0, 100), breaks = seq(0, 100, 10)) +
  labs(
    title = "Late Acknowledgements Rate Analysis (>3 days)",
    subtitle = "Percentage of acknowledgements delayed by more than 3 days",
    x = "Week Number",
    y = "Late Acknowledgements Rate (%)",
    color = "Team Member",
    caption = "Lower percentages indicate better performance"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_late_pct)

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_line()`).

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_point()`).

Late Acknowledgements (>3 days): This graph tracks delays in acknowledgement processing, where lower percentages are better. Spikes in this graph may indicate system issues, increased volume, or process bottlenecks.

GRAPH 3: Invoice Past Due Percentage Analysis

graph_invoice_pct <- ggplot(bd_percentages, aes(x = Week, y = `Invoice past due_%`, color = Name)) +
  geom_line(linewidth = 2, alpha = 0.8) +
  geom_point(size = 5) +
  scale_color_manual(values = team_colors) +
  scale_y_continuous(limits = c(0, 100), breaks = seq(0, 100, 10)) +
  labs(
    title = "Invoice Past Due Rate Analysis",
    subtitle = "Percentage of invoices that are past due",
    x = "Week Number",
    y = "Invoice Past Due Rate (%)",
    color = "Team Member",
    caption = "Lower percentages indicate better invoice management"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_invoice_pct)

## Warning: Removed 5 rows containing missing values or values outside the scale range
## (`geom_line()`).

## Warning: Removed 5 rows containing missing values or values outside the scale range
## (`geom_point()`).

Invoice Past Due: This graph monitors billing and payment management, where lower percentages are better. High percentages may suggest billing process issues, customer payment problems, or credit management challenges that require immediate attention.

Please note that Week 36 and Week 42 show a low drop because I didn’t received the RSO Excel file from Guru during that time, which caused the lower recorded drop.

GRAPH 4: W-OTD (OOR) Percentage Analysis

graph_wotd_pct <- ggplot(bd_percentages, aes(x = Week, y = `W-OTD (OOR)_%`, color = Name)) +
  geom_line(linewidth = 2, alpha = 0.8) +
  geom_point(size = 5) +
  scale_color_manual(values = team_colors) +
  scale_y_continuous(limits = c(0, 100), breaks = seq(0, 100, 10)) +
  labs(
    title = "W-OTD (OOR) Performance Analysis",
    subtitle = "Open Order Report / On-Time Delivery percentage",
    x = "Week Number",
    y = "W-OTD (OOR) Rate (%)",
    color = "Team Member",
    caption = "Higher percentages indicate better order management"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_wotd_pct)

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_line()`).

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_point()`).

W-OTD (OOR) Performance: This graph tracks open order reports and on-time delivery performance, where higher percentages indicate better order management and delivery success.

GRAPH 5: Acknowledgements Quantity Analysis

graph_ack_qty <- ggplot(bd_quantities, aes(x = Week, y = `Acknowledgements_QTY`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "Acknowledgements Volume Analysis by Team Member",
    subtitle = "Weekly quantity of acknowledgements processed",
    x = "Week Number",
    y = "Number of Acknowledgements",
    fill = "Team Member",
    caption = "Shows actual workload and processing volume"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_ack_qty)

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_col()`).

Graphs 7-9 - Volume Analysis: These bar charts show actual workload volumes for each team member, helping understand the scale of operations and workload distribution

GRAPH 6: Late Acknowledgements Quantity Analysis

graph_late_qty <- ggplot(bd_quantities, aes(x = Week, y = `Late Ack >3days_QTY`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "Late Acknowledgements Volume Analysis (>3 days)",
    subtitle = "Number of acknowledgements delayed by more than 3 days",
    x = "Week Number",
    y = "Number of Late Acknowledgements",
    fill = "Team Member",
    caption = "Shows actual volume of delayed acknowledgements"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_late_qty)

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_col()`).

GRAPH 7: Time to Ship Quantity Analysis

graph_tts_qty <- ggplot(bd_quantities, aes(x = Week, y = `TTS < 10 days_QTY`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "Time to Ship Volume Analysis (<10 days)",
    subtitle = "Number of orders shipped within 10 days",
    x = "Week Number",
    y = "Number of Orders Shipped <10 days",
    fill = "Team Member",
    caption = "Shows actual volume of fast shipments"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )

print(graph_tts_qty)

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_col()`).

Time to Ship (<10 days): This graph measures shipping efficiency, where higher numbers mean more orders are shipped within 10 days, which is excellent for customer satisfaction.

GRAPH 8: Invoice Past Due Quantity Analysis

graph_invoice_qty <- ggplot(bd_quantities, aes(x = Week, y = `Invoice past due_QTY`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "Invoice Past Due Volume Analysis",
    subtitle = "Number of invoices that are past due",
    x = "Week Number",
    y = "Number of Past Due Invoices",
    fill = "Team Member",
    caption = "Shows actual volume of overdue invoices"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )
print(graph_invoice_qty)

## Warning: Removed 6 rows containing missing values or values outside the scale range
## (`geom_col()`).

GRAPH 9: W-OTD (OOR) Volume Analysis

graph_wotd_qty <- ggplot(bd_quantities, aes(x = Week, y = `W-OTD (OOR)_Numerator`, fill = Name)) +
  geom_col(position = "dodge", alpha = 0.8, width = 0.7) +
  scale_fill_manual(values = team_colors) +
  scale_y_continuous(labels = comma_format()) +
  labs(
    title = "W-OTD (OOR) Open Orders Volume Analysis",
    subtitle = "Number of open orders reported by team member",
    x = "Week Number",
    y = "Number of Open Orders",
    fill = "Team Member",
    caption = "Shows actual volume of open orders"
  ) +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 16, face = "bold"),
    plot.subtitle = element_text(size = 12, color = "gray60"),
    legend.position = "bottom",
    panel.grid.minor = element_blank(),
    axis.text = element_text(size = 10)
  )
print(graph_wotd_qty)

## Warning: Removed 3 rows containing missing values or values outside the scale range
## (`geom_col()`).

Thank you for your time reviewing this SE KPI Portland Plant Report 2025.