Where are we going?

1. Where are we going? • Business process  model  data base  report • All accounting systems have these… • BPdocumentchart of accountsjournalledgerreports • BPdata modeltriggerdata entryreport

2. Traditional approach: separate systems for each cycle Financing Reporting & G/L Fixed Asset Revenue Acquisition Cycle Expenditure Cycles Inventory Human Resources Acquisition

3. Problems with multiple systems • Coordination difficult because of lack of shared information • Salesinventoryproduction,… • Schedulingbudgetingoperations,… • Multiple versions of the “truth” • What is sales—per accounting, marketing, production?? • Redundancy  higher costs, inefficient, inconsistency

4. ERP: An Integrated Solution Shipping Customer Order Processing Human Resources Ware- house Inventory Management Manufacturing Equip. Receiving Procurement Data about ALL of the various business processes Financial System

5. How do ERP’s provide integration? A/R Materials Management HR Production Common Database Sales A/P Quality Control Cash Mgt

6. ERP and the value chain Just part of the picture SCM Customers Suppliers ERP Product Design CRM Others Adapted from: Numetrix Users Group meeting, 1999

7. ERPs • SAP, Oracle/PeopleSoft/JD Edwards, Lawson, Great Plains (?) • what does it mean to be an ERP • shouldn’t everyone be one • Configure, conform, construct • Integrated v. Best of class • Be sure to read ABCs of ERPs

8. Reporting out of the ERP • Standard—export to *Excel* perhaps • convert to ASCII for SEC • convert to HTML for web • convert to pdf for distibution • ….. • XBRL—export to *Excel* perhaps • convert to ASCII for SEC • automatically encode to XBRL • make available to SEC, web, pdf…

9. Classifying Accounting Systems – types (small to big) • Single Entry • Money, Quicken • Bookkeeping Systems (organized around A=L + OE) • Peachtree, DacEasy, Quickbooks • Multidimensional Accounting • GEAC’s SmartEnterprise, Solomon’s Solomon IV • Modular Integration • JBA Software’s System 21, Lawson’s Insight II Enterprise Suite • Single Source ERP • SAP’s R/3, Oracle Financials, JD Edwards’ One World, PeopleSoft, Baan’s BaanERP

10. An ERP can create a flexible, more democratic organization … An ERP can create a hierarchical, uniform organization… An ERP can strengthen an organization’s ability to execute effective business processes… An ERP can threaten an organization’s ability to execute an effective business strategy… Enterprise-Wide Systems

11. Foundational units • Relational databases • Data modeling • Normalization • Query languages

12. Relational database rules • Every table must have a unique primary key—cannot be null • Foreign keys must be null or have a value corresponding to the value of a primary key in another table • Each attribute must describe a characteristic of the object identified by the primary key—all attributes must relate to the primary entire key • Each column in a row must be single-valued—no repeated attributes • No calculated fields • All related tables must be connected with foreign keys

13. Relational Data Bases Consider the transaction captured by this source document:

14. Relational Data Bases What entities are involved? RESOURCE EVENT AGENT Inventory Sale Customer

15. Relational Data Bases What tables are needed? RESOURCE EVENT AGENT Inventory Sale Customer

16. Relational Data Bases Is all information accounted for?

17. Relational Data Bases What database rule(s) are violated?

18. Relational Data Bases • We correct these problems by adding another table to create a “normalized” data base • Data duplication is minimized • Note the “concatenated” primary key in the Sales-Inventory table

19. Normalization rules • Remove all fields with multiple values—the multiple-valued objects should be their own table • Remove all fields that do not depend upon the entire primary key—the fields belong to one of the connected tables • Remove all fields whose values can be unambiguously predicted by looking at the values of a non-primary key field—the fields should be in their own table • depend upon = value can be predicted by • Do recording normalization exercise… • What business rules have you “determined” with these tables

20. Relational Data Bases We have created an efficient relational database thatuses foreign keys to link the tables:

21. Data modeling • Identify objects of interest • Identify relationships • Identify attributes of the objects • We will use ER diagrams to build an REA model to give a well-structured database • Semantic modeling—start with understanding of business and how processes work to begin model • Data bucket—start with data attributes and sort into normalized tables

22. Activity to Information Business activity to business information • Real business activity occurs • Identify relevant objects • Identify relationships between objects • Identify relevant attributes • Build data base • Entities=tables; Relationships=table links; Attributes=fields • Capture data from business activity, populate database • Query databasebusiness information • No ledgers, journals, debits/credits, chart of accounts…

23. Future of accounting?? • Could lead to the abandonment of double entry accounting ‑ redundancy is no longer required to ensure accuracy of the AIS • External reporting may become a matter of database access by users—See Focus 4-1 (p. 127) • Provides users with powerful ways to access data without aggregating and valuing by accountants. Multiple views, without predefinition by the accountants, are now possible • Accountants must be active participants in designing systems to see that adequate controls are included to safeguard the data and reliability of the information

24. Faculty Evaluation “The material is hard to envision due to the fact that most of it is hopeful thinking as to future developments. Why do accountants need this really?”

25. REA Data Modeling “Building accurate databases requires a great deal of careful planning and design before you even sit down at a computer.” • The REA data model provides a method for designing a database that is well-structured (it creates a “normalized” relational database) • The REA data model consists, in general, of three basic elements and a pattern • INSTEAD of starting with a mess of data and applying “normalization rules” to develop a set of tables the REA data model lets us BEGIN with business knowledge to create a set of normalized tables

