1 Objective

Our goal is to extract the capitalization table from each workbook and transform it into a vector detailing each observation and its variables for the different capitalization periods. It is also to perform and compile summary calculations to be stored in the vector. When finished, our output for COMP1 and COMP2 should appear as:

First, we set up an empty vector which will house lists for closing, current, and summary information. Our first major goal is to retrieve the information for closing and current periods as a list and then import them into the vector:

capitalization <- vector("list", 3)
names(capitalization)=c("closing", "current", "summary")

2 Isolating the Data

With our containers set up, we can start looking at the data. We can take a look at the summary cells with unpivotr::rectify and scroll down to find where the capitalization table is located in each sheet. Doing for COMP1 we see the following:

A glance at the results shows us we need to filter out the columns that do not have data relevant to us as well as filter the rows so that we are just working with the capitalization table.

First, we can set up a column filter to be applied to the data later on: a glance at the Summary spreadsheet with a quick rectify of summary_cells tells us that the data for headers, closing, and current are in columns $3$, $5$, and $11$ while the data for the Valuation Summary sheet are located in columns $3$, $5$, and $10$. With this information, we can set up a tibble with values for col_filter corresponding to where relevant data is found in each sheet; we will use these tibbles later to filter the bag of cells with an inner join.

# Set columns that contain relevant data
xlsx_toggle=1
if (xlsx_toggle==1){
  col_filter <- tibble(col = c(3,5,11))
} else{ 
  col_filter <- tibble(col = c(3,5,10))
}

Next we will narrow the bag of cells we are working with: the information in COMP1 that we desire is between rows $31$ and $44$ while the information needed in COMP2 is within rows $34$ and $52$. We can filter by row in summary_cells to achieve this.

if (xlsx_toggle==1){
  xlsx_obj <- file.path(path_data, "RNFC_COMP1") %>% readRDS
  bag=xlsx_obj %>% # bag of cells for COMP1
    filter(sheet == "Summary", row>=31, row<=44) %>% 
    select(row, col, character, numeric, date, data_type)
} else{ 
  xlsx_obj <- file.path(path_data, "RNFC_COMP2") %>% readRDS
  bag=xlsx_obj %>% # bag of cells for COMP2
    filter(sheet == "Valuation Summary", row>=34, row<=52) %>% 
    select(row, col, character, numeric, date, data_type)
}

We also need to add the missing Period header in each sheet which will be located in the header column on row $32$ and $35$ for COMP1 and COMP2, respectively. We can do this with a replace, telling it to change the value within bag$character corresponding to the matching col and row values within the bag. We also need to change the data_type for the same value so that it is not registered as blank, which can confuse the unpivotr functions. We can also use this method to add closing and current north headings to the second and third columns of col_filter.

if (xlsx_toggle==1){
  bag=bag %>%
    mutate(
      character = replace(character, col==col_filter$col[1] & row==32, "Period"),
      data_type = replace(data_type, col==col_filter$col[1] & row==32, "character"),
      character = replace(character, col==col_filter$col[2] & row==31, "Closing"),
      data_type = replace(data_type, col==col_filter$col[2] & row==31, "character"),
      character = replace(character, col==col_filter$col[3] & row==31, "Current"),
      data_type = replace(data_type, col==col_filter$col[3] & row==31, "character")
    )
}else{ 
  bag=bag %>%
    mutate(
      character = replace(character, col==col_filter$col[1] & row==35, "Period"),
      data_type = replace(data_type, col==col_filter$col[1] & row==35, "character"),
      character = replace(character, col==col_filter$col[2] & row==34, "Closing"),
      data_type = replace(data_type, col==col_filter$col[2] & row==34, "character"),
      character = replace(character, col==col_filter$col[3] & row==34, "Current"),
      data_type = replace(data_type, col==col_filter$col[3] & row==34, "character")
    )
}

We then need to set up the desired headings for the output as a tibble as well as a list that will set the arrangement priority. These headings will be used to filter for and rename the headings in the comp sheets, which use varying terms to refer to the same data. The arrangement priority list will be used later on to rearrange the output to match the desired heading order once the headings are converted to factors.

