Business Event Analysis: Complete the following table based on the specified NBD event.

	Receive Shipment (From Supplier)
Trigger(s) (also indicate type)
Collected Data (originates as input for event)
Utilized Data (exists before event)
Generated Data (results from event processing)
Additional Data (that might be collected but isnt)
	Close Project
Trigger(s) (also indicate type)
Collected Data (originates as input for event)
Utilized data (exists before event)
Generated data (results from event processing)
Additional Data (that could be collected but isnt)

Natural By Design (Interviews)

Stan and Keri have hired IS consultants to do an analysis of the project-oriented processes. Stan has issued the following instructions: The team must confine the investigation to NBDs cost tracking processes throughout a projects life. Also Ms. Connie Nguyen, our Design Group Administrative Assistant is studying MIS. She must accompany and work with the consulting team as it interviews NBD employees. The consulting team has read all of the background information and examined the various reports that are produced for the cost tracking processes to prepare for the interviews.

Interview with Tamara Bakken, Designer

The consulting team, accompanied by Connie Nguyen, met with Tamara to first determine how the designers deal with landscape inventory. Connie began by asking Tamara how she prepares a Design Bid.

I have to admit that, after six years as a NBD designer, Ive fallen into an almost unconscious -- if thats the right word -- method of estimating materials and design and labor hours. I refer to my preliminary sketches to determine the materials needed. Estimating the total price of plants and materials is pretty clear cut. Connie emails updated price lists for all inventory items every morning. All we designers do is find the desired item on the list and enter it and its price on the Design Bid spreadsheet template that Connie created. We enter the quantity of each item, and then the spreadsheet calculates the extended price and the total price for materials.

What do you do if the quantity or type of materials changes later? one of the students asked.

We just bring up the appropriate spreadsheet, plug in new numbers or data, and resubmit the bid for client approval.

Okay, lets get back to the estimation of bid amount, another consultant said. How do you estimate the labor hours?

Thats where experience pays off. I had a lot of trouble estimating the labor hours to implement a project when I first began, but, after seeing so many designs implemented, Ive become pretty good at it. If I need help estimating hours for an unusual project, I talk with Cheryl or Keri or Sue, who can usually point me to an earlier project that was similar in nature. Then I just retrieve the bid or plan or project administration report for that project and use the labor hours from these to estimate my projects hours. Once I have an estimate of the hours required for each task, I consult a list of labor prices -- this one prepared by Administration -- and again just plug the hourly price and number of hours into the spreadsheet. When Im finished, the spreadsheet calculates the total labor price and the total bid amount.

The finished bid is approved by Cheryl and then shown to the client and, if approved, I retain one copy of the signed bid and give the original bid to Connie, who, I guess, forwards one copy to Sue Kaufman in Production and another to Accounts Receivable, and retains the original to file and to update inventory data on her spreadsheet.

Okay, Connie said. If theres nothing more about the bid process, lets move on to the Design Budget and the Designer Daily Work Report.

Okay, Tamara continued. 1 prepare a Design Budget after a preliminary meeting with a client. Again, experience is the main factor in my ability to estimate the number of hours required to prepare the sketches and the bid. We designers are allowed to invest no more than 10 percent of the estimated bid on design activities. So the first thing 1 have to do is to estimate the bid amount based on my preliminary meeting with the client. Then I calculate 10 percent of that and divide that figure by 40 -- my cost per hour -- to come up with an estimate of the maximum number of hours I can spend on the bid process.

Whoa, one of the consultants interjected. Could you give us an example of that?

Sure. You have copies of the forms used for the London Square Mall project, right? Okay, in that case, the bid estimate was $7500. Ten percent of that is $750. Divide 750 by 40, giving about 18-19, the maximum bid process hours.

Thanks. Thats much clearer.

Our daily work report is pretty straightforward. All I do is indicate the number of hours Ive spent each day on each project. I give the Design Budget to Cheryl for approval and the Designer Daily Work Report to Connie.

Connie said. For now, we just want to understand what you do to arrive at the cost. If you cant think of any other details about how the designers prepare the documents, do you have any complaints about the current system? Are there any improvements youd like to suggest? Connie asked.

