Simple Timesheet

[John Simko]

I am currently using MS Project 2002 standalone and I am trying to save a snapshot of the resource usage view using a date range filter into Excel, which I send out to resources as a timesheet. This works, but requires a multi-step cut and paste process. While I intend on installing MS Project Server 2003 in the long run, I need a short term solution. I am willing to purchase a COTS tool if required.

Please advise if there is a better way for doing this.

Thanks,

 

[John]

John,
If you are willing to pay, I would be more than happy to develop a macro
that would export whatever Project data you need into Excel. If
interested, contact me direct.

However there is probably a reasonably close free solution. Try using
the "analyze timescale data in Excel" add-in which is available on the
Analysis toolbar. Run it from the filtered Resource Usage view. You may
have to do some re-formatting in Excel but it's a whole lot better than
cut and paste.

Hope this helps.
John

 

[John Sitka]

"This works, but requires a multi-step cut and paste process"

I don't know how tot cut/copy paste anything from the resource usage view.
How does one do that?

Thanks.

I am currently using MS Project 2002 standalone and I am trying to save a

snapshot of the resource usage view using a date range filter into Excel,
which I send out to resources as a timesheet. This works, but requires a
multi-step cut and paste process. While I intend on installing MS Project
Server 2003 in the long run, I need a short term solution. I am willing to
purchase a COTS tool if required.

 

[John]

John,
From the Resource Usage view a two step copy and paste operation is
required. That has to be followed by manually formatting the column
headers.
1. In the Resource Usage view, select one or more rows on the left side
(static tabular data).
2. Edit/Copy
3. In Excel, Paste
4. In the Resource Usage view, select the corresponding rows on the
right side (timescaled data)
5. Edit/Copy
6. In Excel, Paste
7. In Excel manually add appropriate column headers

Pretty straightforward but tedious. For a more automated approach, see
my other post.

John

 

[John Sitka]

Thanks,
I hope to use the Excel Anaylsis but the machine I have project on dosen't
have Excel,
No problem except the CD is at home. Along with my DVD player to use MSDN
disks.
Off to share someones DVD player... sneaker net revisited.

 

[John]

John,
Wow, I've never heard of a Windows machine that didn't have Excel.

John

 

[John Sitka]

It's my responsibilty to have it on there at my liesure, that's why it isn't
there. ;-)

I'm with the IT department not a user. Most used application is notepad. But
the users have never
been able to make Project work as a viable shop floor scheduling tool. We
need to have the
ability to schedule overallocations, evaluate how to remove them (management
meetings).
And still maintain a fixed finish date. The reality is that every resource
upon Project creation will generally run 300%
or so overallocated. Thus we need an early warning system so to speak that
highlights the urgency
and generates a call to action for outsourcing.

It has been asked of me to figure out how it can be done. Personnaly I don't
think it can without distributing the
task lists and making each individual resource responsible for marking the
actual work and percent complete done at frequent
intervals throughout the day. I feel this should be a web based update
screen that forwards the declarations of completeness
or "how much time left" to a project manager for approval. I like to call it
the, guy with the clipboard. He runs around all day
and taps people on the shoulder and says "Are you done yet?" Like that cell
phone commercial."Can you here me now?" over
and over again. Without that kind of feedback I just don't understand how it
could ever work. That feedback approximates 100% fill of the
resources as described in Project, a thing which only the frantic juggling
of the individuals resposible for the work cells attempts to
maintain now. Their decsion making now keeps them busy but with no
predictability as to the impact a shift in priority or a practical
delay may have three months from now. But I'm no Manufacturing Engineer so
right now I'm just baby stepping through the aspects
of generating useable Production floor reports that will communicate to the
responsible parties just what is required to make a deadline.
For better or worse I don't see this kind of reporting let alone distributed
task status tracking in Project.
My gut feeling a few weeks ago was that only Project Server would have a
better chance of being be capable but without
Project knowledge itself that seemed a big mountain to climb. Beyond that
when the salespeople come to visit I need to be able to
ask them what can this package do that Project can't. At this moment if they
said It will get you a filtered report on overallocated resources
I'd buy it. ;-)

 

