Recurring Revenue Modeling Can Be Tricky, Using Cancellation Curves Can Improve Precision And Results

 In a recent post on recurring revenue financial modeling, I covered some of the main drivers that play a role in the construction of financial forecasts for SaaS and related business models. One of the most important aspects of such financial forecasts is the build out of contracted revenues. In general contracted revenues can be quite predictable, which makes the recurring revenue model so attractive to investors.

The Basics

In a basic format, the recurring revenue forecasting for a good financial model will have the following components to calculate the monthly revenue:

1. Average revenue per subscriber

2. Number of subscribers, beginning of the month (past bookings)

3. Number of subscribers added in the month (new bookings)

4. Composite cancellation rate (the expected % of existing subscribers who will cancel in the month)

5. Number of subscribers lost in the month (2*4, cancellations or churn)

6. Net number of subscribers (2+3-5)

7. Revenue for the month (1*6)

The image below shows a recurring revenue forecast based on the above calculations. It is necessary to understand that in this kind of model, the limited variations in average revenue and cancellation rates lend themselves to a composite view of the revenue build. If these underlying simplifications are reliable, the above methodology works just fine.

Pro-tip: Unless you have some strong need, I allow subscribers to be calculated in fractions and avoid any rounding functions for subscriber counts. I find partial clients (even though there is no such thing) makes models easier to manage because rounding functions sometimes have unintended consequences and also require maintenance and awareness of their use when other people are using your model.

More complex subscriber calculations

But, what if average revenue per subscriber changes for each new cohort of subscribers and the cancellations vary based on the age of the client. In this case, the value of the existing contracted backlog and the forecast of future contracted backlog becomes much more complex. You can stick to the above methodology, but with cancellations being age dependent, you could be in for hidden surprises and also leave your operations teams with a less refined set of objectives when they are trying to reduce cancellations.

One way to resolve this complexity is to look at a cohort-based backlog, which accounts for the average revenue variation by specifically assigning a revenue amount to a cohort and also assigning a cancellation percentage to each cohort based on its age. In this kind of model, each cohort is assigned a date of birth (sometimes called a vintage) so that it can be tracked uniquely throughout time.

The image below shows what the cancellations would look like in a cohort-based format. (I am intentionally ignoring revenue variations, but this would use a similar methodology to accommodate that variation.) Notice how each month of the model needs to have a cancellation percentage for each cohort.

Compared to the basic format at the beginning of this post, the cohort-based format has turned into a matrix instead of being a single vector (line) of the spreadsheet. In fact, to do this precisely, each line of the basic format should become a matrix. Then instead of multiplying lines in Excel, you multiply across matrices to get to revenue.

Using rough math, the composite cancellation rate in the matrix is about 3% over the March to August time frame. However, you can see that the Aug-24 ending revenues in the cohort-based format ($59,420) are slightly lower than the basic format ($60,120). Now you might think that the $700 (1.1%) is not a big deal, but over time and with increased volume this variance will grow and lead to weaker forecasting. While I would love to use a simpler model for expediency, it does not stand to scrutiny when you want to have reliable forecasting of revenues.

Summary

Tracking recurring revenues is tricky and precision comes with model complexity. I find that the complexity is worth it because it instills confidence in your audiences over time and also provides the operations teams with very specific data about handling the execution on their end. For example, in the cohort-based format above, but not shown here, I would easily provide a forecasted cancellation count by age of the subscriber, which enables the operations team to manage their targets very specifically during the subscriber lifecycle journey.

One final note: this post only deals with the build up of subscribers in the future. If you have existing subscribers, you can use the same methodology but you should not mix the existing cohorts with the projected ones. The matrices go in different directions and they are hard to combine. Manage them in separate files if needed. I hope to do a post on that in the future.

FAQs for Recurring Revenue Modeling using Cohorts:

1. Why is cohort-based forecasting important in recurring revenue modeling?Cohort-based forecasting is crucial in recurring revenue modeling because it allows for a more accurate representation of revenue streams by considering variations in average revenue per subscriber and cancellation rates based on the age of the client cohorts. This approach provides a more granular and precise understanding of revenue projections, enabling better decision-making and operational strategies.

