Question

Nomal Crash Nomal Crash Predecessors Time Cost cost MCA CC-NC cost/week Successors Start Start 39600 13600 8 7000 7800 6 9300 11300 3600, 6-0 1087009100 45500 7900 1600 3600 5100 7200 12 12 13 A, B 14 D, F 1310 15 17 18 Start time Actual 20 Activity Crash time duration ES 23 24 26 27 28 29 Completion ime Nomal project cost Cost of crashing - Total cost 34 Sheet1 1. This problem must be done in Excel. The attached Excel Shell file contains data for a CPM project. Setup the CPM model in Excel. Using Solver determine the least possible project completion time at the least possible cost. Assume crashing must be in whole numbers. Prepare a well formatted printout. IMPORTANT You must download the attached Excel shell file and run the macro to personalize your own Excel shell file. You must personalize the Excel shell file by entering your UTC ID whern prompted. If the file you upload is not personalized with your UTC Mocs ID the file will NOT be graded and you will receive a zero as your grade for the problem. Do not get a copy of the Excel shell file from another student, if you do, you will receive a zero and will be reported to the honor court. You must upload the finished Excel file. No Excel file zero credit. . Use must read and follow the instructions given in the do's and don't document. . If you do not follow these instructions your homework will receive a zero.

Answer 1

This is done in two parts - First, least possible project completion time is determined. Secondly, minimum crashed cost is determined to achieve that completion time.

Solver model to determine the least project completion time is as follows:

EXCEL FORMULAS:

Activity Normal Crash Norma Crash Max Crash 1 Activity Predecessor Crash Cost Slope Actual Crash Time Logical 1 Logical 2 Time Time cost cost End time Time Start 3 9600 13600 C2-D2 IFERROR((F2-E2)/(C2-D2),0) -J2-C2H2 Start 12 8 7000 7800 C3-D3 IFERROR( (F3-E3)/(C3-D3),0 J3-C313 6 9300 11300 C4-D4 IFERROR(F4-E4)/(CA-D4),0) 3600 6600 C5-D5 IFERROR((F5 ES)/(CS-DS),0) -J4-C4t14-12 5-C5+15-14 |8700 |9100 |:C6-D6 |:FERROR((F6-E6)/(C6-D6),0 B,C 10 14 -J6-C6+16-J3 45500 7900 C7-D7 IFERROR((F7-E7)/(C7-D7),0 A,B -17-C7H17-12 7-C7+17-13 1600 3600 C8-D8 IFERROR((F8-E8)/(C8-D8),0) 17 -J8-C8+18-15 10 5100 7200 C9-D9 IFERROR(F9-E9/(C9-D9),0) 13 D,F 10 41700 2600 -C10-D10 IFERROR(F10-E10)/(C10-D10),0) 4 26 E,H s]10-C10+110-16 Is]10-C10+110-19 12 Completion time 26 13 Least possible project completion time112 15 16 Total Crash CostSUMPRODUCT(12:110,H2:H10 17 18 19

Solver model to determine the minimum crash cost to achieve the least project completion time is as follows:

Predecess Normal Crash Normal Crash Max Crash Crash Cost Acta Activity Crash Time End time Logical 1 | Logical 21 1 Activity Solver Parameters Time Time cost Time Slo Start 9600 13600 SIS16 Set Objective: 12 Start 7000 7800 9300 11300 400 By Changing Variable Cells: 3600 6600 1000 SIS2 SJS10 10 87009100 100 B,C 14 Subject to the Constraints: A,B 800 5500 7900 sis2:SIS10く. SGS2:SGS10 Add 16003600 18 | SKS2: SLS 10 >= 0 13 700 D,F 10 51007200 Change 10 225 26 E,H 1700 2600 Delete Completion time 26 Reset All 13 Least possible project completion time26 14 Make Unconstrained Variables Non-Negative 15 16 Total Crash Cost :-15,300-l Simplex LP Select a Solving Method: Options 17 Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. 19 21

Draw the table listing ES, LS, EF, LF and Slack time of each activity Earliest Start (ES) is calculated by forward pass method as latest of the predecessors Earliest Finish (EF). Latest Finish (LF) is calculated using backward pass method as earliest of the successors Latest Start (LS). Earliest Finish (EF) Earliest Start (ES) + Activity Duration Latest Start (LS)Latest Finish (LS) - Activity Duration Total Float (TF) or Slack = Latest Finish-Earliest Finish, or Latest Start-Earliest Start Free Float (FF) = Earliest of the Early Start of the successors minus the Early Finish of Current Activity

Activity	Crash time	Actual Duration	ES	LS	EF	LF	Slack
A	2	3	0	0	3	3	0
B	4	8	0	0	8	8	0
C	5	6	3	3	9	9	0
D	3	3	9	9	12	12	0
E	4	10	9	12	19	22	3
F	3	4	8	8	12	12	0
G	1	5	12	21	17	26	9
H	3	10	12	12	22	22	0
I	4	4	22	22	26	26	0