How Much Does Coca-Cola Spend on Advertising?

Coke's AI Generated Ad (Source: WSJ.com)

Ads are meant to evoke a reaction, an emotion, and an action. Great ads can bring you to tears or make you laugh, and those stories and, hopefully, the brands behind them will stick with you forever, generating revenue and increasing the brands' customer lifetime value.

Apple made some of the most iconic ads during the Steve Jobs reign.

• Think Different - This is on my list of all-time favorite ads.
• 1984—This ad aired during Super Bowl XVIII (18) in 1984 and forever changed the NFL and the ad market in the U.S.
• Nike "Failure"—Nike is known for its great ads, and this one by Michael Jordan evokes so much emotion and has thus stood the test of time. Nike spent $4.2 billion on ads in FY 2024 (See data below under the consumer discretionary sector).

Using Snowflake, I analyzed the advertising spending of some of the largest companies in the U.S. Here are some of my findings.

How much did Coca-Cola and Pepsi spend on advertising?

Coca-Cola spent $5 billion on advertising in fiscal year 2023, a 20% increase from its spending in 2022, when it spent $4 billion. Over the past decade, the company has consistently spent $4 billion on advertising (Exhibit 1). Recently, the company has started experimenting with AI to create some of its ads. AI promises massive cost savings for advertisers, and they are using it to stretch their ad budgets.

Exhibit 1: Coca-Cola Ad Spending

SEC.gov

PepsiCo spent $3.8 billion on advertising in 2023 (Exhibit 2), an increase of 8.5% from 2022, when it spent $3.5 billion. Over the past decade, the company has increased ad spending by 65%.

Exhibit 2: Pepsico Advertising Spending.

SEC.gov

Why are Coca-Cola and PepsiCo increasing their ad budget?

One might expect ad budgets to go up at the pace of inflation, but high-single-digit or double-digit growth is scarce. I hypothesize that consumer staples companies have struggled with the lack of volume growth ever since the Pandemic-fueled consumption ended. Many consumer staples companies also face increased competition from store brands such as Costco's Kirkland, Walmart's Great Value, and Amazon's many store brands.

Another reason is that the price increase regime is ending. Consumers globally are frustrated by the price increases and are pulling back on consumption. So, further price increases that would have driven revenue and profitability growth are difficult to sustain now.

Ad Spending By Consumer Staples Companies

Here's a list of some of the biggest ad spenders in the consumer staples sector (Exhibit 3).

Exhibit 3: Ad Spending by Consumer Staples Companies.

Sec.gov

Procter & Gamble, with its storied list of billion-dollar brands (Exhibit 4), protects its turf by spending close to $10 billion in ads annually.

Exhibit 4: Just a Few Procter & Gamble Brands

(Source: Procter & Gamble Website)

Ad Spending By Consumer Discretionary Companies

Here's a list of some of the biggest ad spenders in the consumer discretionary sector (Exhibit 5). Surprisingly, Amazon is one of the largest spenders on advertising in the consumer discretionary sector. Yes, Amazon is categorized under discretionary spending, although it generates vast profits from its AWS division. The General Motors data is from 2013, and the API seems to return data until the fiscal year 2013. General Motors spend $3.6 billion on advertising in fiscal year 2023.

Exhibit 5: Consumer Discretionary Companies Ad Spending (Top 10)

SEC.GOV

Exhibit 6: Consumer Discretionary Companies Ad Spending (Bottom 9)

SEC.GOV

Ad Spending By Pharmaceutical Companies

Big Pharma is a big ad spender. Nobody in the U.S. can escape those ads for new pharmaceuticals. Here are the biggest ad spenders among US-listed pharmaceutical companies. Pfizer remains the top advertiser in the pharma industry (US-listed stocks).

Exhibit 7: Ad Spending By Big Pharma

SEC.GOV

Companies are spending vast sums on advertising. This part of the corporate budget may not abate any time soon, but it remains to be seen whether AI can help create impactful stories to lure consumers to brands.

Snowflake is one of the most accessible data platforms for businesses to gain insights quickly. I used Snowflake to query the SEC data and present it here. You can try Snowflake here.

Disclosures: I am a Sales Engineer at Snowflake. All opinions in this blog post are solely mine and do not reflect Snowflake's views. I am not a Registered Investment Advisor, and any discussion on securities or investments is not an inducement to make a particular investment.

Retail Inventory Status - 2024 Holiday Season

 The 2024 holiday shopping season has officially started with Black Friday on November 29. I want to get answers to a couple of questions:

• How much inventory did U.S. retailers carry coming into this holiday season?
• How has this inventory increased or decreased since last year?