[John]

John,
Wow, I'm not sure where to start. This is the kind of scenario that
Steve House (another Project MVP) likes to delve into.

From my perspective, I think whether or not Project is a viable tool as
a shop floor scheduling application depends on how it is set up and how
much expertise is available to manage it. If your plan starts out with
resources being significantly overallocated something is probably
grossly wrong with the overall approach to getting the work done.

And, the part about someone running around with a clipboard saying, "are
you done yet" is certainly analogous to a chicken running around with
its head cut off (a severely broken planning/scheduling/management
system). Even though you present it as a figurative representation,
unfortunately there are entities (companies) that actually work this way.

I'm not sure how to help you. It sounds like you need some project
management consulting expertise to get things headed in the right
direction. I don't do that. If you are however only interested in
getting reports from a Project plan and those reports are not already
available, given the built-in reporting features of Project, then VBA
can be used to generate any kind of report you need. That I can help you
with.

John

 

[John Sitka]

Thanks for the thoughful response.

When taking on a new project the plan is at best a guess. Picture 50
projects in a Master Project each with 100 tasks fairly well defined in
terms of work as individual tasks but
when all are assigned from the same fixed resource pool the actual work as a
sum of many becomes very significant. Mix in a few "fire fighting" or "top
priority"
situations that effectively bring a resource off line and plans will get
radically changed very qickly. How to capture this?
So without a constant input of "the headless chicken" The plan is flawed
almost as soon as it gets started. I don't have a solution as to how we can
capture the actuals and status in a distributed way except the clipboard
guy. To me the clipboard guy is the best ancient representation of web
interface that a resource (as person) or person responsible for a resource
logs into, is presented with "their" task list and updating abilities to set
the actuals for each task. How else to gather all the nuances that
contribute to massive shifts that go on in a master project?

I don't like the overallocation idea either but it is the only way I have
seen so far to display/recognize early on that the
resource needs to be changed to one of infinite capacity, an outsource. The
other ways I'm guessing hinge on the methodolgy
applied to management and scheduling. Some say run levelling and work back
to meet a deadline? I really don't know, these are skills
I don't possess.

So on to reporting.

Got some results early from Analysis with Exell and at first I said
"woohoo...... score" but then I realsed the numbers in the work row and
percentage allocation
when the analyisis comes from the Resource Pool dosen't have/can't have the
"Project" (no tasks) and similarily the Analysis from the Master Project
dosen't have the
percent allocation (no resources). Is this just one of those join things
SQL folks make look easy?
Combining both Analysis on one sheet can make sense to "look at" in the
simplist case but it won't be to sensible over 50 projects.

So If you know a trick to get false tasks into a resource pool or virtual
local resources into a project that might work.

Thanks.

 

[A.G.]

This is exactly what our company is experiencing. We have different
dept. within our shop and I am trying to use MS Project plans and
create a schedule for different departments and furthermore, for
different machine. What is a feasible solution to the problem you
propose? Have you used Project Server or Project Central to create
production floor reports? thanks in advance...

-ag

 

[John]

John,
When I worked with "live" projects on a daily basis, we had over 70
subprojects, most with several hundred tasks each. And, unfortunately as
soon as a plan was put in place that actually met certain critical
milestones, aw-sh#% happened and we had to come up with workarounds.
That is the reality of project management.

In my mind outsourcing is not a viable solution if, as you implied in
your previous post, procuring outsource help isn't initiated until the
plan is in trouble and management approves the outsource request. If
past experience shows that all the (necessary) work will not be
accomplished in the time required, then up front hiring of temporary
(or permanent) help is prudent planning for success of follow on
projects. Running around with a "chicken chart" sounds inefficient as
hell. Clearly a better process is needed. As I suggested, it might be
worthwhile to hire a consultant to get you started.

