ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
OOS Upside Calculator for FS.OSA
2
3
OSA (a product to monitor OOS rate and improve business processes by helping workers do the right data-driven decisions and spend their time more effectively)NowFutureEffectChange cells colored in yellow
4
OOS rate8.3%7.0%-1.3%
5
Number of stores 2--
6
Number of SKUs per store4,000--
7
Profit margin20.0%--
8
Average transaction size per store visit$100$32-
9
Wage for a store worker with amortization for office, benefits, etc.$18--
10
Weekly revenue per store (sales volume)$10,000$10,073$73
11
12
Firm's OOS-related annual loss (lost sales + labor cost)-$10,449-$8,812$1,637
13
Annual lost revenue due to OOS-$43,160-$36,400$6,760
14
Annual labor cost due to OOS (store workers handling unplanned OOS questions)-$777-$655$122
15
Annual lost revenue due to OOS customer churn to other chains-$5,200-$4,386$814
16
17
OSA total cost (evaluation + training + license) based on a 12 months subscription$0-$14,412-$14,412
18
OSA evaluation cost (given that a firm already monitores OSA and has historical data)$0-$11,700-$11,700
19
OSA training cost (2h per person using the product)$0-$1,920-$1,920
20
OSA license fee (SaaS monthly subscription)$0-$792.00-$792.00
21
BOTTOM LINE (total profit for OSA project per chain)-$12,775
22
23
OOS Upside Details
24
25
TOTAL ANNUAL OOS UPSIDE, $$1,637Source of valuable data:
http://www.fmi.org/research-resources/supermarket-facts
26
http://couponsinthenews.com/2014/01/23/guess-how-many-items-the-average-grocery-shopper-buys-in-a-year/
27
TOTAL ANNUAL OOS UPSIDE FROM OSA, $$409
http://www.nacds.org/pdfs/membership/out_of_stock.pdf
28
TOTAL ANNUAL OOS UPSIDE FROM REPL, $$769
http://yourbusiness.azcentral.com/profit-margin-supermarket-17711.html
29
TOTAL ANNUAL OOS UPSIDE FROM One Supply Chain (1SC), $$458
30
31
TOTAL ANNUAL OOS LOSS, $$10,449
32
Annual sales volume per chain, $$1,040,000Business marginality20.0%
33
Weekly sales volume per store, $$10,000
34
Weeks per year52SKU volume power law
count, out of all SKUs
revenue, out of all SKUs
35
Number of stores in a chain2SKU, high demand (head)100.00%100.00%
36
Number of SKUs4,000SKU, low demand (tail)0.00%0.00%
37
Average transaction size per shopper, $$100
38
Current OOS, probability8.3%decrease TO7.0%
39
Sales / OOS correlation (impact ratio, substitution rate)0.5
40
Shelf-based problems, probability0.25
41
Store-based problems (replenishment and logistics), probability0.47
42
Upstream supply chain problems, probability0.28
43
44
Annual lost direct sales/revenue (not taking into account churn etc.), $$43,160
45
Annual lost revenue per store, $$21,580
46
Weekly lost revenue for a chain, $$830
47
Weekly lost revenue for a store, $$415
48
Daily lost revenue for a store, $$59
49
Hourly lost revenue for a store, $$3
~Essentially, they could hire a senior McKinsey's consultant for each store to work 24/7 during the entire year.
50
51
Annual cost per chain for OOS QA to shoppers, $$777
52
Annual cost per store for OOS QA to shoppers, $$388
53
Weekly cost per chain for OOS QA to shoppers, $$15
54
Weekly cost per store for OOS QA to shoppers, $$7
55
Number of shopping visits per week per store100
56
Number of items per visit10
57
Shoppers with OOS, probability0.83
58
Ask for help given OOS, probability0.1
59
Time to handle a shopper question, hour0.05
60
Wage for customer service employee (regular store worker), $ per hour$18.00
61
62
Annually OOS affected shoppers8,632
63
Annually OOS affected shoppers per store4,316
64
Weekly OOS affected shoppers per chain166
65
Weekly OOS affected shoppers per store83
66
67
Annual lost sales/revenue due to shoppers churn caused by OOS$5,200
68
Annual sales per client$7,800
69
Annual churn0.01
70
Number of clients to churn per year per store1
71
Shopping trips per year78
72
Switch to a different store if experienced OOS, count3
73
Number of unique items a shopper buys per year260
74
Number of shopper trips per week1.5
75
Probability to have a specific product OOS per shopping trip0.003192307692
76
Probability to face OOS 3 TIMES for a specific product in less than a year0.002305
77
Probability to face OOS 3 TIMES for ANY product in less than a yearNegMultDistr needed
78
79
OSA Adoption Cost
80
81
Employee group$/hour
82
Wage for analyst/PM to prepare a pilot $120.00
83
Wage for evaluation personel (merchandising, restocking, warehouse, etc.) $18.00
84
Wage for execs$300.00
85
Wage for store manager$60.00
86
87
Pilot Cost (from 1st contact to the contract signed)Timeline, business daysUnit, hoursCount, people
SUBTOTAL, h
SUBTOTAL, $
88
Call10.510.5$60
89
Meeting OSA.overview (key contact ~ champion)2112$240
90
Meeting OSA project management (key contact ~ champion)6116$720
91
Pilot preparation 44232$3,840
92
Pilot results data analysis1818$960
93
Contract and legal paperwork1224$480
94
Pilot results evaluation (OPTIONAL)7122168$3,024
95
Pilot exec meetings --- internal 2124$1,200
96
Pilot exec meetings --- external 2124$1,200
97
Pilot payment$3,000
98
SUBTOTAL15$11,700
99
SUBTOTAL (WITH OPTIONAL)26$14,724
100