GLOBAL OVERVIEW

Short Introduction

Global Corporation was established as a logistics subsidiary offering general logistics and distribution center services and the subsidiary has expanded its business to offer a suite of turnkey services that meet every possible challenge in the supply chain.

Specialization : Freight forwarding, Distribution, Supply chain, Transportation, Warehouse Management, Transport Management System, Warehouse Management System, Delivery, Logistics Service Provider, Contract Logistics, Freight Forwarding, liquid tank Strorage

Global Has 5 Subsidiaries :

  • STORE ALL PRO
  • RUNFAST GO
  • LIQUIDTANK
  • COMMERCE STORE
  • GETAPSS

And 9 Divisions :

  • Bulk Warehouse
  • Bulk Transportation
  • Liquid Transport
  • Non Bulk Transport & Rental
  • Export Import
  • Liquid Warehouse
  • Package Warehouse
  • Management Warehouse
  • Liquid Trading

Business Questions :

  • How's Group Performance till November 2022?
  • How's Subsidaries Perform?
  • Our top 10 Customer

Data

library(dplyr)
library(tidyverse)
library(lubridate)

Read Data

globalco <- read.csv("GLOBAL_CO.csv")

Checking Data Type & Missing Values

glimpse(globalco)
#> Rows: 2,601
#> Columns: 11
#> $ YEAR_MONTH        <chr> "2022-10-01", "2022-10-01", "2022-10-01", "2022-10-0…
#> $ COMPANY_CODE_DESC <chr> "STORE ALL PRO", "RUNFAST GO", "RUNFAST GO", "RUNFAS…
#> $ CUSTOMER          <chr> "3000008984", "3000017014", "3000019518", "300000737…
#> $ DIVISION_NAME     <chr> "Bulk Warehouse", "Export Import", "Export Import", …
#> $ SALES             <dbl> 1078.0, 9.2, 324.3, 179.0, 92.1, 3.9, 14.0, 297.4, 8…
#> $ COGS              <dbl> 784.4, 5.6, 275.1, 146.8, 68.2, 2.1, 9.6, 245.6, 722…
#> $ GROSS_PROFIT      <dbl> 293.6, 3.6, 49.2, 32.2, 23.7, 1.8, 4.3, 51.7, 779.8,…
#> $ OPERATING_EXPENSE <dbl> 259.9, 1.4, 42.5, 16.8, 0.0, 0.0, 0.0, 36.2, 414.1, …
#> $ OPERATING_INCOME  <dbl> 33.7, 2.2, 6.7, 15.4, 23.7, 1.8, 4.3, 15.7, 365.7, -…
#> $ NPAT              <dbl> -62.9, 17.1, 8.0, 14.2, 23.7, 1.8, 4.3, 34.2, 467.3,…
#> $ EBITDA            <dbl> 87.6, 4.4, 7.9, 15.8, 23.7, 1.8, 4.3, 18.5, 385.4, -…
#Checking if any missing values
anyNA(globalco)
#> [1] FALSE

some data not in correct type and need to convert it

Change Type Data & Column Name

globalco_clean <- 
  globalco %>% 
  mutate (
    YEAR_MONTH = as.Date(YEAR_MONTH),
    COMPANY_CODE_DESC = as.factor(COMPANY_CODE_DESC),
    CUSTOMER = as.factor(CUSTOMER),
    DIVISION_NAME = as.factor(DIVISION_NAME)
  ) %>% 
  rename("SUBSIDARY_NAME" = "COMPANY_CODE_DESC")
globalco_clean$MONTH  <- month(globalco_clean$YEAR_MONTH,
                              label = T)
glimpse(globalco_clean)
#> Rows: 2,601
#> Columns: 12
#> $ YEAR_MONTH        <date> 2022-10-01, 2022-10-01, 2022-10-01, 2022-10-01, 202…
#> $ SUBSIDARY_NAME    <fct> STORE ALL PRO, RUNFAST GO, RUNFAST GO, RUNFAST GO, R…
#> $ CUSTOMER          <fct> 3000008984, 3000017014, 3000019518, 3000007370, 8000…
#> $ DIVISION_NAME     <fct> Bulk Warehouse, Export Import, Export Import, Export…
#> $ SALES             <dbl> 1078.0, 9.2, 324.3, 179.0, 92.1, 3.9, 14.0, 297.4, 8…
#> $ COGS              <dbl> 784.4, 5.6, 275.1, 146.8, 68.2, 2.1, 9.6, 245.6, 722…
#> $ GROSS_PROFIT      <dbl> 293.6, 3.6, 49.2, 32.2, 23.7, 1.8, 4.3, 51.7, 779.8,…
#> $ OPERATING_EXPENSE <dbl> 259.9, 1.4, 42.5, 16.8, 0.0, 0.0, 0.0, 36.2, 414.1, …
#> $ OPERATING_INCOME  <dbl> 33.7, 2.2, 6.7, 15.4, 23.7, 1.8, 4.3, 15.7, 365.7, -…
#> $ NPAT              <dbl> -62.9, 17.1, 8.0, 14.2, 23.7, 1.8, 4.3, 34.2, 467.3,…
#> $ EBITDA            <dbl> 87.6, 4.4, 7.9, 15.8, 23.7, 1.8, 4.3, 18.5, 385.4, -…
#> $ MONTH             <ord> Oct, Oct, Oct, Oct, Nov, Nov, Nov, Oct, Oct, Nov, Oc…

