Spreadsheet: 703832d1605190058-matrix-multiplication-customer-forecast
Sheet: Forum

 |  | Conversion Rate |  |  |  |  |  | Conversion Rate: function of the historical behaviour of customer. Since first seen, 42% are being converted in the first year, 24% between the first and second year, etc.
 |  | Year | % | [NAME] | Cumulative
 |  | 0 | 0.4183542802940479 | 0.3743
 |  |  | 0.2352383210813375
 |  |  | 0.132795826416884
 |  |  | 0.08536874555371117
 |  |  | 0.06943324638368509
 |  |  | 0.03300924828076832
 |  |  | 0.01479724922930994 |  |  |  |  | New: number of new customers. Distinct count of customers firstly seen the corresponding year.
 |  |  | 0.006829499644296894 |  |  |  |  | Converted Join: number of customers that have been first seen that year, that have been converted in the future.
 |  |  | 0.004173583115959213 |  |  |  |  | Converted in Y: number of customers converted in that given year. They are coming from past and current year. E.g. for a total of 592 converted customer the year 2000, 12 are coming from t minus 3.
 |  | Year | New | Lost | Converted Join | Converted in Y |  |  | t0 | t - 1 | t - 2 | t - 3 | t - 4 | t - 5 | t - 6 | t - 7 | t - 8 | t - 9 | t - 10 | t - 11 | t - 12 | t - 13 | t - 14 | t - 15
 |  | 1999 | 1020.236 | 384.564 | 621.624 | 575.968 |  | 1999 | 403.88 | 142.236 | 29.852
 |  | 2000 | 1009.7 | 331.884 | 679.572 | 591.772 |  | 2000 | 412.66 | 105.36 | 61.46 | 12.292
 |  | 2001 | 1048.332 | 393.344 | 632.16 | 597.04 |  | 2001 | 398.612 | 96.58 | 47.412 | 40.388 | 14.048
 |  | 2002 | 1004.432 | 372.272 | 628.648 | 628.648 |  | 2002 | 400.368 | 91.312 | 61.46 | 49.168 | 24.584 | 1.756
 |  | 2003 | 1020.236 | 352.956 | 658.5 | 630.404 |  | 2003 | 395.1 | 96.58 | 54.436 | 36.876 | 35.12 | 10.536 | 1.756
 |  | 2004 | 1136.132 | 402.124 | 755.08 | 777.908 |  | 2004 | 516.264 | 107.116 | 63.216 | 45.656 | 26.34 | 15.804 | 1.756 | 1.756
 |  | 2005 | 993.896 | 416.172 | 593.528 | 577.724 |  | 2005 | 367.004 | 84.288 | 54.436 | 35.12 | 15.804 | 12.292 | 7.024 | 1.756
 |  | 2006 | 960.532 | 326.616 | 639.184 | 626.892 |  | 2006 | 368.76 | 110.628 | 59.704 | 36.876 | 22.828 | 14.048 | 8.78 | 3.512 | 1.756
 |  | 2007 | 853.416 | 377.54 | 498.704 | 609.332 |  | 2007 | 393.344 | 79.02 | 45.656 | 31.608 | 29.852 | 15.804 | 7.024 | 5.268 |  | 1.756
 |  | 2008 | 948.24 | 384.564 | 547.872 | 595.284 |  | 2008 | 354.712 | 80.776 | 61.46 | 29.852 | 38.632 | 10.536 | 10.536 | 5.268 | 3.512
 |  | 2009 | 883.268 | 314.324 | 558.408 | 554.896 |  | 2009 | 335.396 | 64.972 | 45.656 | 33.364 | 36.876 | 17.56 | 7.024 | 1.756 | 7.024 | 3.512 |  | 1.756
 |  | 2010 | 951.752 | 338.908 | 590.016 | 595.284 |  | 2010 | 398.612 | 82.532 | 42.144 | 24.584 | 26.34 | 8.78 | 3.512 | 3.512 |  | 1.756 |  | 1.756 | 1.756
 |  | 2011 | 964.044 | 335.396 | 539.092 | 553.14 |  | 2011 | 333.64 | 96.58 | 45.656 | 29.852 | 17.56 | 15.804 | 3.512 | 3.512 | 1.756 | 1.756 |  |  |  | 3.512
 |  | 2012 | 835.856 | 352.956 | 384.564 | 535.58 |  | 2012 | 344.176 | 80.776 | 38.632 | 15.804 | 21.072 | 22.828 | 3.512 | 7.024 |  |  |  |  |  |  | 1.756
 |  | 2013 | 923.656 | 374.028 | 354.712 | 518.02 |  | 2013 | 295.008 | 75.508 | 57.948 | 29.852 | 28.096 | 7.024 | 8.78 | 5.268 | 1.756 | 3.512 | 1.756 | 1.756 |  |  |  | 1.756
 |  | 2014 | 1023.748 | 503.972 | 347.688 | 537.336 |  | 2014 | 340.664 | 71.996 | 36.876 | 36.876 | 26.34 | 10.536 | 5.268 |  | 3.512 | 1.756 |  |  | 3.512
 |  | Converted in Y is fed by the past hence is stable while going towards the end of available data. Converted Join start decreasing according to the Conversion Rate function.
 |  | Objective: given the conversion rate function and given the amount of new customers, forecast how will converted customer affected in the future years? |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | I still don't get well arrays in excel. I may need to use this format for a monthly structure.
 |  |  | New A | Lost F | Converted Join F | Converted in Y A |  |  |  |  | 8
 |  |  |  |  |  |  |  |  |  | 2006
 |  |  |  |  |  |  |  |  |  | 2007
 |  |  |  |  |  |  |  |  |  | 2008 |  |  |  |  |  |  |  |  |  |  | This is an attempt based on an old FlameRetired suggestion.
 |  |  |  |  |  |  |  |  |  | 2009
 |  |  |  |  |  |  |  |  |  | 2010
 |  |  |  |  |  |  |  |  |  | 2011
 |  |  |  |  |  |  |  |  |  | 2012
 |  |  |  |  |  |  |  |  |  | 2013
0 |  |  |  |  |  |  |  |  |  | 2014
 |  |  | 1141
 |  |  | 1110
 |  |  | 1135
 |  |  | 1004
 |  |  | 1189
 |  |  | 1072
 |  |  |  |  |  |  |  |  |  |  | 8
 |  |  |  |  |  |  |  |  |  | 2006
 |  |  |  |  |  |  |  |  |  | 2007
 |  |  |  |  |  |  |  |  |  | 2008
 |  |  |  |  |  |  |  |  |  | 2009
 |  |  |  |  |  |  |  |  |  | 2010
 |  |  |  |  |  |  |  |  |  | 2011
 |  |  |  |  |  |  |  |  |  | 2012
 |  |  |  |  |  |  |  |  |  | 2013
 |  |  |  |  |  |  |  |  |  | 2014
 |  |  |  |  |  |  |  |  |  |  | 8
 |  |  |  |  |  |  |  |  |  | 2006
 |  |  |  |  |  |  |  |  |  | 2007
 |  |  |  |  |  |  |  |  |  | 2008
 |  |  |  |  |  |  |  |  |  | 2009
 |  |  |  |  |  |  |  |  |  | 2010
 |  |  |  |  |  |  |  |  |  | 2011
 |  |  |  |  |  |  |  |  |  | 2012
 |  |  |  |  |  |  |  |  |  | 2013
 |  |  |  |  |  |  |  |  |  | 2014