According to the Rosewood case study,  moving to acorporate brand from an individual brand is expected to improvecustomer lifetime value (CLV).  Use the Rosewood excelfile Rosewood_students V4.xlsx( alsoavailable in files menu) to calculate the impact of the new brandstrategy on customer lifetime value.

Please note that there are items in the spreadsheet to beincluded in the analysis that are not mentioned in the case studythat will impact the lifetime value. Please see the "Notes:Additional Information for your analysis" at the lowerleft of the spreadsheet. The anticipated 115,000 new guests hasbeen increased to 125,000 guests due to the planned inflightadverting campaign targeting business class customers of Deltaairlines.

After determining the increase in CLV resulting from thechanges, use it to calculate the additional sales Rosewood needs toachieve to realize the new total CLV (see Cells B59 to B61).

Please submit your spread sheet with the analysis(in Excel format) along with a word file(single-spaced not more then 1/2 page): 

(a)explaining your analysis, and

(b) suggesting some possible reasons for the reduction incustomer acquisition cost from $150 to $125 with the new brandstrategy (hint: the reason can't be improved cross-property staysbecause these have not yet been realized). 

Note: You only need to make changes to row 37 downwards of thespreadsheet. No changes are needed above row 37. 

Transcribed Image Text:

C52 X✓ fx =+C49*C51 1 ROSEWOOD HOTELS & RESORTS: CUSTOMER LIFETIME VALUE (CLTV) ANALYSIS 2 Inputs 3 4 Total Number of Unique Guests 5 Average Daily Spend 6 Number of Days Average Guest Stays per Stay 7 Average Gross Margin per Room 8 Average Number of Visits per Year per Guest 9 Average Marketing Expense per Guest (system-wide) 10 Average New Guest Acquisition Expense (system-wide) 11 Total Number of Repeat Guests 12 of which: Total Number of Multi-property Stay Guests 13 Additional Costs Required per annum 14 Discount Rate 15 Average Guest Retention Rate 16 17 18 CL.TV Calculation With No Changes to Brand Strategy 19 Year 20 Number of Nights per Stay 21 Number of Stays per guest (assuming they are retained) 22 Revenue Per Night 23 Revenue per Customer 24 Gross Profit per Customer 25 Less Cost to Acquire Customer 26 Less Annual Marketing Cost per Customer 27 Cash Flow from Customer if Retained 28 29 Probability of Being Retained 30 Expected Cash Flow from Customer 31 Discount Factor 32 33 A 34 NPV of Expected Cash Flow from Customer 35 Total NPV of CL.TV 36 B Inequa Without Rosewood With Rosewood Branding (2003) Corporate Branding 115,000 $750,00 2.0 32% 1.2 $130.00 $150.00 19,169 5,750 8% 16.67% 2003 ($150.00) ($150.00) 1.00 ($150.00) 1.000 C ($150.00) $378.49 115,000 $750.00 2.0 32% 1.3 to be calc $125.00 24,919 to be cale to he cale 8% to be cale 2004 2.0 1.2 $795.00 $1,908.00 $610.56 ($133.90) $476.66 1.00 $476.66 1.080 $441.35 D growing at growing at 2005 2.0 1.2 0.17 $84.90 E 1.2 $893.26 $842.70 $2,022.48 $2,143.83 $647.19 $686.03 1.166 $72.78 2006 2.0 Exhibit 8 6% Exhibit 8 Exhibit 8 Exhibit 8 Exhibit 8 3% Exhibit 8 Exhibit 84 0.03 $15.12 F ($137.92) ($142.05) ($146.32) $509.28 $543.97 $580.87 1.260 Sourcce $12.00 Page 5 Exhibit 8 2007 2.0 1.2 $946.86 $2,272.46 $727.19 0.00 $2.69 1.360 $1.98 G 2008 2.0 1.2 $1,003.67 $2,408.81 $770.82 ($150.71) $620.11 0.00 $0.48 1.469 $0.33 H 2009 2.0 1.2 $1,063.89 $2,553.33 $817.07 ($155.23) $661.84 0.00 $0.09 1.587 $0.05 C52 X✓ fx =+C49*C51 1 ROSEWOOD HOTELS & RESORTS: CUSTOMER LIFETIME VALUE (CLTV) ANALYSIS 2 Inputs 3 4 Total Number of Unique Guests 5 Average Daily Spend 6 Number of Days Average Guest Stays per Stay 7 Average Gross Margin per Room 8 Average Number of Visits per Year per Guest 9 Average Marketing Expense per Guest (system-wide) 10 Average New Guest Acquisition Expense (system-wide) 11 Total Number of Repeat Guests 12 of which: Total Number of Multi-property Stay Guests 13 Additional Costs Required per annum 14 Discount Rate 15 Average Guest Retention Rate 16 17 18 CL.TV Calculation With No Changes to Brand Strategy 19 Year 20 Number of Nights per Stay 21 Number of Stays per guest (assuming they are retained) 22 Revenue Per Night 23 Revenue per Customer 24 Gross Profit per Customer 25 Less Cost to Acquire Customer 26 Less Annual Marketing Cost per Customer 27 Cash Flow from Customer if Retained 28 29 Probability of Being Retained 30 Expected Cash Flow from Customer 31 Discount Factor 32 33 A 34 NPV of Expected Cash Flow from Customer 35 Total NPV of CL.TV 36 B Inequa Without Rosewood With Rosewood Branding (2003) Corporate Branding 115,000 $750,00 2.0 32% 1.2 $130.00 $150.00 19,169 5,750 8% 16.67% 2003 ($150.00) ($150.00) 1.00 ($150.00) 1.000 C ($150.00) $378.49 115,000 $750.00 2.0 32% 1.3 to be calc $125.00 24,919 to be cale to he cale 8% to be cale 2004 2.0 1.2 $795.00 $1,908.00 $610.56 ($133.90) $476.66 1.00 $476.66 1.080 $441.35 D growing at growing at 2005 2.0 1.2 0.17 $84.90 E 1.2 $893.26 $842.70 $2,022.48 $2,143.83 $647.19 $686.03 1.166 $72.78 2006 2.0 Exhibit 8 6% Exhibit 8 Exhibit 8 Exhibit 8 Exhibit 8 3% Exhibit 8 Exhibit 84 0.03 $15.12 F ($137.92) ($142.05) ($146.32) $509.28 $543.97 $580.87 1.260 Sourcce $12.00 Page 5 Exhibit 8 2007 2.0 1.2 $946.86 $2,272.46 $727.19 0.00 $2.69 1.360 $1.98 G 2008 2.0 1.2 $1,003.67 $2,408.81 $770.82 ($150.71) $620.11 0.00 $0.48 1.469 $0.33 H 2009 2.0 1.2 $1,063.89 $2,553.33 $817.07 ($155.23) $661.84 0.00 $0.09 1.587 $0.05

Answer rating: 100% (QA)

Question Property specific advertising was concentration for Rosewood since 1990s An individual prop... View the full answer