Retailers are enjoying the benefits of robust economic growth and low unemployment. At the end of October, the unemployment rate stood at 4.1%, and inflation was 2.6%. Inflation has come down dramatically since a dramatic increase in 2021 and 2022. Since inflation affects inventory costs, the data shows a dramatic increase in inventory held on a company's balance sheet in 2020 and 2021. The initial demand shock of 2020 during March quickly turned into a supply shock as demand skyrocketed as stimulus was rolled out. The increased demand and supply chain constraints likely increased the costs of procuring inventory.

Amazon, for example, saw a steep 30% increase in inventory costs between 2020 and 2021 and another 18% increase in 2023 (Exhibit 1). In 2024, the company only showed a modest 1.9% increase in its inventory. Amazon has a massive number of third-party sellers on its marketplace, and their inventory is not included in Amazon's balance sheet.

Exhibit 1: Amazon's Inventory Costs in its Balance Sheet.

SEC, Snowflake Cortex Analyst, Excel

Similarly, Walmart saw a double-digit increase in its inventory costs in 2021 and 2022 but showed a 1.9% decrease in 2024 compared to 2023 (Exhibit 2). However, Walmart's data is for the fiscal period ending July 2024.

Exhibit 2: Walmart's Inventory Costs.

SEC, Snowflake Cortex Analyst, Excel

Among the big three retailers, Amazon, Walmart, and Costco, Costco has increased its inventory by a double-digit rate going into the 2024 holiday season (Exhibit 3). However, Costco has an excellent inventory turnover practice, so it will quickly convert its inventory into cash. Costco typically only carries 30 days of sales in inventory, the lowest among these three retailers. You can read more about Costco here.

Exhibit 3: Costco's Inventory Costs.

SEC, Snowflake Cortex Analyst, Excel

I looked at the inventory for a few other retail companies, including Dick's Sporting Goods, Deckers Outdoor, Home Depot, Lowe's, Burlington Stores, and Autozone (Exhibit 4). Most of these companies are coming into this holiday season with a mid-to-upper single-digit increase in inventory compared to last year. Lowe's is being cautious, with almost no change in inventory since 2023. Dick's Sporting Goods has the highest inventory cost increase of 13% over 2023.

Exhibit 4: Inventory Costs of Various Other Retailers.

SEC, Snowflake Cortex Analyst, Excel

Inventory costs are normalizing across the board for retailers after the tumult caused by the pandemic. Dick's Sporting Goods increase in inventory costs is a bit worrying, followed by Burlington Stores, Deckers Outdoor, and Autozone. They are hoping for a strong holiday season.

I generated the SQL using Snowflake Cortex Analyst. Cortex Analyst is a massive productivity booster that takes just a little time to get started. Snowflake is one of the most accessible data platforms for businesses to gain insights quickly. You can try it here.

Disclosures: I am a Sales Engineer at Snowflake. All opinions in this blog post are solely mine and do not reflect Snowflake's views. I am not a Registered Investment Advisor, and any discussion on securities or investments is not an inducement to make a particular investment.

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.

Purchase Price Matters: Implementing Volume-Weighted Average Price (VWAP) in Snowflake

 Summary:

• A brief description of VWAP and its importance in trading and asset management.

• The process to calculate VWAP.

• An overview of the Snowflake features used to implement VWAP.

• The architecture of VWAP implementation in Snowflake.

• Code examples

• Examples of charting Microsoft's and Nestle's VWAP in Python.

What is VWAP?

Volume-weighted Average Price (VWAP) is a price signal that takes into account the trading volume. The logic behind the VWAP is simple: if investors think an asset is undervalued compared to its current price, they will purchase more of that asset. Investors use the VWAP as a benchmark price to make buying or selling decisions. If an asset is currently trading above the VWAP for the day, the trader may decide to sell or short an asset with the expectation that the asset would revert to the VWAP line, giving the trader a handsome profit. A trader may consider taking a long position if the asset's current price is below the VWAP.

A portfolio manager looking to acquire assets for her fund may use VWAP as the price to beat - a purchase price at or below VWAP would be considered reasonable. The portfolio manager would feel happy that she did not overpay for an asset.

Purchase Price Matters

The title of this article—Purchase Price Matters—comes from the excellent interview conducted by Nicolai Tangen (CEO of Norges Bank Investment Management) of Marc Rowan (CEO of Apollo Global Management, Inc.). Marc uses this phrase to state that every investment is a value investment. If you overpay for an asset, your investment returns will be lower - a simple yet profound thought. You can listen to the interview in the podcast - In Good Company With Nicolai Tangen. There are many amazing interviews in this podcast. These are three other episodes I would highly recommend:

• Vinod Khosla: Future Trends and the Power of the Improbable.

• Sal Khan - Personalized education, AI, and the drive to learn.

• Satya Nadella, Microsoft Chairman & CEO: AI, chip shortage, empathy, and poetry