Hmmm, let me think. It would be nice if all I had to do to prepare the bid was enter the inventory code and quantity for materials and the system would retrieve the appropriate description, price, etc. instead of me having to copy the data from the inventory update spreadsheet. Same with labor data from the labor report.

That is precisely what I think is needed as well, Connie responded. Anything that can help save me time is a consideration here. Do you have any other complaints or suggestions?

Well, currently, I feel quite constrained as I design a landscape. I have to know when implementation will likely occur so that I can select plants that will be available in that time frame. 1 guess what Im trying to say is that, if a design will be implemented immediately upon approval, I pretty much have to go with plants that are currently available. I sometimes find myself steering a client away from certain plants because I know that were unlikely to have them in time for implementation.

What would the system have to be able to do to help you avoid this constraint? one of the consultants asked.

I have no idea.... I guess if it could shorten the lead-time; that would be a big help.

Interview with Bill Johnson, Greenhouse Clerk

Hi, Connie, folks. Come on in. Sorry I dont have enough chairs for everyone, but I dont think this will take too long.

Hi, Mr. Johnson, Connie said and then introduced the members of the consulting team.

Your role in project administration deals with helping to track inventory as it is received from suppliers and later as it is loaded for delivery to our clients. The documents you work with are the Purchase Orders and related shipping lists from the suppliers for receiving and the Production Daily Work Report (PDWR) which records the daily material deliveries for a project. Lets start with the PDWR. Could you tell us about your role in completing that report?

Well, its pretty simple. All I do is record the unit cost, calculate the extended cost for items being removed from inventory, and sign my name to each item as I verify the quantity being loaded for delivery. The lead production worker has already provided the date, project name, and item code and quantity on the form when I see it. So thats about it. Told you this would be a short meeting.

How do you know what unit cost to enter for each item? asked a consultant.

Pretty simple. All I do is refer to the most recent daily inventory report that lists the net cost and list price for all inventory items. Whatever is left blank, I was told to fill in, so I calculate the extended cost to finish that part of the form. Thats it.

Do you ever have any problems, like entering the wrong unit cost or extended cost?

Oh, occasionally, I botch a number. Especially if Im trying to do two things at once. Some mornings are more hectic than others, particularly when we are trying to load the trucks for the day and then a shipment comes in at the same time. But Connie here usually takes me to task for any errors I make, dont you, Connie?

Well, sometimes, Connie responded, but I live in dread that Ill let errors slip by me. Can you think of any way we could avoid the occasional calculation error?

Humph. Thats your job, not mine. Dont know much about those computer things and cant say that I care to learn. I think its a disgrace that kids nowadays cant compute in their heads. Have to have a calculator to add two plus two. Not me. I do all my calculating by hand, thank you.

Well, for instance, would it help if all you had to do was enter the item code and quantity and the system would retrieve the unit cost and calculate the extended cost? one of the consultants hesitantly suggested.

Entering the codes and quantities is the lead production workers job. Not that I dont know all the codes, mind you, but I tend to believe the best system is one where everyone does his job and doesnt stick his nose in anyone elses business. Of course, if you guys want to give me PDWRs with the numbers already filled in and all I have to do is sign off on the removed inventory, then that is all right with me

Okay, then, what about the shipping list? How do you process that document? one of the consultants asked.

Again, thats pretty simple. All I do is retrieve the appropriate purchase order or orders, Stan sends me a copy each time he prepares one and I compare its items to the shipping list items. Usually things match. If they dont, I call Stan to let him know about a shipment error -- you know, the supplier sent 12 arecastum coco plumosa instead of 10, or substituted 17-inch ficus green gems for 14-inch. Usually, Stan tells me to accept the shipment and forward the shipping list and purchase order to him, instead of to Connie here like I usually do. I guess Stan waves a magic wand over it and then sends it on to Connie.

A magic wand? a consultant interrupted.

Yeah, a magic wand. In other words, he signs off to indicate that he has approved the shipment and I guess he tells Connie what adjustments to make.

