A Case Study and Model for Database Design for Cost Analysis in Manufacturing Dr. Randall McCoy Information Systems Department, Morehead State University Morehead, KY, 40351, USA and Mr. John Gibson Information Systems Department, Morehead State University Morehead, KY, 40351, USA Abstract This paper presents a case study dealing with an actual database development process which was employed by an information systems class to solve a "real-world" manufacturing cost analysis problem. The purpose was to evaluate the manufacturing process and to develop and implement a database application that analyzed production costs. The development process of the application was incorporated into a college-level systems analysis and design course. The outcomes provide a learning experience that demonstrates business problem solving and the use of information technology to answer the problems faced in the manufacturing business world. The paper presents a qualitative case which demonstrates how microcomputer productivity tools are used to store, find, and retrieve information in a manufacturing environment through the use of tables, queries, reports, forms and macro design. The problem and scenario used offers an example case problem that may be utilized in a similar information systems course dealing with database design and development. Keywords: Case Problems, Database Management, Database Development 1. PURPOSE Manufacturing production processes in business typically produce a plethora of information with regards to a typical production run and production cycle. This information ranges from the production run costs, products, customers, raw material consumption, production times, and additional considerations for producing usable data. Typically, manufacturers incorporate information technology in-house to evaluate production costs, but how does an educator bridge the gap of this real world knowledge into the classroom? How might educators implement course content about database application design for cost analysis in manufacturing? There has often been a lack of real world business data and business problem solving in higher education. To prepare educators in solving this inherent lack of real world experience, business information systems should be developed and incorporated into the educational curriculum to demonstrate problem solving for a typical manufacturing environment. As Chen (2001) states, "the importance to prepare students to solve business problems, educators need realistic, complex business data to build simulated business information systems with which instructors can demonstrate and students can learn business problem solving and decision-making." The case presented in this paper intends to utilize manufacturing data and analysis as a guide in the development of a relational database management system to demonstrate production cost analysis information regarding a manufacturing production process. It is hoped by the authors that this case will present an example of an actual business case for database problem solving. 2. THE PROBLEM Some educators typically do not demonstrate effective manufacturing cost analysis and business information systems development within the classroom environment. On the other hand, Businesses need employees who recognize problems and use information technology to solve business problems (Breivik, 1998; Gregson, 1994; Morrison & Morrison 1989; Secretary's Commission of Achieving Necessary Skills, 1991). Since, manufacturing typically accumulates vast amounts of information about their production runs; database management systems should be developed in a learning environment in order to demonstrate how to answer typical production cost analysis questions. This case focused on the development of a problem that involved a database to be utilized as a business tool for production cost analysis. The outcome was uncomplicated in design, but significant in the learning process of database application design in real world manufacturing scenarios. Sternberg, Baskin, and Hofer (1986) conducted a similar study that used the kind of problem needing the storage of large amounts of information and requiring problem-solving strategies. The results indicated that using computers as a tool to store and organize information was a helpful aid to problem solving. In addition to reducing memory requirements for solving-problems, microcomputers can also be used to remove mechanical operations from students, such as tedious calculations, recalculations, sequencing, and other organization processes. Additionally, they permit higher-order thinking, such as problem formulation, problem analysis, and solution interpretation (Pea, 1986; Perkins, 1985; Schoenfeld, 1988; Chen 2001.) The following question guided the development of this case: How can information systems educators implement course content related to a "real-world" example of a relational database management system designed for cost analysis in manufacturing? 3. DATA COLLECTION The data presented in this case represents a service learning project employed by an information systems course instructor at a regional state university for a systems development class taught at the college-level. This study was conducted on local wooden pallet manufacturing facilities in a regional hardwood business community. Three facilities were chosen and agreed to participate in the case study as model manufacturing facilities. Pallet manufacturing was chosen as the case model due to the local economic impact, location, and feasibility of such manufacturing facilities in the regional location. In preparing to collect data, researchers first contacted several local pallet manufacturers to gain their cooperation, explain the purpose of the study, and setup key contacts within each facility. Researchers then setup a time frame to follow to further gain understanding of the pallet manufacturing process, terminology, material calculation, and process cycle. The wooden pallet manufacturing industry is a very unique industry that separates it from many typical manufacturing facilities. A great deal of cooperation between managers and researchers had to be established to understand its role as a production facility. 4. INTERVIEWS Interviews were conducted as a starting point to differentiate how and what each facility collected, stored, and disseminates information regarding cost analysis for each of their production lines. Plant managers and owners at each facility were initially interviewed to answer the question, "How do you determine productions costs for your production lines?" The overwhelming response was, "By the seat of our pants guess." Every manager and owner had an average of 15 years experience in the pallet manufacturing business. This vast amount of experience gave them a sixth sense of costs related to production runs. The only true numbers they could rely on are the financial statements at the end of the month. The financial statement did not allow for dissemination of information for each pallet produced on any given day. The production managers and owners basically only knew collectively what their profit/loss ratio was at the end of the month based on the end of the month financial statements. This response imposed an even more intuitive question, "What would you like to see with regards to analyzing production costs for your production lines?" This question had the greatest influence on how the case study was to proceed. Researchers took detailed notes during the interview process and afterwards as a follow- up process. These notes were then used to determine the starting point to categorize what needed to be recorded in data tables and tallied with query development. Surveys based on the findings in the interviews were issued to the managers and owner to rate the importance of what they believed to be pertinent information. The survey information was then evaluated by researchers to conclude the feasibility of such data as an input for a database application and query development. 5. COLLECTION OF FIELD DATA AND OBSERVATIONS Researchers observed several typical production runs to gain further understanding on the pallet manufacturing production cycle. Next, the survey analysis determined what data should be collected and input into the database tables. The database tables were then designed, (see Tables 1 and 2) and raw field data was then collected. TABLE 1 STRUCTURE OF PRODUCTION LINE TABLE: Field Name Data Type Field Size Run ID (Primary Key) Text 20 PONumber Text 20 ProductID Text 10 Date Date/Time Date/Time CustomerID Text 6 PalletBDFT Number Double (2 Decimals) NailQTY Number Long Integer Run Time Number Double (2 Decimals) DownTime Number Double (2 Decimals) ChangeOverTime Number Double (2 Decimals) DailyHours Number Double (2 Decimals) DTMemo Memo NumberWorkers Number Double (2 Decimals) LaborFactor Number Double (2 Decimals) LumberFactor Number Double (2 Decimals) Overhead Number Double (2 Decimals) QTYProduced Number Long Integer ScrapBDFT Number Long Integer SalePrice Currency Currency NailCost Currency Currency TABLE 2 STRUCTURE OF CUSTOMER TABLE: Field Name Data Type Field Size CustomerID (Primary Key) Text 6 CustomerName Text 15 Address Text 20 State Text 2 ZipCode Text 5 Phone Text 12 ContactName Text 15 TABLE 3 STRUCTURE OF PRODUCT DESCRIPTION TABLE: Field Name Data Type Field Size Product ID (Primary Key) Text 10 ProductDesc Text 20 NailQTY Number Long Interger PalletBDFT Number Double (2 Decimals) SalePrice Currency Currency Researchers designed paper forms based off table entries for field input on the production line. The production manager would oversee the data input for each form that was filled out to ensure validity of the data collection. Each production line would typically produce four to five different pallets in an eight-hour day, thus resulting in a form for each production run. The researchers decided to collect data for each production run to disseminate each product as a single input unit. Production data was collected for 16 months and entered into the database tables. 6. ANALYSIS OF FIELD DATA AND DEVELOPMENT During the collection of field data, database query designs were developed to analyze the raw data. First the researchers had to determine what the production managers wanted to see with regards to productions costs. Initial and follow-up interviews allowed researchers to develop queries for the first phase of development. Microsoft Access(r) was chosen as the relational database application to store and manipulate the field data and to bridge the developed research information into the information systems design and analysis course. During the first phase, researchers set up second round interviews with contacts within the organizations to determine how the managers and owners calculate lumber cost, overhead cost, labor cost, lumber board footage, production times, labor man hours, profit and loss, and pallet production requirements. Researchers took detailed notes during the interview process and afterwards as a follow- up process. The information gathered by the interview process enabled researchers to develop query calculation. During the second phase, cost accounting principles and interviews were used to determine production and process cost analysis through query calculation development. Each calculation developed referred back to specific data stored within the database tables. The query calculations were segregated into categories. During the third phase, after the researchers developed the query calculations, follow-up interviews were completed with the production managers and owners to review the query calculations. The following are queries used in the problem: Queries Associated with Tables: 1. Raw Material Usage Query 2. Production Times Query 3. Labor Man Hours Query 4. Production Costs Query 5. Financials Query 6. Total Cost Query The Total Cost Query groups all the previous query results into a single object. This allows the user to generate a wide carriage cost report for easier access to all the data and for the developer to create specific reports from a single query object. Relationships must be defined within the query design. The following fields were used in the total cost query using the variables in the legend that follows: RunID, ProductID, Date, CustomerID, PalletBDFT, RunTime, DownTime, ChangeOverTime, TotalDTMin, ProductionMin, RunTimePalletMin, ProductionPalletMin, ProdLaborManHours, DTLaborManHours, BTLaborManHours, COTLaborManHours, TotalDTLaborManHours, TotalLaborManHours, FTGOut, FTGIn, UsableYield, TotalNails, LumberCost, ProdLaborCost, DTLaborCost, COTLaborCost, BTLaborCost, OverHeadCost, NailCost, TotalLaborCost, TotalCost, BDFTCost, PalletCost, Rev, RevBDFT, TotalPl, %PL. Query Calculation Legend: COT = Change Over Time BT = Break Time DT = Down Time BDFT = Board Footage Prod = Production FTG = Footage 7. CONCLUSIONS The paper presents a qualitative case which demonstrates how a database application could be used in a manufacturing environment. The problem and scenario used offers a "real-world" example that may be utilized in an information systems course dealing with database design and development designed for cost analysis in manufacturing. Although the parts of the problem that are presented here are limited due to the restriction of space, important components are presented to present a database problem which information systems educators may model in a college-level course that teaches database design and development. Additional information is available from the authors as the case problem is presented. This paper presents one example of steps that may be followed in an experiential exercise exposing database students to a "real-world" business case problem. 8. REFERENCES Chen C. (2000). Using Realistic Business Data in Teaching Business Problem Solving. Information Technology, Learning, and Performance Journal, Vol. 18, No. 2, Fall 2000 (pp. 41). Gregson, J.A. (1994). From critical theory to critical practice: Transformative vocational classrooms. In R.D. Lakes (Ed.), Critical education for work: Multidisciplinary approaches (pp. 161-180). Notebook, NJ: Ablex Publishing Corporation. Lambrecht, J.J. (1997). Teaching problem solving for employment preparation. Journal of Research of Computing in Education, 25(4), 506-520. Morrison J., & Morrison, P. (1989). Technology in business and changing expectations. Business Education Forum, 46(8), 3-6. Pea, R.D. (1986). Beyond amplification: Using the computer to reorganize mental functioning (Technical Report No. 38). New York: Bank Street College of Education, Center for Children and Technology. (ERIC Document Reproduction Service No. ED 297 706). Perkins, D.N. (1985). The fingertip effect: How information-processing technology shapes thinking. Educational Research, 14(7), 11-17. Schoenfeld, A.H. (1988). Mathematics, technology, and higher order thinking. In R.S. Nickerson & P. P. Zodhiates (Eds.), Technology in Education: Looking toward 2020 (pp.67-96). Hillsdale, NJ: Lawrence Erlbaum Assocaites. Secretary's Commission of Achieving Necessary Skills (1991). What will work requires of schools - A SCANS Report for America 2000. Washington D.C.: U.S. Department of Labor.