EXCEL SOLVER SETUP
To setup Excel Solver for the example problem, we go through three steps in the spreadsheet: (1) organize and prepare the input data; (2) define the AON network shown in Figure 1; (3) setup Solver parameters and constraints. The spreadsheet in Figure 2 shows the data information for the example problem. The predecessor relationships and information for activity duration and cost from Table 1 are shown in cells A4:G12.
We first organize and prepare the input data for the convenience of setting up Excel solver. The prepared information is listed in cells B15:E24. A detailed description is given below.
“Maximum Crash Days” (cells B18:B24) is the maximum available days for crashing for each activity. It is calculated from the difference between “Normal Duration” and “Crash Duration” of each activity. Using activity A as an example, cell B18: =C6-D6.
“Number of Days Crashed” (cells C18:C24) is the decision variables. Solver tries to find the optimal number of days to crash for each activity, so that the total project cost will be minimized.
“Actual Crash Cost” (cells D18:D24) is the additional cost needed to crash each activity by a certain duration. It is the result of multiplying “Crash Cost per Day” and “Number of Days Crashed”. For example, cell D18: =C18*G6.
“Actual Activity Duration” (cells E18:E24) is the durations after the activities being crashed. Therefore, it is the difference between “Normal Duration” and “Number of Days Crashed”, i.e., cell E18: =C6-C18.
In addition, “Total Normal Cost” in cell E13: =SUM(E6:E12) is the sum of all normal cost, and “Total Crash Cost” in cell C25: =SUM(D18:D24) is the sum of actual crash cost for each activity. Adding the total normal cost and total crash cost together, we obtain the project’s total cost in cell B2: =E13+C25, which we aim to minimize in the project crashing.
We next define the AON network by specifying the network paths and identifying the critical path. Network structure is fundamentally defined by network paths. A particular set of paths uniquely represent a particular structure of a network. The network diagram in Figure 1 has three paths: ACF, ADG, and BEG. The duration of each path is determined by the actual durations of activities on the path, before or after activity crashing. For example, the path completion time for ACF is calculated in cell G18: =E18+E20+E23, the sum of actual durations of activities A, C, and F. Project completion time is determined by the critical path duration, i.e., the longest path in the project. Thus, we have the project completion time in cell G25: =MAX(G18:G20), reflecting the longest duration of the three paths in the network. The above simple approach clearly defines the AON network structure, without imposing complicated constraints needed for an AOA network as in Mantel et. al (2011).