Charting earned value

The saying goes: A picture is worth a thousand words. And when looking at earned value information, you may find it easier to understand the information if you use a picture rather than study Project's earned value tables. You can export the earned value information to Microsoft Excel (you must be using Excel version 5.0 or later) and then use Excel's Chart Wizard to create charts of earned value information.

- Cross- To find out more about Project's capabilities to export and import data, see

Reference chapter 22.

When you export earned values from Project to Excel, you create an Excel workbook that contains a task ID, a name, and the various earned values for each task, as shown in Figure 14-9.

ä^ Microsati hxcel GoMmSWü costs yls

tzJiiLiS

F il ë F ilii View insert Farm at Iools nais

Wtnrfow

Help

Type à duesttin füt help

- „ s

X

nöHrffa * (a e, - 1 « - n

■ « s - ii U

¡1 ^ lioi - \2)

¡Ëa Sa ü ; ©1 0 % H

«'Reply with Changes... End Review... „

Al ¡al

.11 .

B I U m =

a m

S % . « a 0

iw. _ .

& - A .

B 5 T fî

Name

A

B

c

D

E

£

e

H 1

J

K

1

D

Name

BCWS

BCWP

ACWP

sv

civ

EAC

BAC

VAC

2

1

Conference

J7.057.50

S320.00

SI ,560.00

(36,137.50) (3640.001)

33,277.50

$7217.50

($5 020.87)

3

2

Preplanning

3320.00

5320.00

SI .560.00

