Q2C - (71.43% All customers) (40% Private Label) (20% Branded)
Now that you have done some analysis on D Billie's Brand customer data, it is time to understand more about the profitability of these customers. Refer to the Excel spreadsheet on Canvas for D Billie's data, as well as your work in Part 1. 7. Identify the cash margin ($)* in 2021 for the A) Total, B) Private Label, and C) Branded Segments. (*NOTE: use operating contribution) 8. Assuming a 5% discount rate, calculate the customer lifetime value (CLV) (p.172] for A) all customers, B) private label customers, and C) branded customers. D) Calculate specific customer values for Trader Joes and Walmart. [NOTE: See Q# 2C for Retention Rate %] From 2019-2021, the Company presented new business proposals to three customers for private label (including Weis) and five customers for branded business (including Target and Dollar General). Of these proposals, contracts closed with Weis Markets (private label) and Target. The Dollar General contract was lost in 2020, but it was regained in 2021. 9. Calculate the acquisition rate (%) of prospects purchasing for the A) total company, B) private label segment, and C) branded segment. 10. A) Calculate the cumulative lifetime values of the 3 new customers in Q9. Assume a 5% discount rate. B) Using these figures, calculate the average CLV for the Branded and Total customer categories. [NOTE: See Q# 2C for Retention Rate %] Assume the average margin ($) on the initial purchase is as follows: Weis (PL) 1 Target 3 Dollar General 2 Branded Average 3 Total Average 2 11. Calculate the prospect lifetime value (PLV) (p. 178-179] for the A) total company, B) private label, and C) branded prospects. [NOTE: The company wants to know the BE point for spending on new prospects, so there is currently no acquisition spending ($).] Would you work to acquire a prospect that costs: $9.58 (Total customers) $18.67 (Private Label) $9.19 (Branded) 12. How would prospect expected value (PLV) be used by an organization? What value does it have for an organization? Customers: 11 Trader Joes 160 175 Year 2019 2020 2021 Whole Foods 145 145 150 Food Lion 80 Gross Sales (5000) Private Label Contracts Weis Giant Markets Foods 0 85 25 70 50 75 Private Label Total 470 415 475 Weis Markets 25 25 30 Giant Foods 20 23 25 D Billie's Brand Dollar Walmart General 60 50 75 0 100 30 Brand Total 155 173 Target 0 0 50 75 Company Total 625 588 735 0 0 200 260 1 -D Billie's Brand Trader Joes 200 Whole Foods 150 - Private Label Contracts Weis Giant Markets Foods 50 75 Food Lion 0 Dollar General 50 Gross Sales Discounts Private Label Total 475 0 0 475 Weis Markets 30 3 5 22 Giant Foods 25 3 3 19 Walmart 100 11 5 84 Target 75 8 4 Brand Total 280 25 17 238 Company Total 755 25 17 713 Returns 0 50 Net Sales 200 150 50 75 0 63 100 75 13 39 Manufacturing Cost-Variable Shipping and Warehousing Total Direct Costs 28 3 31 40 4 44 243 7 250 15 2 17 50 5 55 30 4 1 4 147 16 163 390 23 413 100 75 0 14 34 43 Gross Profit % 100 50.0% 75 50.0% 19 38.0% % 31 41.3% 0 #DIV/0! 225 47.4% 5 5 22.7% 5 26.3% 29 34.5% 16 32.0% 20 31.7% 75 31.5% 300 42.1% 3 1 2 6 1 4 3 Commisions Advertising Total Expense 1 1 2 1 2 5 9 3 6 3 4 4 7 12 15 27 18 15 33 0 0 3 1 2 0 0 6 3 0 Operating Contribution % 100 50.0% 72 48.0% 18 36.0% 29 38.7% 219 46.1% % 2 9.1% 3 15.8% 20 23.8% 10 20.0% 13 20.6% 48 20.2% 267 37.4% #DIV/0! Fixed Costs: Manufacturing Cost-Fixed Sales Force General & Administrative Total Fixed Costs 200 50 75 325 Operating Income (58) Now that you have done some analysis on D Billie's Brand customer data, it is time to understand more about the profitability of these customers. Refer to the Excel spreadsheet on Canvas for D Billie's data, as well as your work in Part 1. 7. Identify the cash margin ($)* in 2021 for the A) Total, B) Private Label, and C) Branded Segments. (*NOTE: use operating contribution) 8. Assuming a 5% discount rate, calculate the customer lifetime value (CLV) (p.172] for A) all customers, B) private label customers, and C) branded customers. D) Calculate specific customer values for Trader Joes and Walmart. [NOTE: See Q# 2C for Retention Rate %] From 2019-2021, the Company presented new business proposals to three customers for private label (including Weis) and five customers for branded business (including Target and Dollar General). Of these proposals, contracts closed with Weis Markets (private label) and Target. The Dollar General contract was lost in 2020, but it was regained in 2021. 9. Calculate the acquisition rate (%) of prospects purchasing for the A) total company, B) private label segment, and C) branded segment. 10. A) Calculate the cumulative lifetime values of the 3 new customers in Q9. Assume a 5% discount rate. B) Using these figures, calculate the average CLV for the Branded and Total customer categories. [NOTE: See Q# 2C for Retention Rate %] Assume the average margin ($) on the initial purchase is as follows: Weis (PL) 1 Target 3 Dollar General 2 Branded Average 3 Total Average 2 11. Calculate the prospect lifetime value (PLV) (p. 178-179] for the A) total company, B) private label, and C) branded prospects. [NOTE: The company wants to know the BE point for spending on new prospects, so there is currently no acquisition spending ($).] Would you work to acquire a prospect that costs: $9.58 (Total customers) $18.67 (Private Label) $9.19 (Branded) 12. How would prospect expected value (PLV) be used by an organization? What value does it have for an organization? Customers: 11 Trader Joes 160 175 Year 2019 2020 2021 Whole Foods 145 145 150 Food Lion 80 Gross Sales (5000) Private Label Contracts Weis Giant Markets Foods 0 85 25 70 50 75 Private Label Total 470 415 475 Weis Markets 25 25 30 Giant Foods 20 23 25 D Billie's Brand Dollar Walmart General 60 50 75 0 100 30 Brand Total 155 173 Target 0 0 50 75 Company Total 625 588 735 0 0 200 260 1 -D Billie's Brand Trader Joes 200 Whole Foods 150 - Private Label Contracts Weis Giant Markets Foods 50 75 Food Lion 0 Dollar General 50 Gross Sales Discounts Private Label Total 475 0 0 475 Weis Markets 30 3 5 22 Giant Foods 25 3 3 19 Walmart 100 11 5 84 Target 75 8 4 Brand Total 280 25 17 238 Company Total 755 25 17 713 Returns 0 50 Net Sales 200 150 50 75 0 63 100 75 13 39 Manufacturing Cost-Variable Shipping and Warehousing Total Direct Costs 28 3 31 40 4 44 243 7 250 15 2 17 50 5 55 30 4 1 4 147 16 163 390 23 413 100 75 0 14 34 43 Gross Profit % 100 50.0% 75 50.0% 19 38.0% % 31 41.3% 0 #DIV/0! 225 47.4% 5 5 22.7% 5 26.3% 29 34.5% 16 32.0% 20 31.7% 75 31.5% 300 42.1% 3 1 2 6 1 4 3 Commisions Advertising Total Expense 1 1 2 1 2 5 9 3 6 3 4 4 7 12 15 27 18 15 33 0 0 3 1 2 0 0 6 3 0 Operating Contribution % 100 50.0% 72 48.0% 18 36.0% 29 38.7% 219 46.1% % 2 9.1% 3 15.8% 20 23.8% 10 20.0% 13 20.6% 48 20.2% 267 37.4% #DIV/0! Fixed Costs: Manufacturing Cost-Fixed Sales Force General & Administrative Total Fixed Costs 200 50 75 325 Operating Income (58)
