{"text_format":"3","workbook":"xlsx","n_sheets":2,"sheets":["Start","Customer Profitability"],"not_rendered":{"charts":1},"read":"whole","date_system":"1900"}

{"sheet":"Start","index":0,"n_rows":7,"n_cols":2,"rows_kept":7}
[null,"ABOUT THIS TEMPLATE"]
[null,"Use this Customer Profitability Analysis to track Customer Activity, Costs of Sales and other costs."]
[null,"Fill in information such as Company Name, Date and details in tables."]
[null,"Values in cells containing formulas and Summary Metrics table are auto-calculated and Chart is updated."]
[null,"Note:  "]
[null,"Additional Instructions have been provided in column A in CUSTOMER PROFITABILITY worksheet. This text has been intentionally hidden. To remove text, select column A, then select DELETE. To unhide text, select column A, then change font colour."]
[null,"To learn more about tables in this worksheet, press SHIFT and then F10 within a table, select the TABLE option and then select ALTERNATIVE TEXT."]

{"sheet":"Customer Profitability","index":1,"n_rows":37,"n_cols":6,"rows_kept":37,"merged":[[36,1,36,5]]}
["Create Customer Profitability Analysis in this worksheet. Helpful instructions on how to use this worksheet are in cells in this column. Arrow down to get started."]
["Enter Company Name in cell to the right.","Company Name"]
["Title of this worksheet is in cell to the right.","Customer Profitability Analysis"]
["Enter Date in cell to the right.","Date"]
["Tip is in cell to the right.","Grey cells will be calculated for you. You do not need to enter anything into them."]
["Enter details in Customer Activity table starting in cell to the right. Next instruction is in cell A12.","Customer Activity:","Segment Name 1","Segment Name 2","Segment Name 3","Overall"]
[null,"Number of active customers – beginning of period",5,8,8,{"$f":"SUM(C7:E7)","v":21}]
[null,"Number of customers added",2,4,4,{"$f":"SUM(C8:E8)","v":10}]
[null,"Number of customers lost/terminated",{"v":-1,"d":"-1 "},{"v":-2,"d":"-2 "},{"v":-2,"d":"-2 "},{"$f":"SUM(C9:E9)","v":{"v":-5,"d":"-5 "}}]
[null,"Number of active customers – end of period",{"$f":"SUBTOTAL(109,CustomerActivity[Segment Name 1])","v":6},{"$f":"SUBTOTAL(109,CustomerActivity[Segment Name 2])","v":10},{"$f":"SUBTOTAL(109,CustomerActivity[Segment Name 3])","v":10},{"$f":"SUBTOTAL(109,CustomerActivity[Overall])","v":26}]
[]
["Enter details in Profitability Analysis table starting in cell to the right. Next instruction is in cell A16.","Profitability Analysis:","Segment Name 1","Segment Name 2","Segment Name 3","Overall"]
[null,"Revenue per segment",{"v":1500000,"d":"£1,500,000.00"},{"v":1800000,"d":"£1,800,000.00"},{"v":2500000,"d":"£2,500,000.00"},{"$f":"SUM(ProfitabilityAnalysis[[#This Row],[Segment Name 1]:[Segment Name 3]])","v":{"v":5800000,"d":"£5,800,000.00"}}]
[null,"Weighting",{"$f":"+C13/$F$13","v":{"v":0.25862068965517243,"d":"26%"}},{"$f":"+D13/$F$13","v":{"v":0.3103448275862069,"d":"31%"}},{"$f":"+E13/$F$13","v":{"v":0.43103448275862066,"d":"43%"}},{"$f":"SUM(ProfitabilityAnalysis[[#This Row],[Segment Name 1]:[Segment Name 3]])","v":{"v":1,"d":"100%"}}]
[]
["Enter details in Cost of Sales table starting in cell to the right. Next instruction is in cell A23.","Cost of sales:","Segment Name 1","Segment Name 2","Segment Name 3","Overall"]
[null,"Ongoing service and support costs",{"v":1000000,"d":"£1,000,000.00"},{"v":1400000,"d":"£1,400,000.00"},{"v":1400000,"d":"£1,400,000.00"},{"$f":"SUM(C17:E17)","v":{"v":3800000,"d":"£3,800,000.00"}}]
[null,"Other direct customer costs",{"v":200000,"d":"£200,000.00"},{"v":100000,"d":"£100,000.00"},{"v":100000,"d":"£100,000.00"},{"$f":"SUM(C18:E18)","v":{"v":400000,"d":"£400,000.00"}}]
[null,"Total cost of sales",{"$f":"SUM(C17:C18)","v":{"v":1200000,"d":"£1,200,000.00"}},{"$f":"SUM(D17:D18)","v":{"v":1500000,"d":"£1,500,000.00"}},{"$f":"SUM(E17:E18)","v":{"v":1500000,"d":"£1,500,000.00"}},{"$f":"SUM(F17:F18)","v":{"v":4200000,"d":"£4,200,000.00"}}]
[null,"Gross margin",{"$f":"+C13-C19","v":{"v":300000,"d":"£300,000.00"}},{"$f":"+D13-D19","v":{"v":300000,"d":"£300,000.00"}},{"$f":"+E13-E19","v":{"v":1000000,"d":"£1,000,000.00"}},{"$f":"+F13-F19","v":{"v":1600000,"d":"£1,600,000.00"}}]
[null,"Weighting",{"$f":"MAX(0, MIN(1,C20/$F$20))","v":{"v":0.1875,"d":"19%"}},{"$f":"MAX(0, MIN(1,D20/$F$20))","v":{"v":0.1875,"d":"19%"}},{"$f":"MAX(0, MIN(1,E20/$F$20))","v":{"v":0.625,"d":"63%"}},{"$f":"SUM(C21:E21)","v":{"v":1,"d":"100%"}}]
[]
["Enter details in Other Costs table starting in cell to the right. Next instruction is in cell A31.","Other costs:","Segment Name 1","Segment Name 2","Segment Name 3","Overall"]
[null,"Customer acquisition",{"v":105000,"d":"£105,000.00"},{"v":120000,"d":"£120,000.00"},{"v":235000,"d":"£235,000.00"},{"$f":"SUM(OtherCosts[[#This Row],[Segment Name 1]:[Segment Name 3]])","v":{"v":460000,"d":"£460,000.00"}}]
[null,"Customer marketing",{"v":150000,"d":"£150,000.00"},{"v":125000,"d":"£125,000.00"},{"v":275000,"d":"£275,000.00"},{"$f":"SUM(C25:E25)","v":{"v":550000,"d":"£550,000.00"}}]
[null,"Customer termination",{"v":80000,"d":"£80,000.00"},{"v":190000,"d":"£190,000.00"},{"v":140000,"d":"£140,000.00"},{"$f":"SUM(C26:E26)","v":{"v":410000,"d":"£410,000.00"}}]
[null,"Total other customer costs",{"$f":"SUM(C24:C26)","v":{"v":335000,"d":"£335,000.00"}},{"$f":"SUM(D24:D26)","v":{"v":435000,"d":"£435,000.00"}},{"$f":"SUM(E24:E26)","v":{"v":650000,"d":"£650,000.00"}},{"$f":"SUM(F24:F26)","v":{"v":1420000,"d":"£1,420,000.00"}}]
[null,"Customer profit by segment",{"$f":"+C20-C27","v":{"v":-35000,"d":"-£35,000.00"}},{"$f":"+D20-D27","v":{"v":-135000,"d":"-£135,000.00"}},{"$f":"+E20-E27","v":{"v":350000,"d":"£350,000.00"}},{"$f":"SUM(C28:E28)","v":{"v":180000,"d":"£180,000.00"}}]
[null,"Weighting",{"$f":"MAX(0,MIN(1, C28/$F$28))","v":{"v":0,"d":"0%"}},{"$f":"MAX(0,MIN(1, D28/$F$28))","v":{"v":0,"d":"0%"}},{"$f":"MAX(0,MIN(1, E28/$F$28))","v":{"v":1,"d":"100%"}},{"$f":"SUM(C29:E29)","v":{"v":1,"d":"100%"}}]
[]
["Summary Metrics table starting in cell to the right is automatically updated. Next instruction is in cell A37.","Summary Metrics:","Segment Name 1","Segment Name 2","Segment Name 3","Trend"]
[null,"Average cost per acquired customer",{"$f":"+C24/C8","v":{"v":52500,"d":"£52,500.00"}},{"$f":"+D24/D8","v":{"v":30000,"d":"£30,000.00"}},{"$f":"+E24/E8","v":{"v":58750,"d":"£58,750.00"}}]
[null,"Average cost per terminated customer",{"$f":"-C26/C9","v":{"v":80000,"d":"£80,000.00"}},{"$f":"-D26/D9","v":{"v":95000,"d":"£95,000.00"}},{"$f":"-E26/E9","v":{"v":70000,"d":"£70,000.00"}}]
[null,"Average marketing cost per active customer",{"$f":"+C26/CustomerActivity[[#Totals],[Segment Name 1]]","v":{"v":13333.333333333334,"d":"£13,333.33"}},{"$f":"+D25/CustomerActivity[[#Totals],[Segment Name 2]]","v":{"v":12500,"d":"£12,500.00"}},{"$f":"+E25/CustomerActivity[[#Totals],[Segment Name 3]]","v":{"v":27500,"d":"£27,500.00"}}]
[null,"Average profit (loss) per customer",{"$f":"+C29/CustomerActivity[[#Totals],[Segment Name 1]]","v":{"v":0,"d":"£0.00"}},{"$f":"+D28/CustomerActivity[[#Totals],[Segment Name 2]]","v":{"v":-13500,"d":"-£13,500.00"}},{"$f":"+E28/CustomerActivity[[#Totals],[Segment Name 3]]","v":{"v":35000,"d":"£35,000.00"}}]
[]
["Clustered column chart showing Summary Metrics per Customer Segment is in cell to the right.","Clustered column chart showing Summary Metrics per Customer Segment is in this cell."]