Better way to track materials?

[Pat]

Right now I use a general materials resource (called "materials") to assign
material costs to a task. I have the rate set to $1/unit so the units
entered is the cost estimate of the materials needed for the task. So far
that approach has worked well, especially for initial project planning.

What I'd like to do now though is link these material costs to an itemized
list of the actual materials needed for the task. For example, I may have a
task, "Construct motor stator" that requires the following material items:

1000' of 20 guage wire: $100
10 lbs stamped laminations: $150
10' of slot liner: $20
electrical varnish: $30
-------------------------------
Total = $300

Right now I'm using an Excel spreadsheet to track these items and hand
updating the totals (in the materials resource units) in the project plan.
However, that is rather tedious to do (to say the least), and so was
wondering if anyone knew of a better way to do this? It would be nice to
have all this information together in the project plan (rather than having
it in a separate spreadsheet) but the only way I know to do this now is to
list each individual material item in the Resource Sheet, which would make
the resource sheet very, very long. Besides, most of these materials are
only used for one task. Any suggestions?

Thanks for the help.

Pat

 

[Steve House [Project MVP]]

You're asking for the "better way" and perhaps unfortunately, that will be
the long way you already know about. Enter each item as an entry in the
resource sheet, set the material label to the unit you use to track
consumption of that item, and the rate as the unit cost. When you assign to
a task, enter the estimated number of units you expect to use. If it's any
consolation, you're bound to use things like 20 gauge wire in more than one
task. You wouldn't enter it in the 1000' length in the resource sheet with
an entry for each task where it is used, you'd enter it once as "Wire, 20
gauge, label = feet, rate = $0.10" and assign 1000 feet or whatever as the
units in each of the tasks that use it. It may make a long resource list
but that really is the best way to go about it.

 

[John]

Right now I use a general materials resource (called "materials") to assign
material costs to a task. I have the rate set to $1/unit so the units
entered is the cost estimate of the materials needed for the task. So far
that approach has worked well, especially for initial project planning.

What I'd like to do now though is link these material costs to an itemized
list of the actual materials needed for the task. For example, I may have a
task, "Construct motor stator" that requires the following material items:

1000' of 20 guage wire: $100
10 lbs stamped laminations: $150
10' of slot liner: $20
electrical varnish: $30
-------------------------------
Total = $300

Right now I'm using an Excel spreadsheet to track these items and hand
updating the totals (in the materials resource units) in the project plan.
However, that is rather tedious to do (to say the least), and so was
wondering if anyone knew of a better way to do this? It would be nice to
have all this information together in the project plan (rather than having
it in a separate spreadsheet) but the only way I know to do this now is to
list each individual material item in the Resource Sheet, which would make
the resource sheet very, very long. Besides, most of these materials are
only used for one task. Any suggestions?

Thanks for the help.

Pat

Pat,
First of all Project is not designed to be a detailed accounting
application, although we are finding a fair number of users who try to
use it as one. To be honest, I think you best bet is to keep doing the
detail materials list in Excel. It can always be linked or otherwise
tied to Project (e.g. VBA) to make the updating easier.

It might be possible to come up with some scheme using spare fields to
track the details in Project, but I personally don't think it would be
worth it.

John
Project MVP

 

[Pat]

Thanks Steve and John for the replies. Reading your responses made me think
a little more about this, and I think the crux of the issue for me is that I
would like to see both an itemized list of the specific materials required
for a task and well as a "summary" giving the total material cost. If I
list the specific items in the Resource Sheet, then I'm not sure how I would
show the total material cost for a specific task. Furthermore, if I wanted
to distinguish between material (consumable) and equipment costs, that would
be a further complication. But perhaps there is a way (maybe with a custom
report, or such). Is there?

Another issue also is when assigning a material resource to a task, it would
be nice to be able to "define" the material resource in the same form (or
view) used to assign it, rather than having to define it first in the
Resource Sheet and than select it from a (long) list of resources when
assigning. Again, maybe there is a way to do this. I'm still learning
Project so it could be may of my objections are based on ignorance.

Any further suggestions or comments are welcomed, and thanks again for your
help.

Pat

 

[Trevor Rabey]

It is possible to think too far ahead and make the problem appear more
complicated than it has to be.

