I need help with a consumer equity assignment that asks you to choose one of workbooks in an excel spread sheet and answer a few questions.

I have attached the details of the assignment in the activity guide document and have also provided a spread sheet with the 4 workbook options of which you are supposed to choose one to write on.

Activity\tGuide Customer\tEquity\tModel\t-\tAn\tInteractive\tExercise An\texercise\tdeveloped\tby\tProfessor\tJoe\tCannon,\tColorado\tState\tUniversity Learning\tObjectives By\tcompleting\tthis\tactivity,\ta\tstudent\twill: Be\tintroduced\tto\tcustomer\tequity\tconcepts Understand\tone\ttype\tof\tquantitative\tmarketing\tmodel Be\table\tto\texplain\thow\tsuch\ta\tmodel\tcan\tbe\tused\tto\tmake\tmarketing\tresource\tallocation decisions\tand\tmarketing\tstrategy\tplanning. Be\table\tto\tdescribe\tstrategic\toptions\tavailable\tto\tmarketing\tmanagers. Understand\thow\ta\tcustomer\tequity\tapproach\tto\tmarketing\tstrategy\tplanning\tcan\toperate\tin practice. Description This\tspreadsheet\tand\tassociated\texercise\tis\tdesigned\tto\tgive\tyou\tsome\tinsight\tinto\tthe\tcustomer\tequity approach\tto\tmarketing\tmanagement. The\tspreadsheet\tis\tan\texample\tof\ta\tmarketing\tmodel.\tMarketing managers\tare\tincreasingly\tcombining\tart,\tintuition,\tand\tscience\tto\tcreate\tquantitative\tmodels. The\tCustomer\tEquity\tModel\tallows\tyou\tto\tchange\tassumptions\tand\testimate\tthe\timpact\ton\ta\tfirm, company,\tor\tproduct's\tcustomer\tequity\t(in\tfact,\tsome\tof\tmy\tformer\tstudents\thave\trun\tdifferent\tanalyses across\tdifferent\tcustomer\tsegments\t-\tto\ttailor\tmarketing\tactivities\tby\tsegment). The\tspreadsheet\tincludes\tfour\tworkbooks\tto\tallow\tyou\tto\tsee\thow\tit\tmight\toperate\tin\tdifferent industries.\tThe\tnumbers\there\tare\trepresentative\tand\tdesigned\tto\tfoster\tdiscussion. \"Online\tretailer\" \"B2B\"\t-\tbased\tloosely\ton\ta\twholesale\tdistributor\tof\telectronic\tcomponents \"Restaurant\" \"Edit\tYour\tOwn\"\t-\tyou\tmight\tuse\tthis\tto\tdevelop\tsomething\tbased\ton\tyour\town\tcompany\tor\ta business\tyou\tare\tinterested\tin\tstarting. Choose\tone\tof\tthe\tprepared\tthree\tscenarios\t-\tor\tmake\testimates\tfor\tyour\town\tbusiness.\tThen\tgo through\tand\tcome\tup\twith\tsome\tstrategy\tideas\tfor\ta\tcompany\tin\tthis\tindustry.\tThink\tabout\tthe\tvarious variables\tin\tthe\tmodel\tthat\tmight\tbe\tchanged\tif\tthat\tstrategy\twere\timplemented.\tThen\tenter\tyour estimates\tand\tobserve\thow\tit\tchanges\tNPV\tof\tcustomer\tcontribution. Discussion\tQuestions Think\tabout\tand\tjot\tdown\tnotes\tfor\teach\tof\tthe\tfollowing\tquestions. 1. In\tthe\treal\tworld,\thow\tdo\tyou\tthink\ta\tmarketing\tmanager\twould\tdetermine\tthe\tvalues\tfor\tsome of\tthe\tvariables\tin\tthe\tmodel\t(cells\tB4:B15)? 2. What\tare\tstrengths\tof\tthe\tCustomer\tEquity\tModel? 3. What\tare\tweaknesses\tof\tthe\tCustomer\tEquity\tModel? 4. What\thave\tyou\tlearned\tabout\tmarketing\tfrom\tthis\texperience? Appendix:\tStarting\tValues\tfor\tthe\tCustomer\tEquity\tSpreadsheet Online\tretail Variables in the Model Which May Be Changed 1. Acquisition costs per customer 2. Average revenues from a new customer in first year $75 $150 3. Contribution margin 40% 4. Number of new customers in Year 1 1000 5. Number of new customers added due to marketing efforts 3000 6. Customer retention rate 60% 7. Retention/maintenance costs for current customers 8. Annual revenue growth from customers 9. Cost savings (change in contribution) for retained customers 10. Referrals from current customers $10 20% 1% 20% 11. Price premium from current customers 0% 12. Discount rate for present value calculation 3% Customer Equity: NPV of Customer Contribution $320,910 B2B Variables in the Model Which May Be Changed 1. Acquisition costs per customer 2. Average revenues from a new customer in first year 3. Contribution margin 4. Number of new customers in Year 1 5. Number of new customers added due to marketing efforts $1,800 $10,000 20% 200 25 6. Customer retention rate 80% 7. Retention/maintenance costs for current customers $400 8. Annual revenue growth from customers 9. Cost savings (change in contribution) for retained customers 10. Referrals from current customers 8% 1% 10% 11. Price premium from current customers 0% 12. Discount rate for present value calculation 3% Customer Equity: NPV of Customer Contribution $1,251,714 Restaurant Variables in the Model Which May Be Changed 1. Acquisition costs per customer $100 2. Average revenues from a new customer in first year $250 3. Contribution margin 50% 4. Number of new customers in Year 1 1000 5. Number of new customers added due to marketing efforts 6. Customer retention rate 7. Retention/maintenance costs for current customers 200 60% $25 8. Annual revenue growth from customers 20% 9. Cost savings (change in contribution) for retained customers 10% 10. Referrals from current customers 40% 11. Price premium from current customers 0% 12. Discount rate for present value calculation 5% Customer Equity: NPV of Customer Contribution $497,799 CREATING CUSTOMER EQUITY SPREADSHEET AND MODEL Variables in the Model Which May Be Changed 1. Acquisition costs per customer 2. Average revenues from a new customer in first year 3. Contribution margin 4. Number of new customers in Year 1 5. Number of new customers added due to marketing efforts 6. Customer retention rate 7. Retention/maintenance costs for current customers 8. Annual revenue growth from customers 9. Cost savings (change in contribution) for retained customers 10. Referrals from current customers 11. Price premium from current customers 12. Discount rate for present value calculation Customer Equity: NPV of Customer Contribution $75 $150 40% 1000 3000 60% $10 20% 1% 20% 0% 3% Assumed to operate as a variable cost but not included as a variable cost for contribution margin purposes, constant over the time horizon Assumed constant for new customers over the time horizon Value for new customers, could gradually increase for retained customers (see below) Number of new customers Assumes constant growth "number" per year Assumed to be the same for all current customers from previous year Assumed constant over the full time horizon Customers are assumed to increase their purchases over time from cross-selling and generally preferring the business -- rate does not change over time Assumed to increase annually at this rate for 5 years then level off Percentage of customers who refer a single customer. Assumed that the referrals will come from customers retained for more than one year. These customers are added without acquisition costs. Assumes a premium that is constant beginning in third year as a customer -- ie., premium is constant following the third year a customer is retained $320,910 Pro Forma Income Statement Revenues from new customers from current customers Total Revenues Costs acquisition costs (not part of contribution) variable costs of sales [ie., revenue*(1-contribution)] retention/maintenance costs (# of customers *maint costs) Total Variable, Acquisition and Maintenance Costs Customer contribution after variable, acquisition, and maintenance costs Rate of contribution after variable, acquisition, and maintenance costs Net Present Value of Customer Contribution Joe Cannon 2016 all rights reserved $320,910 $150,000.0 $450,000.0 $129,600 $468,000.0 $482,112 $514,800.0 $751,473 $545,040.0 $985,848 $150,000 $579,600 $950,112 $1,266,273 $1,530,888 $75,000.0 $90,000 10,000 $225,000.0 $400,464 36,000 $225,000.0 $620,473 52,800 $225,000.0 $809,882 66,000 $225,000.0 $965,685 75,936 175,000 661,464 898,273 1,100,882 1,266,621 -25,000 -17% -81,864 -14% 51,839 5% 165,391 13% 264,266 17% CREATING CUSTOMER EQUITY SPREADSHEET AND MODEL Variables in the Model Which May Be Changed 1. Acquisition costs per customer 2. Average revenues from a new customer in first year 3. Contribution margin 4. Number of new customers in Year 1 5. Number of new customers added due to marketing efforts 6. Customer retention rate 7. Retention/maintenance costs for current customers 8. Annual revenue growth from customers 9. Cost savings (change in contribution) for retained customers 10. Referrals from current customers 11. Price premium from current customers 12. Discount rate for present value calculation Customer Equity: NPV of Customer Contribution $1,800 $10,000 20% 200 25 80% $400 8% 1% 10% 0% 3% $1,251,714 Pro Forma Income Statement Revenues from new customers from current customers $2,000,000.0 Total Revenues $2,000,000 Costs acquisition costs (not part of contribution) variable costs of sales [ie., revenue*(1-contribution)] retention/maintenance costs (# of customers *maint costs) $360,000.0 $1,600,000 80,000 Total Variable, Acquisition and Maintenance Costs 2,040,000 Customer contribution after variable, acquisition, and maintenance costs Rate of contribution after variable, acquisition, and maintenance costs Net Present Value of Customer Contribution Joe Cannon 2016 all rights reserved -40,000 -2% $1,251,714 Assumed to operate as a variable cost but not included as a variable cost for contribution margin purposes, constant over Assumed constant for new customers over the time horizon Value for new customers, could gradually increase for retained customers (see below) Number of new customers Assumes constant growth "number" per year Assumed to be the same for all current customers from previous year Assumed constant over the full time horizon Customers are assumed to increase their purchases over time from cross-selling and generally preferring the business -- r Assumed to increase annually at this rate for 5 years then level off Percentage of customers who refer a single customer. Assumed that the referrals will come from customers retained for m Assumes a premium that is constant beginning in third year as a customer -- ie., premium is constant following the third ye $250,000.0 $1,866,240 $410,000.0 $1,845,711 $398,000.0 $1,977,274 $401,200.0 $2,079,746 $2,116,240 $2,255,711 $2,375,274 $2,480,946 $45,000.0 $1,690,330 74,000 $45,000.0 $1,796,228 75,600 $45,000.0 $1,876,040 76,400 $45,000.0 $1,946,738 77,168 1,809,330 1,916,828 1,997,440 2,068,906 306,910 15% 338,884 15% 377,834 16% 412,040 17% argin purposes, constant over the time horizon ally preferring the business -- rate does not change over time from customers retained for more than one year. These customers are added without acquisition costs. constant following the third year a customer is retained CREATING CUSTOMER EQUITY SPREADSHEET AND MODEL Variables in the Model Which May Be Changed 1. Acquisition costs per customer 2. Average revenues from a new customer in first year 3. Contribution margin 4. Number of new customers in Year 1 5. Number of new customers added due to marketing efforts 6. Customer retention rate 7. Retention/maintenance costs for current customers 8. Annual revenue growth from customers 9. Cost savings (change in contribution) for retained customers 10. Referrals from current customers 11. Price premium from current customers 12. Discount rate for present value calculation Customer Equity: NPV of Customer Contribution $100 $250 50% 1000 200 60% $25 20% 10% 40% 0% 5% $497,799 Pro Forma Income Statement Revenues from new customers from current customers $250,000.0 Total Revenues $250,000 Costs acquisition costs (not part of contribution) variable costs of sales [ie., revenue*(1-contribution)] retention/maintenance costs (# of customers *maint costs) $100,000.0 $125,000 25,000 Total Variable, Acquisition and Maintenance Costs 250,000 Customer contribution after variable, acquisition, and maintenance costs Rate of contribution after variable, acquisition, and maintenance costs Net Present Value of Customer Contribution Joe Cannon 2016 all rights reserved 0 0% $497,799 Assumed to operate as a variable cost but not included as a variable cost for contribution margin purposes, constant over Assumed constant for new customers over the time horizon Value for new customers, could gradually increase for retained customers (see below) Number of new customers Assumes constant growth "number" per year Assumed to be the same for all current customers from previous year Assumed constant over the full time horizon Customers are assumed to increase their purchases over time from cross-selling and generally preferring the business -- r Assumed to increase annually at this rate for 5 years then level off Percentage of customers who refer a single customer. Assumed that the referrals will come from customers retained for m Assumes a premium that is constant beginning in third year as a customer -- ie., premium is constant following the third ye $50,000.0 $216,000 $110,000.0 $198,720 $98,000.0 $238,118 $105,200.0 $256,117 $266,000 $308,720 $336,118 $361,317 $20,000.0 $116,400 20,000 $20,000.0 $129,936 23,000 $20,000.0 $128,542 23,600 $20,000.0 $130,059 24,680 156,400 172,936 172,142 174,739 109,600 41% 135,784 44% 163,976 49% 186,579 52% argin purposes, constant over the time horizon ally preferring the business -- rate does not change over time from customers retained for more than one year. These customers are added without acquisition costs. constant following the third year a customer is retained CREATING CUSTOMER EQUITY SPREADSHEET AND MODEL Variables in the Model Which May Be Changed 1. Acquisition costs per customer 2. Average revenues from a new customer in first year 3. Contribution margin 4. Number of new customers in Year 1 5. Number of new customers added due to marketing efforts 6. Customer retention rate 7. Retention/maintenance costs for current customers 8. Annual revenue growth from customers 9. Cost savings (change in contribution) for retained customers 10. Referrals from current customers 11. Price premium from current customers 12. Discount rate for present value calculation Customer Equity: NPV of Customer Contribution $75 $150 40% 1000 3000 60% $10 20% 1% 20% 0% 3% $320,910 Pro Forma Income Statement Revenues from new customers from current customers $150,000.0 Total Revenues $150,000 Costs acquisition costs (not part of contribution) variable costs of sales [ie., revenue*(1-contribution)] retention/maintenance costs (# of customers *maint costs) $75,000.0 $90,000 10,000 Total Variable, Acquisition and Maintenance Costs 175,000 Customer contribution after variable, acquisition, and maintenance costs Rate of contribution after variable, acquisition, and maintenance costs Net Present Value of Customer Contribution Joe Cannon 2016 all rights reserved -25,000 -17% $320,910 Assumed to operate as a variable cost but not included as a variable cost for contribution margin purposes, constant over Assumed constant for new customers over the time horizon Value for new customers, could gradually increase for retained customers (see below) Number of new customers Assumes constant growth "number" per year Assumed to be the same for all current customers from previous year Assumed constant over the full time horizon Customers are assumed to increase their purchases over time from cross-selling and generally preferring the business -- r Assumed to increase annually at this rate for 5 years then level off Percentage of customers who refer a single customer. Assumed that the referrals will come from customers retained for m Assumes a premium that is constant beginning in third year as a customer -- ie., premium is constant following the third ye $450,000.0 $129,600 $468,000.0 $482,112 $514,800.0 $751,473 $545,040.0 $985,848 $579,600 $950,112 $1,266,273 $1,530,888 $225,000.0 $400,464 36,000 $225,000.0 $620,473 52,800 $225,000.0 $809,882 66,000 $225,000.0 $965,685 75,936 661,464 898,273 1,100,882 1,266,621 -81,864 -14% 51,839 5% 165,391 13% 264,266 17% argin purposes, constant over the time horizon ally preferring the business -- rate does not change over time from customers retained for more than one year. These customers are added without acquisition costs. constant following the third year a customer is retained