Snowflake's REDUCE Function to Glean Insights from SEC Filing Data

 Summary

• What is the REDUCE Higher-order Function?
• Learn the JSON structure of the SEC company filing from an example.
• What fiscal end periods are represented in the JSON document?
• Answer the question using the REDUCE higher-order function in Snowflake.

What is the REDUCE Higher-order Function?

Recently, Snowflake made REDUCE Higher-order function generally available. This function adds another powerful, easy-to-use tool to your toolkit to process arrays. The REDUCE function allows you to accumulate values across an array into a single value. It takes an array as input, an initial accumulator value, and a Lambda expression that defines the logic for processing each array element.

REDUCE( <array> , <init> , <lambda_expression> ) 

The JSON Structure of the SEC Filing

My goal is to understand the cash carried by Kimberly-Clark Corporation in its balance sheet. The company is known for its products, such as Huggies and Cottonelle. I want to list all the fiscal end dates in the data. There can be inconsistencies in the data filed with the SEC, especially concerning the fiscal periods represented, so knowing what fiscal periods are in the data can be invaluable. Also, the SEC filing may have repeated data. This is because investors wish to compare current results with past results, so a Q2 report should include Q1 and Q2 data from the previous year. So, the SEC filing would have repetitions.

Note: You can learn about my External Table structure here. In my LinkedIn profile, you can read a series of blogs about my setup to query SEC filings.

Here's the JSON structure we will use in the REDUCE function:

{ 
"cik": 55785, 
"description": "Amount of currency on hand as well 

as demand deposits with banks or financial institutions. 

Includes other kinds of accounts that have the general characteristics of 

demand deposits.", 
"entityName": "KIMBERLY-CLARK CORPORATION", 
"label": "Cash and Cash Equivalents, at Carrying Value", 
"tag": "CashAndCashEquivalentsAtCarryingValue", 
"taxonomy": "us-gaap", 
"units":  { 
"USD": [
                  { 
                    "accn": "0001193125-10-038621", 
                    "end": "2006-12-31", 
                    "filed": "2010-02-24", 
                    "form": "10-K", 
                    "fp": "FY", 
                    "frame": "CY2006Q4I", 
                    "fy": 2009, 
                    "val": 361000000 
                  }, 
                  {
                    "accn": "0000055785-09-000026", 
                    "end": "2007-12-31", 
                    "filed": "2009-08-07", 
                    "form": "10-Q", 
                    "fp": "Q2", 
                    "fy": 2009, 
                    "val": 473000000
                  }
               ]
                }
}

What fiscal end periods are represented in the JSON document?

I have created an external table called CONS_STAPLES_CASH_AND_CASH_EQUIVALENTS. For the REDUCE function, the input is the path to the array:

Path to the Array Elements:

VALUE:"units":"USD" 

I wish to get a concatenated string of all the fiscal end periods represented in the JSON document. This is represented by the "end" key. This is represented in the init parameter as ''. Finally, in the Lambda Expression, the arg1 argument is the accumulator, and the arg2 argument is the current element being processed in the array.

Lambda Expression and the Query:

(arg1, arg2) -> arg1 || ' ' || arg2:"end" || ', '

SELECT 
            TICKER_SYMBOL, 
            VALUE, 
            REDUCE(VALUE:"units":"USD", '',  (arg1, arg2) -> arg1 || ' ' || arg2:"end" || ', ')           
                                                                                             FISCAL_PERIOD_END_DATES 
FROM 
            CONS_STAPLES_CASH_AND_CASH_EQUIVALENTS_ET;

When I execute the query, the REDUCE function retrieves the value for the "end" key, concatenates it to the accumulator, and returns it (Exhibit 1). The screenshot shows that for Kimberly-Clark Corp (KMB), the JSON has data from the fiscal period ending 2006-12-31. But for Target (TGT), the data in the JSON is from 2016-01-30.

Exhibit 1: The Fiscal Period End Date Returned by the REDUCE function.

Snowflake Snowsight

I can also tell that the SEC filing has duplicate data that I must handle in my query. For example, I can see that the 2007-12-31 is represented multiple times in the file I downloaded from the SEC (Exhibit 2).

Exhibit 2: Fiscal Period End Dates Accumulated By the REDUCE Higher-order Function.

SEC.GOV

I can quickly see the data in my JSON files downloaded from the SEC. I did not have to use a LATERAL FLATTEN to get at the data. The REDUCE function boosts my efficiency when I am dealing with JSON data. Try out Snowflake's REDUCE and other Higher-order functions; they will make you more productive.

Why ViacomCBS's Preferred Shares (VIACP) are a Great Buy Currently Offering 10.68% Dividend Yield?

In January 2021, during the start of the meme stock bubble, ViacomCBS (VIAC) stock started climbing from about $36. The stock kept on climbing until March 22, 2021, when it traded briefly above $100. Other than a few Wall Street banks, nobody knew why it was rising. Even the Wall Street banks did not have a complete picture. It was also around the Super Bowl, so CNBC host Jim Cramer speculated that it might have had something to do with all the Paramount+ ads that aired during the Super Bowl. Paramount+ is ViacomCBS's streaming brand. 

