Quick Tour of Project for Excel™

Before You Begin

 

These tutorials guide the user through the process of creating and testing a sample of the Project for Excel™ application. 

The tutorial teaches the basic techniques for opening an Project and generating reports.

Quick Tour Steps

1.      Open an Project Workbook example

2.      Review Categories, Activities and Durations

3.      Generate the Project Diagram

4.      Generate the Project Plan, Gantt and PERT Charts

5.      Review the Project Plan, Gantt and PERT Chart

6.      Review the Probability Analysis

1. Open an Project Workbook Example

There are 7 examples that are prepared for quick tour, the examples are located in the

C:\Program Files\BaRaN Systems LLC\Project for Excel 97

Or

C:\Program Files\BaRaN Systems LLC\Project for Excel 2000

Folders.

Please execute the following steps:

N	Description	Status
1	Start MS Excel
2	Open Example_03_Done.xls in the above folder
3

 

Parameter	Value
Project	Example_03
Description	Basic of Project
Owner	http://hadm.sph.sc.edu/Courses/716.html
Start Date	10/23/00
Time Format
Duration Format	###0.00
Comments
Step	What
1	Fill in the above information
2	Type the Nodes, Activities and Duration in the [Data] worksheet
3	Type the Optimistic Time, Most Likely Time and Pessimistic Time in the [Data] worksheet
4	Type the Predecessor of each task in the [Data] worksheet (P1,P2,…)
5	Choose option [2] from Tools--> Project menu
6	Review the Network Activity Diagram in the [Diagram] worksheet
7	Choose option [3] from Tools--> Project menu
8	Review the PERT in the [Diagram] worksheet
9	Review the Tasks in the [Task] worksheet
11	Review the Gantt Chart in the [Gantt Chart] worksheet
12	Review the Probability analysis in the [Probability analysis] worksheet
	Save the Spreadsheet

 

2. Review Categories, Activities and Durations

In this example there are ten activities with specific durations.

Activity	Duration
A - Product design	5
B - Market research	1
C - Production analysis	2
D - Product model	3
E - Sales brochure	2
F - Cost analysis	3
G - Product testing	4
H - Sales training	2
I - Pricing	1
J - Project report	1

The data is from a very informative web page (http://hadm.sph.sc.edu/Courses/716.html). In this example the activities are connected in the following manner:

 

 

 

 

 

 

 

 

Based on the above Project Diagram, the following tables can be constructed.

Activity Number	Node Name	Activity Name	Optimistic Time	P1	P2	P3
1	Start
2		A - Product design	5	1
3		B - Market research	1	1
4		C - Production analysis	2	2
5		D - Product model	3	2
6		E - Sales brochure	2	2
7		F - Cost analysis	3	4
8		G - Product testing	4	5
9		H - Sales training	2	6	3
10		I - Pricing	1	9
11		J - Project report	1	7	8	10
12	End			11

Note 1: Please note that P1, P2 and P3 represent the predecessors of each Activity. You need to have the Start and End Category for the program to work.

 

Note 2: The activity numbers should be integer (1,2,3,…) and should be incremental without any duplication.

 

Note 3: In this example the Optimistic Time, the Most Likely Time and the Pessimistic Time are set to a same number.

 

3. Generate the Project Diagram

 

Please execute the following steps:

N	Description	Status
1	Select the Diagram worksheet
2	Review the Project
3	From MS Excel choose the following option Tools à Project à [2] Process Node and Tasks…
4	Close the spreadsheet (Without saving it)
5	Open the Example_03_Done.XLS again

Note that the activities in this diagram are changed to show the overall Project.

This will generate an Affinity Diagram that represents the same Project.

 

4. Generate the Project Plan, Gantt and PERT Charts

Please execute the following steps:

N	Description	Status
1	Select the Diagram worksheet
2	From MS Excel choose the following option Tools à Project à [3] Process Activity…

 

 

 

 

5. Review the Project Plan, Gantt and PERT Chart

Please execute the following steps:

N	Description	Status
1	Select the Diagram worksheet to review the PERT chart.
2	Select the Task worksheet to review the project plans
3	Select the Gantt worksheet to review the project plans

Task Worksheet

Task Name	Task Duration	Initial Date	%Done	Standard Deviation	Latest Start	Slack for Task	Earliest Start	Earliest Finish	Latest Finish
Start	0	10/23/2000	0	0	0	0	0	0	0
A - Product design	5	10/23/2000	0	0	0	0	0	5	5
B - Market research	1	10/23/2000	0	0	8	8	0	1	9
C - Production analysis	2	10/23/2000	0	0	7	2	5	7	9
D - Product model	3	10/23/2000	0	0	5	0	5	8	8
E - Sales brochure	2	10/23/2000	0	0	7	2	5	7	9
H - Sales training	2	10/23/2000	0	0	9	2	7	9	11
F - Cost analysis	3	10/23/2000	0	0	9	2	7	10	12
G - Product testing	4	10/23/2000	0	0	8	0	8	12	12
I - Pricing	1	10/23/2000	0	0	11	2	9	10	12
J - Project report	1	10/23/2000	0	0	12	0	12	13	13
End	0	10/23/2000	0	0	13	0	13	13	13

Gantt Chart

 

 

 

 

 

 

 

 

 

 

 

 

Review the Probability Analysis

There are several important additions to the basic network analysis technique and these relate to:

q  Uncertain activity completion times

q  Cost/time tradeoff  (next version)

q  Resource restrictions (next version)

When one faces the uncertain activity completion times, then for each activity, three duration times can be estimated:

q  Optimistic time (T_O) - the completion time if all goes well

q  Most likely time (T_M) - the completion time we would expect under normal conditions

q  Pessimistic time (T_P) - the completion time if things go badly.

This use of three time estimates is the PERT technique. Note here that these three time estimates do not imply that, when the activity is performed, its completion time will be one of three choices - optimistic, most likely or pessimistic. Instead these three numbers are used to produce an underlying probability distribution of completion times such that all times are possible (with an associated probability). (See reference 1)

These three times are combined into a single figure, the Expected Activity Completion or Duration time, given:

Duration time = (T_O + 4 * T_M + T_P)/6

This number is used as the activity completion time when carrying out the calculations presented before to find the project completion time and the critical activities.

Note here that this weighting of optimistic: most likely: pessimistic of 1/6:4/6:1/6 is essentially fixed and cannot be altered (as the underlying theory depends on these weights).

In addition, through the use of the normal probability distribution, we can get an Task of how this project completion time might vary (remember we no longer know the individual activity completion times with certainty).

Essentially we can find answers to questions like:

q  What is the probability that the project will take longer than...?

q  What is the probability that the project will be finished by...?

The program calculates a new field called Standard Deviation. As we have a statistical distribution for the completion time of each activity (as represented by the optimistic, most likely and pessimistic times) we also have an underlying statistical variation in the completion time - as represented by the standard deviation. This standard deviation is calculated as

Standard Deviation  = (T_P - T_O)/6

 In the above example the following durations and standard deviations are calculated.

 

 

 

 

Based on this information the program calculated the following information.

Based on analysis of the Critical Path, the project should be finished after 14 units. However, with 95% confidence ( 2 Sigma) the project will take 17.1 units.