• 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.

Project Portfolio Dashboard Enhancements

PyratSteve

New Member
Hi,


I'd like some assistance in enhancing the Project portfolio Dashboard please. I'd like to add better financial tracking features.


Essentially, I'd like to be able to add (On the 'Project Plans' tab)a Cost column, an 'As Sold Margin' and a 'Actual Margin' columns. The 'Actual margin'should be pro rated against the '% Done' This would then feed through to the 'Dashboard'tab and replace the Budgets box with an overall margin and the individual project Budget and spend graph with the newly created 'Sold Margin' and 'Actual Margin'.


Help please.


Thanks


PyratSteve
 

Faseeh

Excel Ninja
Hi PyratSteve,


Welcome to the Forums! can you share a sample file with forum so that we can work it out.


Regards.
 

NARAYANK991

Excel Ninja
Hi Steve ,


Going through your post , I am not able to understand at which step of your post you need help ; you have mentioned the following 3 To-do steps :


1. Add (On the 'Project Plans' tab) a Cost column, an 'As Sold Margin' and an 'Actual Margin' column.


2. The 'Actual margin'should be pro-rated against the '% Done'


3. Replace the Budgets box with an overall margin and the individual project Budget and spend graph with the newly created 'Sold Margin' and 'Actual Margin'.


Can you explain ?


Narayan
 

NARAYANK991

Excel Ninja
Hi Steve ,


Can you give the calculation for each of the terms you have mentioned viz. As Sold Margin , Actual Margin , Overall Margin ? It would be nice if this calculation could be with respect to cell / column / range references in your workbook.


Narayan
 

PyratSteve

New Member
Hi Narayan,


If cost is A1, and the as sold price is B1


Margin=(B1-A1)/B1


If the actual costs on a job don't change from the prediction when the job was sold the 'As Sold Margin' will equal the 'Actual Margin' but if things change on a job and costs increase (for example) then the 'Actual Margin' will be less than the 'As Sold Margin'. The 'Actual margin'should be pro rated against the '% Done' .


The 'Overall Margin' is a calculation factoring in All projects.


thanks
 

NARAYANK991

Excel Ninja
Hi Steve ,


What about actual references to your workbook ?


What values should appear in the Cost column , once this is introduced in the Project Plans tab ?


Suppose we assume that for Project 1 , the inserted columns are I , J and K ; what should appear in I6 , J6 and K6 ?


Where should the Overall Margin be displayed ?


Narayan
 

PyratSteve

New Member
Hi,


My apologies. I uploaded the wrong file.


Please see here https://docs.google.com/file/d/0BwPHNW9l7JFGRzg4bURVOWJTek0/edit?usp=sharing


I have put some dummy data in Project 1 (Shell UWILD). As you can see I have renamed the columns to be clearer.


Activity 1 is 100% Complete and the Margin and Actual margin Calculations as a result. The rest of the 'Actual Margin'are incorrect as they are not factoring in the fact that they are not 100% complete. I need help with this and the displaying of the information on the dashboard.


Thanks


Steve
 

NARAYANK991

Excel Ninja
Hi Steve ,


Can you check your file here ?


https://docs.google.com/file/d/0B0KMpuzr3MTVYkIxY1kzV1k4OTQ/edit?usp=sharing


I have not made any changes to display any new information on the Dashboard ; can you explain what information you want to display , and in which cells , how ?


Narayan
 

PyratSteve

New Member
Nice work thanks.


To make sure we are on the same page; on the Dashboard tab, change 'Settings' drop down menu to 'Original Order' and 'Monthly'. The Shell UWILD project should be in the top position.


1, The % in C7 should reflect the total % complete.


2, The $484K and the graph below should reflect the Original Margin as a number and below it an indicator showing a + or in the same format as the last column of this dashboard http://chandoo.org/wp/2011/08/10/mlb-pitching-stats-dashboard/


3, In the ' Budget's' Blue box I'd like the same info but for all of the combined projects.


4, Can you please explain the calculations in Row 48 of the Project Plans Tab?


Cheers


PyratSteve
 

NARAYANK991

Excel Ninja
Hi Steve ,


The formulae are :