Putting the data in and making a realistic model of the project is a first
essential step.
Getting the data out again as information categorised, filtered, grouped,
sliced and diced in various ways so that you can analyse it and report on
it, is a separate problem.
But it won't be a problem if the data is in there in the first place.
Printing out the various Views is only one way to get the output.
If they don't give you what you want, you can probably get it from the built
in reports or from the dump to EXCEL with the analysis toolbar.
Then if that doesn't get you there, there are customised Views and custom
Reports and export or even just copy/paste data to EXCEL or ACCESS.

True, MSP does not let you create and define the Resources completely in the
Assign Resources Dialog.
It can be done there to a limited extent but you may as well not bother.
A visit to the Resource Sheet is unavoidable, so you may as well make the
most of it.
I try to identify, create and define the Resources in big batches, rather
than piecemeal, so its done and I don't have to go back and forth too much.
I figure that every Task will have at least probably 1 x Work Resource and 1
x Material Resource, and probably more.
So 1000 Tasks will be expected to generate 2000 Resources minimum.
Of course, usually, Work type Resources will be used for multiple Tasks so I
save a few there.
Similarly, Material type Resources might be used (sort of) for multiple
Tasks, but really, if you use 10 m3 of concrete in one Task and 10 m3 of
concrete in another Task, it's not the same 10 m3 of concrete. You may as
well eliminate this as an issue by creating a separate batch of concrete for
each concrete Task.
eg:

concrete batch #1
concrete batch #2
..
..
etc.

Ok, it makes a long list but it doesn't have to be a lot of typing.
Doing the editing in EXCEL with concatenation and copy/paste etc and pasting
it into MSP makes long lists real fast.

I find it useful to have the Task list from the Gantt Chart printed out next
to me while I work in the Resource Sheet View, listing the resources Task by
Task.
Later, I re-sort them.
Another way is to copy the Task list into Excel (column A) and put the
material resources for each Task into Column B and Work type into Column C,
then copy.paste them back into the MSP Resource sheet.

Assigning the resources to the Tasks can be done in different ways, with the
Task Information dialog, or the assign resources dialog, or the split Gantt
Chart or even just type and copy/paste into the Resource Names field in the
Entry Table.
You don't always have to scroll through 2000 Resouces to get the one you
want.
If I know the ID number of the Resource I want to assign (because the
Resource sheet is printed on a piece of paper next to me) I use the split
Gantt chart and just type in the Resource ID number.

To further categorise materials into consumable and equipment you can use
the Resource Group or a spare text field (better, I think) and then sort,
filter and group on that.

It's really the sorting, filtering and grouping that provides the fine
tuning and turns raw data into information, and is an essential skill.

Trevor

 

[Steve House [Project MVP]]

Equipment is not a material resource, it is a work resource and is handled
in exactly the same way you handle human resources. Material resources are
consumed by the task - either incorporated into the deliverable like the
wire in your example, or consumed such as fuel for a bulldozer. The
purchase cost of the bulldozer itself, however, is not a material cost.
It's cost to the task would be the hourly rate for its use, either as a
chargeback "rental charge" or as the prorated depreciation cost.

The resource usage view can give the summaries you want as will cost table
in the resource sheet view. The resource sheet can give total cost for each
resource while the cost table in the Gantt view gives the total cost per
task. The task usage and resource usage views can also give you breakdown
and summary costs.

It actually is possible to have resources defined "on the fly" as you assign
them but it's not at all a very good idea to turn that "feature" on. If you
use it and make a typo entering the resource, you end up with a new entry
where you really shouldn't have. Believe me, the headaches it'll cause far
outway the small convenience of using it! Had a client call for help a few
years ago who had close to 2000 entries in their resource list, about 100 of
which were real resources and the other 1900 completely bogus resources
caused by typos and inconsistent data entry habits. The only way they could
fix it was to start over - not something that made them very happy.

 

[Pat]

Thanks Trevor and Steve for the additional feedback.

Based on your comments and clarifications I now think my best bet is to just
list the specific material resources in the Resource Sheet (as you advise)
even if it does make for a long list. It sounds like judicious use of
Resource Groups and sorting can help keep it organized (which was my main
concern with doing this), and that I can get the summaries I want with the
right views. I don't want to "force" Project to work in a way it wasn't
intended (which is what I think I was trying to do), and I expect once I get
accustomed to doing it this way I probably like it almost as well. For sure
I will like having all the information in once place!

Thanks again, and I'll probably be post back with additional questions as I
get in to this more.

Pat