Analyze how the value of a loyal customer VLC will change if

Analyze how the value of a loyal customer (VLC) will change if the average customer defection rate varies between 15 and 40 percent (in increments of 5 percent) and the frequency of repurchase varies between three and nine times per year (in increments of one year). Sketch graphs (or use Excel charts) to illustrate the impact of these assumptions on the VLC. Whats the best way to graph this in excel?

Solution

First and foremost, we need to understand what exactly do we mean by Value of a Loyal Customer (VLC). In simple terms it can be explained as the total revenue or profit generated on the basis of the targeted customer base or targeted customer market over a period of customer\'s/buyer\'s life cycle.

> What are the causes of the loss in customer base (Deflection or RF):

1. Poor quality.

2. Poor service or performance.

3. Customer lifestyle change.

4. Product is no more effective in the current market standards.

> We need to have a perfect Plan (P / CM) to calculate the overall system of VLC base.

1. Like to evaluate a newly available game in the mobile android platform.

2. And the level of quality of service provided by the game distributor like complaints and redressals etc.

> Now it depends on the quality (P/ CM) of the application like look and feel of the game application. And on the basis of the same, we need to check the prices of different types of games based on their quality of rendering etc.

>The most important factor in deciding the VLC base would be the Service Performance Level offered (P/ CM) by the game developer.

>Now we need to see like the things that contribute to the Value of Loyal Customer analysis:

VLC = (P) (RF) (CM) (BLC)

P = Revenue per Unit.

RF = Repurchase Frequency.

CM = Contribution margin to Profit and Overhead.

BLC = Buyer\'s Life Cycle.

> Abbreviations for BLC formula:

D# Deflection Number.

DR Deflection Rate.

BLC Buyers Life Cycle.

Calculation of BLC

1. Number of increased customer base -(minus) Number of decreased customer base.

2. D# /(divided by) Number of increased customer base = DR.

3. 1/ (divided by) DR = BLC

Example:

1. 500 customers - 150 customers = 350

2. 350 / 500 = 3.5

3. 1 / 3.5 = 0.9(round figure)

> Calculation of CM (Contribution Margin):

P = Price

COGS = Direct cost of materials.

Labor = Direct cost of labor.

CM$ = Contribution margin left to apply to Profits and Overhead cost

CM = Contribution Margin

Calculation of CM:

1. P - (minus) COGS and Labor = CM$

2. CM$ / (divided by) P

Example:

$50 - $20 = $30

$30 / $50 = 0.6

Now Value of Loyal Customer (VLC) base:

VLC = (P)(RF)(CM)(BLC) which is ($50)(1)(0.6)(0.9) = $27

In the same way for multiple years, these observations can be taken into the consideration and a parallel can be drawn in the form of a chart or though graphical presentation.

Thank you.