Tracking Costs & Billables in 1 Project

[Andrew Lavinsky]

It seems to me that Project does a good job of tracking cost, but for me,
working in a consulting company, I want to track:

1) Billables - i.e. the agreed upon rate w/ the customer of services rendered
2) Costs - our costs for services rendered

So far, it seems to me that the easiest way to do this is to maintain 2 X
MPP files w/ 1 representing internal costs, and 1 representing external billables.
(perhaps w/ 1 on the Server so I don't mess up resource availability calculations).

I would like to combine both functions into one file....which to me seems
to indicate that I have to do some fairly complex custom field calculations.
(i.e. set up a new Resource Field to contain billables (and not cost) - or
perhaps use Rate Tables - then calculate a Task Field which would total billable
cost for that specific task.

My question then is has anyone gone down this road before? Am looking for
suggestions/guidance before I invest the time in developing the fields, calculations,
and views.

Thks

-A

 

[Trevor Rabey]

2 x files sounds like, well, definitely not good, and unnecessary.
MSP is primarily about costs (I mean, besides scheduling, CPM and everything
else etc), which has to be a reasonable starting point.
However, it is not hard (done it many times) to build in pricing, margins
etc in one file just by using spare fields to calculate price as a function
of cost (eg x 1.2), or if price is fixed to calculate margins.
That is a typical calculation that the spare fields are there for.
You use the term "Billable Cost".
Mixing up Price and Cost like this will lead to confusion.
To make it work you must estimate and track costs carefully and accurately.

 

[Steve House [Project MVP]]

Adding to Trevor's comments ... rate tables won't do it. While you can
enter up to 5 tables of rates for a resource, they really are there to allow
for the fact hat some people get paid differently rates on the specifc job
they're doing at the moment. I charge different rates when I'm wearing my
developer hat than when I wear my training hat. But for any given task, one
and only one table can be in effect. So you can't have one table that holds
internal cost rates and another table that holds external billing rates and
have them both in effect on the same task so you can calculate margins etc.
Trying to go down that road will be an excercise in futility.

Trevor's suggestion to use some of the user-definable cost fields to
calculate your billings will work but you need to be very, very careful with
it. For example, if you bill a flat $XX for a day or any portion of it,
there's no way for Project to calcualte the billings for a group of 4 2-hour
tasks the resource is assigned to all happening on the same day. The cost
rate is always treated as an hourly wage no matter how the resource is
actually paid and the cost of a task is the number of man-hours the resource
spends on it multiplied by the hourly cost of the resource. If your
billable dayrate is $100 per day or portion, there's no way for it to
calculate 1 8-hour task is billed $100 but 2 4-hour tasks are billed $50
each if they happen on the same day but $100 each if they happen on
different days, that sort of thing.

The bottom line, IMHO, is to use the right tool for the job. MS Project is
a class act for building efficient project schedules, estimating project
costs, and tracking performance against plan. But it really doesn't have
the tools of a purpose built Time & Billing application, nor is it a very
good project accounting application. It can provide some of the INPUTS to
an accounting system of some sort but it doesn't track all of the
information that is
required for a complete managment picture. Any attempts I've seen to get it
to function as one-tool-that-does-it-all usually end up in a less than
satisfactory kludge. You might be able to get it to work for you but you
need to ask yourself if it is worth the effort and the risk.

 

[Rod Gill]

For a straight consultancy project, if the project doesn't share a pool then
here is one approach to solve a simple calculate margin task:

1) Use standard rate (rate A) for customer billable rate. Use Rate B for
internal rates
2) View Resource Usage
3) Insert Cost rate Table column
4) Set cost rate table for first task to rate B then copy down whole column
5) Costs now calculated based on internal rates
6) insert then copy to the cost column to Cost1 column
7) restore cost rate table column to rate A
8) Add formula to Cost2 column to calculate difference between Cost1 and
Cost.

You now have margin in Cost 2. It won't update as schedule updates: you need
to repeat the process. However, it makes for an easy macro you can run when
ever you want.

However, as Steve points out Project can't do job costing effectively, this
is just a solution for a simple scenario.

--

Rod Gill
Project MVP
Visit www.msproject-systems.com for Project Companion Tools and more

 

[Andrew Lavinsky]

Thanks!

For a straight consultancy project, if the project doesn't share a
pool then here is one approach to solve a simple calculate margin
task:

1) Use standard rate (rate A) for customer billable rate. Use Rate B
for
internal rates
2) View Resource Usage
3) Insert Cost rate Table column
4) Set cost rate table for first task to rate B then copy down whole
column
5) Costs now calculated based on internal rates
6) insert then copy to the cost column to Cost1 column
7) restore cost rate table column to rate A
8) Add formula to Cost2 column to calculate difference between Cost1
and
Cost.
You now have margin in Cost 2. It won't update as schedule updates:
you need to repeat the process. However, it makes for an easy macro
you can run when ever you want.

However, as Steve points out Project can't do job costing effectively,
this is just a solution for a simple scenario.