VWAP Calculation

Investment firms may have their proprietary methodology for calculating VWAP. To implement VWAP in Snowflake, I have followed the method outlined in Investopedia, considering data availability and simplicity.

Here are the steps in this method:

• Take the average of high, low, and close prices for each period.

• If your VWAP period is 5 minutes, you will take the average of the high, low, and close for this period during the trading day. You arrive at the Typical Price for the asset.

  Typical Price (TP) = (High + Low + Close) / 3

• In my calculation, I only used the closing price for a period as my Typical Price.

• Next, multiply the Typical Price by the Trading Volume in this period.

Typical Price Volume (TPV) = Typical Price * Volume

• The VWAP is calculated by dividing the Typical Price Volume by the Volume. In this case, the VWAP for the first 5-minute time period would equal the Typical Price.

VWAP = Typical Price Volume / Volume

• The final step is to calculate the cumulative TPV over a period of time (for e.g. 60 minutes, a day, several days, or a year) and divide it by the sum of volume over the same period.

Cumulative VWAP = (TPV 1st 5-Min) + TPV 2nd 5-Min)/SUM(Vol 1st 5-Min + Vol 2nd 5-Min)

Data Provider

I used Polygon.IO as the data provider. In its free tier, Polygon provides a trade aggregates API that aggregates trades over 1-minute, 5-minutes, hours, days, weeks or months. I have used this data to demonstrate the VWAP implementation in Snowflake. The aggregates data is in the format:

{
  "adjusted": true,
  "next_url": "https://api.polygon.io/v2/aggs/ticker/AAPL/range/1/day/1578114000000/2020-01-10?cursor=bGltaXQ9MiZzb3J0PWFzYw",
  "queryCount": 1,
  "request_id": "6a7e466379af0a71039d60cc78e72282",
  "results": [
    {
      "c": 75.0875,
      "h": 75.15,
      "l": 73.7975,
      "n": 1,
      "o": 74.06,
      "t": 1577941200000,
      "v": 135647456,
      "vw": 74.6099
    },
  ],
  "resultsCount": 1,
  "status": "OK",
  "ticker": "AAPL"
}

VWAP Implementation on Snowflake

Before we get into the architecture, let's introduce some of the Snowflake features used to implement VWAP:

• Snowflake Storage Integration stores the identity and access information for the AWS S3 Bucket.

• A Snowflake Stage object identifies the location where the files are stored.

• Snowflake Snowpipe enables loading of data from files in batches. One can use a COPY statement in Snowpipe to automate the loading of file. An AWS S3 Bucket can be configured to notify Snowpipe of available files to load into Snowflake using AWS Simple Queue Service (SQS).

• The COPY INTO <table> SQL statement helps load data from files into an existing table.

• Snowflake Dynamic Tables offers a simple way to automate the transformation of data. You can easily create data pipelines using Dynamic Tables.

• The TIME_SLICE SQL function calculates the beginning or end of a "slice" of time.

• The Window functions are used to aggregate data over a period of time. I use this to calculate the cumulative VWAP. The Window functions are used to aggregate over a group of related rows, known as a partition. In our case the partion is the TICKER_SYMBOL - MSFT, AAPL, PEP, etc.

• Snowflake Pandas API allows you to run Pandas code in a distributed manner.

This is the architecture (Exhibit 1):

Exhibit 1: Volume-Weighted Average Price Architecture.

VWAP Implementation on Snowflake

• I use a Python app to access the Polygon API and store the JSON output in an AWS S3 Bucket.

• The AWS S3 Bucket is configured to notify Snowflake Snowpipe when a file lands in the Bucket.

• When Snowpipe receives the notification, its picks up the file from the Bucket and loads the raw JSON data into a table in Snowflake.

• At this point, a Dynamic Table, PARSE_STOCK_TRADES_DT, starts the process of transforming the JSON data by parsing the various keys.

• Another Dynamic Table, STOCK_TRADES_INTERMEDIATE_VWAP_DT, calculates the VWAP for various stocks over 20-minute time slices. In short, we take the 1-minute aggregate data from Polygon and calculate the VWAP for 20-minute slices.

• Finally, the last Dynamic Table, VWAP_STOCK_TRADES_DT, calculates the cumulative VWAP using a Window function to aggregate the price and volume data over all the previous rows and the current row.

• The final VWAP from the VWAP_STOCK_TRADES_DT can be presented in a dashboard as a chart.

The Snowflake Code Samples

Creating a Storage Integration

CREATE STORAGE INTEGRATION companystockprices_storage_int 
TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = 'S3' 
ENABLED = TRUE 
STORAGE_AWS_ROLE_ARN = '<AWS  IAM Role ARN>' STORAGE_ALLOWED_LOCATIONS = ('*');