Oh, I see. Do you mark the shipping list in any way?

Usually not. On the purchase order, I initial each line where the shipping list agrees with our purchase order to indicate that we received that quantity of that plant or whatever. If an item on the purchase order is not delivered in the shipment, I indicate that it is backordered or is arriving in another shipment depending on what the shipping list says.

When the items do not agree, I mark both documents, marking items on the shipping list. Then, I circle each quantity on the purchase order thats in error and note the shipped quantity; same with items -- circle any ordered items that dont agree with the shipped items and indicate the name and quantity of the item shipped in error. Course, sometimes, the supplier sends something totally different from what was ordered. You know, sends laccospadix australasica palms instead of marginata. Thats usually the kind of shipment error that Stan has me return, unless, of course, we happen to be low on lacco palms!

One of the consultants frowned, scratched his head, and asked, Would it be correct to say that you usually accept whatever the supplier sends you? From the sounds of it, youre usually so desperate for plant inventory that youll accept whatever you can get.

Thats not quite the case, but with some of these errors, I guess thats where you come in. You high-paid consultants are going to throw some computers at us to make the problem go away.

Okay, Mr. Johnson, youve been very helpful. Thanks for your time, Connie said as the consultants filed out of the cramped office.

No problem, Connie. Just dont go thinking about putting one of those computer things down here in the greenhouse. Its too damp. Cant tell what might happen. It would be even worse for the loading dock especially in the rain.

Interview with Monica Goce, Lead Production Worker

Hi, Monica, Connie said. Thanks for agreeing to meet us after work. Let me introduce the consulting team. After introducing the team, Connie turned the interview over to a consultant.

Weve already talked to Bill Johnson about his role in completing the Production Daily Work Report. Now we need your take on the subject.

That old goat! He fries my pajamas. Just this morning we had a big to-do because he had entered the wrong extended cost for an item. Do you think he could admit that he had multiplied wrong? No way! Monica shook her head in disgust.

Does that kind of thing happen very often?

Often enough in my opinion. Would it be so terrible if hed just condescend to use the calculator sitting on his work table?

What do you do when you discover that a figures wrong?

Monica chuckled. I just wait until hes out of sight, then I scratch out his error and enter the right number. Theres no sense trying to reason with the man.

I think were getting ahead of ourselves, Connie said, trying to change the topic away from employee politics. Monica, would you explain how you complete the PDWR?

Sure, sorry for the digression. Theres a stack of PDWR forms just inside the Greenhouse office. The first thing I do each morning is to consult the Production Plan folder to determine what project or projects my team will be working on that day and what inventory items we need to take to each site. Then I copy the date, project code, item code, and quantity to a PDWR spreadsheet for each project, as well as the employees and tasks that have to get done. Usually, this doesnt take me more than 20-30 minutes. Then I find our driver and have him bring the truck around to the loading dock. I hand the forms to old Bill and keep an eye on him to make sure that he gets all the right stuff loaded on the truck. He signs the next to each item line. At the end of the day, I enter the number of hours and a description of the work done for the employees. I deliver the form(s) to Connies in-box before I punch out.

What about the cost per hour and extended cost figures? one of the consultants asked. Oh, yeah. I enter the average cost per hour for a particular kind of employee for production workers, thats about $18 an hour -- and multiply by the number of hours.

Do you ever make mistakes?

Seldom, unlike some people I could name. Im not too proud to use a calculator. I usually perform the calculations manually and then verify them with a calculator.

Can you think of any changes that would make your job easier?

Sure, if talking the old goat into retiring is something you can include! Just kidding. Hes really not as bad as I make out. My job wouldnt be half as challenging if I didnt have to fight Billy every step of the way. There I go again. Digress. Digress. Digress. The change Id most like to see is for the computer to automatically fill in as much as possible on the PDW Report. I mean, all Im doing is copying information for that day from the latest version of the Production Plan.

So, youre saying that the item code and quantity for each project for each day is already listed on the Production Plan. All you do is copy it?

