Re: Calculating end of project profit margin

From: Andrew (Bangkok) (AndrewBangkok_at_discussions.microsoft.com)
Date: 11/20/04 

Date: Fri, 19 Nov 2004 22:05:02 -0800

Sorry, I may have been a little unclear in my earlier post.

We do actually bill fixed price for the entire project. However the project
progress is tracked at the task level so when tasks are deleted or added
there can be some adjustment to the total price. With the swings and
roundabouts theory in place it tends to even out most of the time but not
always. Anyway, that's not my most immediate problem right now.

Due to the market conditions here it is possible to use a billing rate for
resources that allows for a handsome profit margin. The thing is that there
is a significant difference between the rates for the various resources. So
for example, if I have a project that will take 100 hours and two guys -
Senior Guy and Junior Guy. Rate for Senior Guy is 1500 Baht/hour and I need
him for 20 hours = 30,000 Baht.

Rate for Junior Guy is 700Baht/hour and he will do the remaining 80 hours =
56,000 Baht. Total Project: 86,000 Baht.

If I used the average of the 2 rates to calculate the price that would be
1,100 Baht/hour * 100 = 110,000 Baht. Good if you can get it, but
unfortunately clients aren't that dumb. So I use the current Cost field to
calculate price for each resource and roll that up to a total.

During the projects it is not uncommon for there to be overruns of effort
for tasks. Due to the built in margin this has not been seen as a problem as
the internal cost of resources is so low that a project can overrun by nearly
4 times the initial estimate before it starts to lose money. Yes, I know
that is not great project management and I'm putting measures in place to
address that and in order to do so effectively I want to be able to quantify
the effect this is having on the bottom line. The CEO knows this is a
problem but I need some hard numbers to give impetus to the change and then
to be able to measure the effect of the change.

So, I have used an average cost for internal resources to calculate the cost
of the project and hence the margin for the entire project. Right now, I'm
using a Custom field to do that so I can only use one rate for everyone.
That works ok, as the cost for the Senior Guy is about 250 baht/hour and the
junior guy is around 200Baht/hour. Using the formula above this gives me a
total cost of 21,000 Baht if I use the actual costs for each resource or
22,500 if I use the average for the entire project. Still a difference but
not too bad.

I then use another Custom Field to track the actual internal cost using the
actual hours worked and the average cost. This then gives me the Actual
margin for the entire project.

BTW I know I could use the Cost Rate Table to record the internal costs for
each resiurce but it still doesn't give me the ability to track the increase
or reduction in margin.

Anyway, during the course of the project I can export the numbers to Excel
and graph the rise and fall. This will give an indication that things are
going bad but the thing I really want to know is how bad it will be at the
end. This is where the earned value feature would be useful.

The reason I want to know that is that it will allow me to assessthe
effectivness of any action taken to reverse a project going down the tube.
If halfway through I can predict a project will make a loss if nothing is
done, then take some action intended to prevent that, I can then see if that
action is working during the second half of the project.

This would all then be fed back into the initial planning process so
hopefully it won't happen again.

This is a good idea (at least I think so) but how to do it?

"Steve House [MS Project MVP]" wrote:

> Are you tracking a group of people as a single resource - 5 engineers with a
> salary range of 20k to 40k averaging 30k being listed "Engineers"
> "Avail=500%" "Rate=30k" or are you listing each engineer as a single
> individual, max avail of 100% and their true loaded labour cost as the rate?
> If you list all your resources as individual people rather than dealing with
> groups the inaccuracies you ascribe to using avaerage salaries goes away.
> If we have task "Fidget Widgets" and assign individual resources Bill & Joe
> to do it, Project's cost field will accumulate the true loaded salary costs
> for Bill and Joe and so reflect our true internal costs. If we take Bill
> off and send Fred instead, Project would use Fred's rate, not Bill's, in
> calculating the costs.
>
> I'm confused why you would bill on a per task basis. Billing for the
> complete project makes sense but it's not like the client has an option
> whether tasks are done or not, that's governed by the project scope and the
> requirements of the deliverables - the tasks to be done being driven by the
> work required to produce the deliverables. If a task is dropped, that
> deliverable isn't done and the project scope changes, hence a new bid to the
> client for the work. But he's still paying for the whole deal, not
> individual tasks except in the aggreate. If I'm the client having a house
> built by a contractor I don't pay separately for the east, west, north, and
> south walls of the foundation but it's very likely that they are listed as
> separate tasks in the plan - instead I pay for the foundation, walls, roof,
> whole completed package in one total lump sum. Maybe I'm naive, but it
> seems like trying to track margins on a per task basis would create a sea of
> numbers that don't actually do much to assist the decision-making processes
> of managing either the project or the firm - they might look impressive but
> it seems to me that such micromanaging would obscure more than enlighten.
> I'm still not going to assign someone to a task who is not competant to do
> it just because he's the cheaper resource <grin>.
>
> --
> Steve House [MVP]
> MS Project Trainer/Consultant
> Visit http://www.mvps.org/project/faqs.htm for the FAQs
>
> "Andrew (Bangkok)" <AndrewBangkok@discussions.microsoft.com> wrote in
> message news:AA3FD268-9D59-41EF-B7B4-0A0B9637DDB2@microsoft.com...
> >I thought of that and I may well have to use that approach if nothing else
> > comes along.
> >
> > We are billing on a fixed price basis for both tasks and project. I'm
> > therefore using the existing cost fields to calculate the total cost as
> > that
> > allows me to assign different rates for different resources and those
> > rates
> > vary widely for each skill set. Also, as a percentage of total project
> > work,
> > the effort from the more expensive resources is much less than that for
> > the
> > lower levels.
> >
> > So if is used an average of the billing rates in a Custom Field, the
> > results
> > would be skewed. I guess I'd get some indicative trend results but not
> > close
> > enough to aculaity to be really meaningful.
> >
> > I can get away with using an average of salary plus overheards for
> > calculating internal cost because the difference in salary rates for
> > senior
> > and junior staff is not all that great (this is Thailand, remember). Again
> > the end result is not 100% accurate when totalled, but it is much closer
> > to
> > reality.
> >
> > The best solution would be if I could assign different rates to the same
> > resource and then have them both appear in an earned value report.
> >
> > "Steve House [MS Project MVP]" wrote:
> >
> >> Are you billing based on time & materials (actual work), a fixed amount
> >> per
> >> task, or a fixed price bid for the project? Was thinking that if your
> >> reversed your usage of the fields, using the existing cost fields for
> >> internal costs (which is what they're already computing when you use
> >> burdened labour costs for the resource rates) and the custom fields for
> >> the
> >> billing/revenue data then you might be able to get what you want from the
> >> earned value Estimate at Completion fields. But whether that'll work
> >> depends on the basis for the revnue stream.
> >> --
> >> Steve House [MVP]
> >> MS Project Trainer/Consultant
> >> Visit http://www.mvps.org/project/faqs.htm for the FAQs
> >>
> >>
> >> "Andrew (Bangkok)" <Andrew (Bangkok)@discussions.microsoft.com> wrote in
> >> message news:9228A6A0-7BA9-45FC-8B4C-E23B583E4F4F@microsoft.com...
> >> > Using Custom fields I have set up my project to calculate the estimated
> >> > and
> >> > actual profit margin. The Task Cost field uses the billing rate and
> >> > the
> >> > Custom Field that calculates the Estimated Internal Cost uses the
> >> > formula:
> >> > [Work]*avaerage rate for resource (salary + average overheads).
> >> > Subtract
> >> > one
> >> > from the other in another Custom Field and you have the margin.
> >> >
> >> > Actual Internal Cost uses the formula [Actual Work]*average rate.
> >> > Subtract
> >> > that from Cost and you have the Actual Margin.
> >> >
> >> > This is fine but I really want to see what the Profit Margin will be at
> >> > the
> >> > end of the project. Using Actual Work lets me see the margin
> >> > decreasing
> >> > or
> >> > incresasing on a daily basis but I can't see where it is likely to end
> >> > up,
> >> > based on the current trend. To make it easy to see I'd also like to be
> >> > able
> >> > to graph this somehow.
> >> >
> >> > Any ideas how to do this?
> >> >
> >> > After this is achieved the next step is to role it out across the
> >> > enterprise
> >> > so we can see the overall picture across multiple projects....but one
> >> > thing
> >> > at a time.
> >> >
> >>
> >>
>
>

Relevant Pages

  • Re: project costs
    ... Entry table to the Cost table. ... In your original post when you said you had rate for the resources I ... So, for your concept plan, you need to do a few things. ...
    (microsoft.public.project)
  • Re: Calculating end of project profit margin
    ... all the resources as individuals by name with their real cost to you. ... > resources that allows for a handsome profit margin. ... > using a Custom field to do that so I can only use one rate for everyone. ...
    (microsoft.public.project)
  • RE: [fw-wiz] Log checking?
    ... If you had all the resources you want, or with the resources you have? ... attacks at zero than I am curious about the level of unsuccessful attacks. ... I'd suspect that if you actually looked at utilization and cost, ... I spent years silently dropping stuff at the ...
    (Firewall-Wizards)
  • Re: Managing projects with subcontractors
    ... and resources, but that is a different discussion. ... cost breakdown that shows the labor, materials, and ODCs for each task. ... You could go one step further and assign Bob The Tiler to each ... If you had a different cost for each Task you might create a separate Bob ...
    (microsoft.public.project)
  • Re: Climate Change Is Going TO Kill Us All...
    ... Your experience is skewed by living in a high cost of living city. ... Especially if you're lazy. ... poor are that way because they're extremely lazy and extremely stupid. ... Its not the lack of resources. ...
    (comp.sys.mac.advocacy)