According to the case study,moving to a corporate brand from an individual brand is expected to improve customer lifetime value (CLV).Use the file to calculate the impact of the new brand strategy on customer lifetime value. *PLEASE PROVIDE FORMULAS FOR EACH LINE*
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 the lifetime value. Please see the "Notes: Additional Information for your analysis" at the lower left of the spreadsheet. The anticipated 115,000 new guests has been increased to 125,000 guests due to the planned inflight advertising campaign targeting business class customers of Delta Airlines.
After determining the increase in CLV resulting from the changes, use it to calculate the additional sales Rosewood needs to achieve to realize the new total CLV (see Cells B59 to B61).
Note: You only need to make changes to row 37 downwards of the spreadsheet. No changes are needed above row 37.
Please help with formulas !
A B C D E F G H Inputs Without Rosewood With Rosewood Source Branding (2003) Corporate Branding N Total Number of Unique Guests 115,000 115,000 Exhibit 8 Average Daily Spend $750.00 $750.00 growing at 6% Exhibit 8 5 Number of Days Average Guest Stays per Stay 2.0 2.0 Exhibit 8 6 Average Gross Margin per Room 32% 32% Exhibit 8 Average Number of Visits per Year per Guest 1.2 1.3 Exhibit 8 8 Average Marketing Expense per Guest (system-wide) $130.00 to be calc growing at 3% Exhibit 8 9 Average New Guest Acquisition Expense (system-wide) $150.00 $125.00 Exhibit 8d 10 Total Number of Repeat Guests 19, 169 24,919 11 of which: Total Number of Multi-property Stay Guests 5,750 to be calc 12 Additional Costs Required per annum to be calc Page 5 13 Discount Rate 8% 8% Exhibit 8 14 Average Guest Retention Rate 16.67% to be calc 15 16 17 CLTV Calculation With No Changes to Brand Strategy 18 Year 2003 2004 2005 2006 2007 2008 2009 19 Number of Nights per Stay 2.0 2.0 2.0 2.0 2.0 2.0 20 Number of Stays per guest (assuming they are retained) 1.2 1.2 1.2 1.2 1.2 1.2 21 Revenue Per Night $795.00 $842.70 $893.26 $946.86 $1,003.67 $1, 063.89 22 Revenue per Customer $1,908.00 $2,022.48 $2, 143.83 $2,272.46 $2, 408.81 $2,553.33 23 Gross Profit per Customer $610.56 $647.19 $686.03 $727.19 $770.82 $817.07 24 Less Cost to Acquire Customer ($150.00) 25 Less Annual Marketing Cost per Customer ($133.90) $137.92) ($142.05) ($146.32) $150.71) ($155.23) 26 Cash Flow from Customer if Retained ($150.00) $476.66 $509.28 $543.97 $580.87 $620.11 $661.84 27 28 Probability of Being Retained 1.00 1.00 0.17 0.03 0.00 0.00 0.00 29 Expected Cash Flow from Customer ($150.00) $476.66 $84.90 $15.12 $2.69 $0.48 $0.09 30 31 Discount Factor 1.000 1.080 1.166 1.260 1.360 1.469 1.587 32 33 NPV of Expected Cash Flow from Customer ($150.00) $441.35 $72.78 $12.00 $1.98 $0.33 $0.05 34 Total NPV of CLTV $378.49 35A B C D E F G H 36 CLTV Calculation With New Brand Strategy 37 Year 2003 2004 2005 2006 2007 2008 2009 38 Number of Nights per Stay 2.0 2.0 2.0 2.0 2.C 2.0 39 Number of Stays per guest (assuming they are retained) to be calc: 40 Revenue Per Night $795.00 $842.70 $893.26 $946.86 $1,003.67 $1,063.89 41 Revenue per Customer F #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 42 Gross Profit per Customer $661.44 $701.13 $743.19 $787.79 $835.05 $885.16 43 Less Cost to Acquire Customer ($125.00) 44 Less Annual Marketing Cost per Customer calc 45 Less Additional Marketing Cost per Customer calc 46 Less data management and analytics fee per customer calc 47 Co-Branding payment to airline for advertsing fee of $8.00 (flat) per per customer calc 48 Cash Flow from Customer if Retained ($125.00) 49 50 Probability of Being Retained 1.00 1.00 0.22 0.05 0.01 0.00 0.00 51 Expected Cash Flow from Customer ($125.00) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 52 53 Discount Factor 1.000 1.080 1.166 1.260 1.360 1.469 1.587 54 55 NPV of Expected Cash Flow from Customer ($125.00) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 56 Total NPV of CLTV F #VALUE! 57 58 Increase in CLTV per customer of new Marketing Plan 59 Multiplied by # of Customers to obtain increase in profit of Rosewood from new brand strategy 60 Divided by 32% gross margin to obtain increase in Revenue of Rosewood from new brand strategy 61 62 Notes: Additional Information for your Analysis 63 $1 million growing at 3% per year allocated to 115,000+10,000 64 "Each customer costs a $12 data management fee increasing at 4% per year 65 "Management later realized that customer acquisition would actually be $125 instead of $150 66 "Rosewood launched a a cobranded inflight advertsing campaign with Delta Air costing $8 per customer 67 "For simplicity of analysis we assume that the custmer was acquired the last day of 2003, therefore no costs are attributed to 2003