2. How does cohort-based forecasting differ from basic recurring revenue modeling?In basic recurring revenue modeling, calculations are simplified by using composite averages for revenue per subscriber and cancellation rates. In contrast, cohort-based forecasting assigns specific revenue amounts and cancellation percentages to each cohort based on their unique characteristics, such as date of birth or vintage. This results in a more detailed and nuanced analysis of revenue trends over time.

3. What are the benefits of using cohort-based forecasting in revenue modeling?Utilizing cohort-based forecasting in revenue modeling offers several advantages, including enhanced accuracy in predicting revenue fluctuations, better insights into subscriber behavior over time, and the ability to provide operations teams with specific data to optimize customer retention strategies. While this approach may introduce complexity, the precision it brings to forecasting can lead to more reliable financial projections and improved operational efficiency.

Using a Survival Model for Credit Risk Scoring and Loan Pricing Instead of XGBoost

In the consumer lending space, fintech companies have innovated many aspects of the consumer experience. One of the biggest innovations has been the real-time approval of consumers for installment loans with borrowed cash hitting consumer bank accounts in an expedited and highly satisfying way. For those of you not in the business, the loan origination system, as we often call it, provides all of the capabilities to take a credit shopper and turn them into a borrower. To drive this positive consumer experience, fintech lenders rely heavily on real-time credit-scoring processes built into the loan origination system.

Many fintech lenders have advanced innovations using machine learning and data science to develop algorithms that provide a consumer risk score (probability of default) and loan price (interest rate and APR) to the consumer. These algorithms generally ingest consumer credit and financial data to discern the risk of a consumer and provide an appropriately priced installment loan, if possible, given the risk profile.

At the heart of many of these algorithms lies tree-based classification algorithms such as the XGBoost machine learning model, which seeks to classify consumers into risk categories based on their credit and financial profiles. Loan pricing is subsequently determined to generate a profitable loan. Sometimes, for simplicity, loan prices might be determined statically for each risk bucket; for example, all consumers rated a B+ receive and interest rate of 17.99%. Other more sophisticated pricing approaches might provide dynamic pricing.

We used this approach in the past, but in a new effort, we decided to calculate risk and pricing in a manner that aligns more closely to typical fixed income cash flows. In other words, if a consumer installment loan is a series of cash flows, why not calculate the probability of default for each payment and then do a risk adjusted discounted cash flow valuation of the loan that generates a specified profit regardless of risk? In this manner, the loan pricing accounts for the risk of each cash flow and all loans could be targeted to achieve our profit target with interest rates increasing as risk increases.

This approach evolved from research that one of our data scientists did when examining credit risk pricing models and discovered previous academic research using a survival regression algorithm to predict the payment-by-payment probabilities of default for the duration of the loan. A survival regression model is a technique that models the time until an “event” occurs. This family of models is often used in health-care related analysis, where “survival” means exactly that – did the subject survive to the next period. In our case, survival means “no default on payment” in this period, or that the loan value survives to the next payment.

By taking into account credit and financial factors of the individual influencing a potential event of default and a probability of the event of default occurring at payment of the loan, a projected series of default probabilities is generated for the entire loan duration. This series is called the “hazard function curve”. The same risk profile can also be represented in another curve called “survival curve” where each point in the curve denotes the likelihood that a borrower will not default up to the specific point in time.

Here, for three applicants, are the hazard function curves (showing the probability of default at each loan payment) and the survival function curves (showing the probability of no-default up to each loan payment) for a 36-month installment loan.

The two figures display the same three applicants: A, B and C, in two ways, using the cumulative hazard function and the survival function. The higher the forecasted cumulative hazard curve throughout the months, the lower the ending survival probability of the applicant.

The Cox Proportional Hazards algorithm is the specific survival regression method we improved upon to forecast this series of default probabilities throughout the loan term, as shown in the Hazard Function Curve above. Each point on this hazard curve represents the likelihood that the borrower will default on the loan in a specific month, given no default has occurred up to that point. Similar to other supervised machine learning algorithms, we trained the Cox Proportional Hazards model on a dataset comprising historical loan originations, which includes the borrower's financial attributes, loan default status, and time-to-default labels. Once trained, the model evaluates in real-time the default curve (Hazard Function Curve) for a prospective borrower based on their financial attributes, utilizing the predictive power learned from the model features.