With regard to the "analyze timescale data in Excel" add-in, it is an
excellent generic tool but it won't necessarily do everything you need.
You can get the kind of data you mention, but it requires a custom VBA
macro to pull the data and put it into a convenient form. I guess that
is the trick you are looking for. One of the most amazing things about
Project (and other Microsoft apps) is the inclusion of VBA as a basic
component. With VBA, Project can be made to do anything you need or
want. On countless occasions, I have written code to develop specialized
reports, develop time saving tools for everyday users, and made life
easier for business people who have to interface Project data with
company financial systems. (Sorry, my humbleness escaped for a moment
there.)

At least, that's my humble input.
John

 

[John Sitka]

Well I sure respect what you have to say; I really need to pursue this cause
the light bulb just isn't coming on.

Yes I "AGREE" the chicken chart is ineffiecent but HOW HOW HOW does the Data
get into Project otherwise. Where does all the evolving status information
COME FROM. Sorry for the capitalization but I'm trying to get past the
circular arguements that seem to get generated so frequently when discussing
Project. If I don't have daily or hourly actuals! How can I have a true
schedule?

Check the message from Al Wallace above. He describes a report that is
directly related to the same concept.

Now the idea that outsourcing isn't viable well then maybe that is the
conflicting Project concept that will forever leave this broken.

ex.
If I build bulldozers. HUGE ones that take a year to build, and in January I
set up each bulldozer order as a project. Then suddenly in March there is a
gold strike in northern Canada. The mining companies all use my buldozers
because they are the best. So they order ten more for the end of the year.
Wow now I have to produce ten more bulldozers in the year and I'm already at
capacity. How do a fix this, I weave outsourcing in amongst the projects to
cover the areas where resource constraints are such that they can't be
remidied by hiring more people. I know this works because five years ago the
same thing happend, we delivered all buldozers within a month of original
delivery date rather than making them wait six months as would have seemed
reasonable to do with the way project painted the picture and the customer
was happy to get them. Now this time we want to use project to gain even
more insight to the usage of those outsources even earlier upon the inital
introduction of "boon time" orders and drop that month late to one week. Pat
ourselves on the backs go back to business as usual and be even more ready
for the next gold strike.

So in effect the idea is that there are things that Personnel can solve and
things they can't. Mostly physical things like building bays and huge
overhead cranes that take a year to install themselves but are key critical
path resources. Plus you don't want those bays and cranes sitting unused for
years until the next boon times.

 

[John]

John,
There are several ways to get the data into Project, some of which I am
not knowledgeable (i.e. Project Central and Project Server). I also know
it is possible to set up a web based input but again I do not have any
experience with that. The most direct way, although not necessarily the
best or most efficient, is manual entry. In that context, it becomes the
chicken chart and unfortunately it is sometimes the only method that
really works (sad to say). My example is the following. At my company
our program required a monthly status for each of the 70 Cost Account
Manager (CAM) files. Training and systems were in place so the CAMs
could do the update on their own. However, invariably we had some
laggards. In addition, the business group found that a high percentage
of those who did status, did it incorrectly. The business people had to
call and coerce to get the information so they could run the data
through their monthly accounting process. Kind of a half automated, half
chicken chart approach. We never did find a good solution for lagging
CAM input but we put a squelch on the error rate of those who did
status. We developed automated tools (VBA) that automatically checked
status input and then logged the fact that CAM status was complete once
everything was correct.

If your business is building bulldozers (which understandably aren't a
quick turn operation), I'm a little surprised that you need daily or
hourly input of actuals - weekly seems more realistic. Regardless, there
are basically two schemes for updating a Project file with actual data.
One method is to train the performing resources (CAMs in our case) to
input the data directly to their respective Project file. Incentives can
possibly improve compliance. A central entity (e.g. business, project
manager, etc.) monitors inputs, compiles the data and develops output
(e.g. reports) for feedback to the performing resources and management.
A second method is the direct chicken chart. Have one (harried)
individual gather the necessary data from the performing resources and
then enter it into the file for compilation and report generation. I
favor the former simply because it gives (forces) "ownership" to (upon)
the performing resources. Kind of like, "hey, managing and controlling
your part of the overall project (i.e. your plan of tasks) is part of
your job whether at the individual or functional level". You know, part
of the team.

