Speaker 1: Hello all today, we're going to talk about the baseline or the scheduled percent versus the actual or the real progress completion in Microsoft Project 2.0.2.1. So as far as we are concerned Microsoft project don't actually come with the default baseline or the scheduled percent available however There are several videos which is this or another video over here They have showcased a very nice formula but the main problem here is that after trying with their formula, the summary task level role percentage doesn't seems to tally with the percentage calculated originally by Microsoft Project. So the overall summary percentage level seems to be very important, not seems to be, it's very important for my client and the difference is around 5-6% or even up to 10% when the project is very large or complex. So let me just show you how they did their formula over here so i have a template for the commercial construction this is the default template from microsoft project so let's say this is the project over here and we will use their formula um don't worry i will include the formula at the below section so let's say we will use a custom field called number one and then we'll use their formula over here right click custom view and then click on formula and I will paste this formula for you later click OK and we are going to also apply the formula for the group summary row which is a click use formula here and we can even rename it as maybe YouTube video number one here and click OK so it will definitely come out as an error because we haven't set the baseline for this particular project and the formula requires the baseline date to be set so go to project set the baseline and click ok for here for the entire project it is still error because we haven't set the status that that status date that we wanted it to compare so for example let it let us set here at maybe probably 2 July so you can click on status date change it to 2 July 3 July is fine as well okay so we get some values over here and maybe let's say we can go to the Gantt chart and we will plot out the status line first by going to grid line go to status date and maybe change it to red color so that we can see the status line over here so let's say how we want compare with the value we obtained from Microsoft Project. Microsoft Project come default with this percent completion, percent complete. So let me just hide this column away. So what we're going to do here is that we're going to call Microsoft Project to actually automatically calculate the percentage of completion up to the current date. And there is a simple feature that we can use here which is a we go to task and click on this mark on track so as you can see here mark or track means that based on the current status state we will mark all the completion accordingly so you can see that everything stops at the status state and you can see here overall the percentage complete based on the formula here all the smaller tasks which is the sub task doesn't seems to have an issue okay however the summary level here here and even here seems to be having some issue and in a certain larger project that I've handled it seems that the number can be greater than around 6 or 7 percent and that is a very large number for a commercial project or a commercial construction project so let us test it out with the second formula as well which is from the second video. So let me just include another row. Maybe I set it as a number 2. Then right-click custom field, change the name maybe YouTube 2 and we will include the formula here and apply it for the summary rows as well. So you can see here although this is not in percentage value but this is like a integer which is 1 is equal to 100% but you can see the value obtained for from the both video it's almost exactly the same it's just that for the summary level the number is still not tally so what i can do here is that i'm going to suggest a semi-automated way which is we are going to do some manual calculation a little bit and then we are going to do the comparison then we can get the same percentage as per the Microsoft project calculation okay so I will use back the same project template again in the next part of the section of the video okay so we'll use back the same template area which is the commercial construction so first thing first here we are going to actually add back the percent complete which is we add back the percent complete over here and what going to do here is that we are kind of like doing some semi-automated way which is um we will actually craft out or get the scheduled baseline scheduled baseline percentage and kind of like make a copy of it before we input the real percent complete or the actual percent complete here so what we're going to do here is that it's very simple we have the percent complete here and then we are going to have maybe a status date so let's use back the same status date as earlier which is 3rd of July and we're also going to put back the status line date because I reset the program so change it to read so what we are going to do here is that we are going to make a copy of the scheduled percent which is for example everything until the red line here how much of the percent has been completed so we are going to actually use the mark on track and this will tells you that at this particular 3rd July how much of the percentage we should complete okay so with this in your with this in mind we are going to actually create a new column but before that I think I'll hide away some of it first and add a new column here and let's just use the number one but let's just try to just directly copy and paste it to make a backup and you will receive error here because this 8% which is the percent complete is a text it's not a number so that's why when you try to copy paste here you will have some error and it's correct so you can actually copy paste this entire column into excel or any microsoft word remove the percentage and then put it back here but i think that's a little bit very messy so what we're going to do here is that we are going to insert another column and we're going to use the text one so what this text one is going to do is that it's going to remove the percentage from the percent complete and then we are able to use that and copy paste it back to the number value here so you can use a custom field change the formula use the formula of function of val which is value and we're going to change the remove the percentage from the percent complete um column here click ok and i apply for the summary task level as well we can rename it percent complete text so this is just the name i put the text here so that i know which is which okay you can definitely change the name accordingly click OK and you can see that we get a very nice number over here and we can paste it and basically this number over here this is a static number whereby it's kind of like our scheduled or the baseline number baseline percent complete over here so we can right click custom field rename it scheduled or the baseline percentage and just as a reference we will call this as the number okay so with this i think we have pretty much covered and you can see here this percent complete and this is pretty much the same so let's say later on when we want to actually change the actual real progress so let's say i change this maybe to all of them to zero then i start to fill it in maybe for example hundred hundred hundred hundred and then hundred of course um you can follow the task there to make it more easier which is um you can click all of this and uh you can click on the task and directly change all the percentage if you want to but it's okay i mean there's not much task here so let's just follow here okay so you can see here um now the actual um progress we have done is nine percent the schedule is eight percent and this number is actually correct which is um based on the schedule or the baseline over here we are supposed to complete eight percent but currently we have already completed 9%. For the second part over here, you can see here basically we have completed more a little bit more here 40% and 12%. The overall completion is 45% and the schedule or the baseline percent here which is 31%. So you can see here basically we already done we have the schedule and the actual progress and what we can do next is that we can actually find out the ahead or delay in percentage so we can add another new column so using number two maybe and we can change the custom field and this time we can make it ahead or delay click okay and the formula here is very simple we can just take the schedule minus the percent complete okay so schedule is the number one minus the percent complete and we will apply this for all the we will apply this for all the summary rows as well so you can see here we are a hit by we are hit by 1% based on the schedule we are hit by 14% and other than that others 0 here indicate that everything is on time okay so however for the third part here 15 here it indicates that we are slow by 15% and this is the values that we need over here. So with all these available, we can also generate the graph or the report for us, which I think it's also very necessary. You can go to report and click on the new report card. Click OK. And then you can see here, we don't need the actual work remaining work. work what we need here is the number which is over here and we need the percent complete definitely and also we need the if you click on custom here the small arrow here we will click on the scheduled baseline that we have earlier and also the ahead or delay percentage here so you can see here um if let's say we change it to project summary level um the percentage of complete is around 9% the scheduled percent complete 8% and we are we are actually a hit by 1% over here so you can change the outline level maybe on level 1 on what kind of what kind of the smaller tasks that you like to see or maybe all subtasks okay so or maybe level 1 only level 2 and etc so with With this, of course, you can do some customization here, changing the levels, adding some numbers and etc. So I think this is a great way for you to get the summary level values to be correct. And if you don't need the summary value, of course, the earlier formula that I showed you from the other two videos, that will be already sufficient enough. Because with those formulas, you can get all the task level information, which is the subtask over here all the percentage the scheduled percentage will be automatically generated okay but um for this method the bad the downside of this method is that every time when you have a new baseline you will need to actually manually input this value over here okay so that is the kind of like the downside of this particular method but you can get the summary level percentage correct okay so one last thing here is that sometimes we would also want to have maybe the scheduled baseline to show the percentage value or the ahead or delay to show the percentage value over here so Microsoft project don't really allow us to put the percentage symbol over in a number field over here so what we are going to do is that we are going to insert another column which is called a text field again maybe we use the text tool over here and this text tool is just going to take the scheduled baseline over here and then add the percentage at behind that's all so right click custom field formula and we're going to take the scheduled baseline which is the number one and then we're going to add the percentage value behind hopefully this is correct okay okay so this is rename schedule baseline percentage text the text is just a reference for me okay click okay and basically you can get this up and this is what you need to actually print out on the report and etc okay the same you can also do the same for the ahead and delay if you would like to display the percentage over here so of course if you ask me there's a way that you can directly use the text custom field text and then key in the formulas and save everything here but the main part here is that if you do that in your project report which is the card earlier this card over here doesn't allow text to be actually displayed so if you use a text you will not be able to use the scheduled baseline percentage or even the hit or delay to generate your graph over here okay so that's why for me personally I will recommend that you create several custom field one is the number field one is the text field that you would like to print on your report and etc okay so that's all in this video I hope this will provide you a way to at least figure out the way to calculate the summary level scheduled percentage so that when you submit your report it looks correct it's not that on the only on the individual task level but at the summary level the values is also tallied okay thank you very much and have nice day I'll see you again in your next video