Thats it. Seems kind of silly. The worker names, tasks, and estimated hours are also on the Production Plan. So it seems that your system could generate a default PDW report for the day and I could just edit it to note any changes that occur on the job.

Are the materials unit costs and extended costs also on the Production Plan?

Well, yes, but then, what would Billy have to do?

Are there often changes to plan?

Runs in streaks. For days on end, everything goes according to plan. Then all of a sudden well have unexpected stockouts, or a job will take much more or much less time than expected, or a worker wont show up for a couple of days or the weather doesnt cooperate. Or sometimes a designer will inspect a job in progress and decide that we need to install eight marginata instead of the seven in the blueprint. Designers are given some discretion to make goodwill kinds of adjustments at no cost to the client. They dont go through the bother of revising the bid and plan; they just make the change, getting Stans or Keris approval if the cost is significant.

Are the changes common enough that generating a default form wouldnt be practical?

No, Id guess that at least 95 percent of the time Im just copying the production plan word-for-word. So, yeah, if you could generate a default PDWR and let me edit when necessary, that would be helpful.

Using a default PDWR data entry screen would also help you complete the form more easily. Youd enter the project code and date and the system would provide the latest numbers for the day. Then the unit cost and extended cost would be accessed and computed by the system. You could edit to reflect any actual differences that occur. Same with the labor data; the latest Production Plan data could be provided and youd enter the worker name and number of hours worked for each task, editing where necessary and the system would do the rest.

I dont know. I usually enter materials right away in the morning and labor data at the end of the day. Could I do that with the screen youre talking about?

Sure. One way might be for you to save the morning data and then bring up the incomplete screen at the end of the day in order to finish entering data.

But how would Bill approve the items removed from inventory and how would I sign the form?

Oh, Im sure we could figure something out. Anyway, the main thing is would such a data entry screen make your job easier and improve the accuracy of the PDW reports?

I suppose so. Id have to see it in action before I could say for sure.

Interview with Sue Kaufman

Connie peeked into Sues cubicle. Good morning, Sue. Are you ready for us? Sure, Sue replied. Just let me close this worksheet. Why dont you bring some chairs over for your friends while I finish up?

Okay, were ready if you are, Connie said, after everyone was seated. We want to start today by discussing how you convert a bid into a Production Plan.

Sure. Its not a particularly difficult task; just time consuming and tedious. But Im happy to say that its a little less so now that most of the designers are using your Design Bid spreadsheet template. I have the designers email me an electronic copy of each bid after customer approval. That way, Im able to copy shared data from the bid spreadsheet into my production plan spreadsheet. You know data such as project information, item code and quantity, etc. Keri and I confer on the makeup of the project team. Then I refer to the latest inventory report to copy the cost of each material requirement and enter it in the appropriate cell. That pretty much takes care of the material requirements portion of the plan; the spreadsheet does all the calculations for that. To complete the materials section, I have to figure out the delivery and installation scheduling.

One of the consultants then stated, For now, we want to focus just on cost tracking, as indicated by Stan and Keri, considering only the cost data and the procedures for tracking it. Scheduling doesnt figure into this.

I have a question about the material requirements portion, another consultant interjected. Weve already interviewed Monica Goce. She mentioned that sometimes the quantity of an item actually used in a project is changed without revising the production plan or bid. In your experience, is the quantity of an item always exactly the same on the Production Plan as it is on the Design Bid?

Good question. Sometimes I need to modify the quantity estimates. For example, a designer might estimate that a project requires a yard of top soil, but looking at the blueprint, I see that a yard and a half is needed. Im authorized to make such changes. Were only talking about a $6 change; revising the bid and plan and going through the approval process would be a waste of time and money in such a situation.

Are there any other times that you change the quantities or types of items?

Sure. The designer may have recommended 1-inch gravel, but, after consultation with me and/or with the client, decides that pea gravel would be more appropriate. Again, we just go ahead and make the change. Or we may have trouble getting good quality specimens of a particular plant and decide to substitute a different plant. If its a significant change in terms of dollars or appearance, we get approval from Cheryl, Keri and the client.

Okay then, the quantity and type of item is not always exactly the same as on the approved bid.