Create a Stage Object

CREATE STAGE COMPANY_STOCK_TRADES_STG 
URL = '<AWS S3 Bucket Path>' 
STORAGE_INTEGRATION = companystockprices_storage_int;

Create a Table to Store the JSON Trade Data

CREATE OR REPLACE TRANSIENT TABLE COMPANY_STOCK_TRADES_RAW 
( 
TICKER VARIANT, 
RESULTS VARIANT
);

Create a Snowpipe

CREATE OR REPLACE PIPE COMPANY_STOCK_TRADES_PIPE 
AUTO_INGEST = TRUE 
AS 
COPY INTO DEMODB.EQUITY_RESEARCH.COMPANY_STOCK_TRADES_RAW 

FROM @DEMODB.EQUITY_RESEARCH.COMPANY_STOCK_TRADES_STG 

MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE 
FILE_FORMAT = (type = 'JSON' STRIP_OUTER_ARRAY = TRUE);

Create a Dynamic Table to Store the Parsed Data from the Raw Table

CREATE OR REPLACE TRANSIENT DYNAMIC TABLE PARSE_STOCK_TRADES_DT 
( 
TICKER_SYMBOL   VARCHAR, 
TRADE_TIME           TIMESTAMP_NTZ, 
TRADE_PRICE         NUMBER(20, 4), 
TRADE_VOLUME    NUMBER
) 
TARGET_LAG = DOWNSTREAM 
WAREHOUSE = DEMO_XSMALL_WH 
REFRESH_MODE = INCREMENTAL 
AS 
SELECT 
TICKER::VARCHAR TICKER_SYMBOL, 

TO_TIMESTAMP_NTZ(TO_NUMBER(trades.VALUE:"t"),3) TRADE_TIME, 

TO_NUMBER(trades.VALUE:"c",14, 4) 

TRADE_PRICE, TO_NUMBER(trades.VALUE:"v") TRADE_VOLUME 
FROM 
COMPANY_STOCK_TRADES_RAW CSTR, 
LATERAL FLATTEN (input => CSTR.RESULTS) TRADES 
ORDER BY TICKER_SYMBOL, TRADE_TIME;

Create a Dynamic Table to Calculate the Intermediate VWAP

CREATE OR REPLACE TRANSIENT DYNAMIC TABLE INTERMEDIATE_VWAP_STOCK_TRADES_DT 
( 
TRADE_TIME_SLICE                TIMESTAMP_NTZ, 
TICKER_SYMBOL                   VARCHAR, 
SUM_PRICE                       NUMBER(20, 4), 
SUM_VOLUME                      NUMBER, 
INTERMEDIATE_SUM_PRICE_VOLUME   NUMBER(20, 4), 
INTERMEDIATE_VWAP               NUMBER(20, 4)
) 
TARGET_LAG = DOWNSTREAM 
WAREHOUSE = DEMO_XSMALL_WH 
REFRESH_MODE = INCREMENTAL 
AS 
SELECT 
TIME_SLICE(TRADE_TIME, 20, 'MINUTE') TRADE_TIME_SLICE, 

SUM(TRADE_PRICE)    SUM_PRICE,

SUM(TRADE_VOLUME)   SUM_VOLUME, 

SUM(TRADE_PRICE * TRADE_VOLUME) INTERMEDIATE_SUM_PRICE_VOLUME,
 
SUM(TRADE_PRICE * TRADE_VOLUME)/SUM(TRADE_VOLUME)  INTERMEDIATE_VWAP 

FROM  
PARSE_STOCK_TRADES_DT 
GROUP BY TICKER_SYMBOL, TRADE_TIME_SLICE
ORDER BY TICKER_SYMBOL, TRADE_TIME_SLICE; 

Create a Dynamic Table to Calculate the Cumulative VWAP

CREATE OR REPLACE TRANSIENT DYNAMIC TABLE VWAP_STOCK_TRADES_DT 
( 
TRADE_TIME_SLICE                    TIMESTAMP_NTZ, 
TICKER_SYMBOL                       VARCHAR, 
TICKER_SYMBOL_TRADE_TIME_SLICE      VARCHAR,  
CUMULATIVE_PRICE                    NUMBER(20,4), 
CUMULATIVE_VOLUME                   NUMBER, 
FINAL_VWAP                          NUMBER(20,4)
) 
TARGET_LAG = '30 minutes' 
WAREHOUSE = DEMO_XSMALL_WH 
REFRESH_MODE = INCREMENTAL 
AS 
SELECT 

TRADE_TIME_SLICE, 
TICKER_SYMBOL, 

