This is a Statistical Analysis of Fertilizer Sales of Nutrien from first Quarter of 2018 to third Quarter of 2023. The dataset as been extracted from Nutrien’s website.

Loading the libraries

library(openxlsx)
library(readxl)
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.4     ✔ readr     2.1.4
## ✔ forcats   1.0.0     ✔ stringr   1.5.1
## ✔ ggplot2   3.4.4     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.0
## ✔ purrr     1.0.2     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(dplyr)
library(tidyr)

Now i will be loading the dataset from my directory to the Rstudio and select the Segment Summary (Q) sheet and Income Statement (Q) sheet to perform statistical analysis.

# Load the workbook and select the relevant sheets
workbook <- loadWorkbook("dataset/nutrien.xlsx")
sheetNames <- getSheetNames("dataset/nutrien.xlsx")
sheetNames
##  [1] "Metrics (Q)"          "Metrics (Y)"          "Income Statement (Y)"
##  [4] "Income Statement (Q)" "Balance Sheet (Y)"    "Balance Sheet (Q)"   
##  [7] "Cash Flow (Y)"        "Cash Flow (Q)"        "Segment Summary (Y)" 
## [10] "Segment Summary (Q)"  "Retail (Y)"           "Retail (Q)"          
## [13] "Potash (Y)"           "Potash (Q)"           "Nitrogen (Y)"        
## [16] "Nitrogen (Q)"         "Phosphate (Y)"        "Phosphate (Q)"       
## [19] "ESG Metrics (Y)"
segmentSummary <- read.xlsx(workbook, sheet = "Segment Summary (Q)")
incomeStatement <- read.xlsx(workbook, sheet = "Income Statement (Q)")

After selecting the sheets to work on, I am extracting the rows and columns for quarters, potash, nitrogen, phosphate, and net earnings.

# Extract specific rows and columns for quarters, potash, nitrogen, phosphate, and net earnings
quarters <- segmentSummary %>%
  slice(1:1) %>% 
  select(Q1.18:Q3.23)

potash <- segmentSummary %>%
  slice(27:27) %>%
  select(Q1.18:Q3.23)
potash <- as.integer(potash)

nitrogen <- segmentSummary %>%
  slice(46:46) %>%
  select(Q1.18:Q3.23)
nitrogen <- as.integer(nitrogen)

phosphate <- segmentSummary %>%
  slice(65:65) %>%
  select(Q1.18:Q3.23)
phosphate <- as.integer(phosphate)

retail <- segmentSummary %>%
  slice(9:9) %>%
  select(Q1.18:Q3.23)
retail <- as.integer(retail)

netEarning <- incomeStatement %>%
  slice(20:20) %>%
  select(Q1.18:Q3.23)
netEarning <- as.integer(netEarning)

Now i will combine the extracted data and prepare it for plotting and analysis by first transposing the data and then changing the data types of columns.

# Combine extracted data into a single data frame
fertilizerQuarterlyData <- rbind(quarters, retail, potash, nitrogen, phosphate, netEarning)
rownames(fertilizerQuarterlyData)[which(rownames(fertilizerQuarterlyData) == "1")] <- "Quarters"
rownames(fertilizerQuarterlyData)[which(rownames(fertilizerQuarterlyData) == "2")] <- "Retail"
rownames(fertilizerQuarterlyData)[which(rownames(fertilizerQuarterlyData) == "3")] <- "Potash"
rownames(fertilizerQuarterlyData)[which(rownames(fertilizerQuarterlyData) == "4")] <- "Nitrogen"
rownames(fertilizerQuarterlyData)[which(rownames(fertilizerQuarterlyData) == "5")] <- "Phosphate"
rownames(fertilizerQuarterlyData)[which(rownames(fertilizerQuarterlyData) == "6")] <- "Net_Earning"

# Prepare the data for plotting
fertilizerQuarterlyDataFlipped <- t(fertilizerQuarterlyData)
fertilizerQuarterlyDataFlipped <- as.data.frame(fertilizerQuarterlyDataFlipped)

