Scribd Logo
Upload

Welcome to Scribd!

  • 109 views

    Rosewood Case Solution

    Uploaded by

    Second Floor
    This document summarizes the customer lifetime value (CLV) calculation for a customer over a 20 year period with a 90% retention rate each year. It shows the margin generated each year, discounted by the retention rate to calculate the present value of that customer over the lifetime. The total expected margin over 10 years is $500 while the discounted/present value is $325.66, providing a net present value of $225.97 for that customer.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    Rosewood Case Solution

    Uploaded by

    Second Floor
    109 views15 pages
    This document summarizes the customer lifetime value (CLV) calculation for a customer over a 20 year period with a 90% retention rate each year. It shows the margin generated each year, discounted by the retention rate to calculate the present value of that customer over the lifetime. The total expected margin over 10 years is $500 while the discounted/present value is $325.66, providing a net present value of $225.97 for that customer.

    Original Title

    ROSEWOOD CASE SOLUTION

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document summarizes the customer lifetime value (CLV) calculation for a customer over a 20 year period with a 90% retention rate each year. It shows the margin generated each year, discounted by the retention rate to calculate the present value of that customer over the lifetime. The total expected margin over 10 years is $500 while the discounted/present value is $325.66, providing a net present value of $225.97 for that customer.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    109 views15 pages

    Rosewood Case Solution

    Uploaded by

    Second Floor
    This document summarizes the customer lifetime value (CLV) calculation for a customer over a 20 year period with a 90% retention rate each year. It shows the margin generated each year, discounted by the retention rate to calculate the present value of that customer over the lifetime. The total expected margin over 10 years is $500 while the discounted/present value is $325.66, providing a net present value of $225.97 for that customer.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    of 15

    Customer Lifetime Value

    Year Margin s(RR=0.9)


    1 50 1
    2 50 0.900
    3 50 0.810
    4 50 0.729
    5 50 0.656
    6 50 0.590
    7 50 0.531
    8 50 0.478
    9 50 0.430
    10 50 0.387
    11 50 0.349
    12 50 0.314
    13 50 0.282
    14 50 0.254
    15 50 0.229
    16 50 0.206
    17 50 0.185
    18 50 0.167
    19 50 0.150
    20 50 0.135
    Customer Lifetime Value
    Expected Margin Discount Rate 12% Present Value
    50 1 50
    45 0.892857142857143 40.1785714285714
    40.5 0.79719387755102 32.2863520408163
    36.45 0.711780247813411 25.9443900327988
    32.805 0.635518078404831 20.8481705620705
    29.5245 0.567426855718599 16.7529942016638
    26.57205 0.506631121177321 13.4622274834798
    23.914845 0.452349215336893 10.8178613706534
    21.5233605 0.403883227979369 8.69292431570365
    19.37102445 0.360610024981579 6.98538561083329
    17.433922005 0.321973236590696 5.61325629441961
    15.6905298045 0.287476104098836 4.51065237944433
    14.12147682405 0.256675092945389 3.62463137633919
    12.709329141645 0.229174190129812 2.91265021312971
    11.4383962274805 0.204619812615903 2.34052249269351
    10.2945566047325 0.182696261264199 1.88077700305729
    9.26510094425921 0.163121661843035 1.51133866317104
    8.33859084983329 0.145644340931281 1.21446856861958
    7.50473176484996 0.130039590117215 0.975912242640735
    6.75425858836496 0.116106776890371 0.784215194979162
    Normal Sum of 10 years 500
    Expected Sum of 10 years 325.66077995
    Net Present Value 225.96887705
    Exhibit 8

    Total number of unique


    guests
    Average daily spend
    Number of days average
    guest stays
    Average gross margin per
    room
    Average number of visits
    per year per guest
    Average marketing
    expense per guest
    Average new guest
    acquisition expense
    Total number of repeat
    guests

    of which: Total number of


    multiproperty stay guests

    Additional costs required


    per annum
    Discount rate
    Average Guest Retention
    Rate

    CL

    Year 2003
    Number of Nights per Stay 2
    Number of Stays per guest (assuming
    1.2
    they are retained)
    Revenue Per Night $750.00
    Revenue per Customer $1,800.00
    Gross Profit per Customer $576.00
    Less Cost to Acquire Customer ($150.00)
    Less Annual Marketing Cost per
    $130.00
    Customer
    Cash Flow from Customer if
    $150.00
    Retained

    Probability of Being Retained


    Expected Cash Flow from Customer $150.00
    Discount Factor

    NPV of Expected Cash Flow from


    ($150.00)
    Customer
    Total NPV of CLTV $378.49

    Year 2003
    Number of Nights per Stay 2
    Number of Stays per guest (assuming
    1.3
    they are retained)
    Revenue Per Night $750.00
    Revenue per Customer
    Gross Profit per Customer
    Less Cost to Acquire Customer ($150.00)
    Less Annual Marketing Cost per
    $130.00
    Customer
    Less Additional Marketing Cost per
    Customerb
    Cash Flow from Customer if
    ($150.00)
    Retained

    Probability of Being Retained


    Expected Cash Flow from Customer ($150.00)

    Discount Factor 1.000

    NPV of Expected Cash Flow from


    ($150.00)
    Customer
    Total NPV of CLTV $461.10
    With Corporate
    Without Branding (2003)
    Branding

    115,000 115,000

    $750 $750

    2 2

    32% 32%

    1.2 1.3

    $130 $138.70

    $150 $150

    19,169 24,919

    5,750 11,500

    $10,00,000

    8% 8%

    16.67% 21.67%

    CLTV Without Corporate Branding

    2004 2005 2006


    2 2 2

    1.2 1.2 1.2

    $795.00 $842.70 $893.26


    $1,908.00 $2,022.48 $2,143.83
    $610.56 $647.19 $686.03

    $133.90 $137.92 $142.05

    $476.66 $509.28 $543.97

    1.00 0.1667 0.0278


    $476.66 $84.90 $15.12
    1.080 1.166 1.260

    $441.35 $72.78 $12.00

    CLTV With Corporate Branding

    2004 2005 2006


    2 2 2

    1.3 1.3 1.3

    $795.00 $842.70 $893.26


    $2,067.00 $2,191.02 $2,322.48
    $661.44 $701.13 $743.19

    $133.90 $137.92 $142.05

    $8.957 $9.23 $9.502

    $518.58 $553.98 $591.64

    1.0000 0.2167 0.0470


    $518.58 $120.05 $27.78

    1.080 1.166 1.260

    $480.17 $102.92 $22.05


    Expected Margin 240

    2007 2008 2009


    2 2 2

    1.2 1.2 1.2

    $946.86 $1,003.67 $1,063.89


    $2,272.46 $2,408.81 $2,553.33
    $727.19 $770.82 $817.07

    $146.32 $150.71 $155.23

    $580.87 $620.11 $661.84

    0.0046 0.0008 0.0001


    $2.69 $0.48 $0.09
    1.360 1.469 1.587

    $1.98 $0.33 $0.05

    2007 2008 2009


    2 2 2

    1.3 1.3 1.3

    $946.86 $1,003.67 $1,063.89


    $2,461.83 $2,609.54 $2,766.11
    $787.79 $835.05 $885.16

    $146.32 $150.71 $155.23

    $9.79 $10.081 $10.38

    $631.68 $674.27 $719.55

    0.0102 0.0022 0.0005


    $6.43 $1.49 $0.34

    1.360 1.469 1.587

    $4.72 $1.01 $0.22


    Exhibit 5

    2006 Actual
    Revenue
    Ad Sales $23,06,30,400
    Affiliate Fees $8,00,00,000
    Total Revenue

    Expenses
    Cost of Operations $7,00,00,000
    Cost of Programming $5,50,00,000
    Ad Sales Commissions $69,18,912
    Marketing & Advertising $4,50,00,000
    SGA $4,00,00,000
    Total Expense $21,69,18,912
    Net Income $9,37,11,488
    Margin 30%

    Exhibit 4 Ad Revenue Calcu


    Ad Revenue Calculator
    Current
    TV HH $110,000,000.00
    Average Rating 1.00%
    Average Viewers (thousand) 1100
    Average CPMa $2.00
    Average Revenue/Ad Minute $2,200
    Ad Minutes/Week 2,016
    Weeks/Year 52
    Ad Revenue/Year $23,06,30,400
    Incremental Programming Expense 0
    Exhibit 5 TFC Estimated Financials for 2006 and 2007

    2007 Base Scenario 1 Scenario 2

    $20,75,67,360 $24,90,80,832 $32,28,82,560


    $8,16,00,000 $8,16,00,000 $8,16,00,000

    $7,21,00,000 $7,21,00,000 $7,21,00,000


    $70,000,000 $75,000,000

    $4,12,00,000 $4,12,00,000 $4,12,00,000


    $11,33,00,000 $11,33,00,000 $11,33,00,000

    Exhibit 4 Ad Revenue Calculator

    2007 Base Scenario 1 Scenario 2


    $110,000,000.00 $110,000,000.00 $110,000,000.00
    1.00% 1.20% 0.80%
    1100 1320 880.00
    $1.80 $1.80 $3.50
    1980 $2,376 $3,080
    2,016 2,016 2,016
    52 52 52
    $207,567,360 $249,080,832 $322,882,560
    0 0 15000000
    6 and 2007

    Scenario 3 Assumptions

    $34,59,45,600 Insert scenario results from revenue calculator


    $8,16,00,000 Grows 2% per year with population

    $7,21,00,000 Grows 3% per year with inflation


    Add incremental programming expense
    3% of ad sales revenue
    Reflects increased spending of $15M
    $4,12,00,000 Growing with inflation 3%
    $11,33,00,000

    Scenario 3
    $110,000,000.00 #VALUE!
    1.20%
    1320
    $2.50
    $3,300
    2,016
    52
    $345,945,600
    20000000

    You might also like