=SUMPRODUCT(plan1[Duration]*plan1[% Complete])/plan1[[#Totals],[Duration]]


What this does is multiply each Duration by its corresponding % Complete to arrive at the total number of days that have been taken by the project so far ; dividing this by the total number of days gives a percent rating ; this will not be accurate , since several activities may be going on in parallel , but for want of anything more accurate , I think this will have to do ; probably if you can have the activity dependencies , we can arrive at the actual time taken vs. the project time , but I doubt that this can be done using only formulae ; VBA can possibly do this.


The next formula is :


=(plan1[[#Totals],[Sell Price ($)]]-plan1[[#Totals],[Cost ($)]])/plan1[[#Totals],[Sell Price ($)]]


which is just the formula you posted for calculating margin ; since we want an overall margin , we consider only the totals for the cost and selling prices.


The last one is :


=SUMPRODUCT(plan1[Cost ($)]*plan1[% Complete])/plan1[[#Totals],[Sell Price ($)]]


which is to calculate the Actual Margin as on date ; it multiplies each activity cost by the percent completed , and divides the sum of all these by the overall selling price total.


Regarding the others , give me some time to work on them.


Narayan
 

NARAYANK991

Excel Ninja
Hi Steve ,


The first point that is to be taken up is the following :


All the column headings ( labels ) should be identical in all the individual project tables ; if we have a heading such as % Complete in one table , and % Done in another , it will become difficult to use these while creating the formulae.


For example , the overall project budget figure can be put in G6 on the Project Details tab by :


=INDIRECT("plan"&tblProjects[[#This Row],[ID]]&"[[#Totals],[Sell Price ($)]]")


However , we cannot copy this formula down , since the individual project tables still do not have their totals put in.


Narayan
 

PyratSteve

New Member
Hi Narayan,


Are you saying the format of each of the projects on the 'Project Plans' tab simply require the same column headings? That's not a bother to simply copy the same project data from the Project 1 Shell UWILD to the others as none of the data is real.
 

NARAYANK991

Excel Ninja
Hi Steve ,


Yes ; if the table column headings are identical for all the projects , we can use formulae which refer to the columns instead of using addresses such as $G7:$S33 ; secondly , we can copy the formulae from one table to the next.


Narayan
 

PyratSteve

New Member
Hi,


I have updated the tables but as you can see but I'm missing something in updating your formulas on row 48. It's a mess. Changing the source from Plan 1 to Plan 2 etc doesn't have the desired effect. What am I missing?


https://docs.google.com/file/d/0BwPHNW9l7JFGbW41dmFOblRTaDQ/edit?usp=sharing
 

NARAYANK991

Excel Ninja
Hi Steve ,


Straight off , I see that plan1 has been defined as : ='Project Plans'!$B$6:$J$45 , whereas plan2 has been defined as ='Project Plans'!$M$6:$U$46 ; the TOTALS row is actually a table row.


Once the TOTALS row , as it stands now , is deleted , and you check the TOTALS row checkbox , you can copy the formula from plan1 to plan2 ; do the same for all the other project tables.


Narayan
 

PyratSteve

New Member
Ah. of course. Thanks


https://docs.google.com/file/d/0BwPHNW9l7JFGOXp2b2taZkV1UUE/edit?usp=sharing


I think I'm in a position to ask these questions again:


To make sure we are on the same page; on the Dashboard tab, change 'Settings' drop down menu to 'Original Order' and 'Monthly'. The Shell UWILD project should be in the top position.


1, The % in C7 should reflect the total % complete.


2, The $484K and the graph below should reflect the Original Margin as a number and below it an indicator showing a + or in the same format as the last column of this dashboard http://chandoo.org/wp/2011/08/10/mlb-pitching-stats-dashboard/


3, In the ' Budget's' Blue box I'd like the same info but for all of the combined projects.
 

NARAYANK991

Excel Ninja
Hi Steve ,


Can you check your file here ?


https://docs.google.com/file/d/0B0KMpuzr3MTVOUFkbmhLS1h5Qnc/edit?usp=sharing


I have not done all the 3 points you mentioned ; you can check that the formulae on the Project Details tab are correct.


Narayan
 

PyratSteve

New Member
Hi Narayan,


Looks like my boss has promised that this new dashboard will be ready today. Sorry to be a pain, but if you could help I would be really grateful.


PyratSteve
 
Top