(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?
(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.
Hello Planners and MSP specialists,
How could it be possible to make MS Project compute the percentage between actual work and theoritical work at the status date?
One 5 day task starting Monday
Two resources assigned to this task: R1 is working 10 hours / day; R2 is working 8 hours / day
Status date is Wednesday night
R1 has worked 10 hours on Monday, 9 on Tuesday, 8 on Wednesday, or 27 hours
R2 has worked 5 hours on Monday, 4 on Tuesday, 3 on Wednesday, or 12 hours
Is it possible to get MSP compute that R1 has worked (10+09+08)/(10+10+10) = 90% and R2 has worked (05+04+03)/(08+08+08) = 50%
Thanks for your help
Alexandre
Printer-friendly version
Send to friend
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_updatin...
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.
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
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)
Work Efficiency = Text1 with the formula, Int(([Number2]/[Number1])*100) & "%" , which calculates the Resource Efficiency as at Status Date.
Screenshot below shows final result with other fields (columns) hidden.
It is obvious that this can be set-up quicker in Spider Project compared to MS Project.
Duplicate -please delete
Planned work is relative to the schedule version, each represents a plan. But for P6 Planned Dates are different. The misunderstanding can be an issue of semantics so I made some changes in the hope to make it clear.
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.
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.
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.
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.
Not so sure EVM will always yield correct results.
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) & "%".
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
Email: PPadmin@planningplanet.com
Replies