Capital, Revenue & Capital Charge Costs

[Chris]

Dear All,

I'd welcome any advice on the following please. I have to do a relatively
quick scheduling and costing exercise on a 40-50 project portfolio. I have
MS Project Professional but we are not a server environment yet.

I am going to use just a single line for each of the projects and give each
a (very rough I appreciate) estimate for their duration cost and then link
the dependencies between them to get a view of relative scheduling.

I know that the (very rough again) cost of all of them running in parallel
will be above budget for a single year but through some judicious
dependencies and scheduling across financial years they may be acceptable to
my sponsors.

An issue I have is that each project has an estimate for 3 cost types:

revenue
capital
capital charges (the cost of the loan on the capital - apologies if teaching everyone to suck eggs - not intentional)

I'll assume that the costs are pro rata'd but am happy to be advised
otherwise.

I'm just wondering what the best way to do this might be to set this up so
that I get a relatively simple view on the overall programme costs for
revenue, capital and capital charges in each financial year.

Any advice will be heartfully received.

Cheers
Chris

 

[John]

Dear All,

I'd welcome any advice on the following please. I have to do a relatively
quick scheduling and costing exercise on a 40-50 project portfolio. I have
MS Project Professional but we are not a server environment yet.

I am going to use just a single line for each of the projects and give each
a (very rough I appreciate) estimate for their duration cost and then link
the dependencies between them to get a view of relative scheduling.

I know that the (very rough again) cost of all of them running in parallel
will be above budget for a single year but through some judicious
dependencies and scheduling across financial years they may be acceptable to
my sponsors.

An issue I have is that each project has an estimate for 3 cost types:

I'll assume that the costs are pro rata'd but am happy to be advised
otherwise.

I'm just wondering what the best way to do this might be to set this up so
that I get a relatively simple view on the overall programme costs for
revenue, capital and capital charges in each financial year.

Any advice will be heartfully received.

Cheers
Chris

Chris,
First of all Project is a planning and scheduling application, not a
spreadsheet or financial application. The basic cost fields in Project
are calculated by work effort x resource rate, material cost, and/or
fixed cost (e.g. negotiated contract). Beyond that you can factor the
rate for any given resource type by using one or more of Project's 25
cost rate tables, or you can customize one of Project's spare cost
fields with a formula.

You might just consider using Project for the scheduling graphics and
then put your costing data in a spreadsheet.

John
Project MVP

 

[Chris]

Dear John - apologies for delay in response - have been in bed with 'man' flu!

That's useful advice, thanks. I guess I would say that I would expect MS
Project to give some basic functionality as desired, if only to show a basic
view of funding flow. I did have a further play around and found that by
using a task (say called Proj 1 Rev) with a fixed cost of X prorata'd over a
duration Y then in the Cash Flow Report I got a rough view of what I wanted.

What I couldn't work out was (a) how to just get the report to display
between 2 dates and (b) (taking your welcome advice) how to export the whole
cash flow report to excel (it seems to be in a nice tabular format ripe for
excel) where it would be easy to look at the figures between 2 dates.

Best wishes
Chris

 

[John]

Dear John - apologies for delay in response - have been in bed with 'man'
flu!

That's useful advice, thanks. I guess I would say that I would expect MS
Project to give some basic functionality as desired, if only to show a basic
view of funding flow. I did have a further play around and found that by
using a task (say called Proj 1 Rev) with a fixed cost of X prorata'd over a
duration Y then in the Cash Flow Report I got a rough view of what I wanted.

What I couldn't work out was (a) how to just get the report to display
between 2 dates and (b) (taking your welcome advice) how to export the whole
cash flow report to excel (it seems to be in a nice tabular format ripe for
excel) where it would be easy to look at the figures between 2 dates.

Best wishes
Chris

Chris,
I'll repeat, Project is not a financial planning application. It has the
functionality to show what a project plan will cost based on labor and
material usage. If a baseline is saved, that can constitute a budget and
then as the plan is executed, the actual costs can be compared to that
budget.

Unfortunately the reports feature of Project only allows the user to
display or print that data, it does not allow that formatted data to be
exported. However, most reports can be re-created as a view. For
example, the Cash Flow Report is a formatted version of the Resource or
Task Usage view, depending on how you want to "cut" the cash flow.
Depending on which view you select, you can then use the "analyze
timescaled data in Excel" utility, the Visual Reports feature (if you
have Project 2007), or a custom VBA macro to export the data to Excel.

By the way, you can use the Date Range filter with the Cash Flow Report,
or with either Usage view, to isolate tasks that have data only in the
range specified by the filter.

John
Project MVP

 

[Chris]

Dear John,

Many thanks - point taken on financial expectations. Thanks also for the
useful pointer towards views and filters, which I think will assist with some
other areas I am currently working on.

Best wishes, Chris.

 

[John]

Dear John,

Many thanks - point taken on financial expectations. Thanks also for the
useful pointer towards views and filters, which I think will assist with some
other areas I am currently working on.

Best wishes, Chris.

Chris,
You're welcome and thanks for the feedback.

John