TEPCO - Generating S-Curves in Excel with P6 Data

thanks for watching this is another Primavera p6 training module presented by TEPCO this module is on generating s curves in Excel with p6 data my name is Dan stammen as' and I'm one of the implementation specialists and trainers at TEPCO if you'd like more information about TEPCO please go to WWE COO us before we get started in p6 in Excel let's talk about the different components of an S curve plan value is the amount of hours targeted or planned to be complete as of the data date so in order to understand the planned value hours a baseline target must be assigned to the p6 project since the planned value comes from the baseline the S curve is typically static or in other words does not change but when change management requests cause a change to the schedule the baseline should be updated with these changes and it can cause the S curve to change for the land value so because of this we always create two baselines one is the original baseline which has never changed and the second one is a working baseline or sometimes called a change management request baseline or schedule change baseline and that one gets updated with added work or removed work and that can fluctuate or be dynamic here's an example of the planned value in an S curve this is showing both planned values for the two baselines so we have a working CMR baseline change management request baseline and we have the original baseline so you can see that the CMR baseline has an increase in hours that's because work was added to that baseline the next component in the S curve is the Earned value line the Earned value is the amount of hours or cost achieved as of the data date based on progress entries into p6 you can only earn against the baseline hours so if a task is added to the current schedule but not added to the baseline then there are no planned hours in the baseline and we cannot achieve earned hours because of this the baseline must be updated with added work derived from change management requests this is why we show both the original baseline and the change baseline here's the same s curve example with the Earned value line added you can see that there's scheduled variants here between the planned value from the CMR or change management request baseline against the earned value to date the graph indicates that the schedule is not earning hours at the rate that they had planned to earn them let's look at a simple example that talks about all three components planned value earned value and actual value let's say you hired a contractor to install a fence in your backyard and you're gonna pay him by the hour you and the contractor agree it's gonna take about ten hours on a single day that's the planned value so you're gonna try and earn ten hours in that day at the end of the day the contractor is 80% complete but he was working for 11 hours so he earned 80% which is equal to 8 hours of work earned that's the Earned value hours but we have to pay him for 11 hours because for some reason we had him there a little bit longer that day this is the actual value hours so the planned value equals 10 the earned value equals 8 and the actual value equals 11 the scheduled performance index is the earned value divided by the planned value or in this case it would be 8 divided by 10 which equals point 8 anything below 1 indicates that you're not earning when you intended to now the cost performance index is the earned divided by the actual or 8 divided by 11 in this example which equals 0.72 below 1 indicates that you are not completing the work for the cost that you expected to so in this example for every dollar we spend we are only getting 72 cents of value how much more time and money will it take the contractor to complete the fence a typical answer I get when I'm doing training is that well it's another 2 hours because he earned 8 but based on the trend that is not correct if we apply the following formula for a performance factor based on the cost Performance Index CPI the result is the performance factor multiplied by the budget at completion - the IRR in value hours where the performance factor is 1 divided by the CPI so in this example the performance factor is 1 divided by 0.72 or the answer is 1.38 9 times 10 minus 8 equals 2.77 so the estimate to complete is approximately 2 hours and 45 minutes add the 11 actual hours to this and the estimate at completion is 13 hours and 45 minutes that's 3 hours and 45 minutes over the planned budget here's the same graph we've been looking at but now I've added the actual value you can see that there is a difference or variance between the actual value as of the data date and the earned value as of the data date these two numbers indicate the CPI the cost performance index which also gives us the performance factor so we can determine how much more it may cost us by the time the project is finished in this example the actual hours are higher than the earned hours this means that we are not accomplishing the work for the amount of money that it's costing us also the earned hours are below the planned value hours which is the red line this means that we are not getting the work done at the rate or when we wanted to get the work done with the earned value below the planned value and the actual value higher than the earned value this is the worst case scenario a project could be in now let's talk about the difference between a change to the schedule and re estimating so for turnarounds and outages we do not rest amay Tremayne hours on activities if a contractor comes back and says well p6 is saying that we need to do 4 more hours on this but after looking at it we think it's going to take 6 we do not go into p6 and readjust the remaining hours to 6 hours all added hours or even removed hours must go through a change management process and any changes to the hours are also updated in the change or the working baseline the fence contractor may tell me he expects another 5 hours remaining although p6 says there are 2 hours remaining and then when we applied the performance factor it indicated that we had 2 hours and 45 minutes remaining so in that case I would not add or change the remaining hours based on what the contractor is telling me we're just going to try and get the work done as quickly as we can and if we see it go over the baseline at the end that's what the results will be if I come to the contractor in the middle of the work and say oh I need a gate installed on this fence that is a change request so we must determine what the additional hours are for that work enter a gate installation activity into the current schedule with the added hours and enter that new activity into the change or working baseline as well so that we can earn against that activity why do we use Excel to build RS curves instead of p6 so we can generate s curves directly from p6 by using the activity usage profile there are two primary reasons for using Excel the p6 activity usage profile will only display a single baseline or the plant value so in order to display the original baseline and the change baseline we must extract the data to excel also it is not common to enter actual timesheet hours into p6 which we talked about earlier this is especially for a refinery or offshore turnaround maintenance and shutdown schedules by extracting the planned value and the Earned value labor units from p6 and placing them in Excel we can then extract actual timesheet hours from a time tracking system and merge them into the same excel file now the s curve can display both the plan value and the Earned value from p6 along with the actual value or actual labor units from the time tracking system the tricky part is to figure out how we can match our actual hours from the time tracking system to the plan and earned value hours from p6 if your time system is based on work order numbers we recommend that you build the same work order number structure in p6 typically by using the cost accounts in p6 this way you can extract data from p6 summarized by specific cost accounts which are the work order numbers bring them into Excel and then you can bring in your actual hours based on work order numbers and match them up to the work order numbers from p6 in the excel file and you can build multiple s-curves I'm going to take you into p6 in a few minutes but I want to talk about the upcoming example that I'm going to be building in p6 and Excel the p6 example is of a downstream or finally turn around that I have the durations have been scheduled in hours although you could be scheduling your projects in days the same concepts will apply for building the s-curves but I just want you to be aware that this particular p6 file has been scheduled in hours the change baseline has been set as the project baseline so it's basically set as the main project baseline the original baseline has been set as the primary user number one baseline this way we have both the change baseline and the original baseline attached to the schedule the data date is set to 7 a.m. and progress is updated on a 24-hour cycle because the data date is set to 7 a.m. a resource shift calendar has been set up solely for the sake of showing the p6 Gantt chart and histogram by shift I'll explain more about this when I get into p6 next before I get started showing you the report and how I'm going to extract the s-curve data I want to show you a couple things about this project file first I'm going to go up to projects and then assign baselines I mentioned earlier that the project baseline is going to be set as the current working or the change management request baseline which I have set here in this drop down window I also mentioned that the primary user number-one baseline should be set as the original baseline which I've set here so I have two baselines on this project plan both are assigned the change or working baseline is a sign as a project baseline and the original baseline which we do not touch or edit has been set as the user primary baseline I spoke earlier about using resource shifts on my time scale so I want to elaborate on that right now I have the time scale set to show by day and you'll notice my data date slices down in the middle of Monday that's because the data date is set to 7:00 a.m. and then of course Monday runs from 12:00 a.m. Monday to 12:00 a.m. on Tuesday notice how the histogram down below has a bar here that is partially green and partially blue the Lu is the progress that has been achieved from midnight to 7:00 a.m. on Monday and the Green is the remaining work that comes from 7:00 a.m. forward when we look at our time scale by days but our data date is offset it's not set to midnight it slices down the middle of this bar and to some people that can be confusing to resolve that what I've done is set up some shift calendars only for the sake of using them in the time scales so if I go to enterprise resource shifts I've set up to shift calendars here one is a 24-hour shift calendar and the other one is a to 12:00 shift calendar let's look at the 24-hour shift calendar if I hit modify all I've done is I've set a shift calendar to start at 7:00 a.m. but the duration is for 24 hours I'll close this and if I close and then go out to my time scale I'm going to edit the time scale and instead of using a date interval of week / day I'm going to change it today / shift once I do that the shift calendar becomes available down below here and I'm going to set that to the 24-hour 7:00 a.m. to 7:00 a.m. shift and watch what happens to the time scale now the time scale has a third element on the bottom of it it says 7:00 in there that indicates that it is a 7:00 a.m. start and notice how the data date comes down right where the shift calendar start is and then it slices down and now it has separated that bar because we have applied these shift calendars and now all of the blue is to the left of the data date and all of the green is to the right of the data date I'll go ahead and change it to the - shift as well so you can see what that looks like now this is a to shift calendar and because we set it up for 7:00 a.m. and for 1900 or 7:00 p.m. you'll notice it says 7:00 here and then it says 19:00 this right here between seven and nineteen is the start of the night shift so that would be 7:00 p.m. and again it also ensures that all of the blue is to the left of the data date and all of the green is to the right of the data date so if you've ever wondered why a bar stacks and shows a little bit of blue on the bottom and the rest is green above it it is because of where the data date falls within that day or even that week and we can resolve that visual problem by applying shift calendars also the reason I have set up shift calendars is because when we build out the report data to go out to excel for the s-curves we want to make sure that the data also goes into Excel in a manner that shows the Earned value behind the data date and the remaining work ahead of the data date not slicing down the middle because this can cause a slight problem with how the s-curve displays right around the data date so we want to make sure that we split the earned from the remaining to ensure that when we get to Excel the data is display properly so we will be using these shift calendars again when we go into the report editor before I run any reports for an updated reporting cycle there are a few quality checks that I do in P 6 first of all I want to make sure I don't have any actual dates or actual hours ahead of the data date so I typically build a filter called actuals greater than the data date I'll hit modify here so you can see what that looks like it's a pretty simple filter you need to make sure it says any of the following right here and by hitting that drop down to line items in the filter are where the actual finish is greater than the data date or the actual start is greater than the data date make sure this does not say and here that gets changed with this drop down if I had all of the following it would say and that would not work so make sure that it says any of the following actual finish is greater than the data date or the actual start is greater than the data date what that does is it looks for anything with actual dates ahead of the data date if I apply that and I see anything show up in the list I need to correct those dates and get them behind the data date so I need to adjust the actual starts or actual finishes to be behind the data date looks like I'm okay there I've mentioned a couple times earlier that anytime we add work to the current project schedule or add hours to it we need to add that worker hours to the change management or working baseline to ensure that that has been done I make sure that I look at the budgeted labor units field summary the baseline project labor units and the Act completion labor units all three of these numbers should be the same all the way across you'll notice here that the budgeted labor unit says seventy one thousand seven hundred sixteen my baseline project labor unit says seventy one thousand seven sixteen so that's okay but notice how my act completion labor units are a little bit off right here that happens sometimes when we update p6 and we put in a percent complete on an activity and it typically seems to occur from my experience on activities that have multiple resource assignments with different durations on the resource assignments themselves I have also seen it where that is not the case but these need to be corrected so usually I bring in the variance for baseline project labor units a field so I can look and find these activities that have a variance on them I like to take all my grouping and sorting off although I will put in a grand totals at the top the reason I do this is because I can click the variance baseline project labor units header and I can sort by that and I can see that I've got a positive one here positive variance and we need to make sure we correct that one and then if I hit it again I can scroll to the top and I can see I have negative variance on this one now usually what happens is on the resource assignments the ad completion labor units gets out of sync with the budgeted labor units so what I usually do is go down to my resources tab on that activity and I bring in the budget of labor you that's the total hours filled here that's been relabeled this is the budgeted labor units filled and I look at these two and I check to see what is out of sync I can see that my a completion units for this resource assignment says 14.5 but the original budgeted labor units on it was supposed to be 8 so all I do is come in here and change this to 8 you'll see the actual units or the remaining units change as I do this and that corrects the problem I'm not sure why this issue occurs when we put in percent completes and I've been seeing it for years and have not figured out a quicker way to resolve it I've looked into global changes and other things and doing it manually seems to be the best bet there is one other option though that you can use you can try this and it typically does work although I have seen some instances where it doesn't you can go to the project's view and you can select your project you can go down to the calculations tab and this option right here that says recalculate actual units and cost when duration percent complete changes if I turn that off and then turn it back on p6 gives me a warning saying all of the actual costs and unit values for the project example 10 which is the one I'm using right now will be recalculated from the duration percent complete do you want to continue I can say yes and then I can go back to the activities view and see if that has resolved the problem I can sees here because I still have that one with positive variance that that did not resolve the problem in this example the positive variance because I'm sorted by the baseline project labor units is down at the bottom so I'm just gonna scroll all the way down to the bottom and it's this last activity here that has a variance of 10 on it and again if I go down to my resource assignments I can see that I have some some issues down here with the Act completion units being out of sync with the budgeted labor units so I will manually correct these that needs to be 60 and this one needs to be 10 now if I go back up to the top I can scroll all the way up and I can see I do not have variance and I can see that all three of these numbers are the same another thing I like to check before moving on to running the reports is my actual labor units and my earned value labor units now a minute ago I was at the project's view and I showed you the setting recalculate actual units and costs when duration percent complete changes we have this turned on because we are not entering actual timesheet hours into p6 I mentioned earlier that we're going to get those from a time entry system so we want p6 to go ahead and calculate its own actual units and if it's done properly the actual units and p6 will equal and be identical to the earned value units so what I like to do is take a look at my actual labor units field at a summary level and my earned value labor units field and I want to make sure those two numbers match typically if they don't match it's because the budgeted labor units and baseline labor units are out of sync with the at completion labor units so try and correct this problem first to make sure that you don't have variance on baseline project labor units if you correct that problem that I discussed a minute or two ago usually this will correct itself but sometimes it doesn't and sometimes you do need to go down and make an adjustment to the actual labor units to make sure they match the earned I want to show you one last thing in p6 before I actually go into building the S curve raw data reports I'm showing an S curve in p6 here I'm in the activity usage profile and right now I have turned on the cumulative lines for actual and remaining early notice how my actual is in blue and it comes up here to the data date but then my remaining takes off from the bottom here because they're remaining as of the data date starts at zero and then climbs after that we cannot use the remaining early or the remaining late either one of them when we build our report because it will start down at zero and then build up from there of the data day so we want to turn this off now estimate at completion for labor units is actual plus remaining now it could have a performance factor applied if in your WBS view you change the Earned value settings but we're not doing that here so actual plus remaining equals estimate at completion so I'm going to turn that line on notice how the actuals come up which are in blue and then the remaining takes off in pink watch what happens if I turn the actuals off the at completion line still includes the actual hours because the at completion line is actual plus remaining but to be clear I'm going to turn on the actual line what I can do as well is I can turn on the Earned value line the Earned value as I mentioned a few minutes ago should equal the actual value so when I hit apply here the orange line should lay right on top of the blue one and you can see that that has happened this will happen in Excel as well and that is why we need to make sure that the actual hours and the Earned value labor units equal the same number otherwise the Excel graph will get skewed if I was entering actuals in p6 we would see a separation between the actual line and the Earned line but that's not how we're using p6 because we're not entering true actual hours into p6 another thing here is the planned value labor units has been set as a teal color and you can see that down here I just want to point out I'm gonna turn on the baseline cumulative and hit apply notice how that laid on top of the planned value plan value labor units or planned value is the same as the baseline turning both of them on is a bit redundant so we only need to see the one that says planned value labor units or we could turn on the one that says baseline when we run the report here in a few minutes I'm going to have you select the baseline hours we don't necessarily need to select the planned value hours when we're in the report wizard or the report editor B because we are going to use two bass lines so we needed to select the two bass lines cumulative data okay now is the time to build the reports that we're going to send to excel so I'm gonna go to the reports view I'm gonna hit the plus button over on the right-hand side to add a new report it will be a new report so that's the only option we can select there and then hit next and we're gonna choose activities because we want activity summary data for our s-curve it needs to be time distributed so make sure you turn on the time distributed data checkbox then hit next in the subject area we see activities here we're gonna select columns now I really don't need any column data usually if I'm gonna run any type of column specific data I'll do that in a separate report so for this one I'm actually going to remove these items I'm double clicking them to send them back over to the left-hand side now p6 requires that we have at least one column in there that's why the OK button has been grayed out so I'm just gonna bring in the project name and I'll bring project name over click OK now for the group and sort I could just say show grand totals because I'm just gonna run an S curve against this entire project plan but if I turn that on this show totals option here says bottom that's not going to come over the way that I want it to so I'm gonna turn this off and I'm actually gonna group my project now I can take the show totals over here and say set it to top that way it just comes over as single line items just like we see in Gantt views it in our activities view but I do need to ensure that I turn on show summaries only because I don't want all the activities coming with I only want the summary roll-up for the project data I also don't need the project ID or code so I'm gonna turn that off and then hide if empty we'll go ahead and turn off that wouldn't apply for anything since we're grouping my project but if we were running multiple s-curves and wanted a lot of grouping band data brought over and you may want to turn that on make sure it's turned on still so that you're not bringing over grouping bands that are blank or I have no activities in them click OK here as for the filter usually if I'm doing a turn around project I don't want the pre-phase or post phase data in this s-curve usually I just run the s-curves for the execution window and including the shutdown phase and the startup phase so if I had phase coding built into my activities I could put a filter on here to just only pull over the phases that I want but in this case I'm just gonna run the entire S curve for the entire project plan just to keep it simple I'm gonna click OK in the data options window this is where we set up how the time distributed data is going to come out we do not need to show the period total that's not going to be useful for us so I'm going to turn that off we do need to show time intervals and I'm gonna go to the time scale button now I mentioned that I'm going to run this particular report to run from the project start to the project finish for the entire project if I had filtered out the phases like for pre turnaround then I might hard-code a date in here to set the timescale start to a specific date like the start of our shutdown execution but for this example we'll just leave it as project start now the date interval needs to be changed to shift this is why I explained using shift calendars earlier as we want to set this up to come out of p6 in a shift format rather than by day because our data date is at 7:00 a.m. and the day split between midnight and midnight now the shift calendar you want to use could be either the 24-hour one that we had set up or the two by 12:00 I'm just gonna go with the 24-hour one because that's less data cells in Excel when we bring it over there so will you choose the 24-hour one and the time scale finish can be the project finish if I was only pulling in the phases for the shutdown turnaround execution and startup I might also hard-code a date here for the time scale finish but usually if I do that I hard-code the date to be three or four days after when I expect the turnaround to finish that way if the turnaround slides to the right and we're behind two or three days the s-curve data will still pick up that data at the end of the turnaround window and we'll click OK now the time interval fields this is the more complicated part here we need to pull in specific cumulative fields to run our s curve lines off of we can also pull in time interval fields but let's go with the cumulative fields first first you want to go down under units the first one we want is the cumulative base line one labor units that's what we had set our original base line as as our primary user base line number one so let's go ahead and pull over the cumulative base line one labor units the next one we need is the cumulative base line project labor units which is right here that's the one that we set as our project baseline and that's also our change baseline or our working baseline these two items will give us both of our baseline line items in the S curve the other thing we need is the cumulative at completion labor units which is right here remember at completion labor units is the actual labor units plus the remaining labor units we don't want to pull the remaining labor units over because that will start at the data day - down at the bottom of the graph and climb from there on up and that won't look right when we're in the graph the last element that we need to select is actually under the Earned value grouping for cumulative and that is going to be the Earned value labor units normally I run a separate report for the Earned value labor units and I will explain why after we get the graph built and I will come back and show you how to build the report separately now I can also pull in time intervals this is going to be the vertical bars that we see when we're looking at a p6 activity usage profile in here you don't have to bring in everything normally I would just bring in the baseline project labor units I typically leave out the baseline one project labor units because usually people don't need to see that in a daily interval type of graph the other thing I need to bring over and in this case we do need the remaining labor units because when we're looking at those by interval or by ship that's okay so these are the histogram bars that were bringing in whereas the cumulative czar the actual s curve lines so I brought over the base line project labor units the remaining labor units but I'm also going to bring in the Earned value labor units by time interval which is under the Earned value grouping band and they are right here the next thing I like to do is change the names for these fields this way when they go over to Excel I don't have to change them once I get to Excel and it also makes the names a little bit more clear for management who may not have experience with the p6 field names so the first one I'll select is the cumulative baseline one labor units that is our original baseline so I'm just going to name that one original baseline the cumulative baseline project labor units is our change baseline so I'm just going to label that one change baseline now the cumulative at completion labor units the only reason we're bringing that over is because we need the actual and the remaining but we technically don't need the actuals because our earned line is going to lay on top of that so we're really only bringing it in for the sake of the remaining hours and I'm going to change this to say current remaining and of course the labels you can you can choose whatever you want you might want to just say remaining hours or something like that click OK and then for the Earned value I'm just gonna say this is current earned and I'm going to continue on now the remaining three here are the histogram bars that will be displaying so this one is basically by day or by shift so I'm going to change the baseline project labor units to say change baseline daily I'm gonna change the remaining labor units to say remaining daily and I'm going to change the Earned value labor units to say earned daily again it's your call as to how you want to label these fields sometimes it depends on the clients even different clients even in the downstream turnaround industry have different terminology that they use now I've named all the fields so I'm going to click OK and I'm gonna hit next I need to put a title on the report usually I like to build something logical into this so we're just gonna say this is ta for turn around report - zero one and we're gonna call this one s-curve data hit next you can go ahead and run the report and that is what it should look like now I do have my decimals turned on so I may turn those off just be a little cleaner the other thing you may want to do is turn off the format numbers that way if someone else who runs this same report but their user preferences have the hours label turned on for their units then it will make sure that the hours label doesn't come over into Excel because if the H shows up on each one of those fields in Excel Excel will treat it like a text cell rather than a number cell and we're gonna hit next and we can hit save report and the report gets saved and finish now let's run the report in Senate Excel to see what it looks like here's the report that we created I'm going to right click and say run report in the run report window we have some options we can print preview which we've done already well we want to do a select ASCII text file now I'm going to leave the field delimiter as it is in the text qualifier as it is also the extension on here is a CSV file for me this is running just fine so when I do hit OK it's gonna open in Excel and I'm gonna get what I need I've seen in previous versions of p6 some issues with the field delimiter and the file the output file extension that you select and it could also have something to do with which version of Excel is installed on the machine so you may have to play with these a little bit to figure out which one works properly and drops the data into Excel properly by the way you can also change the file extension to XLS if you did that you would need to change at least for me I would need to change the field delimiter to tab otherwise it's not going to drop into Excel properly you can also change this to a txt file txt and it will open in a text window and then you can copy and paste from the text window into Excel CSV is going to work just fine so I'm going to click OK and this is what our data looks like when it gets into Excel I've ran the report again in p6 doing a print review and I took a screenshot so I want to bring that into view for you to see and I want to point out a few things you can go into the report editor and clean out certain things like the report title and there's also one other thing you need to be aware of I'm on version 15.1 here in previous versions of p6 this field right here where it gives the time scale range used to come over into Excel as two cells and it would cause the dates up here at the top to get offset from the hours by one cell so they would be shifted over by one cell because this field would come in as two in 15.1 I believe Oracle has done something to prevent that from happening because now all we get is this which i think is a tilde or something so just be aware of that if you are working in other versions you may run into a situation where the hours are offset from the time scale and that's because this field down here gets brought into Excel as to cell fields which means that two fields would be put here which means that this would have been off shifted to the right by one cell that's something that is commonly overlooked and you need to make sure that you address that I'm going to go into the report editor and show you some quick ways of cleaning up the data elements inside the report editor to open a report in the p6 report editor just click on the report right-click and say modify the second option there it's going to give us a warning that the report was originally created with the wizard and that if we modify it in the editor we will lose our changes if we go back to the wizard again that's okay we're gonna click yes and for any of you that have never been in here this is what the report editor looks like it is a bit of a complicated tool and we do include a session in our advanced training for p6 at TEPCO on this if you're interested in learning more about the report editor a few things though first of all the report title I really don't need that coming over to Excel so I can click on that and I can hit the Delete key on my keyboard it's gonna ask me if I'm sure that I want to delete it and I'll say yes and that'll go away the notes aren't going to show up but just to be clean I'm gonna go ahead and get rid of that here's my data column titles now this is the one right here that causes the two cell problem in Excel so I'm gonna go to print preview again that is this part right here if you are using earlier versions of p6 and you are finding that this cell is coming over and into Excel and it's being treated as two cells which is off shifting our column dates what I recommend doing is you come into the editor simply double-click on this cell that says date range and it will bring up a properties window take the cell type field and change that to custom text and then down here in the third field down just put a dash or something and what we've done is we've removed the time scale range and we've replaced that with a dash now when that comes over to Excel Excel is gonna treat that as a single cell now it's not going to treat it as the two cells so if I were to print preview this now you'd see that the time scale range has been removed and we don't need that anyway so click OK and click OK I went ahead and ran the report again with those changes that we made in the report editor so now you can see that at the top here the report title is actually gone at this point we're ready to build our graph so all we have to do is highlight all of this data and we can go up to insert and we can select the graph tie it to insert we're going to choose a line graph to start and then we're going to have to make some changes to it afterwards and there we have our graph let's take a closer look at the graph first of all the colors were automatically generated by Excel so normally I would come in and change these colors to be more in line with the colors that p6 typically produces we're not going to do that during this session if you need some information on how to do that you could probably go out on Google and get some information on how to change colors in a graph first thing I want to point out is this earned value line that comes up right here it meets with the remaining which is actually the Act completion in the background the urn is actually laying right on top of the actuals part of the Act completion that's because we ensure that our own actuals were the same in p6 however we see this flat line run across that's because the Earned value when it was generated from p6 it got up to a certain point and then it kept generating that same number over and over and over again until the end of the time scale period now I could come up to excel to the Earned line which is right here and I could scroll away over and find out where it becomes repetitive again which is right where the data day would be now I could come in and I could delete all of these data cells right here so I'm gonna hit the Delete key and then let's go back and look at the graph and see what that did now the line has been removed so the cumulative point came up to here but the repetitive numbers that were running across have been removed from this this graph that's what you would need to do and if you are running multiple graphs here out of 1 p6 report you would have to do that for every graph I'm gonna go into p6 and show you how to set up a second report specifically for earned value that we run separately so that we prevent this from happening since we're going to be building a separate earned value report I'm going to go into our current report and make some modifications to it to remove the Earned lined items from it so I'm going to right click on it and go to modify and I'll be doing this in the editor now if you're aware of this up front we want to have built this these earned lines into the report when we were using the wizard in the first place so I'm just going to use the editor and I'll quickly show you how to remove a line item right now I've selected here's the earned line item for the cumulative earned I've selected that that row inside of the editor I'm just gonna hit the Delete key and it's gonna ask me if I'm sure and I'll say yes and then down here I have the earned for the day intervals and I'm gonna delete that one as well and I'm gonna say yes then I can click OK down in the bottom and that will automatically save that report now let's build the second report specifically for earned value so I'm going to run the same process we did for the first one I'll hit the Add button to add a new report hit next this is time distributed activity data next as for the columns we don't need the ID or the activity status but like we did in the previous one I'm gonna go ahead and bring the project name over and click okay and then under group and sort it's the same concepts there we want to show summaries only we want to show project data and show totals at top I do not need the ID or code so exact same thing there and click OK we didn't do anything with filtering if we had done anything with filtering we would want to apply the same exact filter criteria to this report we'll hit next time scale if I had hard-coded a time scale start date in the previous report I want the exact same date here that's very important both the reports have to have the exact same time scale start date as for date interval we did shift and we did it by the 24-hour 7:00 a.m. to 7:00 a.m. shift that we had created we'll click OK there and I don't need show period totals again so we need to turn that off basically we need to setup the exact same report time interval fields the cumulative we need the cumulative earned value labor units and we need the time interval earned value labor units again go back in and label these so we will say current earned and I believe last time we labeled the interval one earned daily click okay now I'm gonna go back into the time scale this is the most important part about this report and this is the reason we are generating a second report instead of the time scale finish being the project finish which if we leave it like this it's going to generate the same data that we had in the previous report we don't want to do that let's change this to only run up to the data date now the Earned value hours are only going to run the cumulative line up to the data beat and then they're gonna stop we hit next and we'll call this one ta - OH - we'll just call it earned hours and next you can run the report to check it out and there's our earned values now because I edited in the report editor earlier I'm gonna do the same thing here just to be consistent so I'm gonna hit next when I hit save report and then finish now I'm gonna right click go to modify it gives me that warning again I'm gonna say yes and in the previous one we had removed the report title we had removed notes and we had also changed the date range by clicking on the date range cell change the cell type to custom text and just put a dash in here now when I print preview the report title is gone which was right in this area here and this has been changed to a - you don't have to worry about the header and footer information in the port because Excel does not take that in close this click okay and next we'll run the report since we edited the first report by removing the Aaron value data and we created the second report to include only the Earned value data I'm going to start completely over I will run the first report save as ASCII text it's going to save as ta 0 1 s-curve data it takes on the report name that we gave it and that can be a CSV file I'll click OK and there's our first report data now I'm going to go back to p6 and I'm gonna right click on the second report for earned hours and I'm gonna run it this one also can be ASCII text notice how p6 automatically put in the report title here so we don't have to change that and that's a CSV file as well so I'm gonna click OK now I'm gonna go to Excel and I'm just gonna open a new Excel workbook and what I'm gonna do is take each of those CSV files and copy and paste the data in so I'm gonna select the first one here I'm going to copy by ctrl C I'm going to go to the new workbook and paste at a1 and then I'm going to go to the earned value CSV file and I'm gonna copy that data and I'm also going to paste that into the new workbook probably right down here I might put a space there just to keep them separate so now we have all the baseline data up here the remaining which is the add completion change baseline daily and the remaining daily and then down here we have the Earned we're just going to leave the data as is and I will build out the first graph using the top data and then I will add the earned data to the graph after the fact one thing I always do too make sure that everything is in sync before moving on is make sure that my dates at the top match my dates on the earth they do if they are out of sync that means that your time scale settings in one of the reports is wrong and you need to go back to the report and fix that let's go build the initial graph so we need to select from b1 down through row six and then we need to select all the way over to capture all of the data and I'm going to scroll back to the left so that my graph does not appear way over on the right-hand side and we'll go to insert line graph so there we have the line graph again although the earned value data is not in there yet now to bring the earned value into the graph I'm going to make sure I select the graph I'm going to come up to chart tools and select the design tab and I'm going to select the button that says select data in here I'm going to add a legend entry and the series name is going to equal for the first one is going to be current earned and the series values are going to be all of the cumulative data for the current earned now here I need to select all the way across so this could be a little bit painful but I need to go all the way out to the end of the entire dataset not just to the end of the earned because the next time that we bring in an earned report might be after another cycle of updates which means that we will have an extra day of earned and for each update cycle we go through there'll be another day and then we can click OK on that and now we have the current earned down at the bottom I'm going to click OK and you can see that the current earned has come in to the graph as an orange line this time and that's fine now I need to bring the earned daily into the graph so I'm going to use the same steps chart tools design tab select data and I'm going to hit the Add button and the series name is going to be earned data and the series value is going to be from looks like row 10 starting at column C and again I have to run all the way over this ensures that the next time I run the report as data comes in it gets added to the graph now we have current earned and we have the earned daily both in there and we'll click OK go back and take a look at the graph now the earned line for daily has come in at the bottom now to clarify we've brought in the Earned value cumulative we brought in planned value cumulative two baselines basically and then we have the add completion which is showing the green here which is actually the remaining because the data day would be running right down this area here however these lines down at the bottom here these need to be bars not lines so I'm gonna select one of them and I can go up to chart design and I can say change chart type and I'm going to change this to a column and you'll see down at the bottom now it is columns and I'm also going to do that to the other ones and one more now there's one other thing that we need to do to finalize this graph each one of these column sets down here for the daily is currently running off of the left-hand axis here which is a cumulative number so we need to set a secondary axis on these guys the way we do that is you can right click format data series and in the format data series for each one of those data series there's the series options tab at the left-hand side and we can tell it to plot it on a secondary axis now we see numbers come in on the right-hand side and we need to do that for each one of those and secondary access on that one now if we wanted to be consistent with how p6 looks we would actually change those the secondary access in P six over on the right hand side would be the cumulative because that's how P six displays its charts and the interval or prayer period data would be over on the left hand side and I mentioned earlier that normally I change the colors to be more in line with some of the default colors that we see out of p6 and I'll just show you how to do one of those I'm going to click here out on the legend and I'm going to select the text for one of the line items here and I'm going to double click it and now I can change the fill on it and if I wanted to change the color of the change baseline daily data I could change the color of that and I did to red and you can see that it turned out red here and go with black instead so you could go through all of these here and make color changes to them as well you can also change the weighting and the style of the lines if you wanted to put in dash lines for one of them and so on one final thing before we close here now that the graph has been built and we have the p6 data built the next time we run the reports all we have to do is paste them the first one at cell a1 and the second one at cell a8 and the report graph in excel will automatically update with the updated p6 data so we don't have to go through and make changes to the graphs or build a new graph every time we can simply just refresh this data up in this area now there's other quick ways to do that I'm not going to go into details on that you can look for a future tepco video on how to do this but if you need to take numerous reports out of p6 and put them into Excel say we have five or six p6 reports some of them with s-curve data some alone might have other data but we need to take all that report data and drop it into an excel file to create like a dashboard report or something like that you can run a batch on those six reports in p6 and save them off to a shared driver your C Drive and then you can come in to excel and write a macro or record a macro to open all of those files and copy and paste the data in to the areas within your dashboard report that you need the data paste it into this process between running a batch and then using an Excel macro to bring the data in it can take the time down it takes to actually generate all of this data to less than a minute it might take you 15 seconds or 20 seconds to run the batch in p6 and once the macro is written in Excel you can execute that macro and it might take like 10 seconds to run to pull that data and draw it into your master excel file you can imagine the time savings around that if you use a p6 batch report in conjunction with an Excel macro because now you're not having to manually copy and paste data from each one of the reports and drop it into your master excel file you can just let the tools do everything for you thanks for joining me for this tepco video on generating s-curves in excel with p6 data if you'd like more information on tepco whether it be training implementation or consulting services please go to WWF go us

Channel: TEPCOSolutions

Views: 29,166

Rating: 4.8769231 out of 5

Keywords: Primavera, P6, S-Curve, Oracle, Microsoft Excel (Software)

Id: 7BzAT4zZ8Og

Length: 59min 26sec (3566 seconds)

Published: Thu Dec 17 2015