[0.00

(3640 oco

31.560.00

3320 00

(1640.00)

4

3

Initial planning

3320 00

3320 00

SI .560.00

[0.00

(3640 00(1

51.560.00

5020 00

(J640 00)

5

4

Del ermine budget

3720 00

3720 OB

1560.00

SI100

(3240 DO)

$560. DO

3720 03

(1240 CO)

Ë

S

Invitation 1 st

32CO 00

3200.00

$600.00

50. Ü0

(3400 00)

$eoo.oo

$200 00

($400.00)

7

s

Send out invitations

10.00

JO. 00

SO .00

so. oo

$0.00

50.00

$0.00

$0.00

8

7

Selection

t2.537.50

JO. 00

(0.00

(S2.537.50)

$0 00

32,957.50

$2.537.50

($420.00)'

s

Theme

3350.00

JO. 00

(0.00

($350.00)

KI.OO

$350.00

JSO.OO

$0.00 '

10

3

Site

1.1,050.00

JO 00

[0.00

(51,050.00)

JO. 00

31,350.00

$l D50.00

($300.00)

11

10

Keynote speaker

$1,137.50

JO. 00

[0.00

(31,137.50)

JO. 00

31,257.50

$1,137 50

($120.00)

12

11

Hire

$2.70100

JO 00

SO .00

(32,760.00)

JO 00

32.920.00

$2320.00

$0.00

13

12

Caterer

3300.00

JO. 00

[0.00

($800.00)

JO 00

$800.00

JBOO.OO

$0.00

14

13

Bartenders

3320 00

SO 00

[0 00

($320.00)

JO 00

$480.00

J4S0 00

$0 00

14

Security

11 .100:00

30 OD

to -in

(31.100.00)

SO 00

31.100.00

ji .loo do

$0 m

It

15

Photographers

3320.00

JO 00

to ao

($220.00)

JO 00

$220.00

$220 00

$0 00 '

17

1E

Cleanup Crew

3320.00

JO. 00

to .00

($320.00)

$0.00

$320.00

$320.00

$0.00

13

17

Pian Entertainment

3240.00

JO. 00

(0.00

($240.00)

JO 00

$240.00

$240.00

$0.00 '

13

10

Baseball Game

S1EO.OQ

JO. 00

[0.00

($160. DO)

JO 00

$160.DO

J16D.OO

$0.00

20

10

Opera

JSO.OO

JO 00

[0.00

($30.DO)

JO.OO

[00.00

JSO.OO

$0.00 '

21

20

Public Relations

3600.00

10.00

[0.00

($000.00)

JO. 00

$000.00

JCOO.OO

$0.00

22

21

Alert community

3400.00

JO. 00

SO .00

($400.001

JO 00

$400.00

J400.00

$0.00 '

23

22

Press release

3200 00

30.00

[0.00

($200.00)

$0.00

$200.00

J200.00

$0.00 '

24

23

Rem Eauiomeni

KI.OO

JO. 00

[0.00

[O.OD

JO.OO

10.00

$0 00

JO.OO

7.

H H

h i\ Earned Value /

M

I >\r

NOM

Figure 14-9: An Excel workbook that was created by exporting earned value information from Project to Excel.

To create an Excel workbook like the one shown in Figure 14-9, follow these steps:

1. In the Project file containing the information that you want to use in Excel, choose File O Save As to open the Save As dialog box.

2. Type a name for the Excel workbook in the File Name list box. Don't worry about the extension; Project supplies it.

3. Open the Save as type list box, and select Microsoft Excel Workbook. The Save As dialog box should resemble the one that is shown in Figure 14-10.

4. Click Save to start the Export Mapping wizard.

5. Click Next.

6. Choose Selected Data in the next box of the wizard, and then click Next.

Figure 14-10: The Save As dialog box as it appears after you choose to save an Excel workbook file.

7. Choose Use existing map in the next box of the wizard, and then click Next.

8. Choose Earned Value information from the list of available maps in the Export Wizard dialog box, as shown in Figure 14-11, and click Finish.

Note You can continue clicking Next in the wizard, but to chart earned value in Excel, you usually don't need to make any changes in the remaining dialog boxes that the wizard presents.

Export Wizard Mil]) Selection rhfiüse a map for your daa:

Export Wizard Mil]) Selection rhfiüse a map for your daa:

"Who Does What" report

Compare to Baseline

Cost data by task

Default task irf ormatitfi

Export to HTM using standard template

Resource "Export Tabic" map

Task "Expert Table" map

Task and resource PivotTatfe

Task list with errbedcted assignment rows

Tcp Level Tasks list

üelp I :: C:' I I Elext > ~| [jlnlsh 1 cancet |

üelp I :: C:' I I Elext > ~| [jlnlsh 1 cancet |

Figure 14-11: Choose Earned value information as the map from the Export Wizard dialog box.

Open Microsoft Excel and then choose File O Open to open the workbook that you just created. You can use Excel's Chart Wizard to create as many charts from this data as you want. For example, the chart in Figure 14-12 shows all earned values for one task, and the chart in Figure 14-13 shows one earned value for selected tasks.

Figure 14-12: An Excel chart of all earned values for one task.

Note If your project is small, you may be able to chart one earned value for all tasks, but if your project is large. Excel may display an error message if you try to chart one earned value for all tasks.

To create a chart like the one shown in Figure 14-12, follow these steps:

1. Click the Chart Wizard button on the Standard toolbar to start the Chart Wizard. In the first Chart Wizard dialog box, select the type of chart that you want to create.

2. Click Next to open the second Chart Wizard dialog box, as shown in Figure 14-14. In this dialog box, select Rows for the Series in option.

3. Click the Collapse Dialog button at the right edge of the Data range box, which is shown in Figure 14-14, to hide the Chart Wizard so that you can select the task that you want to chart from the worksheet.

Figure 14-13: An Excel chart of one earned value for selected tasks.

4. Highlight (by dragging) the cells in the row containing the information that you want to chart. Note that I've excluded the ID in Column A in Figure 14-15.

Figure 14-14: Chart the data by rows.

[31 Microsoft [ycel - TimeDnto.txt H^ËS

File view Insert Ferrr.ît Tooli chert Wjndow Help - _ 5 x

DtfBiia as;' • ".¡4 z . tti ulit««™- -0.

[31 Microsoft [ycel - TimeDnto.txt H^ËS

File view Insert Ferrr.ît Tooli chert Wjndow Help - _ 5 x

DtfBiia as;' • ".¡4 z . tti ulit««™- -0.

Figure 14-15: Select the cells containing the data that you want to chart.

Your chart will be more meaningful if you omit Column A, which contains the task ID number.

5. Click the Collapse Dialog button to redisplay the Chart Wizard.

6. Click the Series tab.

7. Click the Collapse Dialog button at the right of the Category (X) axis labels box.

8. Select the headings in Row 1 that contain the labels for the earned value fields. Your selection will probably include cells C1 through J1.

9. Click the Collapse Dialog button to redisplay the Chart Wizard. The Series tab should look similar to the one shown in Figure 14-16.

10. Click Next, and fill in the other dialog boxes for the Chart Wizard.

After you finish, your chart should resemble the chart shown in Figure 14-12, which displays all earned values for one task.

Figure 14-16: The Series tab after you have selected X-axis labels.

To create the chart shown in Figure 14-13, use the Chart Wizard again. Then, in the second Chart Wizard dialog box, use the following settings:

♦ On the Data Range tab, select Columns for the Series in option. For the Data Range, select the cells containing the earned value information that you want to chart. In the chart that is shown in Figure 14-13, I charted only about half of the available tasks to keep the chart cleaner.

♦ On the Series tab, remove all series except for one from the Series list (otherwise your chart will be very messy). In the Category (X) axis labels list box, select the cells containing the task names (cells in Column B in the pictured worksheet).

Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook


Post a comment