if (xlsx_toggle==1){
  headings <- tibble(
    value = c(
      "Period", "Revolver", "1st Lien", "Capital Leases", 
      "2nd Lien", "Unsecured Notes", "Seller Note", 
      "Preferred Equity", "Cash", "Equity", "EBITDA", "Capex"
    )
  )
  levels_h <- c(
    "Period", "Revolver", "1st Lien", "Capital Leases", 
    "2nd Lien", "Unsecured Notes", "Seller Note", "Preferred Equity", 
    "Cash", "Equity", "EBITDA", "Capex"
  )
} else{ 
  headings <- tibble(
    value = c(
      "Period", "Revolver", "1st Lien Term Loan", "Capital Leases", 
      "2nd Lien", "Unsecured Notes", "Seller Note", "Preferred Equity", 
      "Cash", "Common Equity", "EBITDA", "Capex"
    )
  )
  levels_h <- c(
    "Period", "Revolver", "1st Lien Term Loan", "Capital Leases", 
    "2nd Lien", "Unsecured Notes", "Seller Note", "Preferred Equity", 
    "Cash", "Common Equity", "EBITDA", "Capex"
  )
}

Finally, we will perform a right_join of the bag with the col_filter, which will leave us only with the values corresponding to the columns in our filter.

bag=bag %>% # Select header and data rows
  right_join(col_filter)

Altogether, the above steps will be put together as the following:

bag %>% 
  rectify() %>% 
  format.dt.f(.)

3 Extracting the Data

Now that we have gotten sheet specific changes and values out of the way, we can use the bag of cells retrieved with the unpivotr package. Our goal will be to create a tibble with columns for values, headings to detail what the value describes, and outer headings to describe which capitalization period the values belong to. Once the values are assigned a heading and capitalization period, they can be split into lists to be added to the vector we set up at the beginning.

To start this process, we will use the unpivotr::behead function to strip the first row of headings and store the information in a new capitalization column in our tibble. By setting the direction as N, the values in our data columns will be assigned either Closing: or Current.

bag=bag %>% # Assign Current or Closing
  behead(direction = "N", name = "capitalization")

bag %>% 
  filter(col==5) %>% 
  format.dt.f(.)

Next, we can do the same process to with the West headings. We will use unpivotr::behead again to strip the West headings and store the information in a heading column within our bag tibble.

bag=bag %>% # Assign value header
  behead(direction = "W", name = "heading")

bag %>% 
  format.dt.f(.)

Now each value is assigned to a capitalization period and descriptive heading, however the headings do not match our tibble of headings. There are headings that are blank and also additional ones that we need to filter out; we also need to rename the headings to match our target output. We can filter out the additional headings and blanks through an inner_join, joining the bag$heading with the values in headings. With these appended headings, we can simply replace the original heading column in the bag by deselecting the original and renaming the appended value column.

bag=bag %>%
  # Rename headers to target headers
  regex_inner_join(headings, by = c("heading"="value")) %>%
  select(-heading) %>%
  rename(heading = value)

bag %>% 
  format.dt.f(.)

Lastly, we can use unpivotr::pack to combine the data columns into one column listing all the values and deselect the row and col since they are no longer needed. This leaves us with the tibble we need to fill our container vector as it has a column for value, a column for headings to detail what the value describes, and a column for outer headings to describe which capitalization period the values belong to.

bag=bag %>%
  select(-row, -col, -character) %>%
  pack()

bag %>% 
  format.dt.f(.)

4 Transforming the Data

Now that we have a tibble with all the required values, we can begin to fill our capitalization vector, which needs a list to fill closing, current, and summary. We can create the list for closing by first filtering the bag tibble for only values with capitalization equal to Closing.

# Separate by capitalization period, extract values into a list, 
# and set names for values equal to W headers
closing = bag %>% 
  filter(capitalization=="Closing") %>% 
  select(-capitalization)

closing %>% 
  format.dt.f(.)

