Case: Memphis PD

Part 1 - Descriptive Analytics

Raw Data

Rate
Cases Filed

50%
10

Rate
Cases Filed

43%
7

Rate
Cases Filed

20%
10

Rate
Cases Filed

10%
10

Rate
Cases Filed

60%
5

Rate
Cases Filed

80%
5

Rate
Cases Filed

40%
5

Rate
Cases Filed

40%
5

Rate
Cases Filed

20%
5

Rate
Cases Filed

60%
5

Rate
Cases Filed

80%
5

Rate
Cases Filed

40%
5

Rate
Cases Filed

20%
5

Rate
Cases Filed

20%
5

Rate
Cases Filed

40%
5

Rate
Cases Filed

40%
5

Rate
Cases Filed

40%
5

Rate
Cases Filed

40%
5

Rate
Cases Filed

87%
15

Rate
Cases Filed

70%
10

Rate
Cases Filed

80%
10

Rate
Cases Filed

40%
5

Rate
Cases Filed

40%
5

Rate
Cases Filed

90%
10

Rate
Cases Filed

60%
5

Rate
Cases Filed

20%
5

Rate
Cases Filed

80%
10

Rate
Cases Filed

87%
30

Rate
Cases Filed

50%
10

Rate
Cases Filed

90%
10

Rate
Cases Filed

85%
20

Rate
Cases Filed

80%
10

Rate
Cases Filed

60%
25

Rate
Cases Filed

40%
10

Rate
Cases Filed

80%
10

Rate
Cases Filed

20%
10

Rate
Cases Filed

30%
10

Analytics

Length
Width:

Profiling of Community Members

5

Community orientation
3

Skills and intelligence based hiring

2

Competent station commanders

1

Community peace and order

3

Proper classification of crimes

4

New crime prevention strategies

2

Efficient complaint desk

2

Use Two-Way Radio

1

Hotspot Identification
3

Use of mobile devices

4

Foot patrol
2

Curfew
1

Posting of Wanted Signs

1

GPS Eqquipped mobile cars

2

Use of CCTV
2

Database of criminal records and offenders

2

Finger print database

2

Burglar alarms
13

Fast and new mobile cars

7

More law enforcement tools

8

Criminal Tracking Device

2

Use of K9
2

Safehouse for evidences

9

Efffective Forensic laboratory

3

Competent lab technician

1

Illegal Possession of Firearms (Detection Device)

8

Illegal Drugs Detection Device

26

Prostitution Profiling
5

Murder Alarms
9

Felony Alarms
17

Effective Communication
8

Effective Prosecution
15

Undamaged Evidence
4

Credible Witness
8

Effective Prosecution Team

2

Legal Documents
3

Size Required:

5 x 25

7
74

Community Involvement
8

Hiring
3

Training
9

Manual Reporting of cases

Reporting of crimes
3

10

Automated reporting of crimes

7

Manual monitoring

Monitoring of streets and community

4

Automatic monitoring
8

Quick Response
30

12

Investigation
15

Apprehension
65

Presentation Against Offender

Case Proceeding
12

Case Prosecution
5

17

TARGET

Cases Solved Conviction Rate

192

16.0%

Court Ruling
40

Level
Witdh

Total Arrests Made

1200
0.16 Conviction Rate
1200 Arrests
192 Case Solved

Case: Memphis PD
Descriptive Analytics

Raw Data

Rate
Rate
Cases Filed
Cases Filed
Rate

70%
85%
460
450
90%

1 (initial)
2
3
4
5
6
7
8
9
10

Size Required:

5 x 25

Length
Width:

Profiling of Community Members

7
4.25

0.50
0.80
80.00
0.85
0.60
0.80
0.40
0.60
0.85
0.50
0.70

7
74

Community Involvement
10
10.25

0.60
0.90
0.70
0.60
0.70
0.90
0.70
0.75
0.80
0.90
0.85

30.00
430.00
570.00
630.00
550.00
540.00
400.00
580.00
540.00
270.00
460.00

TARGET

Cases Solved Conviction Rate

826

25.00
500.00
310.00
230.00
360.00
340.00
570.00
290.00
370.00
760.00
450.00

68.8%

0.80
0.90
0.90
0.90
0.80
0.90
0.95
0.70
0.85
0.85
0.90

