Unit Load Hours Available
Unit Load Typical Unit
Shopping No. Of (hrs per
Medical (hrs Load (50%Sh
(hrs per Professionals professional per
per contract) 50%Med) hrs
contract) day)
Paralegal 4 6 4 6 5
Tax
1 3 3 8 2
lawyer
Senior
1 1 2 4 1
partner
Unit Load Hours Available
Unit Load
Shopping No. Of (hrs per Capacity Sh
Medical (hrs
(hrs per Professionals professional per Case / day
per contract)
contract) day)
Paralegal 4 6 4+1 6 7.5
Tax
1 3 3 8 24
lawyer
Senior
1 1 2 4 8
partner
Capacity of Capacity of Capacity Capacity of
No. Of
one one of the the
Professionals
Resource Resource Resource Resource
Required
Unit / hr Unit / day Pool /day Pool /mon
0.2 1.2 4.8 96 6.25
0.5 4 12 240 1.875
1 4 8 160 1.875
Capacity
Med Case /
day
5
8
8
2.4
Resource Unit Load Theoretical
Pool (min./claim): capacity of a
Tp Resource Unit:
1/T p claims/min
Mailroom 0.6 1.67
clerk
Data-entry 4.2 0.24
clerk
Claims 6.6 0.15
processor
Claims 2.2 0.45
supervisor
Number of the Theoretical capacity of
Resouce Units in Resource Pool:
the Resource R p =c p /T p
Pool: c p Claims/min
1 1.67
8 1.90
12 1.82
5 2.27
Resource Scheduled Unit Load
Pool availability (min./claim):
(min/day) Tp
Mailroom 450 0.6
clerk
Data-entry 450 4.2
clerk
Claims 360 6.6
processor
Claims 240 2.2
supervisor
Theoretical capacity Number of the Theoretical capacity of
of a Resource Unit: Resouce Units in Resource Pool:
(1/T p )(min/day) the Resource R p =(c p /T p )(min/day)
claims/day Pool: c p Claims/min
750.0 1 750.0
107.1 8 857.1
54.5 12 654.5
109.1 5 545.5
Plumbing Window Cleaning Gutter Guards Landscaping
Production 90 70 80 60
Revenue/job 130 170 200 270
Material cost 30 10 70 75
total Revenue 55800
Material Cost 13500
Direct labor 10000
Administrative OH 18000
Non-administrative OH 9000
Net Profit 5300
Plumbing Window Cleaning Gutter Guards Landscaping
Production 90 70 80 60
Labor Hours/job 2 4 3 5
Revenue/job 130 170 200 270
Material cost 30 10 70 75
Labor Cost 20 40 30 50
Resource pool (p) PhyClim HosClaim Needed Available
Mailroom clerk 0.6 1 327.3 <= 450
Data-entry clerk 4.2 5.2 2290.9 <= 3600
Claims processor 6.6 7.5 3600.0 <= 4320
Claims supervisor 2.2 3.2 1200.0 <= 1200
1 1 545.4545
545.45 0
Microsoft Excel 12.0 Sensitivity Report
Worksheet: [Capacity.xlsx]LPFormulation
Report Created: 11/14/2010 11:43:28 AM
Adjustable Cells
Final Reduced Objective Allowable
Cell Name Value Cost Coefficient Increase
$B$8 PhyClim 545.4545455 0 1 1E+30
$C$8 HosClaim 0 -0.454545455 1 0.454545455
Constraints
Final Shadow Constraint Allowable
Cell Name Value Price R.H. Side Increase
$D$3 Mailroom clerk Needed 327.2727273 0 450 1E+30
$D$4 Data-entry clerk Needed 2290.909091 0 3600 1E+30
$D$5 Claims processor Needed 3600 0 4320 1E+30
$D$6 Claims supervisor Needed 1200 0.454545455 1200 240
Allowable
Decrease
0.3125
1E+30
Allowable
Decrease
122.7272727
1309.090909
720
1200
Resource Pool No. Units in Load (Total) Unit Scheduled Loss Net
Resource Batch Load avilability Factor Availability
Pool (minutes ) hours /day (Minutes)
Worker S 1 1 10 8 6.25% 450
Punch press R 1 1 22 8 5% 456
Punch press B 1 1 30 8 5% 456
Worker PR 1 1 22 8 5% 456
Worker PB 1 1 30 8 5% 456
Forming machine R 1 1 12 8 10% 432
Forming machine B 1 1 6 8 10% 432
Worker FR 1 1 12 8 6.25% 450
Worker FB 1 1 6 8 6.25% 450
Welding gun 1 1 13 8 10.0% 432
Worker SA 1 1 13 8 6.25% 450
Worker FA 1 1 10 8 6.25% 450
Inspector 2 1 36 8 6.25% 450
Effective Capacity of
the Resource Pool
(flow units/day)
45.00
20.73
15.20
20.73
15.20
36.00
72.00
37.50
75.00
33.23
34.62
45.00
25.00
Product A B C D Minutes Profit Margin Profit/Minute
P 15 15 15 15 60 45 0.75
Q 10 30 5 5 50 60 1.2
Product A B C D Profit Margin Demand
P 15 15 15 15 45 110
Q 10 30 5 5 60 60
Capacity 2400 2400 2400 2400
Product A B C D Profit Margin Demand
P 15 10 15 10 45 110
Q 10 35 5 5 55 60
Capacity 2400 2400 2400 2400
Resource Product P Product Q Needed Available
Resource A 15 10 1800.0 <= 2400
Resource B 15 30 2400.0 <= 2400
Resource C 15 5 1650.0 <= 2400
Resource D 15 5 1650.0 <= 2400
Market P 1 100.0 <= 100
Market Q 1 30.0 <= 50
45 60 300
100.00 30
Microsoft Excel 12.0 Sensitivity Report
Worksheet: [Capacity.xlsx]LPFromTOC
Report Created: 11/14/2010 4:07:52 PM
Adjustable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$10 Product P 100.00 0.00 45 1E+30 15
$C$10 Product Q 30 0 60 30 60
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$D$3 Resource A Needed 1800.0 0.0 2400 1E+30 600
$D$4 Resource B Needed 2400.0 2.0 2400 600 900
$D$5 Resource C Needed 1650.0 0.0 2400 1E+30 750
$D$6 Resource D Needed 1650.0 0.0 2400 1E+30 750
$D$7 Market P Needed 100.0 15.0 100 60 40
$D$8 Market Q Needed 30.0 0.0 50 1E+30 20
Resource Product P Product Q Product PJ Product QJ Needed Available
Resource A 15 10 15 10 0 <= 2400
Resource B 15 30 15 30 0 <= 4800
Resource C 15 5 15 5 0 <= 2400
Resource D 15 5 15 5 0 <= 2400
Market P 1 0 <= 100
Market Q 1 0 <= 50
45 60 27 40 -6400
Resource Product P Product Q Product PJ Product QJ Needed Available
Resource A 15 10 15 10 2400 <= 2400
Resource B 15 30 15 30 4800 <= 4800
Resource C 15 5 15 5 1800 <= 2400
Resource D 15 5 15 5 1800 <= 2400
Market P 1 80 <= 100
Market Q 1 50 <= 50
45 60 27 40 3000
80 50 0 70
37.037037 Weeks to pay back
Resource Product P Product Q Needed Available
Resource A 15 10 1900.0 <= 2400
Resource B 10 35 2400.0 <= 2400
Resource C 15 5 1700.0 <= 2400
Resource D 10 5 1200.0 <= 2400
Market P 1 100.0 <= 100
Market Q 1 40.0 <= 50
45 55 700
100.00 40