Fraud Risk Management Strategy

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	P	Q
1	Example Risk Management Strategy
2	Click File => Make a copy or File => Download as Excel spreadsheet
3
4	Product	% Fraud sales	Avg. sale	Margin	Fraud to Margin	Last 30 day volume	Total fraud	Fraud to margin quartile	Sales # 0-45 days	Sales € 0-45 days	Sales # 45-90 days	Sales € 45-90 days	Sales # 90-365 days	Sales € 90-365 days	Fraud to margin quartiles
5	iPhone 12	0.035	€850.00	10.0%	35.0%	3,400	€101,150.00	4	5100	€4,335,000	5000	€4,250,000	25000	€21,250,000	0%	1
6	iPhone 11	0.005	€750.00	20.0%	2.5%	180	€675.00	2	270	€202,500	300	€225,000	788	€591,000	2.25%	2
7	iPhone X	0.05	€300.00	25.0%	20.0%	500	€7,500.00	3	750	€225,000	300	€90,000	1200	€360,000	2.78%	3
8	Samsung ActiveTab 3	0.005	€500.00	18.0%	2.8%	150	€375.00	3	225	€112,500	400	€200,000	1700	€850,000	27.50%	4
9	iPad Mini 5	0.001	€400.00	7.0%	1.4%	450	€180.00	1	675	€270,000	740	€296,000	3500	€1,400,000
10	iPad Pro 11	0.08	€900.00	7.0%	114.3%	25	€1,800.00	4	37.5	€33,750	90	€81,000	150	€135,000
11	Lenovo Yoga 2.8	0.003	€285.00	15.0%	2.0%	50	€42.75	1	75	€21,375	150	€42,750	700	€199,500
12
13
14	Products per category
15	COUNTA of Product	Grouped Last 30 day volume
16	Grouped Fraud to Margin	0 - 249	250 - 499	500 - 749	> 1,000	Grand Total
17	0.0% - 20.0%	3	1			4
18	20.0% - 40.0%			1	1	2
19	> 100.0%	1				1
20	Grand Total	4	1	1	1	7
21	* Change groupings by right clicking on the row or column headers and click edit 'pivot group rule'
22
23
24	Total fraud
25	SUM of Total fraud	Grouped Last 30 day volume
26	Grouped Fraud to Margin	0 - 249	250 - 499	500 - 749	> 1,000	Grand Total
27	0.0% - 20.0%	€1,092.75	€180.00			€1,272.75
28	20.0% - 40.0%			€7,500.00	€101,150.00	€108,650.00
29	> 100.0%	€1,800.00				€1,800.00
30	Grand Total	€2,892.75	€180.00	€7,500.00	€101,150.00	€111,722.75
31	* Change groupings by right clicking on the row or column headers and click edit 'pivot group rule'
32
33
34	Rolling card transaction total sales
35	Fraud rolling risk	0-45 days	45-90 days	90-365 days	Avg per 45 days	Factor	Actions
36	Low	€291,375	€338,750	€1,599,500	€261,736	1.1	Monitor
37	Medium	€225,000	€225,000	€591,000	€96,709	2.3	Escalate
38	High	€303,750	€290,000	€1,210,000	€198,000	1.5	Consider controls
39	Critical	€4,368,750	€4,331,000	€21,385,000	€3,499,364	1.2	Escalate
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100