Work progress at status date

Jerome,

I do not use MSP but it looks like you got it, you are very good at MSP. I use external file hosting for my pictures as my experience has been that figures stored at PP tend to get lost. I use Photobucket, is free. Your postings shall be available for future reference by users of MSP, I find them very valuable.

I believe it might be you can even improve on your formulas if using user defined field to define some of the values, but this requires someone with your knowledge of MSP.

I do not find MSP to be bad software, on the contrary it can do many things P6 cannot do, one of the things I will like to explore with you is the ability to model Spatial Resources using MSP resource leveling on hammocks, something P6 cannot do.

Best Regards,

Rafael

Alexandre,

At home contractors do not keep detailed cost tracking via the schedule, for this we use our Unit Costing system integrated with our accounting system. Here we catch up every expense up to the cent. We also track production or volume of work along with effort, something MSP cannot differentiate, it cannot even understand resource production rates.

http://www.foundationsoft.com/unit-cost-production-reporting/

We do not use mere budgeting without tracking volume of work. We do not use EVM unless required by Contract. I consider EVM flawed Budgeting with regard to schedule, the literature with this regard is huge. A strong statement for a few ladies that blindly advocate for EVM. We also avoid tracking billings with the schedule, see next reference.

http://www.nflaace.org/index_files/john_orr_cost_loaded_schedule_updati…

We do not find detailed labor tracking using the CPM practical. At times a few resources work overtime on a few days, at other times a few resources not in the resource plan work on some activities. Trying to input all these variants into a schedule update is a nightmare. To this you must add all materials, subcontracts and all everyday expenses. Then you must add on periodic interval the production rates as to get the unit costing. For tracking of detailed labor cost we not only use our unit costing but also the payroll reports.

At home we use the schedule for planning future work and use other tools to complement it. We cost load the schedule to forecas future cash flow and to resource level financial constraints, as I said before detailed history is on the accounting system.

Anyway, good luck with your formulas.  I hope I was of some help.

Best Regards,

Rafael

Rafael,

Now I understand the point you are making, which is that Alexandre wants to calculate the efficiency of each resource and this has nothing to do with Baseline which is where BCWS comes in.

I have played around with MS Project and the work around is shown below with explanation of the Custom Fields.

[[wysiwyg_imageupload:3231:]]

ResName = Text2 with Resource Name pasted into them since I couldn't find a way to access Resource Name in formulas

StandardHrs = Number1 with the formula, IIf([Text2]="R1",(ProjDateDiff([Start],[Status Date],"10-hr / 5-day Week"))/60,IIf([Text2]="R2",(ProjDateDiff([Start],[Status Date],"8-hr / 5-day Week"))/60)) , this calculates the theoretical (standard) hours each Resource should have worked from Actual Start Date to Status Date based on the Resource Calendar

[[wysiwyg_imageupload:3233:]]

