Compute the increase in customer lifetime value (CLV) for a Rosewood patron under the proposed branding initiative. Your customer lifetime value analysis should use the

Question

27 users unlocked this solution today!

Compute the increase in customer lifetime value (CLV) for a Rosewood patron under the proposed branding initiative. Your customer lifetime value analysis should use the Excel workbook (link posted below) with your assumptions and financial figures from the case entered into the yellow boxes. (Note: do not use the numbers in Exhibit 8 of the case for average number of visits per year per guest?they are inaccurate.)

Exhibit 8 Rosewood's Brand-wide Customer Lifetime Value Spreadsheet Model Without Rosewood With Rosewood Branding (2003) Corporate Branding c Total number of unique guests a 115,000 115,000 Average daily spendb $750 $750 Number of days average guest stays 2 2 Average gross margin per room 32% 32% Average number of visits per year per guest 1.2 1.3 Average marketing expense per guest (systemwide) d $130 To be calculated Average new guest acquisition expense (systemwide) $150 $150 Total number of repeat guests 9 19,169 To be calculated Of which: Total number of multiproperty stay guests 5,750 To be calculated Average Guest Retention Ratef 16.67% To be calculated Average Gross Profit per Guest To be calculated To be calculated ROSEWOOD HOTELS & RESORTS: CUSTOMER LIFETIME VALUE (CLV) ANALYSIS IRECTIONS; This spreadsheet has been provided for your convenience in performing CLV calculations. You should change values in he yellow boxes to reflect values from the case or make assumptions about the values to have the spreadsheet calculate he CLV differences between starting a corporate branding intiative or not doing so. The two red boxes below require that ou enter formulas for the spreadsheet to finish the calculations. nputs Without Rosewood With Rosewood Source Branding (2003) Corporate Branding Total Number of Unique Guests 115,000 115,000 Exhibit 8 Average Daily Spend $750.00 $750.00 growing at 6% Exhibit 8 Number of Days Average Guest Stays per Stay 2.0 2.0 Exhibit 8 Average Gross Margin per Room 32% 32% Exhibit 8 Average Number of Visits per Year per Guest 1.17 1.12 Average Marketing Expense per Guest (system-wide) $130.00 $130.00 growing at 3% Exhibit 8 Average New Guest Acquisition Expense (system-wide) $150.00 $150.00 Exhibit 8 Total Number of Repeat Guests 19,169 13,419 of which: Total Number of Multi-property Stay Guests 5,750 Additional Costs Required per annum Page 6 Discount Rate 8% 8% Exhibit 8 Average Guest Retention Rate 16.67% 11.67% CLV Calculation With No Changes to Brand Strategy fear 2003 2004 2005 2006 2007 2008 2009 Number of Nights per Stay 2.0 2.0 2.0 2.0 2.0 2.0 Number of Stays per guest (assuming they are retained) 1.17 1.17 1.17 1.17 1.17 1.17 Revenue Per Night $795.00 $842.70 $893.26 $946.86 $1,003.67 $1,063.89 Revenue per Customer $1,860.30 $1,971.92 2,090.23 $2,215.65 $2,348.59 $2,489.50 ross Profit per Customer $595.30 $631.01 $668.87 $709.01 $751.55 $796.64 ess Cost to Acquire Customer ($150.00) Less Annual Marketing Cost per Customer ($133.90) ($137.92 ($142.05 ($146.32 ($150.71 ($155.23) Cash Flow from Customer if Retained ($150.00) $461.40 $493.10 $526.82 $562.69 $600.84 $641.41 Probability of Being Retained 1.00 1.00 0.17 0.03 0.00 0.00 0.00 Expected Cash Flow from Customer ($150.00) $461.40 $82.20 $14.64 $2.61 $0.46 $0.08 Discount Factor 1.000 1.080 1.166 1.260 1.360 1.469 1.587 NPV of Expected Cash Flow from Customer ($150.00) $427.22 $70.47 $11.62 $1.92 $0.32 $0.05 CLV $361.60 CLV Calculation With New Brand Strategy Year 2003 2004 2005 2006 2007 2008 2009 Number of Nights per Stay 2.0 2.0 2.0 2.0 2.0 2.0 Number of Stays per guest (assuming they are retained) 1.12 1.12 1.12 1.12 1.12 1.12 Revenue Per Night $795.00 $842.70 $893.26 $946.86 $1,003.67 $1,063.89 evenue per Customer $1,775.53 $1,882.06 $1,994.99 $2.114.69 $2,241.57 $2,376.06 Gross Profit per Customer $568.17 $602.2 $638.40 $676.70 $717.30 $760.34 ess Cost to Acquire Customer ($150.00) Less Annual Marketing Cost per Customer ($133.90) ($137.92) ($142.05) ($146.32) ($150.71) ($155.23) Less Additional Marketing Cost per Customer $0.00 $0.00 $0.00 50.00 $0.00 $0.00 Cash Flow from Customer if Retained ($150.00) $434.27 $464.34 $496.34 $530.38 $566.60 $605.11 Probability of Being Retained 1.00 1.00 0.12 0.01 0.00 0.00 0.00 Expected Cash Flow from Customer ($150.00) $434.27 $54.18 $6.76 $0.84 $0.11 $0.01