The remaining loan origination process requires only fundamental financial analysis to price the loan based on the modeled risks. By applying this resulting default curve to a series of loan payments, we construct a risk weighted cash flow series for the consumer loan. With that series of expected value cash flows, we apply interest rate expenses using a forward curve: In our case, we use the SOFR 1-month forward curve plus our cost of capital spread. We leave a target variable to flex for our interest margin, which iteratively solves (we use an optimization function) to reach the targeted Net Present Value of the loan, which also factors in all origination costs, servicing costs and capital lent to the borrower.

Reference: https://t.co/dkRCobwfts

 

Applying Predictive Modeling to Crypto Futures Trading

Tradery Labs
I recently had the pleasure of doing some advisory and coaching work with a startup called Tradery Labs.

Tradery Labs is bringing futuristic predictive-modeling techniques into a highly honed system that will democratize the use of predictive algorithmic trading. The company’s goal is to give an investor the tools needed to build and test their own algorithms without the need for data scientists and programmers. More on that in a future post.

I recently sat down with Tradery’s head of modeling, Angel Aponte, to talk shop about his latest models in crypto futures.

Some Background on Bitcoin Futures
Unlike stocks where you can “sell short” and bet against the value of a stock, there is no concept of “selling short” actual bitcoin, you can only buy it or not hold it. The futures market for bitcoin changed all of that in 2017 and enabled traders to financially take a positive or negative position on bitcoin. These bitcoin futures enable traders to align their investment with their view on where bitcoin is headed. That is, traders sell futures when they expect bitcoin to decline and traders buy futures when they expect bitcoin to increase.

What are the goals of latest Tradery Labs algorithm?
Tradery has some lofty goals: the current algorithm targets a 50% annual return on investment and tries to achieve the following objectives:

1. Beat a buy and hold on the base asset

2. Have more winning months than losing months

3. The largest winning month needs to be bigger than the largest losing month

4. Worst case drawdown (singular decline in value) of 20%

5. Make money when the market goes up and when it goes down

6. Product profit overall

Like all predictive model builders, Tradery is in continuous improvement mode. The model never gets to perfection. In financial markets, this is especially important because the volatility of markets creates opportunities for new trends with new causes to develop that new information can be used to retrain a predictive model to improve its accuracy.

Testing Models
Predictive modelers always test different approaches to achieve their objectives. Tradery tested both statistical and deep learning methods for this latest project. Statistical techniques use mathematical models to predict outcomes, while deep learning methods use an algorithm to learn from the available data to make predictions. Both techniques have a rich ecosystem of free software libraries that enable flexible model building. The key is to have reliable, reproducible tests, that you can iterate over quickly, and then validate those results in the real world. All strategies that test successfully need to be followed by months of real-world results, before trading them live with real money at stake.

Testing does not necessarily produce clear cut winning models. Modern techniques are so advanced that the top models are usually comparable. Tradery finds that models vary in deciding when to put capital at risk and then de-risk (ie, buying and selling). Some techniques do better in uptrends, some do better in downtrends, and some perform best when the market trading in a range (i.e., generally moving sideways). Over a period of time, these are the only three options that a market can be in, which makes choosing on a small differences between models challenging. In this exercise, Tradery’s winning model is based on statistical techniques, not deep learning, which might be a surprise to people.

Model Performance
Tradery backtests its model using historical data that the model has never seen before to see how well it does in its predictions. This means that the model gets tested using data it has never seen before and its ability to make predictions is repeatedly tested to rate how well it will perform in real life.

The picture below shows how the model has done in backtesting. You can see that the model performed very well predicting the outcomes using data it had never seen before. Green bubbles and dotted lines means good, while read means bad.

Notice the outsized winning trades and the lack of outsized losers at the top of the picture. Those are the upward pointing green arrow heads. There are two in particular that are well above the mixed green and red arrows that are in a tight range. Those two arrow heads show two trades that drove the big uptrends in performance.