# Convert Quarters to date format and rename columns
fertilizerQuarterlyDataFlipped$Quarters <- as.Date(fertilizerQuarterlyDataFlipped$Quarters, format="%m/%d/%Y")
fertilizerQuarterlyDataFlipped$Quarters <- format(fertilizerQuarterlyDataFlipped$Quarters, "%Y-%m")

# Convert data types
fertilizerQuarterlyDataFlipped$Retail <- as.integer(fertilizerQuarterlyDataFlipped$Retail)
fertilizerQuarterlyDataFlipped$Potash <- as.integer(fertilizerQuarterlyDataFlipped$Potash)
fertilizerQuarterlyDataFlipped$Nitrogen <- as.integer(fertilizerQuarterlyDataFlipped$Nitrogen)
fertilizerQuarterlyDataFlipped$Phosphate <- as.integer(fertilizerQuarterlyDataFlipped$Phosphate)
fertilizerQuarterlyDataFlipped$Net_Earning <- as.integer(fertilizerQuarterlyDataFlipped$Net_Earning)

# Prepare data for plotting - pivot longer
fertilizerQuarterlyDataFlippedMerged <- pivot_longer(fertilizerQuarterlyDataFlipped, cols = c("Retail","Potash","Nitrogen","Phosphate"), names_to= "Fertilizer_and_Retail", values_to =  "Sales" )

After preparing the data, i am plotting a bar chart for the fertilizer sales throughout the quarters

Now, I am creating a line graph to analyze the revenue trend

## Warning: The `<scale>` argument of `guides()` cannot be `FALSE`. Use "none" instead as
## of ggplot2 3.3.4.
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.

After plotting the graphs, i will now do some statistical analysis, such as the mean, maximum and minimum sales of fertilizers as well as the mean, maximum and minimum earnings,on the data i gathered from Nutrien.

