According to the Rosewood case study, moving to a corporate brand from an individual brand is expected to improve customer lifetime value (CLV). Use the Rosewood excel file Rosewood_students V3.xlsx ( also available in files menu) to calculate the impact of the new brand strategy on customer lifetime value.

Please note that there are items in the spreadsheet to be included in the analysis that are not mentioned in the case study that will impact lifetime value. The assumed of 115,000 guests has been increased to 125,000 guests as a result of an inflight adverting campaign with business class customers of Delta airlines.

After determining the increase in CLV resulting from the changes, use it to estimate the additional sales to be targeted (see lower left of the spreadsheet).

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 in customer acquisition cost from $150 to $125 with the new brand strategy.

Rosewood_students V3 Q- Search Sheet Review View + Share DHO5 = Home Insert Page Layout Formulas * Times New... - 10 A Paste B IV A B35 x V x 378.494115558325 Data A 2 ' A E Wrap Text Merge & Center Currency $ % Insert * Delete Format 2. ZY ) Conditional Format Formatting as Table Cell Styles Sort & 2. Filter H I J K L M N 1 ROSEWOOD HOTELS & RESORTS: CUSTOMER LIFETIME VALUE (CLTV) ANALYSIS 2 Inputs Source Wthout Rosewood With Rosewood Heunding (2003) Corporate Branding 115,000 115,000 $750.00 $750.00 2.0 2.11 32% 32% Toming at 4 Total Number of Unique Guests 5 Average Daily Speed 8 Number of Days Average Cuest Stays per Stay 7 Average Gross Margin per Room B Average Number of Visits per Yeur per Cuest 9 Average Marketing Expense per Guest system-wide) 10 Average New Guest Acquisition Expense system widel 11 Total Number of Repeat Guests 12 of which: Total Number of Multi-property Stay Cuests 13 Additional Costs Required per 14 Discount Rate 15 Average Guest Retention Rate 16 Exhibit 6% Exhibit 8 Exhibit Exhibit & Exhibit & 3% Exhibit 8 Exhibit ring S1000 S150.00 19.169 to be cle 5125.00 14.99 to the sale to be sale Page 5 Exhibit 8 16.67% to be cale 2004 2005 2006 2007 2008 2009 18 CLTY Calculation with No Changes to Brand Strategy 19 Year 20 Number of Nights per Stay 21 Number of Stays per quest (suing they are retained) 22 Revenue Par Night 23 Revenue per Customer 24 Gimss Profit per Customer 25 Lex Cost to A ire Customer 26 Less Annual Marketing Cost per Customer 27 Cash Flow from Customer if Retina 28 29 Probability of Being Retained 30 Expectal Cash Flow from Customer $795.00 51.90.00 10.56 $842.70 $2.022.48 $647.19 $893.26 $2.143.83 $686.03 $946.86 52.272.-16 $727.19 $1,003.67 52.408.3] 5770.82 $1.067.89 52.55 33 17.07 (815000) 18133.90) 5476.66 (S137.921 $509.28 (S142.051 S541.97 $146.2 $580.XT ($150.71) $620.11 IS.55.23) 5661.84 (S150IHI) 0.00 0.00 1.00 (8150 00) 0.17 584.90 0.00 SO 019 $476.66 515.12 32 Discount Factor OM ONO 1.260 1.360 1.587 33 S441.15 $72.78 $1.98 34 NPV of Expected Cash Flow from Customer 35 Total NPV CLTY $12.00 ($150.00 SUT8.49 SOOS SOS 2003 2004 2005 2005 2006 2006 2007 2008 2009 2.0 37 CLTV Calculation With New Brand Strategy 38 Year 39 Number of Nights per Stev 40 Number of stay perpen (wuming they are need 41 Bee Per Night v1 + to be care to be cais 795.00 $849.70. $899.26. SMG.B. S083.67 $1,063.89 Ready B C - + 100% Rosewood_students V3 QuSearch Sheet + Share DHO5 = Home Insert Page Layout Formulas Data Times New.. 10 A- AY = Paste B IV A B35 , x f x 378.494115558325 Review - = View ab Insert 2 ' A Ep Wrao Text Merge & Center Currency $ % Ex Delete 2. ZY ) 18" Conditional Format Formatting as Table Cell Styles Sort & Format 2. Filter H I J K L M N 36 2003 2004 2005 2006 2007 2008 2009 20 2.0 to be cale: 37 CLTV Calculation With New Brand Strategy 38 Year 39 Number of Nights per Stay 40 Number of Stays per puest (assuming they are rid) 41 R ae Per Night 42 Reverve per Customer 43 Gruss Prolit per Customer 44 Less Cost to Acquire Customer 45 Lex Annual Marketing Casi per Customer 46 Less Additional Marketing Cost par Customer 47 1 mangeant and analyties lis por customer 48 Co-Branding payman lo siline kralversingle or 55.00 (11) per personer 49 Cash Flow from Customer if Retained 5795.00 AVALJE 3661.44 $842.70 $$98.26 5 916.86 V ALUR PAVALIEVALUE! $701.13 $743.19 $787.79 1 .003.67 AVALUE 5 835.05 S..063.89 #VALUE! 5885.16 ($125.00 cale cale (8125.00) 51 Probability of Being Retained 52 Expected Cash Flow from Customer 1.00 IS125.00) 1.00 UVALUE! 0.00 VALLE 54 Discount Factor 1.000 1.080 (8125.00) AVALUEI 0.22 0.05 VALURINVALUIHVALUEHVALUJE! 1.166 1.200 1.960 1.469 AVALUE! INVALUEI AVALUE! AVALUE! .587 WALUE! 56 NPV of Expected Cash Flow from Customer 57 Total NPV of CLTY VALUE! 59 Increase in CLTY per customer of new Marketing Plan 60 Multiplied by of Customers to obtain increase in profile of Rosewood from new brand strategy 61 Divided by 32% gross margin to obtain incase in Revenue of Rosewood from new brand strate 62 64 'S million growing 3% per year located to 115,000 + 10,000 guests 65 "Each cusima costa 59 de mange lineasing 4% per year 66 "Marsmalle relathalastame pasition would wally be 5125 instead of $150 67 Rosewood Laundial as abrand night versing with Delta Air casting 55 per customer 68 70 75 V1 + Ready E C - + 100% Rosewood_students V3 Q- Search Sheet Review View + Share DHO5 = Home Insert Page Layout Formulas * Times New... - 10 A Paste B IV A B35 x V x 378.494115558325 Data A 2 ' A E Wrap Text Merge & Center Currency $ % Insert * Delete Format 2. ZY ) Conditional Format Formatting as Table Cell Styles Sort & 2. Filter H I J K L M N 1 ROSEWOOD HOTELS & RESORTS: CUSTOMER LIFETIME VALUE (CLTV) ANALYSIS 2 Inputs Source Wthout Rosewood With Rosewood Heunding (2003) Corporate Branding 115,000 115,000 $750.00 $750.00 2.0 2.11 32% 32% Toming at 4 Total Number of Unique Guests 5 Average Daily Speed 8 Number of Days Average Cuest Stays per Stay 7 Average Gross Margin per Room B Average Number of Visits per Yeur per Cuest 9 Average Marketing Expense per Guest system-wide) 10 Average New Guest Acquisition Expense system widel 11 Total Number of Repeat Guests 12 of which: Total Number of Multi-property Stay Cuests 13 Additional Costs Required per 14 Discount Rate 15 Average Guest Retention Rate 16 Exhibit 6% Exhibit 8 Exhibit Exhibit & Exhibit & 3% Exhibit 8 Exhibit ring S1000 S150.00 19.169 to be cle 5125.00 14.99 to the sale to be sale Page 5 Exhibit 8 16.67% to be cale 2004 2005 2006 2007 2008 2009 18 CLTY Calculation with No Changes to Brand Strategy 19 Year 20 Number of Nights per Stay 21 Number of Stays per quest (suing they are retained) 22 Revenue Par Night 23 Revenue per Customer 24 Gimss Profit per Customer 25 Lex Cost to A ire Customer 26 Less Annual Marketing Cost per Customer 27 Cash Flow from Customer if Retina 28 29 Probability of Being Retained 30 Expectal Cash Flow from Customer $795.00 51.90.00 10.56 $842.70 $2.022.48 $647.19 $893.26 $2.143.83 $686.03 $946.86 52.272.-16 $727.19 $1,003.67 52.408.3] 5770.82 $1.067.89 52.55 33 17.07 (815000) 18133.90) 5476.66 (S137.921 $509.28 (S142.051 S541.97 $146.2 $580.XT ($150.71) $620.11 IS.55.23) 5661.84 (S150IHI) 0.00 0.00 1.00 (8150 00) 0.17 584.90 0.00 SO 019 $476.66 515.12 32 Discount Factor OM ONO 1.260 1.360 1.587 33 S441.15 $72.78 $1.98 34 NPV of Expected Cash Flow from Customer 35 Total NPV CLTY $12.00 ($150.00 SUT8.49 SOOS SOS 2003 2004 2005 2005 2006 2006 2007 2008 2009 2.0 37 CLTV Calculation With New Brand Strategy 38 Year 39 Number of Nights per Stev 40 Number of stay perpen (wuming they are need 41 Bee Per Night v1 + to be care to be cais 795.00 $849.70. $899.26. SMG.B. S083.67 $1,063.89 Ready B C - + 100% Rosewood_students V3 QuSearch Sheet + Share DHO5 = Home Insert Page Layout Formulas Data Times New.. 10 A- AY = Paste B IV A B35 , x f x 378.494115558325 Review - = View ab Insert 2 ' A Ep Wrao Text Merge & Center Currency $ % Ex Delete 2. ZY ) 18" Conditional Format Formatting as Table Cell Styles Sort & Format 2. Filter H I J K L M N 36 2003 2004 2005 2006 2007 2008 2009 20 2.0 to be cale: 37 CLTV Calculation With New Brand Strategy 38 Year 39 Number of Nights per Stay 40 Number of Stays per puest (assuming they are rid) 41 R ae Per Night 42 Reverve per Customer 43 Gruss Prolit per Customer 44 Less Cost to Acquire Customer 45 Lex Annual Marketing Casi per Customer 46 Less Additional Marketing Cost par Customer 47 1 mangeant and analyties lis por customer 48 Co-Branding payman lo siline kralversingle or 55.00 (11) per personer 49 Cash Flow from Customer if Retained 5795.00 AVALJE 3661.44 $842.70 $$98.26 5 916.86 V ALUR PAVALIEVALUE! $701.13 $743.19 $787.79 1 .003.67 AVALUE 5 835.05 S..063.89 #VALUE! 5885.16 ($125.00 cale cale (8125.00) 51 Probability of Being Retained 52 Expected Cash Flow from Customer 1.00 IS125.00) 1.00 UVALUE! 0.00 VALLE 54 Discount Factor 1.000 1.080 (8125.00) AVALUEI 0.22 0.05 VALURINVALUIHVALUEHVALUJE! 1.166 1.200 1.960 1.469 AVALUE! INVALUEI AVALUE! AVALUE! .587 WALUE! 56 NPV of Expected Cash Flow from Customer 57 Total NPV of CLTY VALUE! 59 Increase in CLTY per customer of new Marketing Plan 60 Multiplied by of Customers to obtain increase in profile of Rosewood from new brand strategy 61 Divided by 32% gross margin to obtain incase in Revenue of Rosewood from new brand strate 62 64 'S million growing 3% per year located to 115,000 + 10,000 guests 65 "Each cusima costa 59 de mange lineasing 4% per year 66 "Marsmalle relathalastame pasition would wally be 5125 instead of $150 67 Rosewood Laundial as abrand night versing with Delta Air casting 55 per customer 68 70 75 V1 + Ready E C - + 100%