• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Detailed Project Portfolio - % Complete

MattGault

New Member
I am looking for any suggestions on a slight modification to the Detailed Project Portfolio dashboard. I am looking for a better way to report % Complete on a project as a way to measure Project Performance. What I would really like to be able to do is calculate Schedule Performance Index (SPI) using the data that is available within the Portfolio Dashboard and then determine if the Project is Red, Yellow, Green (ie SPI above .9 would be Green, between .9 - .8 would be Yellow and anything below .8 would be Red).


I just can't seem to wrap my brain aroudn the formula....


Any thoughts, suggestions, comments are welcomed.
 

NARAYANK991

Excel Ninja
Hi Matt ,


Since your question is related to a product from Chandoo.org , it can probably be answered by those who have used this product earlier ; in case others have to answer , you would need to upload your file ; is this possible ?


Narayan
 

MattGault

New Member
This is the Detailed Project Portfolio Dashboard that is in the Excel Project Management Templates available on Chandoo.org. As you are aware this is a licensed product from Chandoo_Org, so it would not be in my best interest to publically share that file on here! A protected demo is available on Chandoo.org.


However, my question can be answered in more general terms - acess to this specific Dashboard is not really necessary in my opinion. I am trying to fabricate a formula to use within the dashboard to calculate Schedule Performance Index.


Otherwise, you are correct - if someone would like to understand how I would insert a SPI formula back into the Dashboard or draw data from within the Dashboard to use, they would have had to use this product earlier.
 

NARAYANK991

Excel Ninja
Hi Matt ,


So what kind of help are you looking for ?


I understand that you are trying to come up with a formula to calculate the Schedule Performance Index ; what kind of ideas are you looking for ?


You can either discuss in more detail or wait for someone who has done this before to reply.


Narayan
 

MattGault

New Member
Hi Narayan:


Ok I'm thinking about (2) things:


1) Obviously, SPI = Earned Value (EV) /Planned Value (PV) so that is the basic formula. I am assuming this should be easily calculated using the data contained within the dashboard. So I am looking to see if someone has already calculated SPI in the Detail Portfolio dashboard and if so could I steal their formula. If not, I am looking for suggestions on how to incorporate this formula into the Detail Portfolio Dashboard.


2) On the more theoretical side - Earned Value deals strictly in terms of cost, even when measuring performance to schedule. This means that all the metrics used to measure schedule performance are cost based. However, when the executive sponsor wants to know whether our project is on schedule or not they aren't interested in hearing how many dollars we're ahead or behind! They want to know is it on schedule or not. The Schedule Performance indicators compare two absolutes: the work actually performed and the work that was scheduled to be complete by the current date So I was looking for some tricks that will let me convert the cost based information to a time base to calculate SPI.
 

NARAYANK991

Excel Ninja
Hi Matt ,


Sorry , but I cannot help in this ; hopefully someone else will be able to. In the meantime , have you gone through this link ?


http://office.microsoft.com/en-in/project-help/applying-earned-value-analysis-to-your-project-HA001021179.aspx


Though it refers to MS Project , I presume the same concepts must have been implemented in the product you are using.


The following link explains how to convert measures which are cost-based to time-based measures.


http://www.dummies.com/how-to/content/earned-value-management-terms-and-formulas-for-pro.html


Narayan
 

MattGault

New Member
Hi Narayan:


Thank you for your reply. MS Project actually has a report you can generate to assist with EV, however this is a little different in this dashboard. But yes - the formula/calculations are still the same in the end. I may just go ahead and create a few other tables to calculate SPI since this is the only way I can figure out how to best calculate EV - was hoping there may be some other best practices.


The second link you suggeted is an excellent source. Thank you for finding this and sharing!I will definitely use this information.
 

MattGault

New Member
SPI = EV/PV


I can calculate Earned Value (EV) easy enough by inserting a formula in the calculations worksheet of teh Detailed Project Portfolio Dashboard:

=SUMPRODUCT(G29:G48,$H29:$H48) [%Complete, Total Budgeted Cost]


I am stuck scratching my head trying to figure out how to calculate Cumulative Planned Value (PV). In the Dashboard we only input Total Budget for a task and do not break it down over time.
 
Top