Given the above wisdom (?) and a reasonable project plan (i.e. one that
makes sense and works (i.e. meets requirements)), the question now is
how best to set up a method to gather the necessary plan metrics (e.g.
periodic status). For that, refer back to my first paragraph. In our
particular situation, we simply had all our CAM files on a central LAN
server. Each CAM had read/write access to their own file and business
and project management (my role) had full access to everything. This was
before Project Central and Project Server existed. I'm sure the latter
provide more tools and functionality but the concept is the same as our
simple approach.

I don't know if I'm helping you but with all this rhetoric, does this
make me a consultant?

John
P.S. Who is Al Wallace and where did you say that gold was?

 

[John Sitka]

I don't know if I'm helping you but with all this rhetoric, does this

make me a consultant?

In my mind it does. This stuff is awesome.
I had a meeting this morning and my head is buzzing with insight.

P.S. Who is Al Wallace and where did you say that gold was?

Al Wallace posted in the last couple days.
Currently, we can only set/see task dependencies. How can we show resource
dependencies? For instance, I have two >>tasks for Fred. Fred is the only
one I have currently available to work those tasks. The tasks themselves
are not related, and one should be completed before the other begins.
However, the >>tasks themselves are not dependent upon one another. They
are dependent on Fred, though.>>

Sorry that really doesn't directly address the input of actuals which you
replied to. But it is central to the two concepts that are in my head right
now(and may be working at cross efforts).
Al Wallace was wanting a report that outlined resource dependencies as a
tool to make alternative resource decisions. As it applies to our current
conversation that would represent the outsourcing. Non existent resources
until there is an overallocation. That is long lead time manufacturing you
don't know "what and when" you need specifically until you get there because
shit has happened all along the way. I realize now I need project to
automatically substitue resources in order to calculate a "best fit"
constrained be end date. If that means weighting the resource suitablity or
something like that I'd love to know about it.

P.S. Who is Al Wallace and where did you say that gold was?

The gold is all over the place. Come get some if you can stand the cold,
bugs and loneliness.



The bulldozer stuff I made up. But the concept is the same.

Lets say each bulldozer needed to progress through five tasks spaced amongst
a total of five hundred that relied on a single resource
during a year build.

Lets make the resource up for clarity
An extremely articulated long reach gundrill that cuts the bushing holes for
a power take off shaft.
This laser guided big bore long reach drill was $5 Million we only have one.
That was fine for the first year when we just used it
for the one PTO drilling operation per dozer but it does such a great job;
it now handles four additional tasks per dozer.
No problem as long as the scheduler does his job.

If that single resource was booked solid for
the full year and one buldozer's turn came up but it wasn't ready to take
it's turn. (The welder put the bucket bracket
1 inch too far left this morning)
I need the impact of that "actual" turn missed. The actual is him running to
the web screen submitting status/actual
work done this morning " = -3 " cause he has to take it off and reweld!

Now I spring into action.
What do I know? That the turn was missed.
"What-if"... I move the turn three days from now, just before the "extremely
articulated long reach gundrill" was scheduled for maintenance.
We always overestimate the maintenance duration a little.
Well that three day delay of course cascades down to a few hundred other
tasks but what stands out as most devastating is
missing a hydralic fitting session two days from now. So this recovery slot
won't work
Next option is I simply have to get it done in the next two days. But where
do I put it? A shift one way or another;
and I hope I've been graphic enough but I dare say an hour here or there
could swing the rest of the year huge amounts.
Maybe not for this dozer but tack on weeks on the Decembers Dozers because
I'll probably have to do this exact same thing again.
Those welders keep forgetting the thickness of the bucket bracket
reinforcement, argh kids!.

Third option is to bump everything. What is the result? that is what I need
project to tell me.
The only result that is unacceptable is a missed end date or an
overallocation.

