Case Review

## Global options
knitr::opts_chunk$set(echo = FALSE, cache = FALSE)
require(ggplot2)
## Loading required package: ggplot2
require(dplyr)
## Loading required package: 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
require(glue)
## Loading required package: glue
require(kableExtra)
## Loading required package: kableExtra
## 
## Attaching package: 'kableExtra'
## The following object is masked from 'package:dplyr':
## 
##     group_rows
require(gt)
## Loading required package: gt
require(readxl)
## Loading required package: readxl
require(zoo)
## Loading required package: zoo
## 
## Attaching package: 'zoo'
## The following objects are masked from 'package:base':
## 
##     as.Date, as.Date.numeric
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ lubridate 1.9.4     ✔ tibble    3.3.0
## ✔ purrr     1.1.0     ✔ tidyr     1.3.1
## ✔ readr     2.1.5     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter()          masks stats::filter()
## ✖ kableExtra::group_rows() masks dplyr::group_rows()
## ✖ dplyr::lag()             masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
## 
## Attaching package: 'scales'
## 
## 
## The following object is masked from 'package:purrr':
## 
##     discard
## 
## 
## The following object is masked from 'package:readr':
## 
##     col_factor
## 
## 
## New names:

1 Section 1: Current & the Past Performance

1.1 Total Revenue by Product Over Time

1.2 Plot the monthly revenue trend

## Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
## ℹ Please use `linewidth` instead.
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.
## Warning: Removed 112 rows containing missing values or values outside the scale range
## (`geom_point()`).

1.3 Plot stacked area chart

1.4 Faceted Stacked Area Chart by Region

1.5 Looking at Reps

1.6 Plot the monthly revenue trend

1.7 Performance Summary: Top METAL Reps

Performance Summary: Top METAL Reps
Aggregate view of monthly revenue, peaks, and averages
Sales Rep Total Revenue Peak Month Average Monthly Revenue
Rep A1 $4,966,959 Jan 2022 $45,990
Rep A2 $8,056,969 Jun 2022 $74,602
Rep A3 $14,066,361 Sep 2024 $130,244
Rep A4 $5,753,392 Dec 2023 $53,272
Rep A5 $5,621,311 Nov 2023 $52,049
Rep A6 $6,903,497 Jun 2024 $63,921
Rep A7 $3,540,886 Dec 2024 $32,786
Rep A8 $5,827,856 Dec 2024 $53,962
Rep A9 $7,541,475 Mar 2023 $69,828
Rep B1 $2,342,031 Sep 2022 $21,685
Rep B2 $7,676,447 Mar 2023 $71,078
Rep B3 $7,417,144 Jun 2023 $68,677
Rep B4 $2,703,419 Mar 2022 $25,032
Rep B5 $6,633,989 Mar 2023 $61,426
Rep B6 $2,269,086 Dec 2022 $21,010
Rep B7 $5,971,964 Mar 2024 $55,296
Rep B8 $2,355,304 Mar 2022 $21,808
Rep C1 $10,563,169 Jun 2022 $97,807
Rep C2 $5,368,424 Sep 2022 $49,708
Rep C3 $7,378,155 Jan 2022 $68,316
Rep C4 $3,639,774 Jan 2022 $33,702
Rep C5 $2,731,533 Mar 2023 $25,292
Rep C6 $9,669,514 Dec 2022 $89,533
Rep C7 $7,195,952 Feb 2024 $66,629
Rep C8 $5,145,663 Sep 2022 $47,645
Rep D1 $7,180,292 Mar 2023 $66,484
Rep D2 $6,185,732 Jan 2022 $57,275
Rep D3 $4,556,076 Aug 2023 $42,186
Rep D4 $7,103,814 Mar 2023 $65,776
Rep D5 $7,365,021 Dec 2024 $68,195
Rep D6 $5,720,161 Mar 2024 $52,964
Rep D7 $2,206,383 Oct 2023 $20,429
Rep D8 $6,465,188 Sep 2022 $59,863
Rep D9 $5,046,520 Jun 2022 $46,727
Rep E1 $3,850,205 Mar 2022 $35,650
Rep E2 $4,529,042 Dec 2022 $41,936
Rep E3 $5,918,156 Dec 2022 $54,798
Rep E4 $5,604,068 Jun 2023 $51,890
Rep E5 $3,939,085 Jun 2022 $36,473
Rep E6 $4,431,176 Oct 2024 $41,029
Rep E7 $5,245,642 Feb 2023 $48,571
Rep E8 $7,518,107 Jun 2023 $69,612
Rep E9 $4,111,350 Jun 2024 $38,068