(SUM(SUM_PRICE) OVER  (PARTITION BY TICKER_SYMBOL ORDER BY TRADE_TIME_SLICE ASC ROWS 

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) CUMULATIVE_PRICE, 

(SUM(SUM_VOLUME) OVER (PARTITION BY TICKER_SYMBOL ORDER BY TRADE_TIME_SLICE ASC ROWS 

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) CUMULATIVE_VOLUME,

(SUM(INTERMEDIATE_SUM_PRICE_VOLUME) OVER (PARTITION BY TICKER_SYMBOL 

ORDER BY TRADE_TIME_SLICE ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))

/(SUM(SUM_VOLUME) OVER (PARTITION BY TICKER_SYMBOL ORDER BY TRADE_TIME_SLICE ASC ROWS 

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) FINAL_VWAP 

FROM 
INTERMEDIATE_VWAP_STOCK_TRADES_DT 

ORDER BY TICKER_SYMBOL, TRADE_TIME_SLICE ASC;

You can visualize the data pipeline in Snowflake Snowsight (Exhibit 2). The active Dynamic Tables are shown with the dark blue arrows.

Exhibit 2: Data Pipeline Graph in Snowsight.

Data Pipeline Visualized in Snowsight

Charting Intermediate and Cumulative VWAP in Python

In Snowflake Notebook, you can easily create a session object using get_active_session()

import streamlit as st 
import matplotlib.pyplot as plt 
import seaborn as sns

# Snowpark Pandas API. 
# We are loading all the data in the Snowflake Pandas Data Frame.

import modin.pandas as spd 

# Import the Snowpark pandas plugin for modin 
import snowflake.snowpark.modin.plugin 

from snowflake.snowpark.context import get_active_session
 # Create a snowpark session 
session = get_active_session()

# Name of the sample database and the schema to be used 

SOURCE_DATA_PATH = "DEMODB.EQUITY_RESEARCH" 

Query the intermediate VWAP values from the INTERMEDIATE_VWAP_STOCK_TRADES_DT

# Query the Intermediate VWAP Dynamic Table.

intermediate_VWAP_df = spd.read_snowflake(f"{SOURCE_DATA_PATH}

                .INTERMEDIATE_VWAP_STOCK_TRADES_DT")

                .sort_values(["TICKER_SYMBOL","TRADE_TIME_SLICE"], ascending = True) 

# Filter for the MSFT Values in the Pandas Data Frame.

filtered_intermediate_VWAP_df = intermediate_VWAP_df

                        .where(intermediate_VWAP_df['TICKER_SYMBOL'] == 'MSFT') 

# Remove all the NONE values from the Pandas Data Frame.

filtered_intermediate_VWAP_df = filtered_intermediate_VWAP_df.dropna()

Query the cumulative VWAP from the VWAP_STOCK_TRADES_DT

# Query the Cumulative VWAP Table 

final_VWAP_df = spd.read_snowflake(f"{SOURCE_DATA_PATH}.VWAP_STOCK_TRADES_DT")

# Filter for the MSFT values in the Pandas Data Frame.

filtered_final_VWAP_df = final_VWAP_df.where(final_VWAP_df['TICKER_SYMBOL'] == 'MSFT') 

# Remove all the NONE values from the Pandas Data Frame.

filtered_final_VWAP_df = filtered_final_VWAP_df.dropna()

Merge the intermediate VWAP and the Cumulative VWAP to use in a Python chart.

# Merge the Intermediate VWAP and Cumulative VWAP

spd_intermediate_and_final_vwap_df = filtered_intermediate_VWAP_df.merge(filtered_final_VWAP_df,
                                            left_on='TICKER_SYMBOL_TRADE_TIME_SLICE', 
                                            right_on='TICKER_SYMBOL_TRADE_TIME_SLICE', 
                                            how='left')

Use the merged the Snowflake Pandas Data Frame to plot the chart.

data = {  
'TRADE_TIME_SLICE_x': spd_intermediate_and_final_vwap_df['TRADE_TIME_SLICE_x'], 

'INTERMEDIATE_VWAP': spd_intermediate_and_final_vwap_df['INTERMEDIATE_VWAP'], 

'FINALVWAP': spd_intermediate_and_final_vwap_df['FINAL_VWAP']
} 

df = spd.DataFrame(data)
 
# Create the plot 
plt.figure(figsize=(15, 6)) 

plt.plot(df['TRADE_TIME_SLICE_x'], df['INTERMEDIATE_VWAP'], label='INTERMEDIATE_VWAP') 

plt.plot(df['TRADE_TIME_SLICE_x'], df['FINAL_VWAP'], label='FINAL_VWAP') 

# Add title, labels, and legend 
plt.title('Microsoft Volume Weighted Average Price (VWAP)') 

plt.xlabel('TRADE') plt.ylabel('VWAP') plt.legend() # Show the plot plt.show()

I merged the intermediate VWAP (20-Minute Time Slice) and the cumulative VWAP (From Feb 2023) and plotted it in a chart in Snowflake Notebook using Python. Here's how it looks:

We can see from the chart (Exhibit 3) that Micrsoft is currently trading (Intermediate VWAP - blue line) well above its cumulative VWAP line (yellow line). Microsoft is benefitting from the AI-led demand for its products and services mixed with the euphoria and promise of more gains to come from AI-related product releases.

Exhibit 3: Microsoft's Intermediate VWAP (20-Minute Window) and Cumulative VWAP (Since Feb 2023)

Microsoft's Intermediate VWAP (20-Minute Window) and Cumulative VWAP (Since Feb 2023)

On the other end of the spectrum is Nestle. The company is having a no good, very bad year since October 2023. Its intermediate VWAP (20-Minute Time Slice) has dropped well below its cumulative VWAP (Exhibit 4). Nestle has suffered from sky rocketing prices for cocoa and coffee that has pressured its margins. This is Nestle, one of the most iconic brands with a multitude of well-known products and sales in over a 100 countries, it's in pretty bad shape today, but they should recover in the coming years.

Exhibit 4: Nestle Intermediate VWAP (20-Minute Time Slice) and Cumulative VWAP (Since Feb 2023).

Nestle Intermediate VWAP (20-Minute Time Slice) and Cumulative VWAP (Since Feb 2023).

In just a couple of hours you can ingest raw market data into Snowflake and transform it into signals such as Volume-Weighted Average Price (VWAP).

Does Big Tech Hold More Cash Than the Federal Government?

 Summary:

• How much cash does the U.S. Federal Government carry on its balance sheet?

• How does that cash compare to the money on Big Tech's balance sheet?

• How is Big Tech spending on share repurchases?

• Theoretically, how much cash could Big Tech have on its balance sheet, including the repurchase amounts?

How much cash does the U.S. Federal government carry in its Balance Sheet?

The U.S. Treasury Department owns and publishes the government's balance sheet annually. At the end of 2023, the U.S. government held $922 billion in cash (Exhibit 1). Although the Federal government, with congressional authorization, can print money to bolster its balance sheet at any time, we are using this amount as a fun exercise to compare against the cash held by the private sector, especially by the tech giants.

Exhibit 1: Assets Portion of the U.S. Government Balance Sheet

Bureau Of Fiscal Service

How does the federal government's cash holding compare to the private sector?

I queried the U.S. SEC filings using Snowflake to examine the cash held by various companies (Exhibit 2). I wanted to examine Big Tech but also threw in data from other companies, from Berkshire to Coca-Cola. Berkshire Hathaway had to be included in this list since its cash, bonds, and securities on its balance sheet total over $325 billion. The table below details only the cash holdings without looking at long-term bonds and securities companies may hold.

Warren Buffett believes in having a fortress balance sheet and has been selling his stock positions in Apple and Bank of America to bolster it over the past few quarters. So, what does Warren know about the markets that concern him? There's a cottage industry trying to guess Buffett's thinking and investment strategy. Buffett may be saying that U.S. stocks are overvalued, so it's time to cash in on the capital gains he has secured on Apple. My guess is as good as yours. If the market takes a beating in the coming months, Warren might be in a prime position to buy excellent businesses at a cheaper valuation.

Note: It was easy to create an AI app in Snowflake to learn from Warren Buffett's annual letters. You can learn about it here.

Amazon has the highest cash position, with $75 billion on its balance sheet, followed by Meta (Facebook), which has $43 billion. The top 10 companies on this list, from Amazon to Salesforce, held $273 billion in cash.

Note: Procter & Gamble (PG) data has a bug since the cash position is for 2019. I hypothesize that PG started using a different US GAAP tag after the 2019 fiscal year than the one I used in my query. I will look into this. You can learn more about my architecture in a series of blogs here and here.

Exhibit 2: Cash Held By U.S. Companies

Cash Held By U.S. Companies.

This amount of cash on the balance sheet feels tiny compared to many big tech companies' annual free cash flows. Where did all the money go?

How much is Big Tech spending on share repurchases?

Share repurchase is a financial technique companies use to buy back their shares. Repurchases reduce the total outstanding shares, offering multiple benefits to the company and its shareholders.

1. Companies can show earnings per share growth without increasing earnings from their business operations.

2. Existing shareholders see their stake in the business increase. Now, they own more of the business.

3. Reduce the total dividend that the company has to pay out annually.

If you and your friend were partners in a business and you bought out your friend's share, you would end up owning 100% of the company and keeping all the profits. Share repurchases are similar, except on a much bigger scale.

Let's look at an example to see the benefits of share repurchases.

Exhibit 3: Share Repurchase Example

Share Repurchase Example

Suppose a company had a net income of $1000 and a total outstanding shares of 10 in year 1. In this case, its earnings per share (EPS) is $100. Suppose the company earns the same amount of $1000 in net income in year 2 but repurchased 2 of its shares at the end of year 1. The number of outstanding shares is now eight instead of 10. The EPS has jumped from $100 to $125, a remarkable 25% increase without the business earning a single penny more. When EPS grows, the stock price should follow, benefitting the company and its shareholders.

When paying dividends to existing shareholders, share repurchases save the company money, assuming it decides not to increase its dividend per share. In this example, the total dividend amount the company has to pay is reduced from $100 to $80.

In the partnership example, when you buy out your friend, you would consider paying a fair price for his share of the business. You would be careful not to overpay. If you overpay, the returns you generate on your investment will be less. Similarly, buying back shares at any valuation is not recommended. Warren Buffett's rule on buybacks is only to repurchase shares when the stock is trading below its intrinsic value.

How much did Apple spend on buying back its shares?

Since 2018, Apple has spent a mammoth $559 billion on buying back its shares (Exhibit 4 & 5).

Exhibit 4: Amount of Money Apple Has Spent on Share Repurchase Since 2018.

Apple Share Repurchases

Exhibit 5: Apple's Spending on Share Buy Backs in Bar Chart Form.

Apple's Share Repurchases

How much has Apple managed to reduce its share count since 2018?

Apple has reduced its total outstanding shares from 20 billion to 15.4 billion, a 22% reduction.

Exhibit 6: Apple's Weighted Average Diluted Shares Outstanding

How much did Microsoft spend on buying back its shares?

Microsoft has spent $152 billion on share buybacks since 2018 (Exhibits 7 & 8).

Exhibit 7: Microsoft's Spending on Share Repurchases

Microsoft's Spending on Share Repurchases

Exhibit 8: Microsoft's Spending on Share Repurchases in Bar Chart Form

Microsoft's Repurchases.

How much has Microsoft managed to reduce its share count since 2018?

Microsoft has reduced its outstanding shares from 7.79 billion to 7.46 billion, a 4.2% reduction (Exhibit 9).

Exhibit 9: Microsoft's Outstanding Shares.

Microsoft's Outstanding Shares

In theory, Big Tech could have more money on its balance sheet than the Federal government if desired. It spends a lot on Capex, R&D, and share repurchases.

Note: All data analysis was done on Snowflake using Snowsight Worksheets and Snowflake Notebooks. If you like the analytics presented here and wish to do similar analytics with your data, Snowflake may be the easiest platform to gain insights quickly. Try Snowflake for free for 30 days here.

Disclosures: I am a Sales Engineer at Snowflake. All opinions in this blog post are solely mine and do not reflect Snowflake's views. I am not a Registered Investment Advisor, and any discussion on securities or investments is not an inducement to make a particular investment.

The Costco Paradox

What can we learn from the success of Costco?

Summary:

• An analysis of Costco and Walmart's revenue growth over the past eight years.
• A look at the gross margins of these two retailers.
• An analysis of the inventory costs of these retailers.

Costco (COST) is one of the most successful retailers in the world. It has a loyal customer base, its employees are among the highest paid in the retail sector, and that leads to lower employee turnover, and it has a very generous return policy, furthering its customer loyalty. The company presents multiple paradoxes. Here are just a few:

• How is Costco, with such low gross margins, not only still in business but thriving in the highly competitive retail sector?
• How does Costco succeed in the internet era when its e-commerce strategy is an afterthought?
• How does a retailer who pays and treats employees so well, but still manages to deliver superior shareholder returns?

Each retail company is unique in its own way. In many ways, uniquesness is a required feature for retailers. If a retailer is unable to distinguish themselves from the competition, they will not last long. Even among a crowded field of retailers, Costco and Trader Joe's standout. About 80% of products Trader Joe's sells is private label. In comparison, the U.S., with companies with strong brands and billions in marketing budgets, has seen only a 20% penetration by private label brands. Other retailers are yet to replicate Trader Joe's success in private label. Costco, for its part, does well with its Kirkland private label brand, selling many products under this label. Since this is an article about Costco, let's turn our attention to this great American company.

Costco Wholesale is unique in it own way. The way company operates and caters to its customers is a study in human psychology. Over 90% of its members renew each year. The store delivers a treasure hunt experience because you never know what you will find in each aisle. The aisles remain unmarked yet customers aren't frustrated. Over the years Costco has trained its customers to have a mental map of where they could typically find essential items. For example, paper towels and cleaning supplies are typically found at the back of the store.

Costco's executive memberships cost $130 annually yet, over 35 million out of their 76 million total members chose this level rather than pay $65 for the Gold Star membership (Exhibit 1). People perceive much value received from their executive membership. Members know Costco will never overcharge them. The company prides itself in keeping its mark-up 14%-15% above its costs, an extremely low number for a retailer. Costco pays a 2% reward on purchases to their executive members which many feel will help them recoup their membership cost. This reward eats into their already tiny margins, but Costco chose loyal customer base over higher margins. I am willing to theorize that consumers, if given a choice to pick between Amazon Prime and Costco memberships, will overwhelmingly pick Costco.

Exhibit 1: Costco Q4 FY 2024 Membership Numbers and Renewal Rate.

Source: Costco Investor Relations.

What are Costco's Revenue and Growth Rate?

Since 2008, Costco has increased its revenue by 3.5x, from $72 billion to $254 billion in 2024 (Exhibit 2). During the same period, Walmart (WMT), a much larger company, increased its revenue by 1.7x, from $377 billion in 2008 to $648 billion in 2024 (Exhibit 3). Just when people think that the market is saturated with Costco Warehouses, it finds new locations and customers. It has been expanding in China for years, although this comes with a side of geopolitical risk.

Exhibit 2: Costco Wholesale Revenue (2008 - 2024)

SEC.GOV, Snowflake Snowsight

Exhibit 3: Walmart Revenue (2008 - 2024)

SEC.GOV

Costco's revenue grew at an average annual rate of 8.2% between 2008 and 2024. But, if you remove the pandemic-era binge shopping between 2020 and 2022, the growth rate was 6.9%, still an impressive number. Walmart averaged a revenue growth rate of 3.4% between 2008 and 2024, including the pandemic years.

Exhibit 4: Costco's Annual Revenue Growth Rate.

Snowflake Cortex Analyst, Excel

Exhibit 5: Walmart's Annual Revenue Growth Rate.

Snowflake Cortex Analyst, Excel

What are Costco's Gross Margins?

Costco has one of the lowest gross margins among retailers. Costco's gross margin in 2024 was 12.6% compared to 27.6% for Target (TGT), and 24.3% for Walmart. This is by design and may be their secret weapon. A company has to have strict financial discipline when it starts off with such low gross margins. Richard Galanti, the former CFO of Costco, was adept at managing costs. For the most part he was Costco's voice in Wall Street, presenting at the company quarterly earnings calls. Richard managed Costco's investments well, from inventory costs, new store openings, and e-commerce expenditures.

Costco's gross margins are well below that of Target and Walmart.

Exhibit 6: Gross Margins of Target, Walmart, and Costco.

SEC.GOV

How does Costco's Inventory Costs Compare with Walmart's and Target's?

Inventory is one of the largest costs for any retailer. When managed efficiently, inventory can boost operating cashflows and margins. When managed poorly, inventory can lead to losses or worse, bankruptcy.

Days of Sales in Inventory ( DSI) is a good metric to measure the efficiency of a company's inventory costs. This ratio indicates the average time in days it takes a company to sell its inventory. This metric also helps comparing inventory management efficiency across companies in a sector. Costco carries the least amount of inventory as possible on its balance sheet. It also helps that its loyal customer base regularly shops at the stores and helps clean out their inventory quickly.

Costco manages its inventory very efficiently compared to Walmart or Target. Costco manages to turnover its inventory every 31 days compared to nearly 41 days for Walmart and 56 days for Target (Exhibit 7 & 8). I have used ending inventory for each fiscal year in this calculation. When the ending inventory is used to calculate the days sales in inventory, the average number of days for Costco to turnover its inventory is 31.2 days.

Exhibit 7: Days Sales In Inventory For Walmart, Target, Costco.

Snowflake Snowsight

Exhibit 8: Costco Revenue, COGS, Gross Margin, and Days Sales in Inventory (Using Ending Inventory)

Snowflake, Excel

In Exhibit 9, I have used the average inventory to calculate the days sales in inventory. The average number of days it takes Costco to turnver its inventory in this case is 29.9 days.

Exhibit 9: Costco Revenue, COGS, Gross Margin, and Days Sales in Inventory (Using Average Inventory)

Snowflake, Excel.

Costco teaches us that margins are just a number. How you manage that efficiently while focusing on delivering value to the customer is what makes or breaks a company. Costco focus on keeping costs low for their customers helps it sell more stuff faster reducing its inventory costs and giving it enormous clout over its suppliers.

Finally, I have used Snowflake Data Cloud for almost all of my analysis. Snowflake platform is easy to use to gain business insights. I have shown you example of that here.

Disclosures: I am a Sales Engineer at Snowflake. You can reach me here. All opinions in this blog post are solely mine and do not reflect Snowflake's views. I am not a Registered Investment Advisor, and any discussion on securities or investments is not an inducement to make a particular investment.