There is just one problem here, we don’t have all $12$ values from our headings list. This is because not all values were present in the sheet and so our inner_join earlier did not keep those values. Thus, we need to add back those missing headings by performing a full join on closing and headings. After the join, we can convert the headers to factors with levels matching our levels_h values; this will allow us to arrange the headings to match the target order. Next, we will map a function over closing$value to set the value equal to $0$ if is.null returns true so that there are no $NA$ values. We can then clean up the names of the headings by iteratively running it through our clean_names function.

clean_names <- function(old_names){
  new_names <- old_names %>%
    gsub("'", "", .) %>% # remove quotation marks
    gsub("\"", "", .) %>% # remove quotation marks
    gsub("%", ".percent_", .) %>% # starting with "." as a workaround, to make
    # ".percent" a valid name. The "." will be replaced in the call to to_any_case
    # via the preprocess argument anyway.
    gsub("^[ ]+", "", .) %>%
    make.names(.) %>%
    # Handle dots, multiple underscores, case conversion, string transliteration
    snakecase::to_any_case(
      case = "snake" #, 
      # preprocess = "\\.",
      # replace_special_characters = c("Latin-ASCII")
    )
  new_names
}

Finally, we need closing to be a named list so we can set it as equal to the value column and use set_names to set the names of the values to the corresponding headings.

closing=closing %>%
  full_join(headings, by = c("heading" = "value")) %>%
  mutate(heading = factor(heading, levels = levels_h)) %>%
  arrange(heading) %>%
  mutate(value=map_if(value, is.null, ~0))

closing$heading=purrr::set_names(closing$heading %>% clean_names)
closing=closing$value %>% purrr::set_names(closing$heading)
closing$period=as.Date(closing$period)

closing %>% 
  map_dfc(~.x) %>% 
  format.dt.f()

We can use the same process to obtain the desired list for the current capitalization period by initially filtering for capitalization equal to ‘Current’.

current = bag %>% 
  filter(capitalization=="Current") %>% 
  select(-capitalization) %>% 
  full_join(headings, by = c("heading" = "value")) %>%
  mutate(heading=factor(heading, levels = levels_h)) %>%
  arrange(heading) %>%
  mutate(value=map_if(value, is.null, ~0))

current$heading=purrr::set_names(current$heading %>% clean_names)
current=current$value %>% purrr::set_names(current$heading)
current$period=as.Date(current$period)

current %>% 
  map_dfc(~.x) %>% 
  format.dt.f()

One last change we need to make only pertains to COMP2: we need to remember to set the common equity name back to equity to match our desired target output and 1_st_lien_term_loan to x_1_st_lien. This can be done simply with the use of the names function on element $10$ of both lists.

# rename element 10 from common equity to equity to match target headers
if(xlsx_toggle==2) {
  names(closing)[10] <- "equity"
  names(current)[10] <- "equity"
  names(closing)[3] <- "x_1_st_lien"
  names(current)[3] <- "x_1_st_lien"
}

Now that we have named lists for closing and current, we can set the corresponding elements in our capitalization vector equal to them.

capitalization$closing <- closing
capitalization$current <- current

capitalization %>%
  listviewer::jsonedit()

The closing and current values now match that of the target, the positive values for cash just need to be turned negative:

if(capitalization$closing$cash>0) 
  capitalization$closing$cash=capitalization$closing$cash*-1

if(capitalization$current$cash>0) 
  capitalization$current$cash=capitalization$current$cash*-1

Lastly, we fill capitalization$summary by using the data stored in current and closing. This has been left unchanged from the original code.

#---------------------------------------------
capitalization$summary <- vector("list", 3)
names(capitalization$summary)=c("cap_tables", "difference", "vs")
#---------------------------------------------
capitalization$summary$cap_tables <- vector("list", 3)
names(capitalization$summary$cap_tables)=c("capitalization", "ebitda", "ebitda_capex")

capitalization$summary$cap_tables$capitalization=capitalization[1:2] %>% 
  bind_rows %>%
    mutate( # period=paste(c("closing: ", "current: "), period),
        total_opco_debt = revolver + x_1_st_lien + capital_leases + 
          x_2_nd_lien + unsecured_notes + seller_note,
        total_capitalization = total_opco_debt+preferred_equity,
        total_net_debt = total_capitalization + cash,
        total_capitalization = total_net_debt + equity
    ) %>% 
  select(
    period,revolver, 
    x_1_st_lien,
    capital_leases, 
    x_2_nd_lien, 
    unsecured_notes,seller_note, 
    total_opco_debt, 
    preferred_equity, 
    total_capitalization, 
    cash, 
    total_net_debt,equity, 
    total_capitalization, 
    ebitda, 
    capex
  )