Now each field had changed to data type we need

GLOBAL PnL

Summarise Global Pnl by Month

Group_Pnl <- 
  globalco_clean %>% 
  group_by(MONTH) %>% 
  summarise(SALES = sum(SALES),
            COGS = sum(COGS),
            GROSS_PROFIT=sum(GROSS_PROFIT),
            OPERATING_EXPENSE = sum(OPERATING_EXPENSE),
            OPERATING_INCOME = sum(OPERATING_INCOME),
            NPAT = sum(NPAT),
            EBITDA = sum(EBITDA))
Group_Pnl
Group_Pnl_ytd <- 
  globalco_clean %>% 
  summarise(SALES = sum(SALES),
            COGS = sum(COGS),
            GROSS_PROFIT=sum(GROSS_PROFIT),
            OPERATING_EXPENSE = sum(OPERATING_EXPENSE),
            OPERATING_INCOME = sum(OPERATING_INCOME),
            NPAT = sum(NPAT),
            EBITDA = sum(EBITDA))
Group_Pnl_ytd

Check Correlation Between Operating Income & Operating Expense

From table above we could see that on Jan - Feb, Global Corp experiences Negative in Operating Income(OI)

OI formula = Gross Profit(GP) - Operating Expense(OPEX)

and OI has negative correlation with OPEX, Obviously

but let’s check their correlation

cor(Group_Pnl$OPERATING_INCOME,Group_Pnl$OPERATING_EXPENSE)
#> [1] -0.3012641

Since the correlation was -0.3, means that OI and OPEX don’t have strong correlation

View Top Sales, Operating Income, NPAT, EBITDA

NPAT = Net Profit After Tax is the measure of how well a company has performed after you remove its expenses, debts and taxes = aka how well its cash is flowing.

EBITDA = Earning Before Interest, Taxes, Depreciation, and Amortization Is an alternate measure of profitability to net income. By stripping out the non-cash depreciation and amortization expense as well as taxes and debt costs dependent on the capital structure, EBITDA attempts to represent cash profit generated by the company’s operations.

Group_Pnl%>% 
  select(MONTH,SALES) %>% 
  arrange(desc(SALES)) 
Group_Pnl %>% 
  select(MONTH,OPERATING_INCOME) %>% 
  arrange(desc(OPERATING_INCOME)) 
Group_Pnl %>% 
  select(MONTH,NPAT) %>% 
  arrange(desc(NPAT)) 
Group_Pnl %>% 
  select(MONTH,EBITDA) %>% 
  arrange(desc(EBITDA))

Global has Top Sales on Mar but it didn’t represent Operating Income, NPAT & EBITDA

Top Operating Income & EBITDA was on JUNE

NPAT was on SEPTEMBER

SUBSIDIARIES & DIVISIONS

BY SUBSIDIARY

Summarise Subsidiary PnL

subsididary <- 
  globalco_clean %>% 
  select(SUBSIDARY_NAME,SALES,GROSS_PROFIT,OPERATING_INCOME,NPAT,EBITDA) %>% 
  group_by(SUBSIDARY_NAME) %>% 
  summarise(SALES = sum(SALES),
            GROSS_PROFIT = sum(GROSS_PROFIT),
            OPERATING_INCOME = sum(OPERATING_INCOME),
            NPAT = sum(NPAT),
            EBITDA = sum(EBITDA)) %>% 
  arrange(desc(SALES))
subsididary

View Sales & and Top Contributor to Group base on Subsidiary

subsididary$SALES[1]/Group_Pnl_ytd$SALES
#> [1] 0.5894399

RUNFAST GO is top Contributor to Group Sales, it has 50% part of cake followed by STORE ALL PRO

COMMERCE STORE has smallest contribution to Group Sales

BY DIVISION

Summarise Division PnL

division<- 
  globalco_clean %>% 
  select(SUBSIDARY_NAME,DIVISION_NAME,SALES,GROSS_PROFIT,OPERATING_INCOME,NPAT,EBITDA) %>% 
  group_by(SUBSIDARY_NAME,DIVISION_NAME) %>% 
  summarise(SALES = sum(SALES),
            GROSS_PROFIT = sum(GROSS_PROFIT),
            OPERATING_INCOME = sum(OPERATING_INCOME),
            NPAT = sum(NPAT),
            EBITDA = sum(EBITDA)) %>% 
  arrange(desc(SALES))
division
head(division)

TOP 5 Contributor base on Division are :

Division Non Bulk Transport & Rental, Export Import, Bulk Transportation from Subsidiary RUNFAST GO

Division Package Warehouse, Bulk Warehouse from STORE ALL PRO

CUSTOMERS

Summarise Customer PnL and Selecting Top 10

customer<- 
  globalco_clean %>% 
  select(CUSTOMER,SALES,GROSS_PROFIT,OPERATING_INCOME,NPAT,EBITDA) %>% 
  group_by(CUSTOMER) %>% 
  summarise(SALES = sum(SALES),
            GROSS_PROFIT = sum(GROSS_PROFIT),
            OPERATING_INCOME = sum(OPERATING_INCOME),
            NPAT = sum(NPAT),
            EBITDA = sum(EBITDA)) %>% 
  arrange(desc(SALES)) %>% 
  top_n(10)
customer