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
