Current State

"As most of you know, this project is concerned with possibly building a new application called the Resource Management System, which we call RMS for short. This application will take the place of our current processes for scheduling resources and reporting and billing time. I've asked some of you to prepare background information so that we all know how we do these things today. I realize that two days is short notice, so I gave this assignment to the people who are already familiar with how we do things."

Dan glanced at Tim with a smile. "Considering what you went through last night, Tim, it's probably fortunate that I didn't ask you to prepare anything extra for this meeting." Tim nodded. "Only too glad to be a spectator this time, boss."

Scheduling Resources

Dan turned to Marta Wolfe-Hellene, who was sitting at the end of the conference table. "Marta, even though you are fairly new to Ferguson and Bardell, I knew that you had already had some experience with how resources are scheduled. Why don't you begin?"

click to view at full size

Marta stood to speak, referring to the neatly written notes in her binder. "The week I started with Ferguson and Bardell, the Director of Engineering called me into his office. We talked about a number of things, primarily what I would be doing and how the work was done here. One of the things he showed me was his Master Scheduling Sheet.

"This was nothing more than a very large Excel spreadsheet, with dates across the top and names down the left side. The names were all the various resources he was responsible for—the engineers, designers, secretaries, and administrative people. They were listed alphabetically, by last name then first name.

"He explained that whenever a project needed resources, he would open this spreadsheet and scan it to see who was available during the time frame of the project. If someone who was competent to do the work was free, he would block out that person's time by filling in the appropriate dates with the name of the project. If nobody was available, he would have to either juggle resources or negotiate with the salesperson who sold the job to see whether the dates could be adjusted.

"The system is fairly straightforward, and I've seen it used in many small firms," commented Dan. "However, Ferguson and Bardell is not a small firm, which leads me to think that our Director of Engineering might have some issues with this system. Am I right, Marta?"

Marta smiled. "He told me he gave you an earful about it last week on the golf course and that he wasn't taking you golfing again unless you got it fixed. So I think that's a safe assumption," she said to chuckles all around the table. "I've made a list of the issues that he and I discussed. Shall I put them on the whiteboard?"

"That's a good idea," replied Dan. "In fact, put up a Scheduling Resources heading, and we'll list all the issues as we go along."

Marta walked to the whiteboard, wrote Issues List at the top, and then put Scheduling Resources underneath.

The Issues

"The first problem," she began, "is that nothing on the spreadsheet tells you the abilities of the various resources." She wrote the problem on the board. "Because it's a spreadsheet, you can't list a varying number of facts about a resource. So there's no way to search or to sort on abilities. I know all about this problem, because it has already happened to me."

"How so?" asked Tim.

"One of my areas of concentration in graduate school was programmable logic controllers. Unfortunately, the Director didn't know this and it didn't show up on the scheduling sheet, so when he had a PLC job come up, he didn't think of me as a resource. We lost the job even though I was available, because he didn't think he had anyone to do it." Jim squirmed uncomfortably in his chair but didn't say anything.

"A second issue," continued Marta, adding it to the list on the whiteboard, "is that the scheduling sheet is disconnected from the calendar system. Ferguson and Bardell uses Outlook and Exchange as its calendar system, but very few people have any reason to keep their Outlook calendars up to date.

"As a result, managers can't check the availability of resources via their calendars. When managers plug people into a schedule, they either send an e-mail to them, telling them what they are doing and when, or they try to page or call them. Often, managers get back an e-mail saying that so-and-so is not actually available. There is no link back from anyone's calendar to the resource spreadsheets, so someone might look free on the spreadsheet when he or she is actually out on vacation or already committed to a meeting. When this happens, the manager has to go back to the spreadsheet and start over.

"Finally, the scheduling system is basically unmanageable. It worked OK when each manager was responsible for, say, 30 people. With Ferguson and Bardell's drive to create a flattened organization, some managers are scheduling as many as 100 resources. It's simply not possible for them to keep track of everyone's skill set, much less everyone's calendar. To put it simply, we have outgrown the spreadsheet method of scheduling." Marta sat down, and all eyes turned back to Dan.

"Good job, Marta," said Dan. "Succinct and accurate."

Completing Timesheets

Next Dan turned to Bill Pardi. "Chief, I asked you to tell us about timesheets for two reasons: because you've been doing them for years, and because I know you've had problems with your staff's timesheets. So, what can you tell us?"

Bill grunted, "Hope you don't expect me to stand up. I'm worn out from filling out timesheets." Nods and smiles came from the rest of the group around the table.