1.8 Category Performance by Region (Heatmap)

1.9 Monthly (Metal) Revenue Trend by Year

1.10 Revenue YoY growth by Category

1.11 Year-over-Year Revenue Change by Category

Year-over-Year Revenue Change by Category
Absolute and Percentage Change from Prior Year
Year Total Revenue YoY Change ($) YoY Change (%)
METAL
2022 $54,255,918 NA NA
2023 $50,996,927 −$3,258,991 −6.0
2024 $50,403,909 −$593,018 −1.2
PLASTIC
2022 $25,196,548 NA NA
2023 $33,025,395 $7,828,847 31.1
2024 $31,667,679 −$1,357,716 −4.1
WOOD
2022 $695,241 NA NA
2023 $833,549 $138,308 19.9
2024 $1,241,126 $407,577 48.9

2 Answers to Question 1

🥇 Strongest Performing Product: METAL

  • Overall Revenue Dominance: Across all three years, METAL consistently outpaces WOOD and PLASTIC in total revenue, particularly in Regions A, B, and D.

  • Standout Reps: Rep A3, B3, and D5 show sustained high monthly revenue, with peak periods in late 2023 and throughout 2024.

  • Growth Momentum: 2024 shows a clear YoY increase in METAL sales, especially in Regions C and E, which had flatter performance earlier.

🥈 Middle Performer: PLASTIC

  • Surges in 2024: Region C and E show strong acceleration in PLASTIC revenue, likely linked to increased market activity or promotions.

  • High-Variance Performance: Monthly revenue fluctuates more than METAL, suggesting less consistency in rep performance and possibly campaign-driven spikes.

🥉 Weakest Performing Product: WOOD

  • Flat to Declining Trend: Across most reps and regions, WOOD shows erratic month-to-month performance with frequent $0 cells.

  • Rep Performance: Only a few reps (e.g., Rep C6, A3) maintain steady WOOD revenue. Many others see sharp drops post-2023.

  • Possible Drivers: Seasonal products or lack of marketing emphasis could explain minimal growth.

Executive Summary: YoY Revenue Performance by Category (2022–2024)

🚧 Metal: Sustained Decline

  • 2023: Revenue dropped by $3.26M (-6.0%)

  • 2024: Continued decline of $593K (-1.2%)

  • Narrative: Consecutive downturn suggests market contraction or pricing pressure. Stabilization observed in 2024, but strategic cost containment or product mix review may be warranted.

📉 Plastic: Growth, Then Correction

  • 2023: Surged by $7.83M (+31.1%)

  • 2024: Fell by $1.36M (-4.1%)

  • Narrative: Exceptional growth in 2023 (possibly campaign- or SKU-driven) corrected in 2024. Investigate channel saturation or promotional fatigue; consider margin impact analysis.

🌲 Wood: Accelerated Expansion

  • 2023: Increased by $138K (+19.9%)

  • 2024: Jumped by $408K (+48.9%)

  • Narrative: Strong compounding growth trajectory. Signals opportunity for scaling supply or expanding assortment. May be worth spotlighting in QBR as emerging revenue driver.

3 Section 2: Future

3.1 Visual Plot 2025 METAL Forecast (Seasonality-Only)

3.2 Categoy: METALS - Actuals with forecast

## `geom_smooth()` using formula = 'y ~ x'

4 Answering the question 2: 1H & 2H 2025 Forecast for METALS only

📊 Seasonally Adjusted Forecast Summary
Smoothed via 3-mo Moving Avg with Seasonal Adjustment
Half Total Forecast (Smoothed) Avg Monthly Run Rate
1H 2025 $25,122,138.00 $4,187,023.00
2H 2025 $24,966,155.81 $4,161,025.97