Can i please get help, here are the supporting documents needed for the initial questions . Thank you in advance

At the time of the case in 2006, Terrence Conroy was contemplating the challenge of how to increase the profitability of his dealership. With the rise of Internet car sales and the availability of competitive market pricing information, Conroy's Acura is finding it more and more difficult to attract new customers and retain existing customers. Your Task: Assume you have just been hired as an Analyst at Conroy's Acura. You have been observing the debate between Vice President of Sales, Rachel Di Lima, and the sales staff. Di Lima advocates more resources be allocated to retaining existing customers while the sales staff argues that in order to be competitive prices need to be lower. You recall from your Principles of Marketing class a concept called Customer Lifetime Value and you suggest that a CLTV model might be a good way to determine which approach would be best. Terrence Conroy says he doesn't know what a CLTV model is but if you can build something that will help settle debate, he would want to see it. For this assignment focus on the following 4 questions. For each question, please be specific, show your work, and clearly & concisely articulate any logic and/or assumptions required to support your response. It is up to you on how best to submit your responses, but I recommend an Excel file with embedded text boxes to explain your logic and answers. Question 1: Using a spreadsheet program (i.e. Excel or Google Sheets), construct a CLTV model based on the information available in the case. Though all of the key data points are located within the case text, much of the information is summarized on page 4. Note that a formal CLTV formula is provided on page 5. Question 2: Evaluate Di Lima's focus on increasing the retention rate. Specifically, Di Lima wants to double the current marketing budget allocated to retention. How much would the retention rate need to increase in order for this additional expense to be justified? Question 3: Evaluate the sales team's recommendation of lower markups. If Conroy's Acura decides to lower their markup to 6%, how many additional cars would they need to sell to cover the price decrease? Question 4: Now that you have built your CLTV model and understand the dynamics of the business, what sort of strategy would you advocate and why? Note that you do not need to confine your options to the two strategies proposed by Di Lima and the sales team. Conroy's Acura had a number of competitors in the marketplace, the most obvious were the dozen or so other Acura dealerships within a 30-kilometre radius. Conroy's Acura competed directly with dealerships that sold cars comparable to Acura's offerings, such as Saab, Volkswagen and, most notably, Honda. Conroy believed the four biggest competitors in the Toronto market were BMW, Mercedes-Benz, Lexus and Infiniti. BMW and Mercedes-Benz, two German brands, tended to price their cars higher than Acura. Lexus, the luxury brand of Toyota, and Infiniti, the luxury brand of Nissan, tended to price their cars in the same range as Acura. Competition had grown fierce over the past few years, because of the advent of the Internet, which made it much easier for consumers to comparison shop. THE CARS Conroy's Acura sold six models of new cars: Acura CSX - average sales price: $31,860 The Acura CSX was an entry-level luxury car, similar to the Honda Civic. It competed against cars such as the Volkswagen Jetta and the Nissan Sentra. Acura RSX - average sales price: $35,100 A small sporty sedan, the Acura RSX competed with cars such as the Mitsubishi Eclipse and the Volkswagen Golf GTI. Acura TSX - average sales price: $42,984 The Acura TSX competed in the entry-level luxury market with the BMW 3 series, the Mercedez-Benz C- Class, the Audi A4 and the Lexus IS. Acura TL-average sales price: $49,680 The Acura TL was a mid-sized luxury car with many competitors, including the Infiniti G35, the Lexus ES, the Saab 9-3, the Mercedes-Benz E-Class and the Volvo S60. The Acura TL was far and away Conroy's Acura's highest selling car. Acura MDX - average sales price: $61,776 The Acura MDX was a sport utility vehicle (SUV), which had an SUV appearance but a unibody construction typically seen in cars. Other mid-sized crossover SUVs included the Lexus RX, the BMW X5, the Infiniti RX and the Volvo XC90 Acura RL - average sales price: $79,812 The oldest model of the six sold by Conroy's Acura, the Acura RL, competed with the Audi A6, the Lexus GS and the Volvo S80 in the luxury sedan market. SALES AND EXISTING MARKETING EFFORTS The firm was currently spending $120,000 on marketing to new clients each year, split between billboards, direct mail, radio and television spots, and newspaper and community magazine advertisements. Conroy's Acura sold approximately 650 cars each year, spread over six car classes. On each model, the firm's gross THE DATA KEPT BY CONROY'S ACURA The dealership kept simple records of their sales and marketing efforts, including the following: . The $120,000 spent on advertising. The number of sales of each car class, including the sales to first-time clients and the sales to previous Conroy's Acura customers. The dealer cost per car class, the average price and the markup for the firm, which historically was eight per cent per car class. The length of time a customer kept a car before purchasing a new one. Historically, for the Acura brand, the average was four years for each class of car. The historic retention rate, which was the percentage of customers who purchased their next car from the dealership. Since 1999, when Terrence Conroy became president of Conroy's Acura, the retention rate had been stable at 25 per cent. The yearly maintenance cost, or the cost of maintaining the customer relationship. Currently the firm spent $10 per year per current customer (those that had purchased in the past four years). . From these data, the firm could calculate the cost of acquiring a new consumer (the acquisition cost), the customer lifetime value and the return on marketing of increasing the marketing efforts (see Exhibit 1). Acquisition Cost The acquisition cost compared the amount of money spent on marketing to the general public to the number of cars sold to first-time clients. This cost is expressed by the equation: Fixed Marketing Cost / Total Sales to New Clients The firm's marketing budget was currently set at $120,000, and it expected to sell 497 cars to first-time clients this year, leading to an acquisition cost of $241 per client ($120,000/497 = $241). Customer Lifetime Value (CLV) The customer lifetime value related the amount the firm expected the average customer to spend over a lifetime. This amount varied from car class to car class. To calculate the customer lifetime value, the following data were needed: Page 5 . d: The discount rate, which represents the time value of money, the fact that a dollar today is worth more than a dollar next year. The higher the number, the more the future is discounted relative to today. Conroy's Acura used a discount rate of five per cent. y: The length of time a customer keeps a car before purchasing a new one. r: The retention rate, the percentage of consumers who return to the dealership to purchase their next . car. . m: The yearly maintenance cost of maintaining the customer relationship. p: The sales price of the car. c: The dealer cost of the car. The customer lifetime value (CLV) is calculated as follows: CLV=((1 d)' * ((p-C-(m * y))/(1 r))) The customer lifetime value increases as the price p and the retention rate r increase, but decreases as the discount rate d, the dealer cost c, the yearly maintenance cost m and length of time between purchases y increases. At the time of the case in 2006, Terrence Conroy was contemplating the challenge of how to increase the profitability of his dealership. With the rise of Internet car sales and the availability of competitive market pricing information, Conroy's Acura is finding it more and more difficult to attract new customers and retain existing customers. Your Task: Assume you have just been hired as an Analyst at Conroy's Acura. You have been observing the debate between Vice President of Sales, Rachel Di Lima, and the sales staff. Di Lima advocates more resources be allocated to retaining existing customers while the sales staff argues that in order to be competitive prices need to be lower. You recall from your Principles of Marketing class a concept called Customer Lifetime Value and you suggest that a CLTV model might be a good way to determine which approach would be best. Terrence Conroy says he doesn't know what a CLTV model is but if you can build something that will help settle debate, he would want to see it. For this assignment focus on the following 4 questions. For each question, please be specific, show your work, and clearly & concisely articulate any logic and/or assumptions required to support your response. It is up to you on how best to submit your responses, but I recommend an Excel file with embedded text boxes to explain your logic and answers. Question 1: Using a spreadsheet program (i.e. Excel or Google Sheets), construct a CLTV model based on the information available in the case. Though all of the key data points are located within the case text, much of the information is summarized on page 4. Note that a formal CLTV formula is provided on page 5. Question 2: Evaluate Di Lima's focus on increasing the retention rate. Specifically, Di Lima wants to double the current marketing budget allocated to retention. How much would the retention rate need to increase in order for this additional expense to be justified? Question 3: Evaluate the sales team's recommendation of lower markups. If Conroy's Acura decides to lower their markup to 6%, how many additional cars would they need to sell to cover the price decrease? Question 4: Now that you have built your CLTV model and understand the dynamics of the business, what sort of strategy would you advocate and why? Note that you do not need to confine your options to the two strategies proposed by Di Lima and the sales team. Conroy's Acura had a number of competitors in the marketplace, the most obvious were the dozen or so other Acura dealerships within a 30-kilometre radius. Conroy's Acura competed directly with dealerships that sold cars comparable to Acura's offerings, such as Saab, Volkswagen and, most notably, Honda. Conroy believed the four biggest competitors in the Toronto market were BMW, Mercedes-Benz, Lexus and Infiniti. BMW and Mercedes-Benz, two German brands, tended to price their cars higher than Acura. Lexus, the luxury brand of Toyota, and Infiniti, the luxury brand of Nissan, tended to price their cars in the same range as Acura. Competition had grown fierce over the past few years, because of the advent of the Internet, which made it much easier for consumers to comparison shop. THE CARS Conroy's Acura sold six models of new cars: Acura CSX - average sales price: $31,860 The Acura CSX was an entry-level luxury car, similar to the Honda Civic. It competed against cars such as the Volkswagen Jetta and the Nissan Sentra. Acura RSX - average sales price: $35,100 A small sporty sedan, the Acura RSX competed with cars such as the Mitsubishi Eclipse and the Volkswagen Golf GTI. Acura TSX - average sales price: $42,984 The Acura TSX competed in the entry-level luxury market with the BMW 3 series, the Mercedez-Benz C- Class, the Audi A4 and the Lexus IS. Acura TL-average sales price: $49,680 The Acura TL was a mid-sized luxury car with many competitors, including the Infiniti G35, the Lexus ES, the Saab 9-3, the Mercedes-Benz E-Class and the Volvo S60. The Acura TL was far and away Conroy's Acura's highest selling car. Acura MDX - average sales price: $61,776 The Acura MDX was a sport utility vehicle (SUV), which had an SUV appearance but a unibody construction typically seen in cars. Other mid-sized crossover SUVs included the Lexus RX, the BMW X5, the Infiniti RX and the Volvo XC90 Acura RL - average sales price: $79,812 The oldest model of the six sold by Conroy's Acura, the Acura RL, competed with the Audi A6, the Lexus GS and the Volvo S80 in the luxury sedan market. SALES AND EXISTING MARKETING EFFORTS The firm was currently spending $120,000 on marketing to new clients each year, split between billboards, direct mail, radio and television spots, and newspaper and community magazine advertisements. Conroy's Acura sold approximately 650 cars each year, spread over six car classes. On each model, the firm's gross THE DATA KEPT BY CONROY'S ACURA The dealership kept simple records of their sales and marketing efforts, including the following: . The $120,000 spent on advertising. The number of sales of each car class, including the sales to first-time clients and the sales to previous Conroy's Acura customers. The dealer cost per car class, the average price and the markup for the firm, which historically was eight per cent per car class. The length of time a customer kept a car before purchasing a new one. Historically, for the Acura brand, the average was four years for each class of car. The historic retention rate, which was the percentage of customers who purchased their next car from the dealership. Since 1999, when Terrence Conroy became president of Conroy's Acura, the retention rate had been stable at 25 per cent. The yearly maintenance cost, or the cost of maintaining the customer relationship. Currently the firm spent $10 per year per current customer (those that had purchased in the past four years). . From these data, the firm could calculate the cost of acquiring a new consumer (the acquisition cost), the customer lifetime value and the return on marketing of increasing the marketing efforts (see Exhibit 1). Acquisition Cost The acquisition cost compared the amount of money spent on marketing to the general public to the number of cars sold to first-time clients. This cost is expressed by the equation: Fixed Marketing Cost / Total Sales to New Clients The firm's marketing budget was currently set at $120,000, and it expected to sell 497 cars to first-time clients this year, leading to an acquisition cost of $241 per client ($120,000/497 = $241). Customer Lifetime Value (CLV) The customer lifetime value related the amount the firm expected the average customer to spend over a lifetime. This amount varied from car class to car class. To calculate the customer lifetime value, the following data were needed: Page 5 . d: The discount rate, which represents the time value of money, the fact that a dollar today is worth more than a dollar next year. The higher the number, the more the future is discounted relative to today. Conroy's Acura used a discount rate of five per cent. y: The length of time a customer keeps a car before purchasing a new one. r: The retention rate, the percentage of consumers who return to the dealership to purchase their next . car. . m: The yearly maintenance cost of maintaining the customer relationship. p: The sales price of the car. c: The dealer cost of the car. The customer lifetime value (CLV) is calculated as follows: CLV=((1 d)' * ((p-C-(m * y))/(1 r))) The customer lifetime value increases as the price p and the retention rate r increase, but decreases as the discount rate d, the dealer cost c, the yearly maintenance cost m and length of time between purchases y increases