Right. The same is true of labor requirements. I dont usually have reason to change the design labor estimates, but, especially with a new designer, I may need to change the production labor estimates.

Why isnt every bid sent to you for approval first, given that you so often have to change the estimates?

Oh, no, I dont change the estimates very often. I didnt mean to give you that impression. Its very rare that I have to make a change, and even more rare that the change I make is significant. In fact, most new designers work with me as they prepare their bids, so the kinds of significant or frequent changes youre envisioning just dont arise often enough to warrant my perusing every bid before its approved.

Okay. Thanks for clearing that up. You were about to tell us how you prepare the labor requirements section.

Thats a more involved process because I need to assign workers to each task and estimate the number of hours required. Again, making these estimates has become easier with experience, but, in some ways, its still a seat-of-the-pants guess. For example, installing a large plant such as a laccospadix australasica palm generally requires two workers and takes about one to one and a half hours per plant. In the sample production plan you were given, youll notice that two workers, Monica and Bert, were scheduled for eight hours -- four hours each -- to install three lacco palms.

Thats interesting. But I think weve become sidetracked into how you estimate hours. Since our main concern is tracking costs, we need to know how you derive your cost data. For example, how do you determine the cost per hour of a production worker or a designer?

Stan has defined several types of employees and has computed the average total hourly cost of each employee type. For example, including hourly wage, benefits, and other costs of employment, a production worker costs NBD about $18 per hour; a designer costs about $40 per hour. So, from your perspective, all I do is multiply the number of hours to be worked by each employee type by the hourly cost of that employee type to derive the estimated production labor costs.

But I beg to differ with this simplification. I know that at the higher administrative levels, they think in terms of projects. Well, I do too. But I also think in terms of individual workers, and I need some way of tracking whether my workers are performing their jobs efficiently and effectively. I want to know if a worker consistently takes 25 percent more time to complete a task than what I estimated. I want to know if a workers work has to be redone. For the most part, I dont have time to get this information together. But if I notice that projects staffed by a particular production team tend to go over budget, Ill pull out the PDW reports for those projects and manually compare estimated hours to actual hours for each task. Its a tedious, mind-numbing process, but one I feel compelled to perform on occasion. But this is based on the project rather than an individual or pair of individuals executing a particular task.

We would have to clear that with Stan and Keri because it deals with labor hours rather than labor costs. So, for now, we need to focus our attention on our given agenda, which is facilitating and improving the accuracy of cost tracking. Youve said that completing the production plan is a tedious, time consuming process. Is there anything you can think of that would make the process less burdensome?

I suppose the usual things would help. You know, things like, I enter the code and quantity and the system fills in the description and the unit cost. Also, anything that allows me to reuse data that already exists about the project more simply -- like from the bid. But thats about it. Connies spreadsheets were certainly a step in the right direction; it takes care of a lot of the accuracy problems. But what youve said makes me very interested in being able to generate a variety of reports. Im not sure you could do that with Connies spreadsheet.

Probably not. Well talk to Stan and Keri and see what we can do.

Interview with Cheryl Poy, Keri Yamaguchi, and Stan Felton

Connie and the consulting team met with Stan Felton to learn more about the purchasing process. Keri Yamaguchi was supervising an exceptionally high-priced project and promised to join them as soon as she could get away. Cheryl Poy is attending a Design Group Meeting and will join the group later as well.

Formerly, we were incurring unreasonably high procurement and inventory costs because we had no formal process for determining the optimal order and inventory quantities for each item. But, thanks to Connie, I now use a worksheet decision model to help me determine the economic order quantity and economic reorder point for each plant type. Perhaps Connie can explain this for you.

Sure, Stan. The economic order quantity (EOQ) is the quantity of a stock item that a firm should order at one time to minimize its costs of procurement the process of ordering and receiving goods and storage and to reduce the amount of capital it has tied up in inventory.[1] The economic reorder point (ERP) indicates the stock level at which an item should be reordered to avoid stock-outs.[2] By determining the EOQ and ERP for each plant type, we can more effectively manage our inventory and reduce the costs of purchasing and storing our stock.