# Calculate and display statistical information for Net Earnings
averageNetEarnings <- mean(netEarning)
cat("The mean Net Earning is", averageNetEarnings, "millions", "\n\n")
## The mean Net Earning is 738.9565 millions
maxNetEarning <- max(netEarning)
maxEarningColumnIndex <- which(fertilizerQuarterlyData[5, ] == maxNetEarning)
maxSalesQuarterEarning <- fertilizerQuarterlyData[1, maxEarningColumnIndex]
cat("Maximun Net Earning was" , maxNetEarning , " millions, and the Earning Quarter is" , maxSalesQuarterEarning, "\n\n" )
## Maximun Net Earning was 3601  millions, and the Earning Quarter is
minNetEarning <- min(netEarning)
minEarningColumnIndex <- which(fertilizerQuarterlyData[5, ] == minNetEarning)
minSalesQuarterEarning <- fertilizerQuarterlyData[1, minEarningColumnIndex]
cat("Minimun Net Earning was" , minNetEarning , " millions, and the Earning Quarter is" , minSalesQuarterEarning, "\n\n" )
## Minimun Net Earning was -1044  millions, and the Earning Quarter is
# Calculate and display statistical information for Retail sales
averageRetailSales <- mean(retail)
cat("The mean Retail sales is", averageRetailSales, "millions", "\n")
## The mean Retail sales is 4161.348 millions
maxRetailSale <- max(retail)
maxRetailColumnIndex <- which(fertilizerQuarterlyData[2, ] == maxRetailSale)
maxSalesQuarterRetail <- fertilizerQuarterlyData[1, maxRetailColumnIndex]
cat("Maximun Retail sale was" , maxRetailSale , " millions, and the Production Quarter is" , maxSalesQuarterRetail, "\n" )
## Maximun Retail sale was 9422  millions, and the Production Quarter is 06/30/2022
minRetailSale <- min(retail)
minRetailColumnIndex <- which(fertilizerQuarterlyData[2, ] == minRetailSale)
minSalesQuarterRetail <- fertilizerQuarterlyData[1, minRetailColumnIndex]
cat("Minimun Retail sale was" , minRetailSale , " millions, and the Production Quarter is" , minSalesQuarterRetail, "\n")
## Minimun Retail sale was 2017  millions, and the Production Quarter is 12/31/2018
# Calculate and display statistical information for Potash sales
averagePotashSales <- mean(potash)
cat("The mean Potash sales is", averagePotashSales, "millions", "\n\n")
## The mean Potash sales is 971.087 millions
maxPotashSale <- max(potash)
maxPotashColumnIndex <- which(fertilizerQuarterlyData[2, ] == maxPotashSale)
maxSalesQuarterPotash <- fertilizerQuarterlyData[1, maxPotashColumnIndex]
cat("Maximun Potash sale was" , maxPotashSale , " millions, and the Sales Quarter is" , maxSalesQuarterPotash, "\n\n" )
## Maximun Potash sale was 2668  millions, and the Sales Quarter is
minPotashSale <- min(potash)
minPotashColumnIndex <- which(fertilizerQuarterlyData[2, ] == minPotashSale)
minSalesQuarterPotash <- fertilizerQuarterlyData[1, minPotashColumnIndex]
cat("Minimun Potash sale was" , minPotashSale , " millions, and the Sales Quarter is" , minSalesQuarterPotash, "\n\n")
## Minimun Potash sale was 350  millions, and the Sales Quarter is
# Calculate and display statistical information for Nitrogen sales
averageNitrogenSales <- mean(nitrogen)
cat("The mean Nitrogen sales is", averageNitrogenSales, "millions", "\n\n")
## The mean Nitrogen sales is 1044.609 millions
maxNitrogenSale <- max(nitrogen)
maxNitrogenColumnIndex <- which(fertilizerQuarterlyData[3, ] == maxNitrogenSale)
salesQuarterNitrogen <- fertilizerQuarterlyData[1, maxNitrogenColumnIndex]
cat("Maximun Nitrogen sale was" , maxNitrogenSale , " millions, and the Sales Quarter is" , salesQuarterNitrogen, "\n\n" )
## Maximun Nitrogen sale was 2229  millions, and the Sales Quarter is
minNitrogenSale <- min(nitrogen)
minmaxNitrogenColumnIndex <- which(fertilizerQuarterlyData[3, ] == minNitrogenSale)
minSalesQuarterNitrogen <- fertilizerQuarterlyData[1, minmaxNitrogenColumnIndex]

cat("Minimun Nitrogen sale was" , minNitrogenSale , " millions, and the Sales Quarter is" , minSalesQuarterNitrogen, "\n\n" )
## Minimun Nitrogen sale was 540  millions, and the Sales Quarter is
# Calculate and display statistical information for Phosphate sales
averagePhosphateSales <- mean(phosphate)

cat("The mean Phosphate sales is", averagePhosphateSales, "millions", "\n\n")
## The mean Phosphate sales is 425.9565 millions
maxPhosphateSale <- max(phosphate)
maxPhosphateColumnIndex <- which(fertilizerQuarterlyData[4, ] == maxPhosphateSale)
salesQuarterPhosphate <- fertilizerQuarterlyData[1, maxPhosphateColumnIndex]

cat("Maximun Phosphate sale was" , maxPhosphateSale , " millions, and the Sales Quarter is" , salesQuarterPhosphate, "\n\n" )
## Maximun Phosphate sale was 651  millions, and the Sales Quarter is
minPhosphateSale <- min(phosphate)
minmaxPhosphateColumnIndex <- which(fertilizerQuarterlyData[4, ] == minPhosphateSale)
minSalesQuarterPhosphate <- fertilizerQuarterlyData[1, minmaxPhosphateColumnIndex]

cat("Minimun Phosphate sale was" , minPhosphateSale , " millions, and the Sales Quarter is" , minSalesQuarterPhosphate, "\n\n" )
## Minimun Phosphate sale was 277  millions, and the Sales Quarter is

From the data analysis we learned that on second quarter of 2022 we had the highest sales of Potash and Nitrogen and it was also the quarter where we had maximum Net Earnings!