Measure Marketing Help!
DeMario34
ReviewAssignmentModule3afinished.xlsxInformation for Module
| An accounting firm currently provides accounting and taxation advice to both businesses and individual clients. In the acquisition year, the firm acquired 1,100 business clients and 2,500 individual clients. The following contains a summary for these two client groups: |
| •The accounting firm spent $15,000 to acquire each business client. |
| •The accounting firm spent $1,500 to acquire each individual client. |
| •The cost of the average business client is $5,000 each year (after the acquisition year). |
| •The cost of the average individual client is $500 (after the acquisition year). |
| •The average annual income for a business client is $10,000. |
| •The average annual income for an individual client is $1,000. |
| •The average time they remain a client is 10 years (business clients) and 4 years (individual client). |
| •The average retention rate on business clients is 85%. |
| •The average retention rate on individual clients is 75%. |
| •The current interest rate is 5% (for both client groups). |
| On the next two worksheets, calculate both the Customer Lifetime Value for Business Customers and the Customer Lifetime Value for Individual Customers. |
| Then Answer the Following Question: |
| Next, conduct further analysis on the customer segment with lower CLV. Using the worksheets titled Q2a - Q2d, calculate the customer lifetime value for each of the four options below using a percentage change of 10% for each variable in a-d. Which of the following approaches has the greatest impact on CLV (highlight the correct answer below)? Use a percentage change of 10% for each variable in 2a-2d and hold all other variables constant at original levels. Q2a. Enhancing customer loyalty (increasing retention rate by 10%) Q2b. Growing income/revenue per customer (increasing revenue per customer by 10%) Q2c. Reducing acquisition costs (reducing acquisition costs by 10%) Q2d. Reducing service costs (reducing service costs by 10%) |
| Q2B- growing income/revenue |
| By: DeMario McDonald |
| 6/11/20 |
Q1. Should the firm focus on acquiring new business clients OR new individual customers (or both)? Explain your answer by providing support from your CLV analysis. Answer: Although business customers generate more revenue, I think the firm should aquire both business and individual customers to diversify. Individual customers are still supplying a profit margin.
CLV Business Customers
| Interest Rate | 5% | ||||||||||
| Discount Rate (Interest Rate x 2) | 10% | ||||||||||
| Average Retention Rate | 85% | ||||||||||
| Acquisition Year | |||||||||||
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |
| Business Customers | 1,100 | 935 | 795 | 676 | 574 | 488 | 415 | 353 | 300 | 255 | 217 |
| Retention Rate | 85% | 85% | 85% | 85% | 85% | 85% | 85% | 85% | 85% | 85% | 85% |
| Revenue per Customer | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 |
| Total Revenue | $ 11,000,000.00 | $ 9,350,000.00 | $ 7,947,500.00 | $ 6,755,375.00 | $ 5,742,068.75 | $ 4,880,758.44 | $ 4,148,644.67 | $ 3,526,347.97 | $ 2,997,395.78 | $ 2,547,786.41 | $ 2,165,618.45 |
| Acquisition/Mktg Costs | $ 15,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 |
| Total Costs | $ 16,500,000.00 | $ 4,675,000.00 | $ 3,973,750.00 | $ 3,377,687.50 | $ 2,871,034.38 | $ 2,440,379.22 | $ 2,074,322.34 | $ 1,763,173.99 | $ 1,498,697.89 | $ 1,273,893.20 | $ 1,082,809.22 |
| Gross Profit | $ (5,500,000.00) | $ 4,675,000.00 | $ 3,973,750.00 | $ 3,377,687.50 | $ 2,871,034.38 | $ 2,440,379.22 | $ 2,074,322.34 | $ 1,763,173.99 | $ 1,498,697.89 | $ 1,273,893.20 | $ 1,082,809.22 |
| Discount Rate | 10% | 10% | 10% | 10% | 10% | 10% | 10% | 10% | 10% | 10% | 10% |
| Net Present Value (NPV) | $ (5,500,000.00) | $ 4,250,000.00 | $ 3,284,090.91 | $ 2,537,706.61 | $ 1,960,955.11 | $ 1,515,283.49 | $ 1,170,900.88 | $ 904,787.04 | $ 699,153.63 | $ 540,255.07 | $ 417,469.83 |
| Cumulative NPV | $ (5,500,000.00) | $ (1,250,000.00) | $ 2,034,090.91 | $ 4,571,797.52 | $ 6,532,752.63 | $ 8,048,036.12 | $ 9,218,937.00 | $ 10,123,724.05 | $ 10,822,877.67 | $ 11,363,132.75 | $ 11,780,602.58 |
| Customer Lifetime Value | $ (5,000.00) | $ (1,336.90) | $ 2,559.41 | $ 6,767.64 | $ 11,377.00 | $ 16,489.31 | $ 22,221.56 | $ 28,708.81 | $ 36,107.60 | $ 44,600.02 | $ 54,398.33 |
CLV Individual Customers
| Interest Rate | 5% | ||||
| Discount Rate | 10% | ||||
| Average Retention Rate | 75% | ||||
| Acquisition Year | |||||
| 0 | 1 | 2 | 3 | 4 | |
| Customers | 2,500 | 1875 | 1406.25 | 1054.6875 | 791.015625 |
| Retention Rate | 75% | 75% | 75% | 75% | 75% |
| Revenue per Customer | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 |
| Total Revenue | $ 2,500,000.00 | $ 1,875,000.00 | $ 1,406,250.00 | $ 1,054,687.50 | $ 791,015.63 |
| Acquisition/Mktg Costs | $ 1,500.00 | $ 500.00 | $ 500.00 | $ 500.00 | $ 500.00 |
| Total Costs | $ 3,750,000.00 | $ 937,500.00 | $ 703,125.00 | $ 527,343.75 | $ 395,507.81 |
| Gross Profit | $ (1,250,000.00) | $ 937,500.00 | $ 703,125.00 | $ 527,343.75 | $ 395,507.81 |
| Discount Rate | 10% | 10% | 10% | 10% | 10% |
| Net Present Value (NPV) | $ (1,250,000.00) | $ 852,272.73 | $ 581,095.04 | $ 396,201.16 | $ 270,137.16 |
| Cumulative NPV | $ (1,250,000.00) | $ (397,727.27) | $ 183,367.77 | $ 579,568.93 | $ 849,706.09 |
| Customer Lifetime Value | $ (500.00) | $ (212.12) | $ 130.39 | $ 549.52 | $ 1,074.20 |
Q2a Indiv increase retention
| Interest Rate | 5% | ||||
| Discount Rate | 10% | ||||
| Average Retention Rate | 75% | ||||
| Acquisition Year | |||||
| 0 | 1 | 2 | 3 | 4 | |
| Customers | 2,500 | 2062.5 | 1701.5625 | 1403.7890625 | 1158.1259765625 |
| Retention Rate | 83% | 83% | 83% | 83% | 83% |
| Revenue per Customer | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 |
| Total Revenue | $ 2,500,000.00 | $ 2,062,500.00 | $ 1,701,562.50 | $ 1,403,789.06 | $ 1,158,125.98 |
| Acquisition/Mktg Costs | $ 1,500.00 | $ 500.00 | $ 500.00 | $ 500.00 | $ 500.00 |
| Total Costs | $ 3,750,000.00 | $ 1,031,250.00 | $ 850,781.25 | $ 701,894.53 | $ 579,062.99 |
| Gross Profit | $ (1,250,000.00) | $ 1,031,250.00 | $ 850,781.25 | $ 701,894.53 | $ 579,062.99 |
| Discount Rate | 10% | 10% | 10% | 10% | 10% |
| Net Present Value (NPV) | $ (1,250,000.00) | $ 937,500.00 | $ 703,125.00 | $ 527,343.75 | $ 395,507.81 |
| Cumulative NPV | $ (1,250,000.00) | $ (312,500.00) | $ 390,625.00 | $ 917,968.75 | $ 1,313,476.56 |
| Customer Lifetime Value | $ (500.00) | $ (151.52) | $ 229.57 | $ 653.92 | $ 1,134.14 |
Q2b Indiv increase revenue
| Interest Rate | 5% | ||||
| Discount Rate | 10% | ||||
| Average Retention Rate | 75% | ||||
| Acquisition Year | |||||
| 0 | 1 | 2 | 3 | 4 | |
| Customers | 2,500 | 1875 | 1406.25 | 1054.6875 | 791.015625 |
| Retention Rate | 75% | 75% | 75% | 75% | 75% |
| Revenue per Customer | $ 1,100.00 | $ 1,100.00 | $ 1,100.00 | $ 1,100.00 | $ 1,100.00 |
| Total Revenue | $ 2,750,000.00 | $ 2,062,500.00 | $ 1,546,875.00 | $ 1,160,156.25 | $ 870,117.19 |
| Acquisition/Mktg Costs | $ 1,500.00 | $ 500.00 | $ 500.00 | $ 500.00 | $ 500.00 |
| Total Costs | $ 3,750,000.00 | $ 937,500.00 | $ 703,125.00 | $ 527,343.75 | $ 395,507.81 |
| Gross Profit | $ (1,000,000.00) | $ 1,125,000.00 | $ 843,750.00 | $ 632,812.50 | $ 474,609.38 |
| Discount Rate | 10% | 10% | 10% | 10% | 10% |
| Net Present Value (NPV) | $ (1,000,000.00) | $ 1,022,727.27 | $ 697,314.05 | $ 475,441.40 | $ 324,164.59 |
| Cumulative NPV | $ (1,000,000.00) | $ 22,727.27 | $ 720,041.32 | $ 1,195,482.72 | $ 1,519,647.31 |
| Customer Lifetime Value | $ (400.00) | $ 12.12 | $ 512.03 | $ 1,133.49 | $ 1,921.13 |
Q2c Indiv reduce acquisition
| Interest Rate | 5% | ||||
| Discount Rate | 10% | ||||
| Average Retention Rate | 75% | ||||
| Acquisition Year | |||||
| 0 | 1 | 2 | 3 | 4 | |
| Customers | 2,500 | 1875 | 1406.25 | 1054.6875 | 791.015625 |
| Retention Rate | 75% | 75% | 75% | 75% | 75% |
| Revenue per Customer | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 |
| Total Revenue | $ 2,500,000.00 | $ 1,875,000.00 | $ 1,406,250.00 | $ 1,054,687.50 | $ 791,015.63 |
| Acquisition/Mktg Costs | $ 1,350.00 | $ 500.00 | $ 500.00 | $ 500.00 | $ 500.00 |
| Total Costs | $ 3,375,000.00 | $ 937,500.00 | $ 703,125.00 | $ 527,343.75 | $ 395,507.81 |
| Gross Profit | $ (875,000.00) | $ 937,500.00 | $ 703,125.00 | $ 527,343.75 | $ 395,507.81 |
| Discount Rate | 10% | 10% | 10% | 10% | 10% |
| Net Present Value (NPV) | $ (875,000.00) | $ 852,272.73 | $ 581,095.04 | $ 396,201.16 | $ 270,137.16 |
| Cumulative NPV | $ (875,000.00) | $ (22,727.27) | $ 558,367.77 | $ 954,568.93 | $ 1,224,706.09 |
| Customer Lifetime Value | $ (350.00) | $ (12.12) | $ 397.06 | $ 905.07 | $ 1,548.27 |
| $ 4,050.00 |
Q2d Indiv reduce service cost
| Interest Rate | 5% | ||||
| Discount Rate | 10% | ||||
| Average Retention Rate | 75% | ||||
| Acquisition Year | |||||
| 0 | 1 | 2 | 3 | 4 | |
| Customers | 2,500 | 1875 | 1406.25 | 1054.6875 | 791.015625 |
| Retention Rate | 75% | 75% | 75% | 75% | 75% |
| Revenue per Customer | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 | $ 1,000.00 |
| Total Revenue | $ 2,500,000.00 | $ 1,875,000.00 | $ 1,406,250.00 | $ 1,054,687.50 | $ 791,015.63 |
| Acquisition/Mktg Costs | $ 1,500.00 | $ 450.00 | $ 450.00 | $ 450.00 | $ 450.00 |
| Total Costs | $ 3,750,000.00 | $ 843,750.00 | $ 632,812.50 | $ 474,609.38 | $ 355,957.03 |
| Gross Profit | $ (1,250,000.00) | $ 1,031,250.00 | $ 773,437.50 | $ 580,078.13 | $ 435,058.59 |
| Discount Rate | 10% | 10% | 10% | 10% | 10% |
| Net Present Value (NPV) | $ (1,250,000.00) | $ 937,500.00 | $ 639,204.55 | $ 435,821.28 | $ 297,150.87 |
| Cumulative NPV | $ (1,250,000.00) | $ (312,500.00) | $ 326,704.55 | $ 762,525.83 | $ 1,059,676.70 |
| Customer Lifetime Value | $ (500.00) | $ (166.67) | $ 232.32 | $ 722.99 | $ 1,339.64 |