"Our timesheet system is basically a manual process," Bill began. "We have a Word template that people use to create a new timesheet each week. At the top is a place for the employee's Name, ID Number, Department, Title, Status, and Supervisor. Below that is a table, with columns for Date, Job Number, Category, Phase, Total Hours, Billable Hours, Mileage, Meals, Lodging, and Description." He passed a sample timesheet to the other members of the team, even though most of them were already familiar with it.

"There are some things to notice about the sheet," he said. "The employees fill in, line by line, how they spent their work time during the week. Each line is supposed to have a Job Number. This is the ID number for the job or project, and is assigned by the accounting office."

"How do they know the Job Number?" asked Marilou, who, as an independent contractor, kept her own timesheet and was not familiar with the Ferguson and Bardell system.

"They ask what it is, or they're told, whenever they get a work assignment," replied Bill. "Unfortunately, sometimes they get the wrong one, or one hasn't been created yet because the project is so new. That's one of the problems with this."

Dan interrupted, "We'll get to the issues for this section in a minute, everyone. In the meantime, let Bill finish laying out the current system as it is." He turned back to Bill. "What are Category and Phase used for?"

"Well, they aren't always used, Dan. They are primarily for larger projects. Phase refers to the part of the project they are working on, and Category is the generic type of activity." Bill looked at Jane. "For smaller jobs, there is no Phase. And on jobs where it seems obvious what the activity is, we often leave the Category blank and assume that Accounting will fill it in. Right, Jane?"

Jane grinned. "Absolutely right, Bill. Of course, you know the old saying about assume, right?" Everyone laughed, and Jane continued, "I especially enjoy how every time-entry for your department shows Development as the Category. How do you do Development during lunch?"

Before Bill could say anything, Tim interrupted. "That's easy! If you eat where Bill eats, you develop heartburn during lunch!" This got a good laugh from everyone, even Jim. Bill growled back, "I can't help it if you've got a pansy stomach," but it was obvious he was joking.

Although the team had a lot to accomplish during the meeting, Dan wasn't in too much of a hurry to pull them back on track. These moments of good-natured ribbing were part of the process of becoming a team, and he was glad to see the interaction between the members. He paused to allow the laughter to die down and then said, "Perhaps we can all go to lunch with Bill and judge for ourselves." He turned back to Bill. "I see places for Total Hours and Billable Hours. Why is that?"

"Obviously, not all of the work done by everyone in the company is billable," Bill answered. "Even billable resources go to training, for instance. And we occasionally have a situation where a resource decides that a piece of work took longer than it should, and they list the time but don't consider it billable. We try to minimize that sort of thing, but if it is in good faith, we allow it."

He continued, "The Mileage, Meals, and Lodging columns are for employees to turn in expense items. Of course, for meals and lodging they also have to turn in receipts. The last column, Description, is important because it is the column that actually goes onto the invoice for the customer."

Marilou interrupted again. "What if someone has an expense that isn't listed on the form—say, a book they purchase?"

"Well, it depends," said Bill. "If the expense is billable back to the client as part of a project, the employee lists it under one of the three expense columns and then explains what it really is in the Description column. If the expense is not billable to a client but is an internal expense, it doesn't go on the timesheet at all. Instead, it's handled through an internal purchase-order system." Marilou nodded that she understood, and Bill continued. "Once the employee finishes filling in the timesheet, it's e-mailed to the central office. We have a Time alias set up in Exchange so that timesheets can be e-mailed either through Outlook or via the Internet."

"At which point it becomes Jane's problem," Dan said. "Alright, Bill, what are the issues with this system?" Dan moved to the whiteboard, picked up the marker, and turned back to the group. "And, since you are worn out from doing your timesheets, I'll do the writing for you."

The Issues

"My part of the process will probably win the prize for having the most issues," said Bill. "One of the largest issues is the possibility of error because people enter their own time. The Word table can't check the data as it is entered, so people often miscalculate or misenter their hours, especially if their time is so chopped up that the table runs to two or three pages. We also get wrong project numbers or no project numbers all the time. If the project number is wrong, the time gets billed to the wrong project. Sometimes we catch it before it goes to the customer, but occasionally we don't, and the customer gets billed incorrectly.

"Another issue is the fact that the timesheet is static data. In other words, there is nothing on the screen that changes as people enter the data to give them feedback about what they have entered. Sure, they can add it up manually, but often they are hurrying to get their timesheet done and e-mailed before the deadline, and they just don't take the time to check it thoroughly. As a result, they leave out an entire day, or they enter something twice. If something updated a total every time they completed a line, they would be more likely to get it right the first time.

