This supplemental CLV worksheet provides the basic framework for evaluating the profitability of various

retail business model scenarios for the business case: Wildfang

Please Note that this spreadsheet is identical to the spreadsheet suppliement for students, only the formulas
in this spreadsheet have been unlocked in the event that the instructor using the case and speadsheet want
to change the underlying model assumptions. The author advises not providing this unlocked version of the
spreadsheet to students as there is significant risk that once students start changing formulats in the CLV
model that the in-class conversation will degrade into trying to figure out why different studens spreadsheets
are generating different solutions.
 Business Model Private Label B&C Model

Annual
Customer Cohort and Purchase
Primary Acquisition Channel % of Portfolio AOV Frequency

Fashion Engaged Online 15% $ 200 1.4

Fashion Engaged Walkin 30% $ 96 1.2
Core Loyalists - Social Media 40% $ 68 6
WF Exclusive - Online and Retail Walk-in 15% $ 400 4
Blended Portfolio Assumptions $ 146.0 $ 3.6

NOTE: CLV Model assumptions included in

Wildfang case detail and Exhibit 6-7

INPUT FIELDS HIGHLIGHTED

IN YELLOW
Business Model Churn Rate Direct Revenue % Royalty Fee
Gross Margin (yr-yr as % of AOV * Revenue: (15% of
Benchmark CCA inactivity) Freq AOV * Frequency)

0.5 $60.00 70% 100% 0.0%

0.5 $10.00 70% 100% 0.0%
0.5 $20.00 70% 100% 0.0%
0.5 $30.00 70% 100% 0.0%
$ 0.50 $ 24.50 70% 100% 0%
 Active
Private Label Shoppers
B&C Model
Fashion Engaged -
Online 10,000

Years as a Wildfang Customer Segment Size Retention Rate Retained Customers

1 1,500 1 1500
2 0.3 450
3 0.3 135
4 0.3 41
5 0.3 12

Fashion Engaged -
Walkin

Years as a Wildfang Customer Segment Size Retention Rate Retained Customers

1 3,000 1 3000
2 0.3 900
3 0.3 270
4 0.3 81
5 0.3 24.3

Core Loyalists
Years as a Wildfang Customer Segment Size Retention Rate Retained Customers
1 4,000 1 4000
2 0.3 1200
3 0.3 360
4 0.3 108
5 0.3 32

WF EXCLUSIVE

Years as a Wildfang Customer Segment Size Retention Rate Retained Customers

1 1,500 1 1500
2 0.3 450
3 0.3 135
4 0.3 41
5 0.3 12

Private
Label B&C
Model

Blended
Portfolio

Years as a Wildfang Portfolio Size Retained Customers

1 10,000 10,000
2 3,000
3 900
 4 270
5 81

ASSUMPTION: Retention based on annual repurchase

Retention = 1 churn
If Churn = 30%, Average Life =(1/.3) = 3.33 Years
 Inactive BENCHMARK Segment
"Churn" Gross Margin CAC

70% 0.5 $ 60

Adjusted
Contribution
AOV Freq Revenue COGS Margin
$ 200 1.40 $ 420,000 $ 210,000 $ 210,000
$ 126,000 $ 63,000 $ 63,000
$ 37,800 $ 18,900 $ 18,900
$ 11,340 $ 5,670 $ 5,670
$ 3,402 $ 1,701 $ 1,701

BENCHMARK Gross
Inactive 'churn' Margin Segment CAC

70% 0.5 $ 10
Adjusted
Contribution
Margin
AOV Freq Revenue COGS [Rev- COGS]
$ 96 1.20 $ 345,600 $ 172,800 $ 172,800
$ 103,680 $ 51,840 $ 51,840
$ 31,104 $ 15,552 $ 15,552
$ 9,331 $ 4,666 $ 4,666
$ 2,799 $ 1,400 $ 1,400

BENCHMARK Gross
Inactive 'churn' Margin Segment CAC
70% 0.5 $ 20
 Adjusted