capitalization$summary$cap_tables$ebitda=bind_cols( # x EBITDA
    capitalization$summary$cap_tables$capitalization %>% 
      select(period), 
    capitalization$summary$cap_tables$capitalization %>% 
      select(-period) %>% 
      mutate_all(funs(ifelse(. >=0, . / ebitda, NA_real_)))
)

capitalization$summary$cap_tables$ebitda_capex=bind_cols( # x EBITDA - Capex
    capitalization$summary$cap_tables$capitalization %>% 
      select(period), 
    capitalization$summary$cap_tables$capitalization %>% 
      select(-period) %>% 
      mutate_all(funs(ifelse(. >=0, . / (ebitda-capex), NA_real_)))
)

#---------------------------------------------
capitalization$summary$difference <- vector("list", 3)

# Current vs. Closing
capitalization$summary$difference[[1]]=bind_rows(
    capitalization$summary$cap_tables$capitalization %>% 
      mutate(period=as.character(period)),
    capitalization$summary$cap_tables$capitalization %>% 
      mutate_all(funs(diff(.))) %>% 
      slice(1) %>% 
      mutate(period="Current vs. Closing")
) # %>% mutate_at(names(capitalization$summary$cap_tables$capitalization)[-1], formattable::accounting)


capitalization$summary$difference[[2]]=bind_rows(
    capitalization$summary$cap_tables$ebitda %>% 
      mutate(period=as.character(period)),
    capitalization$summary$cap_tables$ebitda %>% 
      mutate_all(funs(diff(.))) %>% slice(1) %>% 
      mutate(period="Current vs. Closing")
) # %>% mutate_at(names(capitalization$summary$cap_tables$ebitda)[-1], formattable::accounting)

capitalization$summary$difference[[3]]=bind_rows(
    capitalization$summary$cap_tables$ebitda_capex %>% 
      mutate(period=as.character(period)),
    capitalization$summary$cap_tables$ebitda_capex %>% 
      mutate_all(funs(diff(.))) %>% 
      slice(1) %>% 
      mutate(period="Current vs. Closing")
) # %>% mutate_at(names(capitalization$summary$cap_tables$ebitda_capex)[-1], formattable::accounting)
#---------------------------------------------
capitalization$summary$vs <- vector("list", 2)
names(capitalization$summary$vs)=c("closing", "current")

# Capitalization @ Closing
capitalization$summary$vs$closing=bind_rows(
    capitalization$summary$cap_tables$capitalization %>% slice(1) %>% mutate(period=as.character(period)),
    capitalization$summary$cap_tables$ebitda %>% slice(1) %>% mutate(period="x EBITDA"),
    capitalization$summary$cap_tables$ebitda_capex %>% slice(1) %>% mutate(period="x EBITDA - Capex")
) # %>% mutate_at(names(capitalization$summary$cap_tables$capitalization)[-1], formattable::accounting)

# Current Capitalization
capitalization$summary$vs$current=bind_rows(
    capitalization$summary$cap_tables$capitalization %>% 
      slice(2) %>% 
      mutate(period=as.character(period)),
    capitalization$summary$cap_tables$ebitda %>% 
      slice(2) %>% 
      mutate(period="x EBITDA"),
    capitalization$summary$cap_tables$ebitda_capex %>% 
      slice(2) %>% 
      mutate(period="x EBITDA - Capex")
) # %>% mutate_at(names(capitalization$summary$cap_tables$capitalization)[-1], formattable::accounting)

Our final outputs will match the target and appear as:

capitalization %>%
  listviewer::jsonedit()

Walkthrough: Capitalization

Nico

2022-11-20

1 Objective

2 Isolating the Data

3 Extracting the Data

4 Transforming the Data