Creating a Cost and Revenue Cash flow Report MS Project 2010

I am not sure what difficulty you are having because what you describe is pretty much how to do it.

You can accumulate your costs in four ways. You have work type resources, material type resources, cost type resources, and fixed costs. There are some advantages in using resources rather than fixed cost, but assigning fixed costs to tasks is also convenient and quick for tasks for which you don't need to categorise or dissect or segregate costs by resource or plan/track the work hours.

For example, "lay 10000 bricks" might cost $14000 and if you have a fixed price subcontract with Bob's Bricklaying Inc, you might just use a fixed cost on the task. However, in this case Bob's Bricklaying Inc does not appear in the resource sheet as a resource, and neither do the bricks.

But let's say that fixed cost on the task is good enough. You then need another task or event which is "get paid for bricklaying", and has zero duration, and this is a FS0 successor to the "lay 10000 bricks" task, or maybe FS+30 days. So if the customer pays $15000 you can put this in as a negative fixed cost on a zero duration task. Your task may cost $14000 pro rata if you choose to accrue the cost for the bricklaying as the days go by, but your customer pays you after it is done, in a single payment. So your cost is continuous but your income is incremental.

Of course, in this business model, unless you get paid a deposit in advance, you are financing the construction project with your cash or overdraft, so you are cash flow negative until you get paid. This is the worst possible business model and is all you need to know to understand why construction is a very risky business. In Western Australia 24% of all business insolvencies are building companies, and they can go bust even in the middle of a construction boom if they lose control of their cash flow.

Rod

The reason for it is accurate forecasting against the programme. In my case revenue is directly related to the schedule. I am a quantity surveyor for a main contractor, a fundamental part of the job is monthly reporting on cost and value. Cost being what we pay the subcontractor for a particular item of work, revenue being what the client pays us for that same item of work. We need to report actual financial performance against forecast. The forecast needs to reflect the construction programme.

There are other ways to do it and we currently use excel. I thought using project might be an accurate method when you have a complex programme involving multiple tasks running simultaneously in any given month. An advantage of linking the cost and value to the programme is if we run behind programme I can slide the programme to reject actual progress and the costs will move too. 

Joe

JoeT-982,

Not true. When you go to the Cash Flow report, edit the crosstab report by checking the option "And resource assignments". The report will now show cost, revenue and the delta.

John

Thanks for your replies. The issue I have is that our business model is based on being paid from the client through monthly valuations due to the scale of the projects, which reduces the cash flow risk that you mention. So having a zero duration task "get paid for bricklaying" doesn't work as that also needs to be pro rata for the duration of the task. 

task.

So to explain based on your example if the bricklaying spans Aug - Oct I can get MS Project to produce cash flow report which shows the $14,000 fixed cost split over the 3 months. What i need if possible is the same cash flow report to display the $15,000 revenue paid from the client over the same months. So 1 task needs 2 separate values to be pro rata over the same duration.

I suppose if there a way to get a cash flow report to show budget cost and actual cost on the same report over duration that would work. This way I could use budget cost as revenue and actual cost as cost from the subcontractor. My problem is getting the report to display the info I need.

I could potentially produce the same programme twice one for cost and one for revenue but I wanted to see if it was possible to combine the two in one cash flow report.

JoT_982,

Indeed using the Fixed Cost field for fixed subcontract work is the normal method, although with Project 2010 you can also create a cost type resource as well as work and material type resources. You can get an explanation of each field if you hover your mouse over the field column title and then hit the F1 key. That will bring up the help file subject for that field.

As far as revenue, keep in mind that Project is not an accounting application so it probably won't do everything you require in that areana. However, what you are referring to as your "revenue" is really the Actual Cost field. You can either have Project calculate actual costs based on resource rates and how much of the effort is completed at any given time, or you can also manually enter actual costs (that option setting is under File/Options/Schedule group).

You may have already discovered it but if not, Project has a cash flow report. You will find it under Project/Reports group/Reports icon.

You should also set a baseline so you can track your projects actual performance against the original plan.

Hope this helps.

John