In the lower portion of the picture, note the positive returns in green for market movements going up and down. This means the model is picking the right position based on expectations of increasing and decreasing prices.

Also, note that the model positioned the trades incorrectly in a sideways market as shown in red on the left. You can see the red dotted line (zoom in) which shows losses in sideways market movement.

In this data set, the model was not wrong about any big swings, which is why there are no outsized losing trades, as stated earlier. However, this particular model did not make winning decisions when the market moved sideways.

Findings and Takeaways

Angel Aponte provided some insights into important observations from the process.

The market adapts and evolves over time.

Therefore, a model’s performance will degrade over time. In addition, more traders are coming into these emerging futures markets and those new entrants create dynamics that change rapidly. As a result, the team must continually test new models and retrain existing ones.

Another important finding indicates that faster and more frequent trading is not necessarily better.

One might think that high velocity trading is a natural outcome of these kinds of models, but the trading signal that the model seeks can get noisy in short intervals making quick decisions unreliable.

In addition, the cost of commissions is an important factor when trading algorithmically. You can have a highly accurate model that loses money on the trading commissions, so including that cost in back tests is important. This is another costs that works against high-velocity trading. A model needs to cover its transaction costs to be successful.

I will report back on live trading results using these algorithms in sometime in the future.

Visit Tradery Labs here.

Reference:  https://shorturl.at/jpyQ7

 

How We Replaced an Implementation of Workday Adaptive Planning Enterprise Management with Microsoft’s PowerBI Tailored for FP&A Reporting

Excel’s powerful capabilities, integrations and flexibility make it a favored tool for all financial and accounting professionals. Like many middle market companies, we considered moving from an Excel dominated financial planning and reporting process to an “enterprise grade” solution. A very difficult decision, we set aside Excel for a unified financial planning tool, also known as Enterprise Planning Management (EPM) systems.

After a review of solutions and recommendations, we decided to move our financial planning to Workday’s Adaptive Planning (WAP). Our financial forecast in Excel is a complete system: It handles recurring revenue waterfalls, consolidations by products and business units, eliminations between business units, balance sheet forecasting, among other complexities. Nevertheless, the transition, despite a good plan on paper, became never ending.

We faced two core problems, which we thought we could overcome. First, the precision and complexity of our Excel forecasting model was hard to replicate in WAP. Second, our lack of deep knowledge in WAP modeling, forced heavy reliance on consultants and a time-consuming iterative process to make any headway.  To minimize the obstacles and make some use of WAP, we paused our forecasting transition efforts and focused on WAP as a reporting tool. We had modest success, but we ended up having a hodge-podge system of exceptions and frequent error checking that was worse than the status quo.

During this failed transition period, the analytics team, which is part of our finance team, dramatically increased its expertise and capabilities in PowerBI. (While I am going to focus on PowerBI, I encourage the finance pros reading this to think about this solution using whatever business intelligence platform that is available. This should work with any BI platform.) PowerBI’s integrations with Excel and our accounting system (Microsoft NAV) provided the light-bulb moment for moving forward with an in-house automated financial reporting system connecting our Excel forecasts to accounting results and producing polished reporting in real-time.

In order to get there, we assigned a skilled data analyst to work directly with accounting and FP&A to create an ETL (extract, transform and load) template in PowerBI that could take our GL-coded accounting records and match them to financial reports that were business friendly and consistent with our forecasting templates. Here are the key success criteria that made this possible.

1. Our data analyst had PowerBI, SQL skills needed for the entire buildout.

2. We were lucky that our data analyst also had solid accounting/finance knowledge to work directly with FP&A and accounting teammates. However, this could have been another team member working in tandem.

3. The financial reporting templates were already matched to our excel forecasting outputs. This line-for-line matching eliminated the need for another ETL template, but that could have been created if necessary.

4. Our data analyst spent time mapping GL codes to our financial reporting templates. Without this, the ETL development would have been impossible.

5. In addition, the data analyst methodically mapped our eliminations entries between subsidiaries and hierarchical entities.

6. Then, it was time for record matching so that financial reporting template, forecast and GL Codes could be connected in sample data with a clear line of sight to each other.

