{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Business Impact","Data Validation"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":1,"defined_names":12,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Business Impact","index":0,"n_rows":49,"n_cols":5,"rows_kept":49,"cut":false,"hidden":false,"merged":[[48,1,49,4],[9,1,9,2],[1,1,6,4],[8,1,8,2],[26,1,26,4],[16,1,16,4],[21,1,21,4]]}
[]
[null,"Business Impact\nThis simple ROI calculation* will provide a high-level business case for deploying digital solutions to combat counterfeiting and diversion. There are three basic questions that can be asked to determine the applicability of an anti-counterfeiting / anti-diversion solution. They are:\n  - How much we expect to reduce the amount of counterfeited products?\t\t\t\n  - How much we expect to reduce the number of diverted products?\t\t\t\n  - How much can the reduction of counterfeited and diverted products increase market share?\n    (i.e., improvement on how brand is perceived/ valued in the marketplace)"]
[]
[]
[]
[]
[]
[]
[null,"All values in ORANGE cells must be entered."]
[null,"Product Profile\nThis exercise is intended for scoping out a single product."]
[null,"Current Overall Production "]
[null,"How many units are produced and sold each year?",600000]
[null,"What is the average selling price for this product?",25]
[null,"Planned Increase","Year 1","Year 2","Year 3"]
[null,"How much will production increase?",0,0.04,0.08]
[]
[null,"Expected Business Impact\nIn this section apply an estimated % to the questions.  These answers are used to build a basic ROI model."]
[null,"Current counterfeit and diversion rates","Year 1","Year 2","Year 3"]
[null,"Estimated counterfeit rate of product",7.0000000000000007E-2,0.05,0.02]
[null,"Estimated diversion rate of product",0.1,0.05,0.05]
[null,"Estimated % of lost sales from the diverted products; \ni.e., from price discounting or channel disruption",0.35,0.25,0.2]
[null,"Reduction in counterfeit/ diversion rates and increased market share AFTER solutions are deployed"]
[null,"Estimated reduction in counterfeiting ",0.2,0.5,0.75]
[null,"Estimated reduction in diversion ",0.1,0.35,0.75]
[null,"Estimated increase in market share ",0,0.02,0.03]
[]
[null,"Solution Cost\nThis includes yearly software subscription and implementation cost of your digital solution."]
[null,null,"Year 1","Year 2","Year 3"]
[null,"Anti-Counterfeiting / Anti-Diversion Solution Cost",150000,100000,100000]
[]
[null,"Product Volumes ","Year 1","Year 2","Year 3"]
[null,"Annual Unit Volume",{"$f":"C12*(1+C15)","v":600000},{"$f":"C12*(1+D15)","v":624000},{"$f":"C12*(1+E15)","v":648000}]
[null,"Annual Sales from Product",{"$f":"(C13*C12)*(1+C15)","v":15000000},{"$f":"C33*(1+D15)","v":15600000},{"$f":"D33*(1+E15)","v":16848000}]
[]
[null,"Estimated Lost Revenue","Year 1","Year 2","Year 3"]
[null,"Estimated Lost Revenue Due to Counterfeiting",{"$f":"(C33*C19)","v":1050000},{"$f":"(D33*D19)","v":780000},{"$f":"(E33*E19)","v":336960}]
[null,"Estimated Lost Revenue Due to Diversion",{"$f":"(C33*C20)*C21","v":525000},{"$f":"(D33*D20)*D21","v":195000},{"$f":"(E33*E20)*E21","v":168480}]
[null,"Estimated Total Lost Revenue",{"$f":"SUM(C36:C37)","v":1575000},{"$f":"SUM(D36:D37)","v":975000},{"$f":"SUM(E36:E37)","v":505440}]
[]
[null,"Yearly Savings/ ROI","Year 1","Year 2","Year 3"]
[null,"Savings from Reduction in Counterfeiting",{"$f":"C36*C23","v":210000},390000,252720]
[null,"Savings from Reduction in Diversion",52500,68250,126360]
[null,"Increased Market Share from Improved Customer Loyalty  ",{"$f":"C33*C25","v":0},{"$f":"D33*D25","v":312000},{"$f":"E33*E25","v":505440}]
[null,"Anti-Counterfeiting / Anti-Diversion Solution Cost",{"$f":"C29-(C29*2)","v":-150000},{"$f":"D29-(D29*2)","v":-100000},{"$f":"E29-(E29*2)","v":-100000}]
[null,"Annual Business Impact",{"$f":"SUM(C41:C44)","v":112500},{"$f":"SUM(D41:D44)","v":670250},{"$f":"SUM(E41:E44)","v":784520}]
[]
[null,"Total 3 Year Impact",{"$f":"SUM(C45:E45)","v":1567270}]
[]
[null,"*This is ROI calculator is intended to provide high-level scenarios and analysis. All calculations are hypothetical, and results depend on many variables which are not captured within this analysis. "]

{"sheet":"Data Validation","index":1,"n_rows":5,"n_cols":13,"rows_kept":5,"cut":false,"hidden":true,"merged":[]}
["foreground",null,"light on dark",null,"fuzzy","uniform","planar","glossy","deformable",null,1,null,"Yes"]
["background",null,"dark on light",null,"sharp","mottled","curved","matte","non-deformable",null,2,null,"No"]
[null,null,null,null,null,null,null,null,null,null,3]
[null,null,null,null,null,null,null,null,null,null,4]
[null,null,null,null,null,null,null,null,null,null,5]