That is a long winded example of how as I see that actuals are so vital. I
don't see it as a MICRO issue either. If I'm to
accomplish 3 custom machining operations of 8 hours each in a twenty four
hour day I'm pretty sure I need to know if the cutting speeds needed to
be reduced and each operation took 10 hours then I'm into the next day
right. That affects my plan, stack 100 projects under it pulling from the
same
resource pool and I can't imagine anything other than profound effects on
end dates. I do agree though that status expectations could be modelled to
take
place a few steps farther removed but where, to what end and where is the
break even point?. What is too short a time span for status input? I'm not
sure, but I can't
PLAN for one that is too long a delay as the resource utilization would not
be anywhere near what experienced humans making judgement calls can
approximate
without anything more than a general guideline. If long range impacts could
be seen at each of those continuously reported status requests
those experienced individuals would make even sounder judgements, meet more
and more oppresive end dates or ask for help(outsorce)
earlier, when it matters most. That's really the most basic question where
are we, enterprise wide.

Thus when first investigating Project Server the act of distributing the
status requests seem so vital to any kind of succes and I still believe
that.
But I still haven't resolved some other key issues about the capability of
Project. Unfortunately another recent post returned a "no" on that one but
I'm
still trying, to understand the Project landscape.


Whew, I'm beat.

 

[John]

John,
Wow, does your Mom know what you do for a living? Does she approve?
By the way, my head is buzzing too, but it is not with insight.

The best tools I know of in Project to handle resource dependencies are
priority and leveling. Unfortunately, I have never used either so I
can't help you other than to direct you to read about them in the help
file.

With regard to your struggle with the viability of Project as a shop
floor scheduling application. Project schedules everything in minutes so
the basic capability is there but I've never tried (or know anyone who
has) set up Project as a shop floor schedule app. Our company used MRP
but I can't say I was too impressed with that either. One of things that
is always a compromise with setting up Project is the level of detail.
My experience says that too detailed a plan (i.e. hour to hour or day to
day) will only result in someone (or several people) spending full time
maintaining the file. As Jack said in your other post, (the one with the
weekend beer), Project will never be able to do a better job of decision
making than the plain old human brain, and Project isn't meant to. In my
opinion a plan in Project should be set up at a level that tracks the
major elements of a program, significant yet measurable tasks that
combine to meet an end requirement. Too much detail clouds the main
issues and only adds to the plan maintenance overhead. That is not to
say that sub-elements of the top level plan cannot have more detail and
for a day to day work plan, a certain level of detail is necessary and
desirable. There will always be aw-sh#% that occur (you probably need to
get some new welders by the way) and except for the major ones that
affect the top level, (the gundrill breaks and a replacement part is 3
months away), day to day problems need to be resolved by the people
involved. The bigger issues (broken gundrill) probably merit a trackable
workaround (outsource gundrill operation to another shop) incorporated
into the master plan.

There are no easy answers. How do 'best in class' companies like yours
do their shop floor control? As far as using Project for one application
or another, all I (or any other of the MVPs), can really do is relate
our experience and share whatever expertise we have. Just so you
understand, we may or may not be called 'the experts' but I have found
that when it comes to using Project, there are no experts, only people
with varying levels of experience (and opinions).

Let me suggest this, lest this gab fest turn into an even larger
exchange of ideas. I don't know about you but I'm running out of
available free time. If you can define some specific things you need (or
would like) Project to be able to do, myself or others may be better
able to give you more help.

John

 

[John Sitka]

Thanks again John, I wrote a huge post this morning but deleted rather than
posted when other duties got my attention.

Wish these kind of things didn't fall on my shoulders, Scheduling is a
discipline in itself and while I have learned much over
the last couple of days the extreme lack of skills has done nothing but
beaten me down. At the moment I feel Project and
current Project users here are no where near capable of getting anywhere
close to what we need but I've felt that way
about stuff before and then things start to click. Like you said

but I've never tried (or know anyone who
has) set up Project as a shop floor schedule app.

maybe there is a reason for this (and lack of Mom's approval., I was a bit
wild early, and the Alley Cat stigma still persists.)

I keep getting drawn to this....

http://www.nmetric.com/production-scheduling-software.asp

hype or not at least they claim to possess what I've been trying to
understand if Project is capable of.

Thanks again.