Problem exporting to Excel

[Jan M.]

Hi,

I want to make a graphic of SPI and CPI over time. When I export SPI and CPI
from MS Project to Excel( using "analyse timescaled data in excel"), the
totals are always zero.

So I tried to export CBWS, CBWP and ACWP and do the calculations in Excel.
The problem is that there are differences between values in MS Project and
values in Excel.

ACWP seems always correct
BCWS varies from time to time
BCWP is never the same

I have tried changing the status date a couple of times: numbers are
changing all right but are never correct.

Anyone knows how to solve this problem?

Thanks.

Jan M.

 

[John]

Hi,

I want to make a graphic of SPI and CPI over time. When I export SPI and CPI
from MS Project to Excel( using "analyse timescaled data in excel"), the
totals are always zero.

So I tried to export CBWS, CBWP and ACWP and do the calculations in Excel.
The problem is that there are differences between values in MS Project and
values in Excel.

ACWP seems always correct
BCWS varies from time to time
BCWP is never the same

I have tried changing the status date a couple of times: numbers are
changing all right but are never correct.

Anyone knows how to solve this problem?

Thanks.

Jan M.

Jan,
What do you mean when you say the totals are all zero? What are you
referring to when you say "totals"? I just tried an export on a sample
file and the SPI values came through as expected.

With regard to differences in values, the export values are the same as
those seen in either the Resource or Task Usage views. When you say the
values are not always correct in Excel, do the Excel values match those
in the Usage view? If not, it could simply be a rounding issue. Excel is
generally more precise than Project (at least in the format of numerical
data). What you might be seeing in Project is a rounded value but in
Excel the value is shown to more precision.

Hope this helps.
John
Project MVP

 

[JulieS]

Hi Jan,

To echo John's comments, I agree that variations between Earned Value in
Excel and Project are usually rounding problems. The small rounding
variation in Project can accumulate over time.

As far as the SPI and CPI over time, yes the last rows (Total) in the
exported Excel spreadsheet is zero and that is what appears in the graph.
Odd, I know. If I want to graph CPI and SPI for the entire project I export
the time phased data to Excel and then copy the timescaled Project Summary
CPI and SPI into the Excel file, paste over the zeros in the Total rows and
graph that.

Hope this helps. Let us know how you get along.

Julie

 

[Jan M.]

Hi JulieS, thanks for answering my question.

Please take a look at the values I got from MS Project:

BCWP=184.38$
BCWS= 413.28$
ACWP=188.50$
CPI=0.98
SPI=0.54

Now, here are the values I got from Excel:

BCWP=132.85 $ (exported)
BCWS= 415.10$ (exported)
ACWP=188.50$ (exported)
CPI=0.70 (calculated)
SPI=0.32 (calculated)

I do agree that the small variation between 413.28$ and 415.10$ (BCWS) is
probably due to a rounding problem.

But the variation between the BCWP's is more problematic since it's making
my CPI drop from 0.98 to 0.70 and my SPI drop from 0.45 to 0.32. I don't
think this one is caused by rounding.

As for the graph, I did exactly as you said: I copied the timescaled Project
Summary CPI and SPI into the Excel file, pasted them over the zeros and it
worked just fine. The results were the same I got when exporting from Excel.

So I still don't understand the dispcrepency between Project and Exel. I
would like my graph to show the values indicated by MS Project ( or the other
way around...)

Hope you have an idea.

Jan M.

 

[Jan M.]

Hi John,

I think I wasn't very clear: when I said the totals wee zero, I meant the
line "Total" read only zeros. Thus, I had to export BCWP, BCWS and ACWP to
calculate and graph CPI and SPI.

Take a look at the values I got from MS Project:

BCWP=184.38$
BCWS=413.28$
ACWP=188.5$
CPI=0.98
SPI=0.45

Here are the values I got from Excel:

BCWP=132.85$
BCWS=415.10$
ACWP=188.5$
CPI=0.70
SPI=0.32

I agree that the rounded valued might explain the slight difference between
the BCWS. But the difference between the BCWP is too big to have been caused
by rounded values.

My graph shows CPI at 0.70 but MS Project reads CPI at 0.98: I would like my
reports to show the same values.

Hope you have an idea.

Jan M.

 

[Jan M.]

Here is new information to my problem:

When I say that the MS Project value for BCWP is 184.38$, I'm mean that the
field BCWP reads 184.38$ in the task sheet view.

But the time scale reads 132.85$, which is the value I get in Excel.

Which one is good and how come the values are different?

Thanks

 

[JulieS]

Hi Jan,

I read your post about difference in values appearing in the Task Sheet as
compaired to the a timescaled export.

When you view the BCWP etc. in the Task Sheet (Earned Value table applied)
you are seeing the data as of the status date. If the timescaled export had
a different status date at time of export that will definitely account for
the difference.

When I view the Task Usage View with the Earned Value table (adding the SPI
and CPI fields as well) applied to the left side and then view ACWP, BCWP,
BCWS, SPI and CPI in the timescaled portion the data (from the *last* column
in the timescaled view) matches the data in the Earned Value table.

When I commented that differences will appear between data in Excel and data
in Project, I was referring to BCWP, BCWS, ACWP, SPI, and CPI you *calculate*
in Excel. When you *export* data from Project to excel no calculations are
performed, just the raw data is dumped. If I compare timescaled data in
Project (Task Entry view) to the data exported to Excel the values match.

