The customer loyalty model in Example 16.7 assumes that once a customer leaves (becomes disloyal), that customer never becomes loyal again. Assume instead that there are two probabilities that drive the model, the retention rate and the rejoin rate, with values 0.75 and 0.15, respectively. The simulation should follow a customer who starts as a loyal customer in year 1. From then on, at the end of any year when the customer was loyal, this customer remains loyal for the next year with probability equal to the retention rate. But at the end of any year the customer is disloyal, this customer becomes loyal the next year with probability equal to the rejoin rate. During the customer’s nth loyal year with the company, the company’s mean profit from this customer is the nth value in the mean profit list in column B. Keep track of the same two outputs as in the example, and also keep track of the number of times the customer rejoins.
CCAmerica is a credit card company that does its best to gain customers and keep their business in a highly competitive industry. The first year a customer signs up for service typically results in a loss to the company because of various administrative expenses. However, after the first year, the profit from a customer is typically positive, and this profit tends to increase through the years. The company has estimated the mean profit from a typical customer to be as shown in column B of Figure 16.28 below. (See the file Customer Loyalty.xlsx.) For example, the company expects to lose $40 in the customer’s first year but to gain $87 in the fifth year—provided that the customer stays loyal that long. For modeling purposes, we assume that the actual profit from a customer in a customer’s given year of service is normally distributed with mean shown in Figure 16.28 and standard deviation equal to 10%, of the mean. At the end of each year, the customer leaves the company, never to return, with probability 0.15, the churn rate. Alternatively, the customer stays with probability 0.85, the retention rate. The company wants to estimate the NPV of the net profit from any such customer who has just signed up for service at the beginning of year 1, at a discount rate of 8%, assuming that the cash flow occurs in the middle of the year. It also wants to see how sensitive this NPV is to the retention rate.
To use simulation to find the NPV of a customer, and to see how this varies with the retention rate.
Where Do the Numbers Come From?
The numbers in column B of Figure 16.28 are undoubtedly averages, based on the historical records of many customers. To build in randomness for any particular customer, we need a probability distribution around the numbers in this figure. We arbitrarily chose a normal distribution centered on the historical average and a standard deviation of 10% of the average. These are educated guesses. Finally, the churn rate is a number very familiar to marketing people, and it can also be estimated from historical customer data.
The variables for this model appear in Figure 16.27. (See the file Customer Loyalty Big Picture.xlsx.) The idea is to keep simulating profits (or a loss in the first year) for the customer until the customer churns. We simulate 30 years of potential profits, but this could be varied.
Big Picture for Customer Loyalty Simulation Model
The simulation model appears in Figure 16.28. (See the file Customer Loyalty Finished.xlsx.) It can be developed with the following steps.
As usual, Excel’s RAND function can be used inside an IF statement to determine whether a given event occurs.
1. Inputs. Enter the given inputs in the blue cells.
2. Retention rate. Although an 85% retention rate was given in the statement of the problem, it is useful to investigate retention rates from 75% to 95%, as shown in row 4. To run a separate simulation for each of these, enter the formula
=RiskSimtable(D4:H4)
in cell B4.
3. Timing of churn. In column C, use simulation to discover when the customer churns. This column will contain a sequence of No values, followed by a Yes, and then a sequence of blanks (or all No values if the customer never churns). To generate these, enter the formulas
=IF(RAND()<1–B4,”Yes”,”No”)
and
=IF(C11<>”No”,””,IF(RAND()<1–$B$4,”Yes”,”No”))
in cells C11 and C12, and copy the latter formula down column C. Study these formulas carefully to see how the logic works. Note that they do not rely on @RISK functions. Excel’s RAND function can be used any time you want to simulate whether or not an event occurs.
4. Careful discounting is required if cash flows occur in the middle of a year.
Actual and discounted profits. Profits (or a loss in the first year) occur as long as there is not a blank in column C. Therefore, simulate the actual profits by entering the formula
=IF(C11<>””,RiskNormal(B11,$B$6*ABS(B11)),0)
in cell D11 and copying it down. (The absolute value function, ABS, is required in case any of the cash flows are negative. A normal distribution cannot have a negative standard deviation.) Then discount these appropriately in column E by entering the formula
=D11/(1+$B$5)^(A11–0.5)
in cell E11 and copying it down. Note how the exponent of the denominator accounts for the cash flow in the middle of the year.
5. Outputs. Keep track of two outputs, the total NPV and the number of years the customer stays with the company. Calculate the NPV in cell H10 by summing the discounted values in column E. (They have already been discounted, so the NPV function is not needed.) To find the number of years the customer is loyal, count the number of No values plus the number of Yes values, that is, all nonblanks. Calculate this in cell H11 with the formula
=COUNTIF(C11:C40,”No”)+COUNTIF(C11:C40,”Yes”)
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
