Transcription
Journal of Information Systems Education, Vol. 23(2)Teaching CaseEnhancing Knowledge Integration with REA Modeling inan AIS ProjectSunita AhlawatJoyce VinceletteSchool of BusinessThe College of New Jersey2000 Pennington Road, Ewing, NJ 08628-0718ahlawat@tcnj.edu, vincelette@tcnj.eduABSTRACTThis paper describes a project that can be used as an educational tool to develop student understanding of the interrelatednature of business processes. Since accounting databases involve economic resources, events and transactions, and agents(REA), this project requires coverage of business processes, relational data base concepts, data modeling, and familiarity withMS-AccessTM. During this semester-long project, students learn how data is created, how it is interrelated, how it is stored,and how it may be used for business intelligence. By the assignment of deliverables, students gain a fuller and more accurateunderstanding of accounting systems and databases that support core business processes than by completing short, disparateexercises. Comprehensive teaching notes and solutions to the required deliverables are provided.Keywords: Database design and development, Entity-relationship modeling/diagram, Normalization, Business intelligence1. INTRODUCTIONThe ability to document information systems and processmapping are important skills, and are critical tounderstanding the accounting system and its controls. In thisproject, students use the REA modeling approach toconstruct an extended ER model for supporting the corebusiness activities of DataTech, a hypothetical small-sizedenterprise in Ireland. The purpose of the project is to furtherstudent understanding of accounting and informationtechnology (IT) concepts and illustrate how these conceptscan be applied in practice; to connect the IT theory and skillswith enterprise practices of today. These skills include: dataorganization, storage, analysis, presentation, and thesafeguarding of accounting data, in addition to businessprocesses and system design issues. The project describedbelow is more comprehensive than many currently availableas end of chapter exercises and projects described inAccounting Information Systems (AIS) textbooks, such asRomney and Steinbart 2012; Gelinas, Dull, and Wheeler2012; Bagranoff, Simkin, and Norman 2010; and Kay andOvlia 2012.The project requires students to integrate two businessprocesses, sales and purchase. It also requires students tocombine the theory underlying the business processes withdatabase creation and accounting applications. Studentscreate a model based on business needs, use the model tocreate a workable database, and use the database to answer119management questions. The project has been piloted testedover the course of three semesters and modifications madebased on student feedback and instructor assessments andexperience. Typically the project is completed by smallgroups of students (ideally three) but it can also becompleted individually.Acting as accounting consultants, students design, create,and populate a database system so that the company canbetter track and analyze its growing number of clients,agents, transactions and contracts. Students are given basicinformation about the company and its operations and whatinformation management would like to see in order to moreeffectively manage its operations.Based on thisinformation, students must determine what information tocapture in the database, and design a schema for thispurpose. To accomplish this, students need to use a differentapproach from the more familiar, historical double-entrysystem of capturing accounting information in journals andledgers. Employing an entity-relationship (E-R) approach,students use the REA model to capture and store complexevents in a robust model that will serve as a blueprint for arelational database.2. REA MODELINGThe REA framework, first introduced by McCarthy in 1982,is based on accounting theory, including Sorter’s (1969)work on events accounting, and on database theory,
Journal of Information Systems Education, Vol. 23(2)including Chen’s (1976) work on entity-relationshipdatabases. Complex systems often cannot be understood intheir entirety so a model is a useful tool for minimizing thiscomplexity. Models are built so that the system beingdeveloped can be better understood. In essence, a model is asimplification of reality, an “abstraction.” Every model canbe expressed at different levels of precision (abstraction).The reality of most (perhaps all) enterprises conforms to thesame pattern at the business process level. A businessprocess is a series of activities that accomplish the businessobjective of adding value to input resources. The termtransaction cycle is often used interchangeably with businessprocess. The commonly interconnected business processesare financing, acquisition/payment, human resources,conversion (manufacturing), and sales/collection (Dunn et al.2005).At the value chain level, the REA model shows theinterconnection of the business processes in an enterpriseand the resource flow between them. McCarthy (2003)described the semantic modeling of accounting phenomenawhere each economic event in each transaction cyclecorresponds to a resource inflow or outflow. The resultingREA diagram can then be used to understand databasedesign by identifying which entities should be included inthe AIS database and prescribing how to structurerelationships among them. Recent studies have shown thatthe REA modeling approach results in a more accurateunderstanding of the business processes and policies as usersare better able to recognize the model's core pattern ofenterprise information in the diagram (Poels et. al., 2011).Additionally, companies that purchase rather than developsoftware in-house still follow the systems development lifecycle (SDLC) process. An important aspect of the SDLC isdetermining AIS requirements and whether or not softwarethat meets these requirements is commercially available inthe marketplace.The REA model captures the interaction of economicresources, economic events and economic agents.Accordingly, there are three main entity types in the REAontology: Resources, Events, and Agents. A resource haseconomic value to the enterprise, such as inventory, cash, orother assets. An agent is a person or organization involved inevents and can include a vendor, employee, or customer.Events can be classified into three types: economic events,business events, and information events. Economic eventsare those which change the quantity of a resource, such assale and cash receipt. Business events are additional eventsthat provide the organization with new information whichmanagement can use to better plan, monitor, and control theeconomic events. For example, placing a purchase order is abusiness event because it provides management with newinformation: goods are scheduled for receipt, and prices arenegotiated. Information events are processes that areperformed solely to capture or communicate informationabout the business and economic events. These includeactivities such as generating an invoice, printing an accountsreceivable aging report, and displaying customer history.Economic events encompass three main relationshiptypes: duality (D), stockflow (sf), and participation (P). Aduality relationship is a causal relationship between twoeconomic events, a Give and a Get Event, or an Event-Eventrelationship. Stockflow is a relationship between an Eventand a Resource depicting either an inflow or outflow of aresource. A participation relationship is between an Eventand an Agent, where an agent is either an internal or anexternal agent. Figure 1 illustrates the REA model in itsmost basic configuration from the perspective of the businessentrepreneur (McCarthy 2003). Besides duality, businessevents include three more relationship types: fulfillment,commitment, and proposition.Figure 1: Basic REA Model of an Economic ExchangeIn short, the REA model is a pattern for business processesthat answers the what, the when, the who, and the why(duality stockflow value chain) questions about anaccounting transaction. This is accomplished through a seriesof business events that move a transaction through tocompletion. Greets and McCarthy (1997) describe dualityrelationships as the glue that binds a firm’s separateeconomic events together into rational economic processes,and stock-flow relationships as weaving these processestogether into an enterprise economic chain.The primary difference between an E-R diagram and aREA is how entities are defined. In an E-R diagram entitiesare business objects or events. In REA, entities can beresources, events, or agents. The business object in the E-Rdiagram is basically subcategorized in the REA model aseither a resource or agent. An event is the same as under theE-R modeling. Like E-R models, the database componentsincluded in REA modeling are entities, relationships,attributes, and cardinalities. Cardinalities are assigned torepresent business rules. The basic REA business processlevel model can be extended to include commitments andtypes, and relationships involving those commitments.Typification relationships can link resources, events, agents,and commitments to “entity-types”. This is needed whenattributes of category level entities need to be stored. BothREA and ER diagrams should result in the same number ofentities and relationships.120
Journal of Information Systems Education, Vol. 23(2)2.1 Converting Conceptual Data Model to RelationalDatabaseTo create well-behaved database tables, students mustconsider entities, relationships, conversion rules forcardinalities, and the “one fact-one place” rule as follows: 1)for each entity in the REA model, create a separate tablewith an entity identifier field called a primary key (pk) whichuniquely identifies each record, and 2) for each relationshipin the REA model, determine whether a separate bridge tablerequiring a composite primary key (cpk) should be created orwhether the relationship be represented with a foreign key. Aforeign key (fk) is a field in a table that matches a primarykey in another table and is used to cross-reference tables.Redundancy and Load (percentage of non-null values in aforeign key column), and cardinalities are key factors in thisdetermination.3. PROJECT DESCRIPTIONYou have been asked to design and implement a relationaldatabase application for DataTech’s sales/collection andpurchase/payment subsystems, based on the followingnarrative.3.1 Company BackgroundDataTech is an IT consultancy business based in Dublin,Ireland. The company provides IT solutions to small andmedium size enterprises (SME) throughout Ireland. ITconsulting has seen huge growth in recent years due to anincreased demand among companies in the SME sector tomodernize their operations. From a single owner/employeein early 2009, DataTech has grown to a current staffing levelof 15. It now looks to upgrade its accounting informationsystem.3.2 Current SystemDataTech’s management originally implemented a flat-fileMS-Excel based information system. While this system metthe requirements of the nascent operation, it has becomeinadequate to effectively track and analyze the company’sgrowing number of agents and transactions, with limitedrelationships between separate worksheets. Additionally,when entering data, the current spreadsheet system is proneto human error that may stem from insert, delete, or updateanomalies.3.3 DataTech’s Basic Operating ProceduresA detailed examination of DataTech’s business processes isessential for gaining a better understanding of what a newsystem would require. This project focuses on two suchprocesses: the sales/collection process and thepurchase/payment process.3.3.1 Sales Cycle: The sales and collection cycle containsactivities designed to market and sell IT solutions (productand service), as well as collect on these sales. Therefore, itcontains activities involved in marketing and sales, saletransaction processing, delivery of product, collection ofpayments, and the processing of returned product (if any).The key economic goals of these activities are to maximizesales revenues and the cash flow generated by the collection121of sales revenues.In order to align the company’s proposed IT solutionsseamlessly with the business objectives of its customers;DataTech must first gain an understanding of the ITrequirements of each customer. To that end, prospectivecustomers are afforded a complementary consultation by oneof the company’s three IT Consultants. The consultation isthe first point of contact between a customer and DataTech.The Consultant’s task is to impress upon the client the widerange and superior quality of DataTech’s products.Consultants are encouraged to discuss a wide range ofoptions at any given consultation, before recommendingspecific products based on the observed needs of thecustomer. Consultants are tasked with obtaining customerinformation at this initial consultation. This informationincludes a contact person and credit limit, as well as standardcompany information. Consultants are also expected torecord the different products which are recommended at eachconsultation. Quantities of each item are not discussed at thispoint. Not all consultations necessarily relate to newcustomers, as some customers require more than oneconsultation before committing to placing an order.However, only final consultations are linked to theculminating orders. When a customer decides to place anorder, they do so by contacting one of DataTech’s five SalesAssistants, whose main responsibility is to receive an orderand ensure that it is dispatched correctly and efficiently.Once a customer places an order, it is recorded in the saleorder file.A Sales Assistant arranges for the order to be assembledand shipped. At times, one or more of the inventory itemsordered by a customer are not in stock. In these cases,DataTech ships partial orders. A sales order can be for manydifferent items, and an item can be ordered many times.Discounts are often extended to repeat customers and largerorders. As standard product prices are stored for eachproduct, Sales Assistants enter discounts in percentage termsfor each line item in an invoice. For example, if a product’slist price is 100 and the agreed selling price for an invoiceis 95, a Sales Assistant enters a discount of five percentonto the sales entry form.For various reasons, products are sometimes returned.For example, it sometimes happens that products aredamaged in transit, or that the customer overestimated theirIT requirements. A customer may return sales items in one ormany batches. When a Sales Return is received, one of thefive Accounts Administrators ensures that the return iscorrectly recorded. The Accounts Administrator must thenupdate the Sales Return and Accounts Receivable ledgerbalances. This is a very laborious and a time-consumingprocess. It is desirable that any new information system becapable of integrating the Sales Return event with thesefunctions.DataTech’s collection activities include documenting thereceipt of payments and depositing the payments in thecompany’s bank account. A critical feature of the collectionprocess is linking the payments back to invoices to makesure all invoices are ultimately paid in full. Customers areexpected to pay their invoices within thirty days. Mostcustomers pay on time with only one payment. However,some customers arrange to make partial payments over two
Journal of Information Systems Education, Vol. 23(2)or three months (i.e., installment payments). Each customerpayment is for only one invoice. Customers do not run a tab(i.e., combine several invoices into one payment), as mostare not frequent, repeat customers. Payments received aredeposited into only one bank account, the operating account.DataTech may receive cash from sources other thancustomers for sales (e.g., a loan from a bank).The REA model for DataTech’s sales cycle is presentedin Exhibit 1. Major events of interest modeled areconsultation (aka sales call), receive sale order, sales (akashipment), sales return, and cash receipt. Each entity(resource, event or agent) is represented by a rectangularbox. The attributes are contained within the boxes.The first event in the REA model is Consultation, aninitiating and a business event. A relational schema for thisevent is provided in Figure 2. It shows typical relationshipsbetween the Employees, Consultations, Inventory, andCustomers.The relationships between the Customer andConsultation tables; Employee and Consultation tables;Employee Type and Employee tables; and the InventoryType and Inventory tables are one-to-many (1—N). Forexample, each employee type can have many employees,hence a 1: N relationship. The relationship between theConsultation and Inventory tables is many-to-many (N—N).That is, a given consultation can involve several inventoryitems. Any particular inventory item can be the subject ofseveral consultations. Note that a relationship table(ConsultationItem) is required when necessary to convertone N—N relationship into two 1—N relationships.3.3.2 Purchase Cycle: The purpose of the purchase andpayment cycle is to secure economic resources thatDataTech uses to provide services. The main goal is tosecure economic resources of sufficient quality and quantityto meet DataTech’s needs at the lowest possible cost and topay for them on time. The major activities of the purchaseand payment cycle parallel those of the sales and collectioncycle because they present the view of the sales transactionfrom the standpoint of the purchaser instead of the seller.Inventory is generally acquired by DataTech fromwholesalers and software vendors. Purchasing Agents ensurethat sufficient levels of inventory are maintained in thecompany. Inventory levels are not currently recordedautomatically, but it is expected that any prospectiveinformation system will incorporate this feature. This willgreatly reduce the burden on DataTech’s two PurchasingAgents. Once a Purchasing Agent decides that an orderneeds to be placed with DataTech’s suppliers, a PurchaseOrder form is completed. This form records an expectedDelivery Date as well as generic purchase order information,to enable DataTech to gauge which suppliers are meetingdelivery schedules.A supplier may fulfill a purchase order over one or manyshipments. When a shipment arrives at the receiving dock, areceiving clerk must count the inventory items and recordwhich items were received and their quantities on aninventory receipt. Completing this receipt reduces thepossibility of error (e.g., receiving unordered goods). Theinventory receipt record includes a notation of the purchaseorder on which the inventory items were ordered.DataTech makes payments after an invoice has beenreceived from the supplier. The Accounts Administratorcompares the purchase order, receiving reports, and invoicesto ensure a three-way match. Once the Administrator verifiesthe three-way match, a payment check is generated withinten days of receiving the inventory. Although DataTechusually pays for inventory receipts in full, on rare occasions,the company makes installment payments for large orders.DataTech does not pay for multiple invoices with only onecheck (i.e., it does not run a tab). Payments are usually madefrom the operating account.Figure 2: Relationship Between Entities Surrounding the Consultation Event122
Journal of Information Systems Education, Vol. 23(2)3.3.3 Cash Management: Accounts Administrators overseethe sales returns, cash receipt, cash disbursement andaccounting functions of the company. Effective cash flowmanagement is very important to DataTech, and AccountsAdministrators endeavor to maintain healthy liquidity ratiosat all times. Collecting information regarding total debtorand creditor levels is currently a laborious task, andDataTech would like the new accounting information systemto auto
students use the REA model to capture and store complex events in a robust model that will serve as a blueprint for a relational database. 2. REA MODELING . The REA framework, first introduced by McCarthy in 1982, is based on accounting theory, including Sorter’s
