i cannot make it much clearer unless u want column by column...

zoom in to see more detail...

Question Statement: A firm acquires 40 new customers in year 1. Their purchase probability is 50% in year 1 and 40% in year 2. The Account service costs per customer for years 1 and 2 are respectively $20 and $10. The customer expected purchases for years 1 and 2 are $80 and $60 respectively. The firm's profit margin per unit of sale is $40. Calculate the lifetime value of 40 customers over a two-year period. Show your calculations and explain your results. Hint: Follow exhibit 5-5 page 134 to arrive at your answer. Exhibit 5-5 ScheduTrax Customer Lifetime Value Analysis Expected customer migrations following acquisition Yrs. since the Purchase Expected s Last Purchase Probability Purchases Account Service Acquisition 1st Yeafter Period Acquisition 2nd Yr. after Acquisition Costs 3rd Yr. after Acquisition 4th Yr. after Acquisition 0,60 590 $20 100 60.0 312 27.4 customers 360 16.0 52.0 9.6 83 4.8 29 45.6 19 40.5 0.40 510 40.0 24.0 20.8 182 0.20 560 $50 550 24.0 0.10 52 192 12.5 11.5 173 Profit forecasts Acg +1 160 buyers (590) 8 gross profit-1100 accts x $20 service/act) Acq-2 136 buyers (590) + 16 buyers (560) 8 profit-160 acets x 520 + 40 acets x 510 - Ag +3 131.2 buyers (590) 9.6 buyers (560)+48 buyers (850) 8 gr profit - 152 accts x 520 +24 X 510 + 24 x 54 Aca -4 127.4 buyers (590) 8 3 buyers (560) 29 buyers (550)+1.9 buyers (550) 145.6 x 520 +20.8x 510+144 54 19 2 x 52) 52.320 51.760 51,523 31,345 Discounting profits Ag +1 Acq-2 Acq3 Acq4 LTM100 customers NPV O 10% $2,109 $1,455 $1,144 $. 919 55,627 NPV O 15% 52 017 $1,331 51,002 5769 55.119 NPV O 2096 51.933 $1.222 5881 $ 649 $4.686 Expected customer migrations following acquisition Yrs. since the Purchase Expected s Last Purchase Probability Purchases Account Service Acquisition 1st Yr. after Costs Period Acquisition 2nd Yr. after Acquisition 3rd Yr. after Acquisition 4th Yr. after Acquisition o 0.60 $90 $20 60.0 312 274 100 customers 360 16.0 520 9.6 83 29 48 45.6 560 19 40.5 18.2 125 $10 4004 0.40 020 208 240 240 5.4 14.4 550 $50 0.10 52 19.2 115 173 Profit forecasts AQ1 AcQ +2 Acq3 ACQ4 160 buyers ($90) 8 gross profit-1100 accts x 520 service/acct 136 buyers (90) + 16 buyers (560) 8 gr profit-160 accts x 520 + 40 accts 510) 312 buyers ($90) 9 6 buyers (560) + 48 buyers (550) 8 gr profit - 152 accts 520 + 24 X 510.24% 54 - 1274 buyers (390) + 8 3 buyers (560) 2.9 buyers ($50) + 19 buyers (550) 1456520 +208510 - 144x5419.252 52.320 $1,760 51,523 $1,345 NPV O 10% Discounting profits Acg.1 Acq+2 Acq3 Acgy LTV100 customers 52.109 51,455 51,144 $919 55,627 NPVO 15% NPV O 20% 52,017 $1,933 51,331 51.222 51,002 5881 5769 $649 55,119 54,686 Yrs. since the Purchase Expected s Last Purchase Probability Purchases Account Service Acquisition Costs Period 0 0.60 $90 $20 100 - customers 1 0.40 $60 $10 w N- . 0.20 $50 $.4 0.10 $50 $ 2 Expected customer migrations following acquisition Service Acquisition 1st Yr. after Period Acquisition 2nd Yr. after Acquisition 3rd Yr, after Acquisition 4th Yr. after Acquisition 100 60.0 36.0 31.2 27.4 customers 9.6 8.3 16.0 52.0 2.9 4.8 45.6 1.9 40.5 40.04 24.0 20.8 18.2 240 14.4 12.5 19.2 11.5 17.3 Profit forecasts Acq +1 Acq +2 Acq +3 Acq +4 (60 buyers (590) x 8 gross profit - [100 accts