But, Bob Bakish, the CEO of ViacomCBS, did not care about why the stock was rising. He needed all the money he could get to feed his company's push into the streaming business, and with the stock trading at an all-time high, he decided to make use of the opportunity. So, the company sold 10 million preferred shares at $100 with a 5.75% yield and issued a further 20 million of class B common stock. The class B common stock causes immediate dilution of existing shareholders.

Exhibit: The Spike in the Price of ViacomCBS During the Archegos Scandal 

(Source: Seeking Alpha)

The dividend on the preferred works out to a quarterly dividend of $1.435. The preferred shares have a mandatory conversion clause that would take effect in April 2024. The preferred shares will convert between 1.0013 and 1.1765 into ViacomCBS common shares. Since the preferred stock is currently trading below the offer price of $100, each preferred share would be converted to a maximum of 1.1765 shares of the ViacomCBS common shares.  

Once Bob made this announcement about the issuance of the common and preferred shares, the stock promptly tanked. Soon it became clear that ViacomCBS and Discovery (DISCA), Inc (home of HGTV, Food Network, and other channels, were both caught up in the Archegos Capital Management scandal that nearly brought down Credit Suisse. Other Wall Street banks booked millions of dollars in losses when they allowed Bill Hwang (owner of Archegos Capital Management) to speculate on the stocks of ViacomCBS and Discovery. Credit Suisse booked over $5 billion in losses due to the scandal.

Exhibit: Spike in Discovery Shares During the Archegos Scandal

(Source: Seeking Alpha)

The stock has flat-lined lately due to increased competition in the streaming business and the increased cost of customer acquisition. Did you notice the deal that Discovery was offering during the 2021 Thanksgiving break? Discovery offered three months of subscription at $0.99 per month.     

ViacomCBS is a solid company with good cashflows, and its content library is top-notch. The preferred stock now offers a 10.6% dividend yield, which you can get until 2024. In early 2024, we can decide to either stay invested in the preferred shares or exit before the mandatory conversion date in April.

The debt to EBITDA multiple is at 4x. So, debt is a concern, and I prefer to see companies at a debt to EBITDA multiple 2x. One caveat to remember is that Discovery is acquiring Warner Media from AT&T. Discovery will have a 5x debt to EBITDA after the acquisition.

Exhibit: Debt to EBITDA Ratio for ViacomCBS, Walt Disney Co., and Discovery 

(Source: Seeking Alpha)

The high level of debt may be negative, but the company has the cashflows to cover it. Its quick ratio is above 1 indicating that the company has the liquidity to cover the current liabilities.  

Exhibit: Quick Ratio for ViacomCBS, Walt Disney Co., and Discovery

(Seeking Alpha)

 

The company's return on equity is much higher than that of Walt Disney and Discovery and comes in at around 18%. 

Exhibit: Return on Equity for ViacomCBS, Walt Disney, and Discovery

(Source: Seeking Alpha)

Its return on invested capital is higher than that of Walt Disney and Discovery.  

Exhibit: Return on Invested Capital for ViacomCBS, Walt Disney, and Discovery

(Source: Seeking Alpha)

I own both ViacomCBS Class B (VIAC) and the preferred stock (VIACP).  I also own Discovery (DISCA), and Walt Disney (DIS). But, I am most bullish about ViacomCBS. They have already acquired over 43 million customers. They have a customer acquisition deal with T-mobile to offer their Paramount+ subscription as part of their plan. That should bring in millions more customers to ViacomCBS.  

Exhibit: Paramount+ Subscription Included in T-Mobile Cell Phone Service 

(Source: T Mobile)

Finally, the company is trading at an EV to EBITDA multiple of just 6x and Discovery is trading at just around 3x. Disney is trading at an EV to EBITDA multiple of 34x. Both ViacomCBS and Discovery are trading at steep discounts. Yes, the competition is intense and the customer acquisition cost is high, but the content is top-notch. All media companies need content more than ever. But ViacomCBS will thrive in the long term.  The ViacomCBS' dividend on the preferred and the common is too good to pass up.       

 

    

    

MongoDB: Number of $100,000 or greater ARR Customers

Here's how MongoDB (NASDAQ: MDB) defines Annualized Recurring Revenue (ARR):

"We define ARR as the subscription revenue we would contractually expect to receive from customers over the following 12 months assuming no increases or reductions in their subscriptions."

Number of  $100,000 ARR Customers each year:

• 2016: 164
• 2017: 246
• 2018: 354
• 2019: 557
• 2020: 751
• 2021: 975

42.8% Compound Annual Growth Rate in $100K or greater ARR customers.  

(Source: MongoDB.com)

The company also closely monitors ARR expansion rate and here's is what they have to say how that:

"We also examine the rate at which our customers increase their spend with us, which we call net ARR expansion rate. We calculate net ARR expansion rate by dividing the ARR at the close of a given period (the “measurement period”), from customers who were also customers at the close of the same period in the prior year (the “base period”), by the ARR from all customers at the close of the base period, including those who churned or reduced their subscriptions. For Direct Sales Customers included in the base period, measurement period or both such periods that were self-serve customers in any such period, we also include annualized MRR from those customers in the calculation of the net ARR expansion rate. Our net ARR expansion rate has consistently been over 120%."

The ARR expansion rate is another critical metric for software subscription companies. It shows whether the customer is willing to buy more of the product and/or expand its use within the company.  

Sources:

• Form 10-K (Annual Report) FY 2021
• Form 10-K FY 2018