Level
Witdh

Total Arrests Made

1200
0.16 Conviction Rate
1200 Arrests

SUMMARY OUTPUT
Regression Statistics
Multiple R
R Square
Adjusted R Square
Standard Error
Observations

0.997762707
0.9955304194
0.9899434437
0.0171095741
10

ANOVA
df
Regression
Residual
Total

SS
5 0.260811
4 0.001171
9 0.261982

Coefficients
Standard Error
-0.0051100294 0.083693
-0.0286873085 0.069621
0.121330414 0.072803
0.0007438745 4.97E-005
0.0004339476 5.44E-005
0.0945515146 0.094703

Intercept
Profiling of Community Members %
Community peace and order %
Illegal Drugs Detection Device #
Effective Prosecution #
Credible Witness %

RESIDUAL OUTPUT
Observation
1
2
3
4
5
6
7
8
9
10

Predicted Conviction Rate %

0.1621507015
0.6683553958
0.7005000063
0.6968492675
0.7036022035
0.7154682796
0.7030701981
0.6921534801
0.7101917237
0.7007587439

Residuals
-0.002151
0.023861
-0.002617
0.004151
0.002398
-0.023468
-0.00107
-0.000153
-0.003192
0.002241

MS
F
Significance F
0.0521622299 178.1877114 8.7009720E-005
0.0002927375

t Stat
-0.061056702
-0.4120519666
1.6665503295
14.962525871
7.9790031803
0.9983966991

P-value
0.9542430035
0.7014264231
0.1709320759
0.0001162274
0.0013371947
0.3745898555

Lower 95%
-0.2374795421
-0.2219851049
-0.0808040359
0.0006058412
0.0002829473
-0.1683871451

Upper 95%
0.2272594833
0.164610488
0.3234648638
0.0008819078
0.0005849478
0.3574901743

Lower 95.0%
-0.2374795421
-0.2219851049
-0.0808040359
0.0006058412
0.0002829473
-0.1683871451

Profiling of Community Members % Line Fit Plot

100.0%
Community
peace andConviction
order
Rate %% Line Fit Plot
50.0%

Predicted Conviction

100.0%
Illegal Drugs
Detection
Device # Line Fit
Rate %
Conviction Rate %

Conviction Rate %

0.0%
50.0%
Conviction Rate
0.00 %0.50 1.00

Predicted Conviction
100.0%
Effective
Prosecution
# Lin
Rate %
Conviction Rate %

0.0%
Profiling of Community Members
% 50.0%
100.0%
Conviction Rate
%
0.400.600.801.00
0.0%
500.00
Community peace and order
%50.0%
Conviction Rate
% 1,000.00
0.00
0.0%

Predicted Conviction

Credible Witness
%
Rate %
C
100.0%

500.00
Illegal Drugs Detection Device
# 50.0%
Conviction Rate
% 1,000.00
0.00

Upper 95.0%
0.2272594833
0.164610488
0.3234648638
0.0008819078
0.0005849478
0.3574901743

Pr
Ra

0.0% #
Effective Prosecution
0.60 0.8

Credible Witn

e Fit Plot

r%% Line Fit Plot

ction

onConviction
Device
# Line Fit Plot
Rate %

Predicted Conviction
rosecution
# Line Fit Plot
Rate %
Conviction Rate %
Predicted Conviction

100.0%
edible
Witness
% Line Fit Plot
Rate %
Conviction Rate %

500.00
er
%50.0%
100.0%
%
0 1,000.00
0.0%
500.00
Detection Device
# 50.0%
ction Rate
% 1,000.00
0.00

Predicted Conviction
Rate %
Conviction Rate %

0.0% #
Effective Prosecution
0.60 0.80 1.00
Credible Witness %

Predicted Conviction
Rate %