Connie continued, The procurement costs and inventory cost rate[3] (shown in the worksheet) must be known in order to determine the appropriate order and inventory quantities for each plant type. Also needed are the average net cost and annual sales for each plant type.

What you should notice here, Stan explained, is that our inventory cost rate is exceptionally high, largely because of the high cost of maintenance. Someone has to take care of the plants in inventory on a daily basis -- water, fertilize periodically, remove dead leaves, inspect for disease, whatever. We keep a full-time botanist on staff not only to recommend plants for certain settings but also to inspect and treat our inventory of plants. And, of course, any plants that die in our care are a total loss unless we can trace the disease back to the supplier, in which case the supplier may agree to replace the diseased plant.

Your procurement costs also seem rather high, one of the consultants noted. I imagine the labor cost is primarily the cost of your time to review inventory levels, consult the worksheet, and then complete a purchase order.

Thats right, Stan responded. If we could reduce procurement costs, the EOQ would be lower. Thus, we could order fewer plants more often, in turn reducing our maintenance costs. What Id like you to do is to set up the system so that each plant type has an assigned reorder point. When the quantity in stock is equal to or less than the reorder point, the system should automatically alert me to place an order for that plant.

Thats quite feasible, a consultant explained. In fact, thats the key to reducing your costs. In some ways, what you need is a just-in-time inventory system. If a plant is received from the supplier within 24 hours of the time it is to be installed in a landscape project, your inventory maintenance costs would be minimized.

Yes, but, as you can see, most of our suppliers require at least a 2-week lead time.

Right, the consultant agreed. So the trick is to figure out how to reduce that lead time. That goal should be feasible, especially with local suppliers. I mean, youre in California, for Petes sake! There must be a number of nurseries that could guarantee over-night delivery.

Thats true .... Stan paused in thought. Maybe my real problem is that I havent been demanding the level of service I need. Im a pretty easy-going guy; I tend to take what Im given. When we were just starting out, that was necessary. But weve grown into a pretty large operation, so I should have some leverage with my suppliers. I mean, Im doing in excess of $100,000 a year with many of them.

From what weve heard, though, some of your suppliers arent very dependable.

What gave you that idea? Keri interrupted, joining the group. Sorry to be late. Unfortunately -- or perhaps I should say fortunately -- a mega-bucks project takes precedence! Anyway, our suppliers would not be preferred if they werent dependable.

Thanks for making time for us, a consultant said and then explained,

Bill Johnson noted problems with receiving unordered items or receiving the right item but the wrong quantity.

Bill. Hes just never been able to forget the time we placed an emergency order with a new supplier who messed up the order and cost us a project. That was a once-in-a-blue-moon incident that Bill insists on blowing way out of proportion, Keri laughed. If you learn anything during this interview process, you should learn that Bills view of things should not be taken too much to heart!

Then do you think its feasible to reduce lead times? Stan asked, Thats what we were discussing when you came in, Keri,

Im pretty sure of it. In fact, Ive recently begun hinting to several of our suppliers that we cant continue to operate competitively if they have a strangle-hold on the availability of our plant stock.

Great, a consultant concluded. Then, well pursue this further with you, Keri. For now, lets talk more generally about your expectations for the new system. What business goals do you expect the system to achieve?

Stan replied, Weve just enumerated a number of them in our discussion: reducing procurement and inventory costs, reducing supplier lead times, making us more competitive if our costs are lower, we can offer our clients lower prices. Overall, we simply need to be able to provide better service to our clients. Clients want a wide choice of plants, pottery, and materials; many of them also want immediate implementation.

How immediate is immediate?

Some clients plan ahead and get the process moving as much as six months before they expect implementation to begin. A few expect us to begin implementing as soon as the ink is dry on their bid signature. In general, we need to begin implementing within four -- at most, six weeks of bid approval. Longer than that and we risk losing the bid.

