IN THE CLASSROOM
Rick Hesse, Feature
Editor, Industrial and Systems Engineering Department,
Mercer
University
Student Spreadsheet Models
by Rick Hesse, Feature Editor
Since I've been teaching at
Mercer University Engineering School for the past 9 years, one of
the delights has been to work with undergraduate students on
their two-quarter senior design projects in Industrial
Engineering or Industrial Management. The students work in teams
of 2-4 people on a real problem from one of the companies here in
middle Georgia. One of the interesting results over the years has
been the comments from students who have been co-ops or had
summer jobs about how much companies appreciate their spreadsheet
skills, although not thought of by engineering students as a
high-tech skill. Many students know MAPLE, MathCad or programming
languages such as Pascal or C++, yet the ability to use
spreadsheets both as a computation tool and communication device
has been a surprising side benefit of their education.
Since this column has accidentally become a platform for
spreadsheet use (but not exclusively, please send me anything
that you do in the classroom that might benefit our membership),
I thought I might recap some successful uses in the past years
that students have had with their senior design projects using
spreadsheets. These applications have for the most part not been
high tech at all and much of the project revolved around learning
the company lingo (terms, how they did it with paper and pencil)
and designing and developing a product that can actually be used
by the company.
Quality Control Monitoring
One of the earliest uses for spreadsheets for a senior design
project was five years ago for a cigarette manufacturer. When one
of the dozen cigarette-making machines would go down, workers had
to manually write down information from the machine readout as to
trouble codes, time down, and so on. These were summarized weekly
and then the supervisor had to draw graphs and charts for a
weekly report, and hopefully spot some trend or underlying cause.
A project team was able to write a program to be able to use a
laptop computer to plug into the troubled machine, extract the
data, and then with a few key strokes add how long it had been
down, who the operator was, and so on. All of this data was then
imported into a spreadsheet, sorted and organized, and
automatically put into reports that could be easily printed and
analyzed. Not only did this application save time weekly for the
supervisor, but also for each downtime occurrence. The actual
calculations and data
manipulation were not difficult, but were certainly valuable.
Fax Transmittal Ordering System
A large manufacturer of zippers orders 175 critical raw materials
that take weeks to arrive first by boat from Japan and then rail
across the U.S. A senior design team was able to download the
daily inventory data, combine it with forecasted production use,
and make up an order form which was then faxed directly to Japan
electronically, greatly increasing the readability from forms
that had been previously filled out by hand. The old forms not
only were sometimes misread in Japan, but also in copying down
the order, mistakes were made on the U.S. side. The new system
not only saved time, but increased the accuracy of communication.
Sales Analysis and Forecasting
The local energy company augments its business by selling
appliances that use gas, such as dryers, barbecue pits, gas
lamps, and so on. A student team took the last 2-3 years of
monthly sales data and used a spreadsheet for both the data base
and analysis program. Using the macro functions to make up unique
menus, the spreadsheet program could show a sales graph of any of
several product lines, allow new data to be entered, or compute
seasonality indices. The biggest benefit for management was
simply being able to "see" the data in graphical form, which led
to an understanding of the numbers behind the graphs.
Telephone Operator Staffing
A national insurance company, with telephone offices here in
Middle Georgia, used spreadsheets to collect data on the number
of calls for its three telephone divisions. Management knew that
at times they had too many operators and at other times not
enough, but didn't have the time to investigate the problem. A
senior design team tackled a year's worth of data for one of the
divisions that was currently using 115 phone counselors. The team
used both forecasting and integer programming staffing models to
develop a recommendation for the shifts and number on each shift
to balance the work load. All of this was done using spreadsheet
models and since management was already familiar with
spreadsheets, the team was able to hand the model over to the
company and be assured that it would be updated and analyzed in
the future. The team came up with a solution that needed only 92
phone counselors, and rather than force current employees to work
certain shifts, management was able to take the "optimal"
solution and judiciously implement it over time as employees left
due to attrition and guided them into certain shifts.
Alternative Fuel Analysis
A package delivery company has been successful in certain areas
converting some of their 100,000 package delivery trucks to run
on natural gas rather than gasoline. One of the problems for each
manager of a large region is identifying which towns would be
good candidates for this conversion. The company has a detailed,
large, expensive program to determine whether a candidate area
would be worthwhile for such conversion. However, for each
analysis, a lot of data must be gathered and entered for a time-
consuming analysis. What the company wanted was a simpler, macro
model that would indicate which towns would be good candidates
for the more
expensive analysis. A senior design team developed a Decision
Support System on a spreadsheet that was both a data base and
quick analyzer for candidate towns. Federal, state and local
regulations and tax breaks were entered for Georgia to illustrate
how the program could select possible candidates with a maximum
payback of 3 years. Other information entered was more local:
number of trucks, average mileage, cost of gas and natural gas
(and
availability) for each town.
Gas and Water Forecasting
One senior design team helped one company with a spreadsheet
model that was able to take historical production and usage data
for gas and water usage and build confidence levels and forecast
costs for negotiated contract levels. Because the model could
also give graphs, it helped management to "see" the various
possibilities. Standards Report Writing
Currently a design team is working with a large airplane
manufacturer in middle Georgia. Performance and standards data
are input into a terminal connected to the mainframe in
California. Plant industrial engineers currently must retrieve
data from screen output that is hard to read in the dark plant,
transfer it to other forms, and finally produce reports for
management. In all, the process takes over 70 worker-hours each
week, much of which requires verifying hand-written data or
searching for the data on the mainframe. The team will be using
Access database for the data from the mainframe ported down to a
PC, but will transfer it to Excel for generating reports, charts
and graphs. The database will allow the data to be sorted for
different reports, but is not the best vehicle for report
writing. Thus the transfer to a spreadsheet helps transform the
data into a more usable and readable form. Estimates for the
weekly process is a maximum of seven hours, or a reduction of 90%
of engineering hours.
Conclusion
These six examples are just a sample of what some students have
accomplished over the years, and of course there is much more
involved in each senior design project than can be described in
just a few words. But they each point to the fact that
spreadsheet models have become a great platform for analysis and
communication.
|