Case: Memphis PD
Part 2 - Predictive Analytics
Raw Data (X's)

Sample
1 (initial)
2
3
4
5
6
7
8
9
10
Decision

Profiling of
Community
Members %
0.50
0.80
0.80
0.85
0.60
0.80
0.40
0.60
0.85
0.50
0.60

Community Illegal Drugs

Effective
peace and
Detection
Prosecution
order %
Device #
#
0.60
30.00
25.00
0.90
500.00
300.00
0.70
570.00
310.00
0.60
630.00
230.00
0.70
550.00
360.00
0.90
540.00
340.00
0.70
400.00
570.00
0.75
580.00
290.00
0.80
540.00
370.00
0.90
270.00
760.00
0.90
540.00
300.00

\Data \Data Analysis \Regression \OK \Y \X \Labels \New Worksheet Ply \Line Fit Plots \OK

Y
Credible
Witness %
0.80
0.90
0.90
0.90
0.80
0.90
0.95
0.70
0.85
0.85
0.70

Conviction
Rate %
16.0%
69.2%
69.8%
70.1%
70.6%
69.2%
70.2%
69.2%
70.7%
70.3%

ksheet Ply \Line Fit Plots \OK

Prediction
#REF!

Target

68.5%

70.0%

Case: Memphis PD
Part 3 - Prescriptive Analysis (Linear Programming)
Let:
PC
SC

=
=

Number of Petty Crimes per week

Number of Serious Crimes per week

Total Cases Solved (Petty Crimes), Efficiency Rate:

Total Cases Solved (Serious Crimes), Efficiency Rate:

0.1
0.6

Hours
Tasks
PC
1.5
3

Collect Evidences
Cases Investigated

SC
5
24

Objective Function:
Maximize Cases Solved/week T, Effiency

Assigned Hour
per Cases per
Week

168
504

Constraints:
Collect Evidences
Cases Investigated

1.5
3

0.1

PC
PC

+
+

GRAPHICAL REPRESENTATION
Intercepts:
Collect Evidences
PC (x)
SC (y)
0
33.6
112
0

Points
y-intercept
x-intercept

Cases Investigate
PC (x)
0
168

\Insert \Chart \Scatter \Enter \Right Click Chart \Select Data \Edit

Cases Solved
40
35
30
25

Serious Crimes

20

15
10
5
0

20

40

60

80

100

Petty Crimes

120

140

160

180

Serious Crimes

20

15
10
5
0

20

40

60

80

100

120

140

160

180

Petty Crimes

SOLVER SETUP
Product
No. of Cases Filed
Collect Evidences
Cases Investigated
Cases Solved

PC
72
1.5
3
0.1

SC
12
5
24
0.6

H
Total
168
504
14

mes per week

Crimes per week

Assigned
(1) Police Officer
(3) Investigators

PC

5
24

SC
SC

Cases Investigated
SC (y)
21
0

Collect Evidences
Cases Investigated

160

180

0.6

<=
<=

SC

168
504

Collect Evidences
Cases Investigated

160

180

Hours/Week
Sign
Constraint
Slack
<=
168
0
<=
504
0
=
Maximize

Microsoft Excel 15.0 Answer Report

Worksheet: [MemphisPDCaseStudy Part4.xlsx]CaseMPDPart4 Critical Path
Report Created: 7/18/2015 9:11:51 AM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.157 Seconds.
Iterations: 1 Subproblems: 24
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNeg

Objective Cell (Min)

Cell
$F$113

Name
Total = Crash Cost ($000)

Original Value
400

Cell

Name
A Crash Time (days)
B Crash Time (days)
C Crash Time (days)
D Crash Time (days)
E Crash Time (days)
F Crash Time (days)
G Crash Time (days)
H Crash Time (days)
I Crash Time (days)
J Crash Time (days)
K Crash Time (days)
L Crash Time (days)
M Crash Time (days)
N Crash Time (days)
O Crash Time (days)

Original Value
0
0
0
0
0
0
0
0
0
0
0
24
0
0
0

Cell

Name
Total = Crash Cost ($000)
A Crash Time (days)
B Crash Time (days)
C Crash Time (days)
D Crash Time (days)
E Crash Time (days)
F Crash Time (days)
G Crash Time (days)
H Crash Time (days)

Variable Cells
$E$98
$E$99
$E$100
$E$101
$E$102
$E$103
$E$104
$E$105
$E$106
$E$107
$E$108
$E$109
$E$110
$E$111
$E$112

Constraints
$F$113
$E$98
$E$99
$E$100
$E$101
$E$102
$E$103
$E$104
$E$105

Cell Value
400
0
0
0
0
0
0
0
0

$E$106
$E$107
$E$108
$E$109
$E$110
$E$111
$E$112
$E$98:$E$112=Integer

I Crash Time (days)

J Crash Time (days)
K Crash Time (days)
L Crash Time (days)
M Crash Time (days)
N Crash Time (days)
O Crash Time (days)

0
0
0
24
0
0
0

art4 Critical Path

ality conditions are satisfied.

Use Automatic Scaling

er Tolerance 1%, Assume NonNegative

Final Value
400

Final Value

Integer
0 Integer
0 Integer
0 Integer
0 Integer
0 Integer
0 Integer
0 Integer
0 Integer
0 Integer
0 Integer
0 Integer
24 Integer
0 Integer
0 Integer
0 Integer

Formula
$F$113=$F$90
$E$98<=$B$98
$E$99<=$B$99
$E$100<=$B$100
$E$101<=$B$101
$E$102<=$B$102
$E$103<=$B$103
$E$104<=$B$104
$E$105<=$B$105

Status
Binding
Binding
Binding
Binding
Binding
Binding
Binding
Binding
Binding

Slack
0
0
0
0
0
0
0
0
0

$E$106<=$B$106
$E$107<=$B$107
$E$108<=$B$108
$E$109<=$B$109
$E$110<=$B$110
$E$111<=$B$111
$E$112<=$B$112

Binding
Binding
Binding
Not Binding
Not Binding
Not Binding
Binding

0
0
0
36
60
1
0

Case: Memphis PD
Part 4 Project Management
Tool: Critical Path Method

Min. Req.

Government Project (New Building Precint)

Size 4X10

15 X 12

Normal Time Summary

Normal Time t
(days)

Activity

Predecessor /s

A
B
C
D

A
B
C

20
15
3
15

30

F
G
H
I
J

E
F
D,G
H
I

3
2
15
240
30

15

L
M

I
H

90
120

K,L,M

N, J, K

Description
Acquire Building Plan
Approved Building Plan
Sign Building Contract
Get Building Permit
Search For Supplier (Labor and
Materials)
Bid for Suppliers
Award Bid Winners
Schedule Construction
Construct Building
Inspect Building
Acquire Neccesary Clearances and
Certificates
Installation of Electricity, Water and
Communication
Purchase of Furnitures and Fixtures
Turnover and Accept Building
Property
Pay Suppliers

Project Network

20

15

Path
1
2
3
4
5
6
7
8
9
10
11
12

A
A
A
A
A
A
A
A
A
A
A
A

B
B
B
B
B
B
B
B
B
B
B
B

C
C
C
C
C
C
C
C
C
C
C
C

D
D
D
D
D
D
D
D
D
D
E
E

Earliest Start
(ES)
0
20
35
38
38
68
71
73
88
328
358
328
88
418
420

Earliest Finish
(EF)
20
35
38
53
68
71
73
88
328
358
373
418
208
420
422

Critical Path Table (Normal)

Activity
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O

Prede-cessor(s)

Time t (days)

A
B
C
C
E
F
D,G
H
I
J
I
H
K,L,M
J, K, N

20
15
3
15
30
3
2
15
240
30
15
90
120
2
2

Crashed Time Summary

Activity

Prede-cessor/s

A
B
C
D
E

A
B
C
C

Time t (days)
Normal
Crashed
20
15
15
10
3
2
15
10
30
20

Cost PHP
Normal
60000
45000
9000
45000
90000

F
G
H
I
J
K
L
M
N
O

E
F
D,G
H
I
J
I
H
K,L,M
J, K, N

3
2
15
240
30
15
90
120
2
2

2
1
12
200
25
11
51
60
1
1
602

Solver Setup
\Data \Solver

9000
3000
45000
6000
90000
45000
6000
6000
6000
6000
Due Date (days) =
421
-56

Activity

Time Allowed

Cost Increase

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O

5
5
1
5
10
1
1
3
40
5
4
39
60
1
1

1000.00
1000.00
1000.00
6000.00
5000.00
1000.00
1000.00
1000.00
1000.00
1000.00
1000.00
1000.00
1000.00
2000.00
1000.00

Crash Cost/Day
200.00
200.00
1000.00
1200.00
500.00
1000.00
1000.00
333.33
25.00
200.00
250.00
25.64
16.67
2000.00
1000.00
Total =

Crash Time
(days)
0
0
0
0
0
0
0
0
40
0
0
17
0
0
0
57

Critical Path Table (Crashed)

Activity

Prede-cessor(s)

A
B
C
D
E

A
B
C
C

Crashed t
(days)
20
15
3
15
30

Earliest Start
(ES)
0
20
35
38
38

Earliest Finish
(EF)
20
35
38
53
68

F
G
H
I
J
K
L
M
N
O

E
F
D,G
H
I
J
I
H
K,L,M
J, K, N

3
2
15
200
30
15
73
120
2
2

68
71
73
88
288
318
288
88
361
363

71
73
88
288
318
333
361
208
363
365

D
15

30

H
H
H
E
E
E
H
H
E
E
F
F

I
I
I
F
F
F
M
I
F
F
G
G

Latest Start
(LS)
0
20
35
58
38
68
71
73
88
375
405
328
298
418
420

Latest Finish
(LF)
20
35
38
73
68
71
73
88
328
405
420
418
418
420
422

Cost PHP
Crashed
61000
46000
10000
51000
95000

J
J
J
G
G
G
N
L
G
G
H
H

K
K
O
H
H
H
O
N
H
H
M
I

Idle Time

Critical Path?

0
0
0
20
0
0
0
0
0
47
47
0
210
0
0

Yes
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes
No
No
Yes
No
Yes
Yes

N
O

I
I
I

J
J
J

O
M
I
N
L

N
L
O
N

10000
4000
46000
7000
91000
46000
7000
7000
8000
7000
365

Crash Cost
($000)
0
0
0
0
0
0
0
0
1000
0
0
435.8974359
0
0
0
1435.897436

Slack
(Allowed)
5
5
1
5
10
1
1
3
0
5
4
22
60
1
1
36

Latest Start
(LS)
0
20
35
58
38

Latest Finish
(LF)
20
35
38
73
68

Idle Time
0
0
0
20
0

Yes
Yes
Yes
No
Yes

68
71
73
88
318
348
288
241
361
363

71
73
88
288
348
363
361
361
363
365

0
0
0
0
30
30
0
153
0
0

Yes
Yes
Yes
Yes
No
No
Yes
No
Yes
Yes

M
120

L
90

15

240

30

G
2

K
K
O

O
N
O

N
O

15

O
2

Case: Memphis PD
Part 4 Project Management
Tool: Critical Path Method
WWF Concert at Cincinnati Zoo
Normal Time Summary

Activity

Predecessor /s

A
B
C
D
E
F
G
H
I
J

A
B
A
B
C
D,F
D
E,G,H
I

Normal Time t
(days)
18
12
24
20
19
22
14
10
16
8

Description
Process permits
Organize team
Book performers
Reserve venue
Invite sponsors
Schedule rehearsals
Advertise
Sell tickets
Subcontract staging
Conduct/evaluate concert

Project Network
E

Path
1
2
3
4

A
A
A
A

B
B
D
D

E
C
G
H

I
F
I
I

J
G
J
J

Critical Path Table (Normal)

Activity
A
B
C
D
E
F
G
H
I
J

Predecessor(s)

Time t (days)

Earliest Start
(ES)
0

Earliest Finish
(EF)

Latest Start
(LS)

A
B
A
B
C
D,F
D
E,G,H
I

Crashed Time Summary

Activity

Prede-cessor/s

A
B
C
D
E
F
G
H
I
J

A
B
A
B
C
D,F
D
E,G,H
I

Time t (days)
Normal
Crashed
16
11
21
18
17
21
12
6
15
6

Cost ($000)
Normal
Crashed
12
18
10
14
17
20
15
23
11
17
14
18
16
24
9
25
13
16
8
12
Due Date (days) =
106

Solver Setup
\Data \Solver
Activity
A
B
C
D
E

Time Allowed

Cost Increase

Crash
Cost/Day

Crash Time
(days)

Crash Cost
($000)

F
G
H
I
J
Total =

Critical Path Table (Crashed)

Activity
A
B
C
D
E
F
G
H
I
J

Predecessor(s)
A
B
A
B
C
D,F
D
E,G,H
I

Crashed t
(days)

Earliest Start
(ES)

Earliest Finish
(EF)

Latest Start
(LS)

Latest Finish
(LF)

Slack
(Allowed)

Idle Time

Critical Path?

Latest Finish
(LF)

Idle Time