Guolin Lai

DSC8240 Course Web

Background
Objective Hierarchies
Variables and Attributes
Influence Diagram
Mathematical Representation
Testing and Validation
Implementation and Use

Background

DoItQuick is a software company that sells programs to individuals for keeping track of home finance, home inventory, and other common tasks. The company has done extensive research into its cost and revenues, and it has discovered that new customers are much less profitable on an annual basis than long-standing customers. .

Objective

What is a loyal customer worth to a company --- a 20-year period from a typical customer who has made his or her first purchase from DoItQuick this year?

Variables and Attributes

Variable	Variable Type	How Measured	Related to
intercept mean	Input Variable	number	profit
slope mean	Input Variable	number	profit
intercept stdev	Input Variable	number	profit
slope stdev	Input Variable	number	profit
probability p	Input Variable	%	years loyal to company
probability q	Input Variable	%	years loyal to company
interest rate	Input Variable	%	NPV

Influence Diagram

Mathematical Representation

1, Use RISKIMTABLE(Simulation Indexes) to obtain the index to further obtain the corresponding values of p and q with formula VLOOKUP(...,...,...).
2, Mean = (intercept mean) + (slope mean) * LN(number of year).
    Stdev = (intercept stdev) + (slope stdev) * (number of year).
    Proftit = RISKNORMAL(Mean, Stdev) for the first year, and for succeeding years, use the formula
    Profit = IF(OR(quits = "Yes", profit = ""), "", RISKNORMAL(Mean, Stdev)).
3, NPV = RISKOUTPUT() + NPV(interest rate, prifits).

Testing and Validation

Implementation and Use

The model can be manipulated in Microsoft Excel.
Please click here to view the Excel Model.