26. Basic Business Processes A set of Give-Get exchanges

27. Merchant GiveCash Cash Ship GetShip Shipbuilder Capital Acquisitions What wasexchanged? What was theexchange? Who wasinvolved?

28. Merchant GiveCash Cash Inventory GetSilk Vendor Expenditure What wasexchanged? What was theexchange? Who wasinvolved?

29. Merchant GiveSilk Inventory Cash GetCash Customer Revenue What wasexchanged? What was theexchange? Who wasinvolved?

30. Internal GiveSomething ResourceDecreased ResourceIncreased GetSomething External REA Business Process Model RESOURCES EVENTS AGENTS

31. Steps in REA data modeling • Identify the basic exchange (give – get) • Identify the resources affected by each event and the agents who participate in each event • Combine both events into the basic exchange template • Add information about cardinalities • Identify magnitude of relationships • Implement Model in Relational Database: • Table for each entity • Table for each M:N relationship • Use foreign keys for 1:1 and 1:N relationships

32. Resource Event Agent Ship Builder Get Ship Ship Merchant of Venice Model for one event in the exchange

33. Ship Builder Give Cash Cash Merchant of Venice Model for the other event in exchange Resource Event Agent

34. Internal Agent Resource Give Event External Agent Resource Get Event Internal Agent The Basic REA “Exchange” Template On the diagram, it helps to model different internal agents separately, even though will all appear in just one table called EMPLOYEES

35. Merchant of Venice “Captain” Get Ship Ship Ship Builder Give Cash Cash Merchant of Venice “Cashier” Create the basic Exchange template Resource Event Agent

36. Some special cases • Commitment event—an agreement to engage in an economic exchange in the future • Purchase order • Sales order • Observation event—activity that is relevant for planning, evaluation or control but is not directly related to an economic exchange • Marketing call • Computer support call

37. Step 2: Cardinalities • Cardinalities explain how many instances of the entity on one side of the relationship can be linked to one instance of the entity on the other side of the relationship • In a relational database, eachinstance of an entity = a row in a table • To understand this, first really need to understand what each entity represents • Cash • Inventory • Customer • Etc….

38. Now we can model cardinalities • Definition: Cardinalities indicate how many instances of one entity can be related to a single instance of the another entity • Cardinalities come as pair of numbers: (minimum, maximum) • Minimum can be 0 or 1 • Maximum can be 1 or N (N = many)

39. Cardinality Identification • Consider two related objects, Receive Cash and Customer • For a single event of Receive Cash, what is the minimum number of agent Customers, zero or 1? • For a single event of Receive Cash, what is the maximum number of agent Customers, 1 or many? • For a single agent Customer, what is the minimum number of event Receive Cash, zero or 1? • For a single agent Customer, what is the maximum number of event Receive Cash, 1 or many? • The answers reflect the business rules of the organization.

40. Generalized Statement • For a single occurrence of OBJECT A, what is the minimum number of related OBJECT B occurrences, zero or 1? • For a single occurrence of OBJECT A, what is the maximum number of related OBJECT B occurrences, 1 or many? • This gives the minimum and maximum cardinality from A to B • This is shown, in the ER format as:

41. Example of cardinality displayed graphically

42. Another example

43. Summary - Cardinalities • Cardinalities tell how many instances in one entity can be linked to one instance in the other entity • Cardinalities expressed as pairs of numbers (minimum, maximum) • Minimums can be 0 or 1 • Maximums can be 1 or N • There are four possible cardinality pairs: (0,1) (0,N) (1,1) (1,N)

44. Relationship Specification • Maximum cardinality of each entity • 3 possible relationship combinations: • 1:1 (maximum both sides = 1) • 1:N (maximum one side =1, other side = N) • M:N (maximum both sides = N) • Important for relating data tables

45. Implementing an REA model in a relational database • Once an REA diagram has been developed, it can be used to design a well-structured relational database. • The three steps to implementing an REA diagram in a relational database are: • Create a table for: • Each distinct entity in the diagram • Each many-to-many relationship • Assign attributes to appropriate tables • Use foreign keys to implement one-to-one and one-to-many relationships. • Remember…REA diagrams will differ across organizations because of differences in business policies.

46. Employees (Salesperson) Call on Customer Suppliers Customer Take Cust. Order Inventory Order Inventory Employees (Purchase Agent) Employees (Salesperson) Receive Inventory Customer Suppliers Sales Employees (Cashier) Employees (Cashier) Receive Cash Disburse Cash Cash

47. Identify the entities to be represented • Total entities to be represented in separate tables: Events 7 Resources 2 Agents 3 12

48. Look for N:M relationships • Total number of tables in database: Events 7 Resources 2 Agents 3 12 Plus: Many-to-Many Relationships 6 18

49. Create tables • Table names for these 18 tables correspond to the names of the entities in the REA diagram. • The tables for M:N relationships are hyphenated concatenations of the entities involved in the relationship. • Makes it easier: • To verify that all necessary tables have been created. • To use the REA diagram as a guide when querying the database.

50. Sample names • Table names for our integrated diagram: • Call on Customer • Take Customer Order • Give Inventory • Receive Cash • Order Inventory • Receive Inventory • Disburse Cash • Inventory • Cash • Customer • Supplier • Employee • Take Order-Inventory • Give Inventory-Inventory • Give Inventory-Receive Cash • Order Inventory-Inventory • Receive Inventory-Inventory • Receive Inventory-Disburse Cash