FILE NAME: CPMDAILY.xls
FUNCTION: Critical-path-method, daily schedule OVERVIEW: A project can be defined as a collection of tasks which must be done according to a set of precedence relationships. This worksheet schedules each task and identifies the critical path. For those familiar with project management software, you should jump directly to the templates and INPUT section and start with your own data. Others should read the remaining paragraphs in this section to gain a basic understanding of project management.
Our entire life, both at business and at home, are comprised of projects - each project a series of tasks. Every recipe is a project. The tasks include: decide on what to eat, get in car, go to store, get a cart, buy food, return home, heat in microwave, eat, throw away container. Most vacations turn out to be projects: obtain brochures, plan trip, make reservations, drive, eat, drive, sleep, repeat for several days. Fortunately, most everyday projects don't require a computer, but there are many, usually for business projects, that could benefit from a product such as the Spreadsheet Scheduler. As an example, we will run through a film making project, "The Creature of the Black Lagoon". Please follow along with the printouts and/or the actual template on the screen.
There are eight tasks to be done. The movie company has completed a rough script, so it can start two tasks immediately, editing the script and casting. When the script is complete, special effects can be developed and sets built. When both sets and casting are complete, other scenes can be filmed. Editing the film cannot start until both types of scenes are complete.
However, previews can be prepared using only horror scenes.In the early-start schedule, each task is scheduled to start as early as possible. The numbers in columns I and J are days since the beginning of the project. The project starts on day 1 and ends on day 9, the finish day for the last task, editing the film. The start day for each task depends on when its preceding tasks are complete. For example, other scenes can start filming as soon as both tasks #2 and #4 are complete. The template automatically checks and finds that task #2 finishes at day 1, while task #4 finishes at day 4. In this case, task #4 dictates that filming other scenes (task #6) cannot start until day 5, the day after task #4 is done.
The late-start schedule (page down from Early-Start) is an alternative in which each task is scheduled to start as late as possible, provided there is no impact on completion of the project by day 9. The template automatically determines when each task should start. For example, casting does not have to start until it is needed for a succeeding task. Casting is succeeded by two tasks #5 and #6. The template finds that task #5 must start at day 5, while task #6 must start at day 7. They both need to finish on the eighth day (one day before the end), to allow for the final day of editing. The duration of task #5 is three days, while the duration of task #6 is one. When you add these to the start days, they both total eight. Column K shows the number of days each task may be delayed beyond its early start date without affecting the project completion date. If a zero appears in this column, the task cannot be delayed and it is on the critical path.
INPUT: Enter task descriptions in C9..C16. C8 and C17 are always labeled dummy start and dummy finish. Number the tasks in E8.E17. The dummy start is always zero and the dummy finish is assigned an arbitrary number much larger than the last task. Enter the number of days needed to do each task in F9..F16. In G9.H17, enter the numbers of the immediately preceding tasks. If there is only 1 immediately preceding task, use zero as the second. The dummy start is always preceded by two zeros while the dummy finish is preceded by the last task and NA. In G28..H37, enter the numbers of the immediate successors to each task. If there is only one immediate successor, enter the dummy finish number. For the dummy finish, enter NA. For the dummy finish, enter NA in both cases. Starting in cell C47, enter a list of consecutive integers starting from one and extending a couple of days past your expected finish. If you need more than 16 days, read the section on modifying the templates to expand the template. Copy the formula from the D column (45..) through the blank cells next to the consecutive integers in column C.
The current formula for the date skips Saturday and Sunday. If you will work these days, you can modify the formula or just enter the date by =DATE(YR,MO,DY). Finally, enter the kick-off date (the first working day for project) in cell D66.
OUTPUT: Early start and early finish day numbers for each task are shown in I7.. Late start and late finish day numbers are shown in GI27... The number of days of slack for each task is shown in K9.. and repeated in cells K25... Gantt charts of the early-start and late-start schedules are shown in the last two sections of the worksheet.
MODIFYING THE WORKSHEET: In larger projects, you may need more than two preceding tasks. If so, add one column to the worksheet for each predecessor, by placing cursor on column I and inserting column(s) as needed. Then, expand each MAX function in column I by adding one =VLOOKUP for each predecessor to the formula in cell G6 and copying down through all cells in table below.
Place your cursor on cell, press F2, and add another VLOOKUP inside the parenthesis as in the following example:
MAX(VLOOKUP(E6,$C$5..$I$14,5),VLOOKUP(F6,$C$5..$I$14,5),VLOOKUP(G
6,$C$5..$I$14,5))+1
In a similar fashion, you may need more than two succeeding tasks. If so, repeat above procedure. To add more tasks, insert the same number of rows in each of the four sections of the worksheet: early-start schedule, late start schedule, early start Gantt-chart, and late-start Gantt chart. Place the cursor in the fourth row of each section and insert rows. Then copy the fourth row to the new rows.
NOTE ON WORKSHEET RECALCULATION: Ordiinarily spreadsheets recalculate your worksheet automatically in natural order. That is, before recalculating a cell, all cells on which it depends are recalculated. Furthermore, the program makes one pass through the worksheet for each recalculation. The logic of CPM scheduling requires a different recalculation scheme.
Recalculation must be set to rowwise, meaning that the program begins in cell A1 and recalculates all formulas in row 1 from left to right. Then all formulas in row 2 are recalculated, then row 3, and so on. Recalculation should be set to manual, rather than automatic, and 10 passes (iterations) should be made for each recalculation.
These settings, manual with 10 iterations, are already a part of the worksheet and do not have to be changed. This worksheet requires 7 iterations to recalculate completely. Iterations were set at 10 for safety. In other projects, it is difficult to tell in advance how many iterations you will need. Even if iterations are set at 50, the maximum allowed, you may have to press the Calc key (F9) a few times to complete a large schedule. Incomplete schedules are indicated by negative numbers in the late start and finish columns. To change these recalculations setting in Excel, select Tools Option Recalculation. For 1-2-3, use the following selections:
/Worksheet Global Recalculation Rowise Manual Iterations XX
where XX is any number from 1-50.
NOTE ON FORMULAS: The formulas in column Iand K set the early start day for each task equal to the maximum early finish day of its immediate predecessors. For example, look at the formula in cell I11, the early start day for other scenes. The MAX function in I11 picks the maximum of two VLOOKUP (variable lookup) functions. The first lookup says this: Take the value in cell G11, the first preceding task. This is task #2. Look up 2 in the leftmost column of the table range $E$5..$J$14.
Then move 5 columns to the right in that row. The result of the lookup is the finish day for task 2 or day 1. The second lookup does the same thing for the other preceding task (# 4). This lookup yields a finish of day 4. The maximum of these two finishes is day 4. Day 4 plus 1 is day 5 (in cell G11), the earliest day that other scenes can start. The finish day (in cell J11) is day 5+1-1=5.
In a similar fashion, the late start for each task is equal to the minimum of the late finish days for its immediate successors. The dummy finish task simply provides a stopping point for the series of lookups. Again, let's think through the calculations for one of the cells. Look at cell I26, the late finish for editing the script. The first lookup says this:
take the value in cell G26, the first succeeding task. This is task number 3. Look up 3 in the left most column of the table range $E$25..$J$34. Move 4 columns to the right in that row.
The result of the lookup is the start for task 3 or day 3. The second lookup does the same thing for the other succeeding task (number 4). This lookup yields a start of 5. The minimum of these two starts minus 1 is day 2, the late finish for editing the script. The start for the script is then 2-2+1=1.
A single formula is used to draw the bars in the Gantt charts. This formula checks first to see if the date at the top of the column is greater than or equal to the early start date. If so, another check is made to see if the date at the top of the column is less than or equal to the early finish date. If both conditions are true, a task is underway during that week and a bar must be displayed in the cell. Next, the formula test to see which type of bar to use. If slack is greater than zero, the task is critical and |****** is displayed. Finally, if no task is underway, a vertical mark (|) is displayed at the left border of the cell and remaining characters in the cell are blank.
1. Get Started
2. Workstation Production Models
3. Scheduling 2 Workstation in Series
4. Scheduling 3 Workstation in Series
5. Scheduling 4 and more Workstation in Series
6. Scheduling 2 Workstation in Parallel
7. Project Management - Daily Schedule
10. Gantt Charting