ActualHrs = Number2 with the formula, [Actual Work]/60 , this copies the actual hours each Resource has worked from Actual Start Date to Status Date  from the Actual Work field. I copied this to a custom number field so that my calculations will use the same data type (this also assumes you've earlier assigned actual hours for each Resource / day using the Task Usage form)

[[wysiwyg_imageupload:3226:]]

Work Efficiency = Text1 with the formula, Int(([Number2]/[Number1])*100) & "%" , which calculates the Resource Efficiency as at Status Date.

[[wysiwyg_imageupload:3230:]]

Screenshot below shows final result with other fields (columns) hidden.

[[wysiwyg_imageupload:3232:]]

It is obvious that this can be set-up quicker in Spider Project compared to MS Project.

=jerome

https://plannerstips.blogspot.co.uk/

Duplicate -please delete

• Baseline Bars [actual/planned] are in yellow, Current Schedule Actual Bars are in grey/blue the other bars represent Current Schedule remaining planned work, you should be able to figure out any date you need from the Gantt, it is a single activity schedule so late and early bars are the same.  

You said - I think you are getting 30 because your formula uses Actual Start Date (07-Dec-15) instead of Baseline Start Date (01-Dec-15) for User Field A.

• Close, date1=Data Date of current schedule, date2=Start of current schedule, Start is actual if the activity started, is current planned/early start if not started. Of course the dates can be any compatible field you select from current, baseline or compared schedule, can be another computed value.
• None of my formulas use Baseline Values, but it is possible to create formulas using baseline values when needed.
• On a full model with all conditions considered:
  • If not started no computation shall be performed 
  • If started but not finished this formula is to be used.
  • If finished then date1=actual finish, date2=actual start.
  • A couple of nested IF statements shall take care of it in a single formula.

• On Figure 2 user field is calculated as per formula using a date  and time function, it is the difference un hours between Data Date and Start Date Resource A calendar. Three days at 10hr/day equals 30 hours. This is your key formula I expect MSP can provide. BCWS is calculated using the Baseline, by DD of 12/10/2015 8:00AM it is 50, it has been 50 science end of 5th baseline day.

If Planned Bars are in yellow, figure 2 shows 5 working days so where has the 3 days used in calculating User Field A come from? I expect this to be Diff_Hours_Calen ( #Data Date; Start[Original]; calendar) and with Data Date of 09-Dec-15, Baseline Start Date of 01-Dec-15, should give 50 in User Field A since 07-Dec-15 is the Baseline Finish Date. I think you are getting 30 because your formula uses Actual Start Date (07-Dec-15) instead of Baseline Start Date (01-Dec-15) for User Field A.

I have never used Spider Project so downloaded a demo version to replicate your example but it was hard deciphering how to use it so gave up.

• Please take a look at the formula properties it was set up to calculate after every schedule run.
• You can also take control of which formulas and on what order if using scripts you can record using keystroke recording or the script language. Scripts can also be set up to be run automatically. 

• On Figure 2 user field is calculated as per formula using a date  and time function, it is the difference in hours between Data Date and Start Date Resource A calendar. Three days at 10hr/day equals 30 hours. This is your key formula I expect MSP can provide. BCWS is calculated using the Baseline, by DD of 12/10/2015 8:00AM it is 50, it has been 50 science end of 5th baseline day. 
• 

• Baseline Planned Bars are in yellow, Actual Bars are in grey/blue the other bars represent current remaining planned work, you should be able to figure out any date you need from the Gantt, it is a single activity schedule so late and early bars are the same.  Planned work is relative to the schedule version.

Even with BCWS I don't see why it fails.

Are your sure your User Fields autoupdate? I find it strange that in Figure 2 User Field A is still 30 for Resource A considering that BCWS for Resource A is 50 and at 10 hrs / day,  for us to get BCWS of 50 it means 5 days of Planned Work and as such I expect User Field A to be 50 and not 30.

Maybe you should show the Planned Start & Planned Finish for both figures so that I can replicate your User Field A calculations.

The problem is not with ACWP but with BCWS.

• On the schedule version [1], first figure on my posting:
• Resource A ACWP for Dec 9  = 27 while BCWS is 30.
• % = 27/30 = 90%

• On the schedule version [2], second figure on my posting:
• Resource A ACWP for Dec 9  = 27 while BCWS is 50.
• % = 27/50 = 54%

90% is the percentage Alexander is looking for, it is the same on both versions if using as reference the actual work days of the activity instead of the baseline planned work days.

Rafael,



I'd expect different results if Actual Start Date = Planned Start Date from if Actual Start Date < > Planned Start Date but not sure this means it fails. So can you please elaborate of the fail aspect.



ACWP calculates cost of Actual Work carried out as at Status Date so does not really matter if Actual Dates differ from Planned Dates, as calculation will only start from first date Actual Work is recorded.

=jerome

http://plannerstips.blogspot.co.uk/

Not so sure EVM will always yield correct results.

1. If planned/baseline start and finish matches actual it might do the trick.
2. As soon as planned/baseline start and/or finish differ actual it fails.

Alexandre,

Yes, MSP can compute the Work Progress at Status Date for you using BCWS & ACWS. Create a text custom field (Work Progress field) and apply the formula = Int(([ACWP]/[BCWS])*100) & "%".

=jerome

http://plannerstips.blogspot.co.uk/

 

If what you are looking is to calculate Current Average Workload to compare against planed I believe with use of formulas it can be done.  MSP provides one of the most extensive sets of formulas.

If using Spider the following figure illustrates how it can be done. Of course the formula for C sould be expanded using some nested IF Statements as to consider if Activity is finished, in progress or not started. If using MSP I suspect you might need to use a different view other than Gantt. 

At the beginning I did not see much value on this but after realizing it can be used as to compare planned workload vs current actual I realized its value.

Good luck,

Rafael

Regrettably, I don't think you can do what you want through the MSP user interface alone, though with a bit of work you could get the data into Excel and process it there.  (I've manually copied out the task usage table and processed the numbers in Excel - that gets tedious for repeated exercises, however.)

Fundamentally, although "%Complete" and "Cum. %Complete" are available for viewing in the task usage table, these fields get computed and populated only for the tasks, not the individual assignments.  Also remember that these fields are strictly duration-related for a task - they are never earned value.

You are specifically wanting to divide the actual work of each assignment by its baseline work (cumulatively as of a certain date/time.)  For me the main issue lies in the storage (or lack thereof) of time-phased data in MSP, not to mention the weak integration of the "Status Date."  If you had a dire need, then you could develop a vba application to compute and extract the time-phased data from MSP then process it in Excel.  That's not a trivial exercise.    

Good luck, tom