Contribution
Margin
AOV Freq Revenue COGS [Rev- COGS]
$ 68 6.00 $ 1,632,000 $ 816,000 $ 816,000
$ 489,600 $ 244,800 $ 244,800
$ 146,880 $ 73,440 $ 73,440
$ 44,064 $ 22,032 $ 22,032
$ 13,219 $ 6,610 $ 6,610

BENCHMARK Gross
Inactive 'churn' Margin Segment CAC
70% 0.5 $ 30
Adjusted
Contribution
Margin
AOV Weighted Freq Revenue COGS [Rev- COGS]
$ 400 4.00 $ 2,400,000 $ 1,200,000 $ 1,200,000
$ 720,000 $ 360,000 $ 360,000
$ 216,000 $ 108,000 $ 108,000
$ 64,800 $ 32,400 $ 32,400
$ 19,440 $ 9,720 $ 9,720

BLENDED
Inactive BLENDED Gross BLENDED
'churn' Margin CAC

70% 50% $ 24.50

Adj Contribution
Blended Margin
Blended AOV Purchase Freq Rev (000) COGS (000) (000)
$ 146 3.57 4,798 $ 2,399 $ 2,399
1,439 $ 720 $ 720
432 $ 216 $ 216
130 $ 65 $ 65
39 $ 19 $ 19
Cost of Funds:
Discount Rate

0.12

NPV of future cash Discounted Cash Cumulative Cash

flows CAC Flows Flow CLV
$ 210,000 $ 90,000 $ 120,000 $ 120,000 $ 80
$ 56,250 $ 56,250 $ 176,250 $ 118
$ 15,067 $ 15,067 $ 191,317 $ 128
$ 4,036 $ 4,036 $ 195,353 $ 130
$ 1,081 $ 1,081 $ 196,434 $ 131

Cost of Funds:
Discount Rate

0.12

NPV of future cash Discounted Cash Cumulative Cash

flows CAC Flows Flow CLV
$ 172,800 $ 30,000 $ 142,800 $ 142,800 $ 48
$ 46,286 $ 46,286 $ 189,086 $ 63
$ 12,398 $ 12,398 $ 201,484 $ 67
$ 3,321 $ 3,321 $ 204,805 $ 68
$ 890 $ 890 $ 205,694 $ 69

Cost of Funds:
Discount Rate
0.12
NPV of future cash Cost Acquiring Discounted Cash Cumulative Cash
flows Customer (CAC) Flows Flow CLV
$ 816,000 $ 80,000 $ 736,000 $ 736,000 $ 184
$ 218,571 $ 218,571 $ 954,571 $ 239
$ 58,546 $ 58,546 $ 1,013,117 $ 253
$ 15,682 $ 15,682 $ 1,028,799 $ 257
$ 4,201 $ 4,201 $ 1,033,000 $ 258

Cost of Funds:
Discount Rate
0.12

NPV of future cash Cost Acquiring Discounted Cash Cumulative Cash

flows Customer (CAC) Flows Flow CLV
$ 1,200,000 $ 45,000 $ 1,155,000 $ 1,155,000 $ 770
$ 321,429 $ 321,429 $ 1,476,429 $ 984
$ 86,097 $ 86,097 $ 1,562,526 $ 1,042
$ 23,062 $ 23,062 $ 1,585,587 $ 1,057
$ 6,177 $ 6,177 $ 1,591,764 $ 1,061

Cost of Funds:
Discount Rate

0.12

NPV of future cash Cost Acquiring Discounted Cash Cumulative Cash

flows (000) Customer (000) Flows (000) Flow (000) CLV (000)
$ 2,399 $ 245 $ 2,154 $ 2,154 $ 215
$ 643 $ 643 $ 2,796 $ 280
$ 172 $ 172 $ 2,968 $ 297
$ 46 $ 46 $ 3,015 $ 301
$ 12 $ 12 $ 3,027 $ 303