"A third issue—Marta already mentioned this one—is that people's timesheets are disconnected from their calendars. Some people use their Outlook calendar, and others use various other calendar programs and hand-held things. Whatever time-tracking method they use, they have to open it up, read the data from it, and re-enter the data into their timesheets. There's no quick-and-easy method for dumping their calendar data into their timesheets, even as a starting point that they could then edit.

"Which leads to another issue," Bill said as Dan continued to write. "This may seem esoteric to some of you, but our current system encourages duplication of data. One of the primary principles of data management is to store data once and only once, and preferably in a central location that is easily accessible to everyone who needs it. Currently we have time data stored in various user devices and documents, which is then rekeyed into the timesheets, which are then entered again somewhere else. We need to cut down on the number of places this data is kept."

"We hear you, and we agree," said Dan as he finished noting the issue on the board. "We may not solve this problem completely, especially in the first RMS release, but we can certainly put it up here as a target." He turned back to the board. "What else?"

Bill looked at his notes and then at the group. "All of the above come together to make the system a pain to use." There were nods of agreement around the table. "So most of our employees do their timesheets at the last minute because they hate the hassle. And as a result of that, the timesheets are often late, which makes the rest of the process late." He looked at Jim, the CFO. "I'm assuming that when the billing process runs late, it causes some other issues that you would know about, right?"

Jim nodded vigorously and started to say something, but Dan interrupted him. "Jim, would you mind dealing with those further implications as part of item III on the agenda? I would rather give you a block of time all together than piece it out as part of item II, because I think what you have to say will be valuable and I don't want to dilute its impact." Jim thought about that for a moment, then said, "I think that's a good plan, Dan. I'll make some notes as we go along, and deal with them when we get to item III."

Recording Time

Dan turned to Jane. "Alright, Jane, we've scheduled the resource, done the work, filled in the timesheet, and e-mailed it in. What's next?"

Jane opened the manila folder in front of her and pulled out copies of a flowchart, which she passed to everyone around the table. "I thought it might be easier to follow this part of the process if I charted it out for you." Murmurs of appreciation followed as the team members looked at the chart.

"As you can see, Accounting's part of the process begins in the upper left, when the timesheets are received in the Time alias in Exchange." She looked up at the group. "Because of the volume of timesheets, we have eight different clerks with access to that alias. We rotate the task among them, because if we assigned it to anyone full-time, he or she would quit." Looking back at the chart, she continued. "No matter who's doing the work, the process is the same. We open each e-mail and save the attached timesheet in a directory named for the employee. We use the date the e-mail was received as the name of the file when we save it. Then we open the file and print it.

"Once we have the paper copy, we enter the data into the time and billing add-on for our accounting package. We then print a summary of the time for the week, by team, and fax or hand-deliver the summary to the appropriate manager for approval. The manager reviews each employee's time totals and either approves or disapproves the time for that employee. He or she then faxes or hand-delivers the signed summary back to us, along with any notations of approval or disapproval."

While Jane paused, Marta asked, "How often is a timesheet not approved?"

"About 5 percent of the time," Jane answered. "Usually it's because people haven't entered all their time, or they've miscalculated it in some way, and the manager knows it." Marta nodded that she understood, and after waiting to see if there were any other questions, Jane continued.

"If a timesheet is disapproved, we e-mail it back to the employee with a note about what the manager said, and we ask the employee to redo the timesheet and resubmit it. We also delete the one we saved so that two timesheets don't cover the same time period. Then all the approved timesheets are posted into the time and billing system."

"Let's stop there, Jane, and list the issues just for what you've covered so far," said Dan, moving to the whiteboard. "What are the issues, as you see them, with the way we record the timesheets currently?"

The Issues

"There are two big issues, but they are caused by a third issue, so which one should I start with?" asked Jane.

"Let's do the primary one first, then we'll list the problems it causes," replied Dan. "What in your mind is the 'driver' issue?"

Jane said emphatically, "Just the fact that it's a large, cumbersome, manual process. It has multiple steps, most of which involve keying, printing, and keying again. There are too many hands and too few computers in it for me."

Dan wrote Manual Process on the board. "I think we can all agree that anything manual associated with 800 timesheets has to be both large and cumbersome." He drew an arrow downward from Manual Process. "So what are the two issues caused by this one?"

"The first is the number of errors we get in data entry. The second is the amount of time this process takes each week."

As he wrote, Dan said over his shoulder, "Elaborate for us, please."

Warming to her subject, Jane responded, "Some weeks we enter over 10,000 line items into the time system. Even excellent data-entry people, which we have, make mistakes when that much work is involved. We catch most of them in the review process, but even so, some get out to the customer. I've worked hard to get the mistake rate down, and we've cut it substantially, but our eyes and fingers can only take so much.