7. Finally, the ETL template was ready to be programmed and tested.

8. PowerBI reporting dashboards were then developed and tested with initial data flows. Here the finance team compared PowerBI financial reports to our previous reports. Checking for accuracy at the line-item, subtotal, and total levels. Any errors were traced all the way back to GL-codes to ensure the fixes could be implemented in the ETL template.'

9. We then iterated step 8 until multiple periods showed no errors and everything tied out to the most important GL line items such as net income, fixed assets, total revenue, cash balance in every grouping variation we needed (e.g., consolidated, product, business unit, geography, etc.).

The above process took about 120 days to get through Step 8 and then another 60 days (2 reporting cycles) to get through Step 9. All of this was achieved with one resource dedicated to the project and all other FP&A and accounting teammates being on call as needed.

With our financial reporting now published in an automated way, we have dramatically reduced the processing time and eliminated exceptions handling for information flows from accounting to financial reporting. While the EPM also promised financial modeling automations, we never went back to that. Instead, we have improved our Excel-based forecasting models in ways that would be hard to replicate in a new system given the resources we have and the connections of these models to our PowerBI reporting system.

If you are considering an EPM, especially for reporting, it might be worth looking at your existing business intelligence platform for an easier and more manageable solution.

Reference: https://salvatoretirabassi.substack.com/p/how-we-replaced-an-implementation

 

Cracks in Consumer Credit Card Delinquency Despite High Cash Balances

On January 22, I posted an article on consumer financial strength driven by the amount of cash consumers have in checkable deposits as reported by the Fed. If you look at the bottom 50% of households by wealth, they are sitting on an astounding 2.5x as much cash in their checking accounts as they had before the start of COVID. See the chart below.

You can see that the amount of cash peaked in September 2022 and has since been declining. The rate of decline though indicates that it will be some time before consumers get back to pre-COVID cash levels.

In January, Transunion reported that more recently issued credit cards are reaching high delinquency rates much earlier than expected. If you are new to consumer finance, we look at how credit performs from the date of issuance (also called a vintage) and that gives you the ability to compare how different issuance dates perform against each other.

Issuance dates closer to hard financial times should underperform the preceding issuance dates.

Let’s look at the Transunion delinquency chart.

This chart shows the percentage of credit cards (as a pool) issued in Q4 of each of the last 5 years to reach 90+ days delinquency (no payments in the last 90+ days). Each recent vintage pool has reached the level of delinquency of the previous vintage pool in a shorter period of time. For example, the orange line (Q4 2018 vintage pool) took 54 months to reach a delinquency rate of about 10%. Now look at the light blue line (Q4 2021 vintage pool). It took only 15 months to reach 10% delinquency. The most recent issuance date, the purple line (Q4 2022 vintage pool), is already at a faster pace than all previous vintage pools. Notice it is steeper than the light blue line that preceded it in Q4 2021.

If consumers are sitting on so much cash, why are credit cards going delinquent at a quickening rate?

There are many factors that could be at play here. Here are some of the drivers that I think are important.

• The cash balances above reflect the bottom 50% of households, as a group. Hidden in that data are the stratifications of cash by household wealth, which would likely show lower cash savings as you move down to less wealthy households.

• Similarly, the Transunion data above also groups all credit risk stratifications together. In a stratified view by credit risk wealth tier, you would likely see that the rates and pacing of delinquency will be higher and faster for lower-credit consumers.

• The positive effects of COVID economics (higher wages, stimulus, new credit availability, savings from stay-at-home orders) are unwinding more quickly for the lower credit consumers.

This part of the market had the opportunity to spend more and put more on credit during COVID, and the banks were eager to bring new credit card accounts on. As the economy has gone back to normal over the last 24 months, these consumers have more regular demands on their cash, which took a backseat during COVID. Moreover, they now have credit card bills to address, which carry interest rates at the highest rates we have seen in years. The combination leads to increasing delinquencies, even though cash looks abundant.

 Reference: https://salvatoretirabassi.substack.com/p/cracks-in-consumer-credit-card-delinquency