Absolutely, Keri agreed. Weve lost some projects because we couldnt implement the landscape as quickly as the client wanted. We had innovative designs, a ready labor force, but we couldnt get the desired plants quickly enough. Our whole business hinges on the availability of plants. Weve worked out all the other details: creative, personable designers who create fabulous designs and who interact famously with clients; hard-working, dependable production workers who do a quality job implementing the designs; and so on. But there just isnt any landscape without plants.

Cheryl Poy enters the room.

Sorry Im late. Our group meeting ran long, Cheryl apologized as she entered Stans office.

No problem, we been discussing inventory issues, Keri said. I think its wonderful, Connie, that you are participating with the consultants. I hope its a good learning experience as well.

It is, Connie said. Its really amazing how everyone seems to have a different view of things, a different agenda. Ive worked here for almost four years and always thought I knew the procedures for cost tracking for project inventory and labor inside and out. But Im learning a lot.

Sorry to curtail the chitchat, but Ive got an off-site meeting in an hour. So I think we should get right to the topic at hand, Cheryl interjected.

Sure thing, Connie responded as she took out her project notebook. The main topics we need to cover with you are how you use the Project Administration Report and what other reports you need to make it easier to track project costs. Lets begin with the Project Administration Report. How does each of you use this report? Stan, lets start with you.

Let me tell you that the accuracy of our bid and production estimates can make or break NBD. If our designers consistently underbid or Sue underestimates production costs, were in serious trouble. So my primary thoughts as I examine the PA report are How are we doing? How accurate are our estimates? Are we staying within the 50 to 70 percent range for production costs to bid amount? Are designers investing a reasonable amount of time in the bid process? So, for example, when I looked at the report for June 18, I was very upset to see that design costs for the Fremont project were over 300 percent higher than estimated. Then I realized that there was a typo -- 100 should have been 1000 -- and breathed a sigh of relief. Not to cut on you, Connie, but I sometimes worry about the accuracy of this report. How many errors are on the forms used to generate the report and on the report itself? How confident can I be in the information presented to me?

Connie shook her head. Reminds me of what my first MIS instructor always used to say: Garbage in, garbage out. I understand your concern; thats been partially eased by the designers using the design bid spreadsheet and Sue using the production plan spreadsheet. I also try to verify all the figures before I use them to produce reports, but sometimes I just simply dont have time to double-check to make sure all the data got transferred correctly. Anyway, if I understand you correctly, Stan, you use the report as a control device and to get a general sense of the profitability of the company.

In a nutshell, yes.

How about you, Keri?

I also use the report to gauge the companys profitability, and Im also concerned about the accuracy of estimates. But I primarily look for trends over time. Ill often pull out all the reports for several weeks to see if any designers are consistently over-estimating or under-estimating bid amounts. Obviously Im more concerned about underestimates, but overestimates can be a problem too. We dont want to overbid if doing so ends up costing us business.

The underestimates are my primary concern, Cheryl interrupted. I especially pay attention to projects designed by new designers or unusual projects that differ substantially from anything NBD has done before. So Ive been tracking the Fremont project closely; its a fairly large project being designed by one of our recent hires, and its a new kind of design for NBD. We havent done a formal garden for anyone in the time that Ive been here.

As Im listening to you, one of the consultants said, I cant help but think that, in some ways, this report provides the wrong information, or, at least, not all the information you need, organized in the way you need. Ms. Yamaguchi, you noted that you sometimes pull out several reports to spot trends. Ms. Poy, you need to be able to determine how effectively each designer is estimating bids. This report doesnt really give you this information.

No, it doesnt, Keri responded, but I didnt want to ask Connie to prepare two or three reports from the same data. Shes already working overtime as it is.

It seems to me that you need some kind of database system, another student suggested. Connie has said that she developed a spreadsheet to generate this report, but a spreadsheet doesnt provide the reporting flexibility that your business need. The report itself might contain fewer calculation errors, but Connie has to create a new spreadsheet and re-enter the data for each report. With a database, you could store the data in one format and generate a variety of reports from the same data. Ms. Yamaguchi could get a report showing trends over time or trends for particular types of projects. Ms. Poy could get a report sorted by designer, and Mr. Fenton could get a report showing the overage/underage for each project or for all projects for a given time period. In fact, you could generate an exception report listing projects that missed the mark by a certain percentage on a certain variable, for example, the implementation costs to bid amount ratio.

