The tidy data principles are a cornerstone of financial data management and the data modeling workflow. The foundation for tidy data management is the tidyverse, a collection of R packages: purrr, dplyr, tidyr, tibble, ggplot2, readr, that work in harmony, are built for scalability, and are well documented in R for Data Science. Using this infrastructure and the core tidy concepts, we can apply the tidy data principles to the Saudi Aramco Discounted Cash Flow Valuation.
Saudi Aramco has set a price range for its listing that implies the oil giant is worth between USD$1.6 trillion and US$1.7 trillion, making it potentially the world’s biggest IPO. The numbers that are laid out in the prospectus are impressive, painting a picture of the most profitable company in the world, with almost unassailable competitive advantages. In this post, I valued Saudi Aramco between US$\(1.69\) and US\(\$1.83\) trillion using the following R packages.
The tabulizer package provides a suite of tools for extracting data from PDFs. The vignette, Introduction to tabulizer has a great overview of tabulizer’s features. We will use the extract_tables() function to pull out tables 42 (pg 131 - gearing), 43 (pg 132 - capital), 45 (pg 133 - income statement) and 52 (pg 144 - balance sheet) from the Saudi Aramco prospectus. This returns a list of data.frames.
The fuzzyjoin package is a variation on dplyr’s join operations that allows matching not just on values that match between columns, but on inexact matching. This allows the Aramco’s financial accounts (e.g., gearing, capital, income statement, balance sheet) to be quickly matched with the tables it is reported on and without having to looking for the correct location in the prospectus, a behemoth weighing in at \(658\) pages.
The World Bank makes available a large body of economic data from the World Development Indicators through its web API. The WDI package for R makes it easy to search and download the annual percentage growth rate of Gross Domestic Product (GDP) for Saudi Arabia.
rvest makes it easy to scrape daily treasury yield curve rates from the website of the U.S. Deparment of the Treasury. Here, I use it with magrittr so that I can express complex operations as elegant pipelines composed of simple, easily understood pieces.
tidyxl imports non-tabular data from Excel files into R. It exposes cell content, position, formatting and comments in a tidy structure for further manipulation. I use tidyxl to capture Damodaran’s spreadsheets (risk premium, credit spread, unlevered beta, marginal tax rate) in a tidy fashion allowing for seamless interaction between rows and columns.
library(knitr)
library(kableExtra)
library(ggpage)
library(magrittr)
library(tidyverse)
library(WDI)
library(pdftools)In this section, I extract financial data from the prospectus, using tabulizer and fuzzyjoin. It automates work that would have taken significant manual collection and manipulation.
# Saudi Aramco Prospectus
# f <- file.path("data", "saudi-aramco-prospectus-en.pdf")
download.f <- function(url) {
data.folder = file.path(getwd(), 'data') # setup temp folder
if (!dir.exists(data.folder)){dir.create(data.folder, F)}
filename = file.path(data.folder, basename(url))
if(!file.exists(filename))
tryCatch({ download.file(url, filename, mode='wb') },
error = function(ex) cat('', file=filename))
filename
}
extract.values.f <- function(pdf.file, page, names){
require(tabulizer)
require(fuzzyjoin) # regex_inner_join
# https://www.saudiaramco.com/-/media/images/investors/saudi-aramco-prospectus-en.pdf
area = case_when( # tabulizer::locate_areas(f, pages = 222, widget = "shiny")
page == 220 ~ c(459.77, 69.76, 601, 427.98), # Table 42 (pg 131)
page == 221 ~ c(168.03, 69.76, 394.53, 404.59), # Table 43 (pg 132)
page == 222 ~ c(180.11, 68.38, 413.04, 412.05), # Table 45 (pg 133)
page == 233 ~ c(181.57, 70.99, 673.96, 448.91) # Table 52 (pg 144)
)
extract_tables(
pdf.file, pages = page, area = list(area),
guess = FALSE, output = "data.frame"
) %>%
purrr::pluck(1) %>%
map_dfc(~trimws(gsub("\\.|[[:punct:]]", "", .x))) %>%
set_names( c("Heading", paste0("X", if(page==233){1:4}else{0:4})) ) %>%
regex_inner_join(
data.frame(regex_name = names, stringsAsFactors = FALSE),
by = c(Heading = "regex_name")
) %>%
select(X4) %>%
pull %>%
as.numeric
}
prospectus.pdf <- 'https://www.saudiaramco.com/-/media/images/investors/saudi-aramco-prospectus-en.pdf' %>%
download.f(.)For working with function programming, we solve the issue for one element, wrap the code inside a function, and then simply map extract.values.f to a list of elements in different tables (42, 43, 45 and 52).
Saudi Aramco’s average reserve life is \(52\) years, against \(17\) years at ExxonMobil. Its crude reserves are about five times that of the combined oil reserves of the five major international oil companies, comprising ExxonMobil, Shell, Chevron, Total, and BP.
# 4.6.1.2 - Long reserves life
# lines_text <- raw_text %>% read_lines()
# lines_text[lines_text %>% str_which("proved reserves life")] %>%
inputs <- prospectus.pdf %>%
pdf_text() %>% read_lines() %>%
grep("proved reserves life", ., value = TRUE) %>%
str_match_all("[0-9]+") %>%
purrr::pluck(1) %>%
unlist %>% first() %>% as.numeric() %>%
set_names(c("LONG_RESERVES_LIFE")) %>% as.list
listviewer::jsonedit(inputs)Gearing is a measure of the degree to which Saudi Aramco’s operations are financed by debt. It is widely used by analysts and investors in the oil and gas industry to indicate a company’s financial health and flexibility.
# Table 42 - Gearing and reconciliation
inputs <- extract.values.f(prospectus.pdf, 220,
c("Total borrowings", "Cash and cash equivalents", "Total equity")
) %>%
set_names(c("TOTAL_BORROWINGS", "CASH_AND_CASH_EQUIVALENTS", "TOTAL_EQUITY")) %>%
as.list %>% append(inputs)
listviewer::jsonedit(inputs)Saudi Aramco has a comprehensive and disciplined internal approval process for capital allocation. Average capital employed is the average of Saudi Aramco’s total borrowings plus total equity at the beginning and end of the applicable period.
# Table 43 - Return on Average Capital Employed (ROACE) and reconciliation
inputs <- extract.values.f(prospectus.pdf, 221,
c("Capital employed")
) %>%
last() %>%
set_names(c("CAPITAL_EMPLOYED")) %>%
as.list %>% append(inputs)
listviewer::jsonedit(inputs)The numbers in the financial statement are impressive, painting a picture of the most profitable company in the world, with almost unassailable competitive advantages.
# Table 45 - Income statement
inputs <- extract.values.f(prospectus.pdf, 222,
c("Operating income", "Income taxes", "Income before income taxes", "Net income")
) %>%
set_names(c("OPERATING_INCOME", "INCOME_BEFORE_INCOME_TAXES", "INCOME_TAXES", "NET_INCOME")) %>%
as.list %>% append(inputs)
listviewer::jsonedit(inputs)Saudi Aramco’s unique reserves and resources base, operational flexibility, field management, and strong cash flow generation serve as a foundation for its low gearing and flexible balance sheet.
# Table 52 - Balance sheet
inputs <- extract.values.f(prospectus.pdf, 233,
c("Shareholders equity", "Investment in joint ventures and associates",
"Investment in securities", "Noncontrolling interests")) %>%
purrr::discard(is.na) %>%
set_names(c("INVESTMENT_JOINT_VENTURES_ASSOCIATES", "INVESTMENT_SECURITIES",
"SHAREHOLDERS_EQUITY", "NON_CONTROLLING_INTERESTS")) %>%
as.list %>% append(inputs)
listviewer::jsonedit(inputs)For Saudi Aramco, the growth rate in earnings corresponds closely to the growth in Saudi Arabia’s GDP. The reason is simple. Saudi Arabia derives almost \(80\%\) of its GDP from oil.
# World Development Indicators (WDI)
inputs <- WDI::WDI(
country=c("SAU"),
indicator="NY.GDP.MKTP.KD.ZG", # = GDP growth (annual %)
start=2018,
end=2018
)$NY.GDP.MKTP.KD.ZG[[1]] %>%
set_names(c("GDP_GROWTH")) %>% # (annual %)
as.list %>% append(inputs)
listviewer::jsonedit(inputs)We use the \(10\) year U.S. treasury rate because the currency choice for the Saudi Aramco discounted cash flow valuation is U.S. dollars.
treasury.rates.f <- function(year=2019){
require(rvest)
require(janitor)
# year=calendar year to pull results for
# Data is generally updated at the end of each business day
rate_url <- paste(
'https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yieldYear&year=',
year,
sep=''
)
# 1 mo, 2 mo, 3 mo, 6 mo, 1 yr, 2 yr, 3 yr, 5 yr, 7 yr, 10 yr, 20 yr, 30 yr
rates_raw <- read_html(rate_url) %>%
html_node('.t-chart') %>%
html_table()
# Returns treasury rates for the given duration
rates <- rates_raw %>%
clean_names(.) %>%
mutate(
date = as.Date(date, "%m/%d/%y"),
month = factor(months(date), levels=month.name)
) %>%
mutate_at(
vars(-one_of("date", "month")),
as.numeric
)
summary <- rates %>%
select(-date) %>%
group_by(month) %>%
summarise_all(list(mean))
return(summary)
}
rates <- treasury.rates.f(2019) # last update dec 7, 2019
# save(rates, file="data/dec_7_2019_treasury_rates.rda")
inputs <- rates %>%
select(x10_yr) %>%
slice(n()) %>% # Dec 10_yr Avg.
pull %>%
set_names(c("TREASURY_YIELD_10YR")) %>%
as.list %>% append(inputs)
listviewer::jsonedit(inputs)We use a credit spread that lenders would charge a large integrated oil & gas company with a specific credit rating, and add it to the avg. 10 year U.S. treasury rate to arrive at Saudi Aramco’s cost of debt.
rating.spread.f <- function(){
require(readxl)
# data_file <- file.path("data", "ratings.xls")
# Ratings, Interest Coverage Ratios and Default Spread
url <- 'http://www.stern.nyu.edu/~adamodar/pc/ratings.xls'
data_file <- download.f(url)
v <- read_excel(
data_file, sheet = "Start here Ratings sheet",
range = "A18:D33") %>% # A18:D33 -> rating table for large manufacturing firms
janitor::clean_names() %>%
filter(rating_is=="A1/A+") %>%
# https://www.bloomberg.com/news/articles/2019-04-01/saudi-oil-giant-aramco-starts-bond-roadshow-gets-a-rating
pull(spread_is)
return(v * 100)
}
cs <- rating.spread.f()
inputs <- cs %>%
set_names(c("CREDIT_SPREAD")) %>%
as.list %>% append(inputs)
listviewer::jsonedit(inputs)In calculating the cost of equity, we use an unlevered beta for Saudi Aramco based on integrated oil companies for both cash flow models: (1) cash flows after reinvestment needs and taxes, but before debt payments (FCFF); and (2) cash flows after taxes, reinvestments, and debt payments (FCFE).
# Effective Tax rate, Unlevered beta
unlevered.beta.f <- function(){
require(readxl)
# data_file <- file.path("data", "betaGlobal.xls")
# Unlevered Betas (Global)
url <- 'http://www.stern.nyu.edu/~adamodar/pc/datasets/betaGlobal.xls'
data_file <- download.f(url)
# A10:F106 -> Industry Name, Number of firms, Beta, D/E Ratio,
v <- read_excel(data_file, sheet = "Sheet1", range = "A10:F106") %>%
janitor::clean_names() %>%
filter(industry_name=="Oil/Gas (Integrated)") %>%
pull(unlevered_beta)
return(v)
}
ub <- unlevered.beta.f()
inputs <- ub %>%
set_names(c("UNLEVERED_BETA")) %>%
as.list %>% append(inputs)
listviewer::jsonedit(inputs)The marginal tax rate is the number we use to compute Saudi Aramco’s after-tax cost of debt. Given Saudi Aramco’s marginal corporate tax rate, the after-tax cost of debt equates to the treasury rate plus the credit spread that lenders would charge Saudi Aramco multiplied by one minus the marginal tax rate.
marginal.tax.f <- function(){
require(readxl)
# data_file <- file.path("data", "countrytaxrates.xls")
url <- 'http://www.stern.nyu.edu/~adamodar/pc/datasets/countrytaxrates.xls'
data_file <- download.f(url)
# Corporate Marginal Tax Rates - By country
v <- read_excel(data_file, sheet = "Sheet1") %>%
janitor::clean_names() %>%
filter(country=="Saudi Arabia") %>%
pull(x2018)
return(v * 100)
}
mtr <- marginal.tax.f()
inputs <- mtr %>%
set_names(c("MARGINAL_TAX_RATE")) %>%
as.list %>% append(inputs)
listviewer::jsonedit(inputs)Calculate the current earnings and cash flows of Saudi Aramco for equity investors and for all claimholders.
Calculate the future earnings and cash flows of Saudi Aramco by estimating an expected growth rate in earnings (GDP growth).
Calculate Saudi Aramco’s Discounted Cash Flow valuations.
equity.valuation.f <- function(inp){
for (j in 1:length(inp)) assign(names(inp)[j], inp[[j]])
#-------------------------------------------------------------------------------------
# Calculated inputs
EFFECTIVE_TAX_RATE <- INCOME_TAXES / INCOME_BEFORE_INCOME_TAXES
INVESTED_CAPITAL <- CAPITAL_EMPLOYED - CASH_AND_CASH_EQUIVALENTS
DEBT_RATIO <- TOTAL_BORROWINGS / ( TOTAL_BORROWINGS + TOTAL_EQUITY )
COST_DEBT <- ( CREDIT_SPREAD + TREASURY_YIELD_10YR ) / 100
COST_EQUITY <- ( TREASURY_YIELD_10YR + UNLEVERED_BETA * EQUITY_RISK_PREMIUM ) / 100
COST_CAPITAL <- COST_DEBT * ( 1 - ( MARGINAL_TAX_RATE / 100 ) ) * DEBT_RATIO +
COST_EQUITY * ( 1 - DEBT_RATIO )
NUMBER_YEARS <- LONG_RESERVES_LIFE
#-------------------------------------------------------------------------------------
# Free Cash Flow to Equity (FCFE)
EXPECTED_RETURN_EQUITY <- NET_INCOME / SHAREHOLDERS_EQUITY
EXPECTED_GROWTH_EARNINGS <- GDP_GROWTH / 100
PAYOUT_RATIO <- 1 - EXPECTED_GROWTH_EARNINGS / EXPECTED_RETURN_EQUITY
VALUE_EQUITY <- NET_INCOME * PAYOUT_RATIO *
( 1 - ( ( 1 + EXPECTED_GROWTH_EARNINGS ) ^ NUMBER_YEARS /
( 1 + COST_EQUITY ) ^ NUMBER_YEARS ) ) /
( COST_EQUITY - EXPECTED_GROWTH_EARNINGS )
FCFE_EQUITY_VALUATION <- VALUE_EQUITY + CASH_AND_CASH_EQUIVALENTS +
INVESTMENT_JOINT_VENTURES_ASSOCIATES + INVESTMENT_SECURITIES
#-------------------------------------------------------------------------------------
# Free Cash Flow to Firm (FCFF)
EXPECTED_GROWTH_RATE <- GDP_GROWTH / 100
EXPECTED_ROIC <- OPERATING_INCOME * ( 1 - EFFECTIVE_TAX_RATE ) / INVESTED_CAPITAL
REINVESTMENT_RATE <- EXPECTED_GROWTH_RATE / EXPECTED_ROIC
EXPECTED_OPERATING_INCOME_AFTER_TAX <- OPERATING_INCOME *
( 1 - EFFECTIVE_TAX_RATE ) * ( 1 + EXPECTED_GROWTH_RATE )
EXPECTED_FCFF <- EXPECTED_OPERATING_INCOME_AFTER_TAX * ( 1 - REINVESTMENT_RATE )
VALUE_OPERATING_ASSETS <- EXPECTED_FCFF *
( 1 - ( ( 1 + EXPECTED_GROWTH_RATE ) ^ NUMBER_YEARS /
( 1 + COST_CAPITAL ) ^ NUMBER_YEARS ) ) /
( COST_CAPITAL - EXPECTED_GROWTH_RATE )
FCFF_EQUITY_VALUATION <- VALUE_OPERATING_ASSETS + CASH_AND_CASH_EQUIVALENTS +
INVESTMENT_JOINT_VENTURES_ASSOCIATES + INVESTMENT_SECURITIES -
TOTAL_BORROWINGS - NON_CONTROLLING_INTERESTS
#-------------------------------------------------------------------------------------
# Use set_names to name the elements of the vector
out <- c(INVESTED_CAPITAL, DEBT_RATIO, EFFECTIVE_TAX_RATE) %>%
set_names(c("INVESTED_CAPITAL", "DEBT_RATIO", "EFFECTIVE_TAX_RATE"))
out <- c(NUMBER_YEARS, COST_CAPITAL, COST_EQUITY, COST_DEBT) %>%
set_names(c("NUMBER_YEARS", "COST_CAPITAL", "COST_EQUITY", "COST_DEBT")) %>%
as.list %>% append(out)
out <- c(FCFE_EQUITY_VALUATION, VALUE_EQUITY, PAYOUT_RATIO,
EXPECTED_GROWTH_EARNINGS, EXPECTED_RETURN_EQUITY) %>%
set_names(c("FCFE_EQUITY_VALUATION", "VALUE_EQUITY", "PAYOUT_RATIO",
"EXPECTED_GROWTH_EARNINGS", "EXPECTED_RETURN_EQUITY")) %>%
as.list %>% append(out)
out <- c(FCFF_EQUITY_VALUATION, VALUE_OPERATING_ASSETS, EXPECTED_FCFF,
EXPECTED_OPERATING_INCOME_AFTER_TAX, REINVESTMENT_RATE,
EXPECTED_ROIC, EXPECTED_GROWTH_RATE) %>%
set_names(c("FCFF_EQUITY_VALUATION", "VALUE_OPERATING_ASSETS", "EXPECTED_FCFF",
"EXPECTED_OPERATING_INCOME_AFTER_TAX", "REINVESTMENT_RATE",
"EXPECTED_ROIC", "EXPECTED_GROWTH_RATE")) %>%
as.list %>% append(out)
#-------------------------------------------------------------------------------------
return(out)
}
output <- equity.valuation.f(inputs)
listviewer::jsonedit(output)Below, I valued Saudi Aramco at about USD$1.76 trillion using a weighted DCF equity valuation: \(50\%\) for Operating income & FCFF and \(50\%\) for Equity income & FCFE.
data.frame(
Weighted = 0.5 * (output$FCFF_EQUITY_VALUATION + output$FCFE_EQUITY_VALUATION) / 1000000,
FCFF = output$FCFF_EQUITY_VALUATION / 1000000,
FCFE = output$FCFE_EQUITY_VALUATION / 1000000,
check.names = FALSE
) %>%
mutate_all(scales::dollar) %>%
kable() %>%
kable_styling(c("striped", "bordered")) %>%
add_header_above(c("Saudi Aramco Equity Valuation ($ trillions)" = 3))| Weighted | FCFF | FCFE |
|---|---|---|
| $1.75 | $1.83 | $1.68 |
It is very likely that investors will reward Saudi Aramco for:
However, investors could also penalize Saudi Aramco for the geopolitical risk and the central banking conspiracy to keep interest rates low.
Central banks around the world have conspired to keep interest rates low and artificially push up the price of financial assets. The end game in this story is that the central banks will eventually be forced to face reality, where the U.S. \(10\) Year Treasury will rise to normal levels and the value of Saudi Aramco could decrease.
out <- map(
seq(1, 4, 0.25),
~list_modify(
inputs,
TREASURY_YIELD_10YR=.x
) %>%
equity.valuation.f(.)
)
map2_dfr(
out,
seq(1, 4, 0.25),
~list(
TREASURY_YIELD_10YR=.y,
WEIGHTED=(.x$FCFF_EQUITY_VALUATION+.x$FCFE_EQUITY_VALUATION) / 2 / 1000000,
FCFF=.x$FCFF_EQUITY_VALUATION / 1000000,
FCFE=.x$FCFE_EQUITY_VALUATION / 1000000
)
) %>%
# arrange(-TREASURY_YIELD_10YR) %>%
mutate_at(
vars(one_of("FCFF", "FCFE", "WEIGHTED")),
scales::dollar
) %>%
mutate_at(
vars(one_of("TREASURY_YIELD_10YR")),
function(v) sprintf(v, fmt = "%.2f%%")
) %>%
rmarkdown::paged_table()Saudi Aramco’s oil equivalent reserves were sufficient for proved reserves life of \(52\) years, which was significantly longer than the \(9\) to \(17\) year proved reserves life of any of the five major international oil companies based on publicly available information.
out <- map(
40:52, # Long reserves life
~list_modify(
inputs,
LONG_RESERVES_LIFE=.x
) %>%
equity.valuation.f(.)
)
map_dfr(
out,
~list(
RESERVES_LIFE=.x$NUMBER_YEARS,
WEIGHTED=(.x$FCFF_EQUITY_VALUATION+.x$FCFE_EQUITY_VALUATION) / 2 / 1000000,
FCFF=.x$FCFF_EQUITY_VALUATION / 1000000,
FCFE=.x$FCFE_EQUITY_VALUATION / 1000000
)
) %>%
arrange(-RESERVES_LIFE) %>%
mutate_at(
vars(one_of("FCFF", "FCFE", "WEIGHTED")),
scales::dollar
) %>%
rmarkdown::paged_table()The report was produced using RStudio/knittr
on 2019-12-12 at 00:36:59 (+08, GMT+0800)