Consumer Credit Card Interest Savings in a Decreasing Rate Environment

The stock market took some swings last week. It was down hard on January 31st on fears of no rate relief from the Fed and then rebounded firmly the very next day. An emotional roller coaster for many, to be sure. In this post, I am going to look at the upcoming rate environment but focus in on consumer debt and the potential savings consumers will experience as rates decline.

While the stock market will fluctuate wildly based on changing sentiments about interest rates over the course of 2024, consumer borrowers will be affected in a direct and consistent way because most consumer debt products are pegged to the Fed Funds rate. I am not going to focus on mortgages because much of the consumer housing market is carrying historically low rates into this environment already. As a result, decreasing rates will benefit new borrowers, not those who closed or refinanced their mortgages before 2022, which represents most mortgage holders in the US. Instead, I want to focus on total consumer debt and the fluctuating interest rates in credit cards which have rates priced as a function of the Prime Rate, which is a function of the Fed Funds rate.

The Fed has pointed to as many as three rate cuts of 25 basis points each. With the Fed Funds rate at 5.5%, this would bring down the rate to 4.75% at some point during 2024. The CME’s (Chicago Mercantile Exchange’s) Fedwatch indicator points to six cuts of 25 basis points during 2024 – 1.5% in total. This would bring the rate down to 4%. Bookending the two estimates, the Fed Funds rate will end up somewhere between 4% and 4.75% barring any unforeseen events that could change that.

Where the Fed ends up, will depend partially on the strong wage growth due to the low unemployment rate which is currently 3.7%, putting it below 4.0% for 23 months in a row. Because unemployment has been sustainably low, wages have benefited and real disposable income on a per capita basis has increased 3.7% year-over-year, which is much, much higher than the 1.7% averaged before the previous nine recessions since 1959. With that said, inflation has come into a more manageable range for the Fed, so the strength of the consumer, in my view, is less likely to cause inflation to rebound and is more important in avoiding a recession.

Now, with respect to consumer disposable income, interest expense has taken a some of the positive gains in wages. The chart below shows how total consumer debt has grown through Q3 of 2023. Total consumer debt in the United States is approaching levels not seen since the housing bubble highs before the 2008 crisis.

However, debt service remains well below the highs. This was caused by the massive deleveraging after 2008, where many debts carried high interest rates, and then re-leveraging into a low-rate environment that was sustained by the housing recession and then by the COVID actions taken by the Fed. So overall consumers have borrowed consistently since 2013 at very low rates, which fuels their ability to spend. You can see in the chart below, that during COVID (the absolute bottom of the line chart) debt service reached an impressive low point but even with the rebound in interest payments caused by all the recent Fed increases, debt service payments are still at a relative low point. In fact, debt service as a percentage of disposable income as of the middle of 2023 was lower than any time since 1981, excluding the pandemic period.

While the information above is about 3-9 months old (the Fed is slow with its data), the charts probably have not moved materially since they were published, in my opinion. So, taken at face value, decreasing interest rates in 2024, will significantly help the consumer.

Right now, US consumers are at near all-time lows in debt service with the highest interest rates highs that they have seen in 17 years. However, US consumers are reaching their highest accumulated debt in 17 years. Thanks to historically low rates, the US consumers have locked in super low mortgage rates, which are predominantly fixed rates and has allowed these seemingly contradictory facts to co-exist.

Back to credit cards. The variable debts US consumers hold in credit cards are what will drive interest expense fluctuations, and much of the increases in debt service are related to rising credit card interest rates. In fact, since the Fed started raising interest rates, credit card average interest rates have risen from 15% to 21.5%. This trend will reverse with the Fed rate cuts and US consumers will feel less payment pressure on credit cards. We can see credit card interest rates reverse to below 20% in the foreseeable future – still high but providing immediate savings to consumers.

I estimate that the savings in interest they might experience should range between $100 to $200 per household per year. This can continue to grow if rates decline further in the future.  $100-$200 per household per year may not sound like a lot. But if you put in the context of spending events, it becomes more meaningful. For example, it’s a few extra dinners out for the average American household that they previously couldn’t afford.

Reference: https://shorturl.at/uADX4