Interview with Connie Nguyen

OK, Connie. Its time for you to submit to our interrogation.

Please be gentle, Connie joked.

Yeah, right, one of the consultants said. What we most need from you at this point is an explanation of how plant costs and prices are calculated. We understand that these tend to be more volatile than pottery or material prices. Stan said something about your maintaining the average cost and computing the selling price from that. Could you explain, please?

Sure, its pretty simple. Each shipping list indicates the quantity, unit cost, and extended cost of the plants received. Whenever I receive a shipping list, I add the quantity of each plant to the total annual quantity of that plant; I also add its extended cost to the total annual cost of that plant. Then I divide the total annual cost by the total annual quantity, giving the average cost. To calculate the seIling price, I multiply the average cost by 1.665, which means that we have about a 67 percent markup on plants.

Isnt that a little high? one of the consultants asked.

No, in fact, it may be a bit low. You have to remember that the labor costs of preparing the design bid are factored into the seIling price of plants. The only time we charge up-front for pre-bid-approval design labor is when the project is exceptionally complicated, thus requiring a significant investment.

Okay, I understand the rationale. So, for example, if we look at the inventory report, the average cost (AveNet$) for lacco is $450; multiplying that by 1.665 gives a list price (List$) of $749. Currently, you do this manually, right?

Thats partially true. I compute the totals manually, but I use the Excel spreadsheet to calculate the average cost and the list price.

Okay, thats pretty clear. Just one more question. We have a pretty clear idea of how you maintain the QIS, IS/OB, QOO, OO/OB counts, tedious as it must be for you.

Truer words were never spoken! Connie interrupted. Sometimes I feel completely bonkers by the time I finish all those machinations.

I can believe that, the consultant responded. So you must go totally off the deep end when you receive a bid that lists plants whose in-stock and on-order quantities are already on bid. In other words, all in-stock and on-order plants are already spoken for. What do you do then? Theres no place on your spreadsheet to deal with on-bid but neither in-stock nor on-order quantities.

Fortunately, that seldom happens because designers know that the likelihood of actually being able to use those plants is almost nil, unless, of course, the project wont be implemented for a few months. Anyway, when that does happen, I run to Stan, begging him to place an order immediately, if for no better reason than so that my spreadsheet will make sense. But Stans pretty sharp. Hes probably already noticed that all current and on-order stock is on-bid, so hes probably in the process of ordering or just hasnt forwarded his order to me yet.

How do you keep project data organized and accessible?

To achieve some degree of order and easy access to documents, I maintain a folder for each project. In each project folder, I maintain all versions of the design bid and production plan, filed sequentially by date. I also include the design budget and any revisions to it, and every PDW report and DDW report, again filed sequentially by date. As these forms come in -- and as time allows, I identify any corrections or notes on the forms. I usually set aside at least two hours every day for reviewing the cost data for projects. I have created a spreadsheet workbook for the project documents that were filled out electronically to track the costs of each project each day. There is a separate spreadsheet template that I use to format the Project Administration Report (PAR) data.

To prepare the Project Administration Report, I compute hour and cost totals for each cost type for each project for that week -- a week runs Monday through Friday. Then I add these to the appropriate actual hour and cost totals from the previous weeks report. If the project is still in the design stage, I subtract the grand total from the hours and dollars remaining figures from the previous weeks report. Copies of the report are emailed to Keri, Stan, Cheryl and Sue.

[1] The formula for calculating the EOQ is:

SQRT((2 *procurement cost)/inventory cost rate) *SQRT(annual unit sales/unit cost)

where SQRT indicates the square root.

[2] The formula for calculating the ERP is:

order lead time * weekly unit sales

where order lead time is the number of weeks from the date of placing an order to the date of receiving it and

weekly unit sales = annual unit sales/52.

[3] Inventory Cost Rate is represented as a percentage of the plant types net cost. For example, if a plant types net cost is $100 and the inventory cost rate is 42 percent, its inventory cost is $42.00.