Hope this helps. Let us know how you get along.

Julie

 

[John]

Here is new information to my problem:

When I say that the MS Project value for BCWP is 184.38$, I'm mean that the
field BCWP reads 184.38$ in the task sheet view.

But the time scale reads 132.85$, which is the value I get in Excel.

Which one is good and how come the values are different?

Thanks

Jan,
If Julie's comment about the "time stamp" effect isn't applicable, then
I would have to look at your file itself to offer any further
suggestions.

John
Project MVP

 

[Jan M.]

Hi John,

I have looked into the problem a little deeper:

I was using physical percent physical complete to calculate earned value:
using % complete instead solved the matching problem between the earned value
table and the time scale. But I still have the problem that made me choose
physical % complete instead of % complete:

I have applied work contours to some activities: mostly bells and late
peaks. So the work was planned to start very slowly. But we managed to put in
much more work than planned in the first activities. Unfortunately, the
earned value calculation with % complete was not showing reality: CPI was way
too low and FAC was saying my budget would almost doublec.

Personnally, I think that % work complete is more precise than % complete in
earned value calculation. Unfortunately, MS Project only allows % complete or
% physical complete in earned value calculations. So I decided to paste the
field % work complete in the field % physical complete and use % physical
complete to calculate earned value. CPI and SPI seemed much more realistic
this way. But I ended up with the problem we've been discussing for a couple
of days.

Have you ever heard of people having this discrepency problem between earned
value table and time scale using % physical complete.?

Since we are using Project Server, it is very easy to us to collect actual
work. So I want to use the proportionality rule for earned value calculation.
Any suggestions on how to use % work complete as a means of earned value
calculation?

Thanks

 

[John]

Hi John,

I have looked into the problem a little deeper:

I was using physical percent physical complete to calculate earned value:
using % complete instead solved the matching problem between the earned value
table and the time scale. But I still have the problem that made me choose
physical % complete instead of % complete:

I have applied work contours to some activities: mostly bells and late
peaks. So the work was planned to start very slowly. But we managed to put in
much more work than planned in the first activities. Unfortunately, the
earned value calculation with % complete was not showing reality: CPI was way
too low and FAC was saying my budget would almost doublec.

Personnally, I think that % work complete is more precise than % complete in
earned value calculation. Unfortunately, MS Project only allows % complete or
% physical complete in earned value calculations. So I decided to paste the
field % work complete in the field % physical complete and use % physical
complete to calculate earned value. CPI and SPI seemed much more realistic
this way. But I ended up with the problem we've been discussing for a couple
of days.

Have you ever heard of people having this discrepency problem between earned
value table and time scale using % physical complete.?

Since we are using Project Server, it is very easy to us to collect actual
work. So I want to use the proportionality rule for earned value calculation.
Any suggestions on how to use % work complete as a means of earned value
calculation?

Thanks

Jan,
To be honest, I have never used the % Physical Complete field. However a
little research into the on-line help file revealed the following
information (copied for reference only from the on-line MS Project Help):

"Remarks While the default earned value method field is % Complete,
you can change it to Physical % Complete for any tasks that apply.

You can also set the earned value method for a task on the Advanced tab
in the Task Information dialog box.

To set the default earned value method for all new tasks, click Options
on the Tools menu. Click the Calculation tab, and then click Earned
Value. You can also use this dialog box to choose which of the 11
available baselines should be used for earned value calculations.

The Physical % Complete field is available by default on the Tracking
table."

If you change the earned value method in your file, I'm betting it will
resolve the data discrepancy you are seeing.

You also mentioned you are using Project Server. Whether that also has
something to do with the problem I don't know. I have no experience with
Project Server but several of my fellow MVPs do. Hopefully one of them
will jump in and offer some advice.

Hope this helps.
John
Project MVP

 

[Jan M.]

John, thanks for taking the time.

I was already aware of how to use physical % complete. I had changed both
the default earned value method for new tasks and the earned value method of
all existing tasks. It's only when I set everything back to % complete that
the discrepency disappeared. You have to know that the field physical %
complete is not calculated by Project. That's why I had to copy and paste %
work complete in P.% C.

Since you've never used P.% C. I have to suppose you have always used %
complete. Personnally, I think % complete (as calculated by MS Project) is
not a good indicator because it's based on duration. I do think that % work
complete is better because value is earned as work is done, not as time goes
by! What do you think about the way MS Project calculate earned value?

Thanks.

Jan.

 

[Ian Coletti, PMP]

One of the guys on my team and I were looking at this very problem. One of
the things he discovered in the time phased section is that the BCWP = BCWS
and the task % Complete was not 100%. If you look at a Task Usage view and
add up the BCWP from each of the resource assignments on a task, the BCWP
totals DO NOT match the BCWP in the summary of the same task. We think this
is a major bug!!

Test it out and let us know if you are experiencing the same thing!

Cheers,

Ian

 

[Ian Coletti, PMP]

This is in fact a bug... at least in MSP 2002.

You have to call for the hotfix:

http://support.microsoft.com/default.aspx?scid=kb;en-us;867822#XSLTH3124121123120121120120

Enjoy!

Ian