Find empty cells like gros profit per guest. also explain how you find them write your formulas.

1 Exhibit 8 Rosewood's Brand-wide Customer Lifetime Value Spreadsheet Model 2 3 Without Rosewood Branding (2003 With Rosewood Corporate Branding 4 Total number of unique guests 115.000 115.000 5 Average daily spend $750 $750 6 Number of days average guest stays 7 Average gross margin per room 32% 32% 8 Average number of visits per year per guest 1,2 1,3 9 Average marketing expense per guest (systemwide) $130 To be calculated 10 Average new guest acquisition expense (systemwide $150 $150 11 Total number of repeat guests 19.169 To be calculated 12 Of which: Total number of multiproperty stay guests 5.750 To be calculated 13 16,67% To be calculated To be calculated To be calculated 1 2 3 4 14 Average Guest Retention Rate 15 Average Gross Profit per Guest 16 17 Years 18 Gross profit per guest 19 Acquisition expense per new guest 20 Marketing expense per guest 21 Net Profit per Guest 22 23 Retention factor 24 Discount factor 25 Net Present Value (NPV) 26 How to Calculate the Customer Life Time Value (CLTV) for Rosewood Hotels and Resorts For each year between 2003 and 2009 you have to do the following calculations using the excel sheet provided and use the assumptions given in the case. Step 1: Calculate the Gross Profit per Customer Gross Profit is the profit a company makes after deducting the costs associated with making and selling its products, or the costs associated with providing its services. The gross margin represents the portion of each dollar of revenue that the company retains as gross profit. For example, average gross margin per room is 32% for Rosewood Hotels & Resorts means on the average company retains $0.32 from each dolac egned for a room after deducting the costs. Revenue per Customer = Revenue per night * numbers of days average guest stays per stay * number of stays per guest (assuming they are retained) Gross Profit per Customer = revenue per customer * average gross margin per room Step 2: Calculate Cash Flow from Customer if Retained Cash Flow from Customer if Retained = gross profit per customer-cost to acquire customer-annual marketing cost per customer Cost to acquire customer is only incurred during 2003 and given as $150 annual marketing cost per customer grows at 3% each year with corporate branding Step 3: Calculate Expected Cash Flow from Customer Expected Cash Flow from Customers cash flow from customer if retained * probability of being retained Probability of being retained = 1 for 2004 Probability of being retained = Probability of being retained previous year * average guest retention rate Step 4: Calculate Present Value (PV) of Expected Cash Flow from Customer PV of Expected Cash Flow from Customer = expected cash flow from customer * discount factor discount factor for yeart = 1/(1+0.08)", for example t=1 for 2004 and 2 for 2005 Step 5: Calculate total Net Present Value (NPV) of CLTV NPV of Expected Cash Flow from Customer = sum of the PV of expected cash flow from customer between 2003 and 2009 Step 6: Calculate Total NPV of CLTV for Individual Branding and Corporate Branding and compare the two 1 Exhibit 8 Rosewood's Brand-wide Customer Lifetime Value Spreadsheet Model 2 3 Without Rosewood Branding (2003 With Rosewood Corporate Branding 4 Total number of unique guests 115.000 115.000 5 Average daily spend $750 $750 6 Number of days average guest stays 7 Average gross margin per room 32% 32% 8 Average number of visits per year per guest 1,2 1,3 9 Average marketing expense per guest (systemwide) $130 To be calculated 10 Average new guest acquisition expense (systemwide $150 $150 11 Total number of repeat guests 19.169 To be calculated 12 Of which: Total number of multiproperty stay guests 5.750 To be calculated 13 16,67% To be calculated To be calculated To be calculated 1 2 3 4 14 Average Guest Retention Rate 15 Average Gross Profit per Guest 16 17 Years 18 Gross profit per guest 19 Acquisition expense per new guest 20 Marketing expense per guest 21 Net Profit per Guest 22 23 Retention factor 24 Discount factor 25 Net Present Value (NPV) 26 How to Calculate the Customer Life Time Value (CLTV) for Rosewood Hotels and Resorts For each year between 2003 and 2009 you have to do the following calculations using the excel sheet provided and use the assumptions given in the case. Step 1: Calculate the Gross Profit per Customer Gross Profit is the profit a company makes after deducting the costs associated with making and selling its products, or the costs associated with providing its services. The gross margin represents the portion of each dollar of revenue that the company retains as gross profit. For example, average gross margin per room is 32% for Rosewood Hotels & Resorts means on the average company retains $0.32 from each dolac egned for a room after deducting the costs. Revenue per Customer = Revenue per night * numbers of days average guest stays per stay * number of stays per guest (assuming they are retained) Gross Profit per Customer = revenue per customer * average gross margin per room Step 2: Calculate Cash Flow from Customer if Retained Cash Flow from Customer if Retained = gross profit per customer-cost to acquire customer-annual marketing cost per customer Cost to acquire customer is only incurred during 2003 and given as $150 annual marketing cost per customer grows at 3% each year with corporate branding Step 3: Calculate Expected Cash Flow from Customer Expected Cash Flow from Customers cash flow from customer if retained * probability of being retained Probability of being retained = 1 for 2004 Probability of being retained = Probability of being retained previous year * average guest retention rate Step 4: Calculate Present Value (PV) of Expected Cash Flow from Customer PV of Expected Cash Flow from Customer = expected cash flow from customer * discount factor discount factor for yeart = 1/(1+0.08)", for example t=1 for 2004 and 2 for 2005 Step 5: Calculate total Net Present Value (NPV) of CLTV NPV of Expected Cash Flow from Customer = sum of the PV of expected cash flow from customer between 2003 and 2009 Step 6: Calculate Total NPV of CLTV for Individual Branding and Corporate Branding and compare the two