"The other thing is the way this process eats up my resources! I could get much more done, even with fewer people, if we didn't have to deal with this monster each week. And it never stops. Every Monday, you know as you walk through the door that you are going to spend the entire day keying in time. I'm telling you, it's a killer." Realizing she was getting too excited, she paused and took a deep breath. Letting it out, she concluded, "Anyway, those are the three issues."

"I think there's a fourth," said Marta.

"And a fifth," added Marilou.

"OK you two, let's hear them," said Dan, turning back to the board. "Marta, you first."

Marta looked at Jane. "Didn't you start out by saying that you rotate the time-entry task among eight different resources, because if you assigned anyone permanently to it they would quit?" Jane nodded. "And didn't you also say that when people are assigned to it, they dread it?" Again, Jane nodded, and Marta continued, "Then I would say another issue with this part of the process is the effect it has on employee morale and retention, wouldn't you, Jane?"

Jane thought about it a moment. "You know, I hadn't considered that as an issue, because I was mainly focusing on the technical or data pieces of the problem. But come to think of it, the biggest gift I could give my staff would be to take weekly time-entry off their plates." She turned to Dan. "Are we allowed to consider quality-of-life issues like these, Dan?"

"Absolutely," Dan replied. "Sometimes they are harder to quantify, as we'll see when we look at return-on-investment issues later on. But Marta's point is a good one. Making Ferguson and Bardell a good place to work is important, especially when you consider the cost of recruiting and training a new hire." He turned to the board. "Let's list it just as Marta said it: 'Employee morale and retention.'" Looking at Marilou, he asked, "And what's your issue, Marilou?"

Pointing to Bill, Marilou said, "It's the same problem Bill talked about in his section. We've got duplication of data again. By my count, we've got time data in at least three locations: the employee's machine where the timesheet is created, the central directory where the sheet is stored, and the time and billing system where it is entered." She looked at Bill. "Kind of tough to guarantee data integrity, not to mention concurrency, wouldn't you say?"

"Sure would!" Suddenly Bill realized he was agreeing with the person he had called an Excel-clicker-turned-trainer in their last meeting. He looked up at Dan, who had set him straight about Marilou's technical background and who now had a slight smile on his face. "If it were up to you to redesign this monster, Marilou, what would you do?"

"Get a central data store to start with, that's for sure," she said firmly. "Try to cut down on the number of places time data is kept and analyzed. We might not be able to get it all the way to one, but we could get pretty close if we thought it through carefully." Bill nodded his agreement.

"That's the right direction, Marilou," Dan interjected, "but it's a topic for a later meeting. Hang onto it, because we'll need it soon."

Generating Invoices

Dan turned back to Jane. "There's more to your flowchart, Jane. Tell us about this last section."

"It's actually fairly simple," said Jane, looking down at the chart in front of her. "As you can see, once the timesheets are posted to the time and billing system, we run trial invoices and print them. The trial invoices are then sent to both management and sales for review. After both of them sign off on the invoice, the trial invoices are posted to the main accounting system, which runs the actual invoices."

"Are there any issues with this part of the process?" Dan stood at the board, marker ready.

The Issue

"I've given it some thought, and the only issue I can come up with is not so much an issue with this process as it is with the way Ferguson and Bardell is structured."

"And that is?"

"Well," Jane answered, "some of our work is billed on a project basis, and some of it is strictly hourly. The hourly work is managed through this process. The project work is billed the same no matter how much time we spend on it. When we go over the amount of time allotted to a project, we lose money. But because of the way we track time, we often don't know we are over until we do the invoice."

"So the issue is that there is no way to track time by project and compare the amount of time we actually spend with the amount of time the bid allows for?" asked Tim.

"That's right," said Jane. "It doesn't happen often, because most of our sales people and managers work together to make sure we have good estimates for our larger projects. Typically, it happens on smaller jobs where the work is only going to last a few weeks. By the time we catch it, the project is already over the estimate and there's no room to make it up."

"Alright," said Dan, "let's put it on the issues list, and perhaps as we move through the process, we can come up with a good way to deal with the problem as part of the overall RMS project." He moved away from the whiteboard and back to his place at the table.

Microsoft Corporation - Analyzing Requirements and Defining Solutions Architecture. MCSD Training Kit
Microsoft Corporation - Analyzing Requirements and Defining Solutions Architecture. MCSD Training Kit
Year: 1999
Pages: 182 © 2008-2017.
If you may any questions please contact us: