Download
1 / 16
160 likes | 185 Views
Learn how to optimize project timelines using critical path analysis and event time calculations. Utilize Solver tool to minimize completion time and identify critical activities.
E N D
Example 5.10 Project Scheduling Models
Background Information • Tom Lingley, an independent contractor, has agreed to build a new room on an existing house. • He plans to begin work on Monday morning, June 1. • The main question is when will he complete his work, given that he works only on weekdays. • The owner of the house is particularly hopeful that the room will be ready in 15 or fewer working days – that is, by the end of Friday, June 19. • The work proceeds in stages, labeled A through J, as summarized in the table on the next slide.
Background Information -- continued • Three of these activities, E, F, and G, will be done by separate independent subcontractors.
Background Information -- continued • Lingley wants to know how long the project will take, given the activity times (durations) in the table. • He also wants to know the critical activities.
Solution • The project network appears here. The activity time for each activity is shown on its arc.
Solution -- continued • We suggest that you verify rule 3 from our general discussion – if an activity’s arc leads out of a node, then all of this activity’s predecessors should have arcs leading into the node. • The key to the solution is finding event times for each node in the network, where the event time for node i is the earliest time we could reach that point in the network. • Denote the event time for node i by Ei. We begin by setting E1=0. Node 1 is the start node, so its event time is 0 – right away.
Solution -- continued • Also, if node n is the finish node, then the earliest the entire project can be completed is at time En. Therefore, the total project time is E8 for our example. In general, let i and j be any nodes joined by an arc with activity time tij. • Then we must have Ej Ei + tij. The reasoning is that the event at node j cannot occur until at least time tij after the event at node i. • Actually, we can do better than this. The event time Ej is equal to the maximum of the quantities Ei +tij, where the maximum is taken over all arcs that lead into node j.
Solution -- continued • We could use this relationship to find the event times. In fact, we will do this when we revisit this example in Chapter 12. • For right now, however, we will not use maximums. • Instead, we will exploit inequalities.
Developing the Model -- continued • The completed spreadsheet model appears on the next slide. • It can be developed with the following steps. • Enter data. The data for this model include the predecessors and activity times (durations) in the range C5:D14. • Event times. We will eventually use the Solver to find the event times that satisfy inequalities. For now, enter any event times in the EventTimes range. The EventTime range will be the changing cells range.
Developing the Model -- continued • Network Information. Enter the information in the range D18:F28. This comes directly from the network shown earlier. • Inequalities. Now we implement inequalities in the range G18:I28. We could enter the formulas one cell at a time, but it is more convenient to use VLOOKUP functions. To do so, enter the formula VLOOKUP(F18,LTable2,2) in cell G18 and copy it down. Each of these values corresponds to Ei + tij. As usual, make sure you understand exactly how these formulas work. Lookup functions can be intimidating, but they can save a lot of work! • Project time. Enter the formula =B25 in cell B30. This creates a link to the event time for the final node in cell B25 – that is, to the time to complete the project.
Using Solver • We want to choose the event times so as to minimize the project time and satisfy inequalities. Therefore, set up the Solver as shown here.
Using Solver -- continued • When we click on Solve and see the dialog box indicating that the Solver has found the optimal solution, we now request a sensitivity report, which is shown on the next slide. • We stated in Chapter 3 that these sensitivity reports can sometimes be misleading, but they provide useful information in this example. • Specifically, consider the Shadow Price column of the sensitivity report.
Using Solver -- continued • Each row of this table corresponds to one of the inequalities in the model – that is, to one of the activities in the project. • In general, a shadow price indicates the change in the target cell if the right side of a constraint increases by 1. • Because our constraints include activity times on their right sides, each shadow price indicates how much the total project time will increase if the corresponding activity time increases by 1.
Using Solver -- continued • We see that some activity time increases have no effect on the project time, whereas others have a positive effect. • This is how we find the critical path. It includes exactly those activities with positive shadow prices. These are indicated by asterisks. • The activities that are on the critical path are A, B, D, E, H, and J. • If the activity times for activities not on the critical path could increase, at least a little, with no effect on the total project time.
