Budget and Demand Forecasting using Markov model in SAS [Tutorial]

Budget and demand forecasting are important aspects of any finance team. Budget forecasting is the outcome, and demand forecasting is one of its components.

In this article, we understand the Markov model for forecasting and budgeting in finance.  

This article is an excerpt from a book written by Harish Gulati titled SAS for Finance.

Understanding problem of budget and demand forecasting

While a few decades ago, retail banks primarily made profits by leveraging their treasury office, recent years have seen fee income become a major source of profitability. Accepting deposits from customers and lending to other customers is one of the core functions of the treasury. However, charging for current or savings accounts with add-on facilities such as breakdown cover, mobile, and other insurances, and so on, has become a lucrative avenue for banks. One retail bank has a plain vanilla classic bank account, mid-tier premier, and a top-of-the-range, benefits included a platinum account.

The classic account is offered free and the premier and platinum have fees of $10 and $20 per month respectively. The marketing team has just relaunched the fee-based accounts with added benefits. The finance team wanted a projection of how much revenue could be generated via the premier and the platinum accounts.

Solving with Markovian model approach

Even though we have three types of account, the classic, premier, and the platinum, it doesn't mean that we are only going to have nine transition types possible as in Figure 4.1. There are customers who will upgrade, but also others who may downgrade. There could also be some customers who leave the bank and at the same time there will be a constant inflow of new customers. Let's evaluate the transition states flow for our business problem:

In Figure 4.2, we haven't jotted down the transition probability between each state. We can try to do this by looking at the historical customer movements, to arrive at the transitional probability. Be aware that most business managers would prefer to use their instincts while assigning transitional probabilities.

They may achieve some merit in this approach, as the managers may be able to incorporate the various factors that may have influenced the customer movements between states. A promotion offering 40% off the platinum account (effective rate $12/month, down from $20/month) may have ensured that more customers in the promotion period opted for the platinum account than the premier offering ($10/month).

Let's examine the historical data of customer account preferences. The data is compiled for the years 2008 – 2018. This doesn't account for any new customers joining after January 1, 2008 and also ignores information on churned customers in the period of interest.

Figure 4.3 consists of customers who have been with the bank since 2008:

Since we are only considering active customers, and no new customers are joining or leaving the bank, we can calculate the number of customers moving from one state to another using the data in Figure 4.3:

In Figure 4.4, we can see the customer movements between various states. We don't have the movements for the first half of 2008 as this is the start of the series. In the second half of 2008, we see that 30.28 out of 30.68 million customers (30.68 is the figure from the first half of 2008) were still using a classic account.

However, 0.4 million customers moved away to premium and platinum accounts. The total customers remain constant at 37.92 million as we have ignored new customers joining and any customers who have left the bank. From this table, we can calculate the transition probabilities for each state:

In Figure 4.5, we have converted the transition counts into probabilities. If 30.28 million customers in 2008 H2 out of 30.68 million customers in 2008 H1 are retained as classic customers, we can say that the retention rate is 98.7%, or the probability of customers staying with the same account type in this instance is .987.

Using these details, we can compute the average transition between states across the time series. These averages can be used as the transition probabilities that will be used in the transition matrix for the model:

The probability of classic customers retaining the same account type between semiannual time periods is 98.2%. The lowest retain probability is for platinum customers as they are expected to transition to another customer account type 26.7% of the time. Let's use the transition matrix in Figure 4.6 to run our Markov model.

Use this code for Data setup:

DATA Current; 
input date CL PR PL; 
datalines; 
2017.2 25.3 10.3 2.32 
; 
Run; 
 
Data Netflow; 
input date CL PR PL; 
datalines; 
2018.1 0.21 0.1 0.05 
2018.2 0.22 0.16 0.06 
2019.1 .24 0.18 0.08 
2019.2 0.28 0.21 0.1 
2020.1 0.31 0.23 0.14 
; 
Run; 
 
Data TransitionMatrix; 
input CL PR PL; 
datalines; 
0.98 0.01 0.01 
0.02 0.93 0.05 
0.06 0.21 0.73 
; 
Run;

In the current data set, we have chosen the last available data point, 2017 H2. This is the base position of customer counts across classic, premium, and platinum accounts. While calculating the transition matrix, we haven't taken into account new joiners or leavers. However, to enable forecasting we have taken 2017 H2 as our base position. The transition matrix seen in Figure 4.6 has been input as a separate dataset.

Markov model code

PROC IML; 
use Current; read all into Current; 
use Netflow; read all into Netflow; 
use TransitionMatrix; read all into TransitionMatrix; 
 
 
Current = Current [1,2:4]; 
Netflow = Netflow [,2:4]; 
 
Model_2018_1 = Current * TransitionMatrix + Netflow [1,]; 
Model_2018_2 = Model_2018_1 * TransitionMatrix + Netflow [1,]; 
Model_2019_1 = Model_2018_2 * TransitionMatrix + Netflow [1,]; 
Model_2019_2 = Model_2019_1 * TransitionMatrix + Netflow [1,]; 
Model_2020_1 = Model_2019_2 * TransitionMatrix + Netflow [1,]; 
 
Budgetinputs = Model_2018_1//Model_2018_2//Model_2019_1//Model_2019_2//Model_2020_1; 
 
Create Budgetinputs from Budgetinputs; 
append from Budgetinputs; 
Quit; 
 
Data Output; 
Set Budgetinputs (rename=(Col1=Cl Col2=Pr Col3=Pl)); 
Run; 

Proc print data=output;
Run;

The Markov model has been run and we are able to generate forecasts for all account types for the requested five periods. We can immediately see that there is an increase forecasted for all the account types. This is being driven by the net flow of customers. We have derived the forecasts by essentially using the following equation:

Forecast = Current Period * Transition Matrix + Net Flow

Once the 2018 H1 forecast is derived, we replace the Current Period with the 2018 H1 forecasted number while trying to forecast the 2018 H2 numbers. We are doing this as, based on the 2018 H1 customer counts, the transition probabilities will determine how many customers move across states. This will help generate the forecasted customer count for the required period.

Understanding transition probability

Now, since we have our forecasts let's take a step back and revisit our business goals. The finance team wants to estimate the revenues from the revamped premium and platinum customer accounts for the next few forecasting periods. As we have seen, one of the important drivers of the forecasting process is the transition probability. This transition probability is driven by historical customer movements, as shown in Figure 4.4.

What if the marketing team doesn't agree with the transitional probabilities calculated in Figure 4.6? As we discussed, 26.7% of platinum customers aren't retained in this account type. Since we are not considering customer churn out of the bank, this means that a large proportion of platinum customers downgrade their accounts. One of the reasons the marketing teams revamped the accounts is due to this reason.

The marketing team feels that it will be able to raise the retention rates for platinum customers and want the finance team to run an alternate forecasting scenario. This is, in fact, one of the pros of the Markov model approach as by tweaking the transition probabilities we can run various business scenarios. Let's compare the base and the alternate scenario forecasts generated in Figure 4.8:

A change in the transition probabilities of how platinum customers moved to various states has brought about a significant change in the forecast for premium and platinum customer accounts. For classic customers, the change in the forecast between the base and the alternate scenario is negligible, as shown in the table in Figure 4.8. The finance team can decide which scenario is best suited for budget forecasting:

To summarize, we learned the Markov model methodology and learned Markov models for forecasting and imputation.

To know more about how to use the other two methodologies such as ARIMA and MCMC for generating forecasts for various business problems, you can check out the book SAS for Finance.

Read more

• How to perform regression analysis using SAS
• Performing descriptive analysis with SAS
• Akon is planning to create a cryptocurrency city in Senegal