There are four versions of this freeware available for download. Their differences are briefly as follows:
Version 2: I wrote version 2, and it is primitive but completely reliable. You drag the red bars to create your own Gantt chart and then input start and finish dates manually. When complete, it can have a professional appearance and provide useful data for estimating and resource planning. It has two other worksheets, that use the same data, to provide a budget spend plan and staffing profiles.
Version 4.0: This is an update to v2 provided by a helpful user. It has the wonderful capability of creating the Gantt bars from the dates you input in the dates columns.
Version 3.2: This is a simplification of the format of the other two versions. This file does not have the second two worksheets, which provide the additional resource management features for budget spend plan and staffing profile.
Version 3.3: This is a further enhanced version of the short sheet. It has some slick features, although I haven't been able to master a few aspects of the new calendar. The author provided these useful comments: (1) Made the bars move so that it adjusts to always be Monday at the start (If it's Sunday or Saturday start there will be a blank week as a warning). I found the version 3-2 would take the first date and make that the beginning of the week. (2) Added a completion % column with selectable values. (3) Linked the color coded bars to the status so that it was clear to see the status of the activity. (4) Added week number, month and year details. (5) Coloured the entry fields yellow. (6) Some other minor changes.
Versions 2 and 4 consists of three worksheets: (1) a Gantt chart scheduling tool (named after Henry L. Gantt, circa 1924), (2) a budget spend plan driven off the Gantt chart, and (3) weekly labor hour profiles, to assist with resource leveling during the initial planning process.
Admonition: Unless you are particularly adept at EXCEL, and follow the logic of the formulas on both worksheets, don't initially add columns or rows to the worksheets, because your changes may spill over into the formulas, and to the subsequent worksheets that are all linked together. (If you want to add and delete columns and rows, consider that you may end up rebuilding the worksheets.) Experienced users, have at it.
The spreadsheet format is made vaguely to resemble an MS-Project Gantt Chart view.
Set up the Calendar:
To begin, you will need to make sure the calendar of weeks and months fit your project planning window. The calendar is the time-scale showing weeks and months that runs horizontally across the top of the spreadsheet from middle to right. The spreadsheet is for 48 weeks maximum project duration. For a longer project you may want to extend the spread sheet, recast the spread sheet so one cell = one week instead of one day, or spring for $600 and get real software. (The reason I did 48 weeks was to keep it to a two page printout. It should extend fairly easily.)
The weekly dates are for Monday, beginning of week. For the early versions only, pPlace the date of the first Monday of your project schedule in the first data cell, and you will see all the other Mondays appear. The formula (j4+7) in the second week cell is copied horizontally, and it will extend the dates for all other weeks. Once the weekly dates have been established, you can update the month names, and appropriate borders segmenting the months across your schedule. The monthly names can be inserted manually. Highlight the entire space, and make sure the monthly calendar space and the see that the format/cells/alignment/merge-cells box is not checked. You can simply put a border around each month space and write in the month name (format/cells/border/outline). This has been done in v2.
Since the number of weeks in a month varies, you may have to revise the merged-cell borders to accommodate either four or five weeks per month, based on your project start date. Each cell represent a single project day, so the calendar merges five cells into a week and 20 or 25 cells into a month, in the calendar. (To modify the months, first turn "Merge cells" off for all months, then Merge cells for each monthly boarder. Use the command Format/Cells/Alignment/Merge cells/checked or unchecked.)
For much longer schedules, over lots of years, you might want to have one cell equal a week; that will require a rebuild of the spreadsheet.
Column Inputs:
You should have some idea of a basic Work Breakdown Structure for your project before you begin. The major elements and tasks in your WBS should be the initial items you insert in the main “Tasks” column. This spreadsheet has room for about forty WBS Elements and tasks. (If you want to add more to the spreadsheet, you will have to extend the formulas and rebuild links on subsequent pages.)
WBS
numbering is optional, but nice. The standard numbering format is illustrated, where
project is 1.0, first major element is 1.1, first tasks is 1.1.1, second major element is
1.2, etc.
I like to
add a column for owner or lead or some such, to make sure there is "clear
ownership" for each task, for planning and for execution. You can figure out how you want to manage that.
The first 6 columns (a-f), highlighted in pale yellow, are for user input. That is to say, don't put data in the “Days” and “Hours” columns; they are computed from the Start and Finish dates and the FTE factor that you insert, respectively. (FTE means "full time equivalent" and simply refers to what part of a whole person is working on that task on that day.)
Drawing the Gantt Bars:
In version 2, you draw the Gantt bars by hand, essentially finger-painting with your mouse. Touch your cursor to the bottom right hand corner of a red cell, and then left click and drag horizontally or vertically to establish the red bar in the appropriate task and start/finish times. Then you will have to fill in the column dates manually. For more specific information on "cell-dragging" in EXCEL click here.
In version 3 and 3.2, the Start and Finish dates for each tasks will generate the Gantt bars.
FAQ: How can I change the color of the bars? (Provided by a helpful user.)
To
change the bar color:
1.
Highlight the cell(s) that you want to change.
2.
Select "Format" / "Conditional Formatting" from the menu bar
3.
A conditional formatting box will pop up showing the current color assigned to the
cell(s) depending on the value determined by the formula in the cell(s)
4.
To change the assigned color, click on the "Format" button and select the
"Patterns" tab.
5.
Select a new color.
6. Click on the "OK" button.
Resource Loading:
Once the total calendar "days" are established for a task, the next step is to input the amount of labor that will be expended for each day, expressed in terms of "full time equivalents" (FTEs). The FTE factor, which you have to estimate yourself, multiplied by the number of days, and 8 hours per day, will compute the total labor hours to be expended on that task. To illustrate, if I am spending 20% of my time on general project supervision throughout the 240 day duration of a project, that will result in 384 labor hours for that task (240 days * 0.2 FTE factor * 8 hour/day = 384 work hours).
The scant sample data shows a spend plan on the 2nd worksheet, but in real life it would start slower, giving the usual "S-curve." Such a labor profile might need to be smoothed through a planning process of "resource leveling" (3rd worksheet). Some dummy "actuals" have been added to for illustration.
This program does nothing with the concept of “dependencies” between tasks. If you have multiple resources applied to the same task, with different FTE factors and labor rates, you might want to either average the data or show it as two parallel tasks.Here is the Program Description and User Guide for the budget spend plan and labor profile worksheets.
Return to Project Schedule in EXCEL
Return to Project Home