• 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

NARAYANK991

Excel Ninja
Hi Steve ,


Can you check the file , and let me know what remains to be done ; please give full details such as cell addresses from where data is to be used , and cell addresses where outputs should appear.


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


Narayan
 

PyratSteve

New Member
HI,


I'm not spotting what has changed???


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 ,


When I open the file , I find :


1. The Shell UWILD project should be in the top position. ; it is.


2. The % in C7 should reflect the total % complete. ; it is ; however , this is in D7.


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


Can you check and confirm ?


Narayan
 

PyratSteve

New Member
Hi,


Happy with your points 1 and 2. before 3 can happen the following is needed:


Cell F7 (months info) needs to be replaced with a Gauge that conveys the Original Margin as a Numerical figure and also shows the current margin like this; https://docs.google.com/viewer?url=http%3A%2F%2Fchandoo.org%2Fwp%2Fwp-content%2Fuploads%2F2008%2F08%2Fgauge-chart-template.xls


In the ' Budget's' Blue box I'd like the 'Textbox 61' and 'Group 25' to be a combined projects figure of in the same format as the paragraph above.
 

NARAYANK991

Excel Ninja
Hi Steve ,


The gauge chart requires some amount of space ; I find that there is very space in your dashboard in the location F7 , as well as the Budget box ; I doubt that the real beauty of the gauge chart will be shown when it is reduced to such a small area.


Narayan
 

NARAYANK991

Excel Ninja
Hi Steve ,


I tried reducing the gauge chart to a size where it will fit in the available space , and the data label will not be legible !


Narayan
 

PyratSteve

New Member
Fair point. How about 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/
 

PyratSteve

New Member
Usual jobs have a sell of 25% margin and the scale should be between 0 to 50%.


Essentially I'd like to be able to represent that a job was sold at x % and the red or green bar showing if a project manager has been able to increase the margin or if the profit erodes. Does that make sense.
 

NARAYANK991

Excel Ninja
Hi Steve ,


So , an actual margin less than 25 % would be in RED , while anything in excess of 25 % would be in GREEN ?


Narayan
 

NARAYANK991

Excel Ninja
Hi Steve ,


The actual figures from your file ( unless these are dummy figures ) are :

[pre]
Code:
Planned Margin	Actual Margin
18%              20%
19%              18%
88%               6%
19%              18%
21%              16%
18%              20%
-1%              22%
26%              16%
[/pre]
I feel that the difference is too small to be visible on a bar chart ; it would be better to just show the actual margin as a % value , and use CF to either color it GREEN or RED based on whether it is above the planned or below the planned margin.


Narayan
 

PyratSteve

New Member
Hi Narayan,


They are very much dummy figures. The 25% Planned margin will be the case most of the time. I'm quite keen on sticking to the bar graph if possible


Steve
 

NARAYANK991

Excel Ninja
Hi Steve ,


Can you check the file now ?


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


One chart has been placed for the first project ; similar charts will have to be created for the remaining projects ; there are two series for each project , one for the positive value , and the other for the negative value. The former will be in GREEN , while the latter will be in RED.


All the values are with respect to a center point of 25 %.


Narayan
 

PyratSteve

New Member
Hi,


Checked and I like where it is headed. I have tinkered a bit but have hit a few roadblocks.


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


1, The Dashboard shows the Margin in F7 as 18.2%. This is correct. The graph below shows the project is in the red but the Actual Margin (Plans J46) is an increase to 19.6%. It should be slightly Green.


2, Cell F7 is looking to the Plan table. Shouldn't it look to a cell on the Calculations tab?


3, I copied the 60% image to sit next to the bar graph to better indicate the Actual Margin. It should be simple to have it correlate to the Actual margin but I couldn't do it.


4, Rolling this across all projects and then updating the Budgets and Margins Box to remove the "$1.2 Spent" and the 60% complete graph and replacing them with the Total of all projects margin and the red and green graph.


Steve
 
Top