Bài giảng Enterprise information systems - Chapter 8: The Sales/Collection Business Process

Chapter 8The Sales/Collection Business ProcessChapter Learning ObjectivesIdentify activities and documents common to sales/collection business processes for various enterprisesRecognize similarities and differences between different types of revenue-generating enterprisesIdentify the components of the REA ontology in the sales/collection business processExplain how the sales/collection business process fits into an enterprise’s value systemExplain how the sales/collection business process fits into an enterprise’s value chainCreate a REA business process level model for an enterprise’s sales/collection business processCreate queries to meet common information needs in the sales/collection process2Sales/Collection Process in an Enterprise Value System3Sales/Collection Business Process in Enterprise Value Chains4REA Sales/Collection Business Process Level Pattern5Sales/Collection Process EventsInstigation Events in the Revenue CycleMay be internally instigated (marketing events such as sales calls, advertising campaigns, or promotions)May be externally instigated (customer inquiries)Internal agents involved in revenue cycle instigation events usually are sales/marketing personnel or customer service representatives External agents involved in revenue cycle instigation events usually are customersTypically there is no need to specifically identify a good or service for purposes of instigation events; information about the type of good or service promoted is usually sufficient6Customer Inquiry EventAn instigation event that is externally initiated; typically involves a customer contacting the enterprise, either via telephone, e-mail, or in person, to inquire as to the pricing and availability of products or servicesData is not typically captured for this event; the customer’s questions are answered and if the customer does not immediately place an order, the customer may be added to a sales representative’s list of future sales calls. If data were to be captured, it would be similar to the data captured for sales calls7Sales Call EventAn instigation event that is internally initiated; typically involves a sales representative calling on a customer, either via telephone or in person, to describe the features of one or more products or services Is usually symbolized by a sales call report document or data entry screen that summarizes information about this event (e.g., which sales representative(s) and customer(s) participated, date, start time, end time, location, what products and/or services were presented to the customer and what the customer reactions were to those products/services, etc.)8Sales Call Report9Relational tables Encompassing Sales Call Event10Sales/Collection Process EventsMutual Commitment Events in the Revenue CycleInvolve the enterprise and an external business partner agreeing to exchange resources at a defined future timeDon’t always happen at a discrete point in time; often involves a series of activitiesInternal agents are usually sales or customer service representatives and/or order entry clerks who assist the customer and collect the order dataExternal agents usually are customersTypically there is no need to specifically identify a good or service for purposes of mutual commitment events; information about the type of good or service promoted is usually sufficientIdeally enterprises would like to be able to trace mutual commitment events to the instigation events that triggered them11Sale Order EventA mutual commitment event whereby the enterprise agrees to deliver goods to a customer and that customer agrees to pay an ascertained price for those goodsIs usually symbolized by a sale order document or data entry screen that summarizes information about this event (e.g., order date, promised delivery date, payment terms, dollar amount, which sales representative(s) and customer(s) participated, what products the customer ordered, etc.)12Rental Contract EventA mutual commitment event whereby the enterprise agrees to transfer possession of a product to a customer for a defined time period and bestows on that customer the right to use the product for a prescribed purpose and in exchange the customer agrees to pay an ascertained priceIs usually symbolized by a rental contract document that summarizes information about this agreement (e.g. contract date, dollar amount, product(s) to be rented, time period for which each product is to be in customer’s possession, payment terms, which sales representative(s) and customer(s) participated, etc.)13Service Contract EventA mutual commitment event whereby the enterprise agrees to perform one or more services for a customer during a defined time period for an agreed upon price and in exchange the customer agrees to pay an ascertained priceIs usually symbolized by a service contract document that summarizes information about this agreement (e.g. contract date, dollar amount, service(s) to be provided by enterprise, time period during which service(s) will be performed, payment terms, which sales representative(s) and customer(s) participated, which employees or types of employees will perform the services, etc.) 14Sale Order Document15Service Agreement Document16Relational Tables Encompassing Sales Order Event17Sales/Collection Process EventsEconomic Decrement Events in the Revenue CycleRepresent the revenue generating activities; the giving up of one or more resources in order to get some other resource (usually cash)Do not always happen at discrete points in time; may involve a series of workflow activities (e.g. picking, packing, shipping, and so on)Resources vary for different types of businessesInventory (Inventory type may substitute for Inventory), Labor (Service Type may substitute for Labor), Temporary use of asset (as in a rental)Internal Agents are typically salespeople, shipping clerks, delivery clerks, and service engagement personnelExternal Agents are typically customers or clients18Sale EventAn economic decrement event in which title (ownership) of one or more products is transferred from the enterprise to a customer. The transfer may take place in person (e.g. a sale of a t-shirt at a campus bookstore) or in transit (e.g. a shipment of a video game from an enterprise with a web-based storefront)Is usually symbolized by a bill of sale, sale invoice, or similar document (or data entry screen) that summarizes information about the title transfer (e.g. date, dollar amount, product(s) for which title transferred, payment terms, which sales representative(s) and customer(s) participated, etc.)Sales that occur via transit are usually also symbolized by a packing list (which lists quantities of specific products shipped) and a bill of lading (which indicates the number, dimensions, and weight of packages in which products were shipped)19Rental EventAn economic decrement event in which possession of one or more products is transferred from an enterprise to a customer for an agreed upon length of time for an agreed upon purpose and priceUsually occurs over an extended period of time, rather than occurring at a single point in timeIs usually symbolized by documents or data entry screens that summarize information about the transfer of possession of products to the customer and then the return of products to the enterprise (e.g. dates, times, which products, rental agent, and customers were involved, etc.) 20Service Engagement EventAn economic decrement event in which one or more agents of the enterprise provide one or more types of service to a customerMay occur over an extended period of time, or may be performed at a point in timeIs usually symbolized by service invoice or similar documents or data entry screens that indicate the services have been performed and summarize information about the performance (e.g. date(s), time(s), types of service performed, which employee performed the service(s), for which customer, possibly some notes made by the employee about the service, etc.)21Sale Invoice22Picking List23Packing List24Bill of Lading25Relational Tables Encompassing Sale Event26Sales/Collection Process EventsEconomic Increment Events in the Revenue CycleAlmost always is a Cash Receipt eventAn economic increment event in which an external agent transfers ownership of cash (or a cash equivalent) to the enterpriseIs usually symbolized by a remittance advice document or data entry screen that summarizes information about the event (e.g. date, dollar amount, customer from whom cash was received, employee who processed the cash receipt, sale or other revenue generating activity to which cash receipt applied, etc.)Resource is typically cash (a type-level resource – usually a listing of cash accounts owned by the enterprise)Internal agents are usually cashiers, accounts receivable clerks, or other financing personnelExternal agents are usually customers or clients27Customer Statement with Remittance Advice28Relational Tables Encompassing Cash Receipt Event29Sales/Collection Process EventsEconomic Decrement Reversal EventsEvents in which previous economic decrement events are reversed or negatedAlthough similar in mechanics, such events are inherently different from economic increment events Getting something back that you were happy you had given up (and for which you now have to give back the thing you were originally happy to get) is not the same as getting something you wanted to get in exchange for giving up something you were willing to give upResources, Internal Agents, and External Agents are the same as those for economic decrement events30Sale Return EventAn economic event in which title (ownership) for products that were previously transferred from seller to buyer is transferred back from buyer to sellerAlthough a resource (or resource type) is increased as a result, the nature of sale returns is different than other economic increment events, I.e., it isn’t generally viewed as a desirable result. The substance of the event is such that the original economic decrement is negated. Thus, we label such events as economic decrement-reversal events. Is usually symbolized by sales return authorization, receiving report and credit memo documents or data entry screens that summarize information about the event (e.g. date, what quantities of which products were received from customer; dollar amount, which customer is involved, which employee processed return, etc.)31Sale Return Authorization32Receiving Report33Credit Memorandum34Relational Tables Encompassing Sale Return Event35Sales/Collection Process RelationshipsFulfillment relationshipsBetween Marketing, Sales Call, or Customer Inquiry and Sale Order eventsBetween Sale Order and Sale (or Rental or Service engagement) eventsDuality relationshipsBetween Sale (or Rental or Service engagement) and Cash Receipt (or Receipt of Bartered Goods/Services) eventsReversal relationshipsBetween Sale and Sale Return eventsParticipation relationshipsBetween each event and each internal and external agent36Sales/Collection Process RelationshipsProposition relationshipsBetween Marketing, Sales Call, or Customer Inquiry event and Inventory (or Inventory Type or Service Type)Reservation relationshipsBetween Sale Order, Rental Contract, or Service Contract and Inventory (or Inventory Type or Service Type)Also between Sale Order, Rental Contract, or Service Contract and CashStock flow relationshipsBetween Sale (or Rental or Service Engagement) and Inventory (or Inventory Type or Service Type)Between Sale Return and Inventory (or Inventory Type)Between Cash and Cash Receipt37Resource Query TypesDetailed status information at one or more points in time for each resource instanceDetailed status information at one or more points in time for only those resource instances meeting specified criteriaSummarized status information at one or more points in time for all resource instancesSummarized status information at one or more points in time for only those resource instances meeting specified criteria 38Revenue Cycle Resource QueriesA list of each inventory item or item type offered for sale by an enterprise A list of all inventory items or item types that possess certain characteristics (e.g. all books, real estate listings with lake frontage, toys with selling prices within a certain range, video games in GameBoy Advance format, preventive dental care services, etc.)Quantity on hand of an inventory item type as of a specified dateTotal cost value of inventory on hand as of a specified dateA list of all cash accounts owned by an enterprise as of a specified dateBalance in a specific cash account as of a specified dateTotal balance in all cash accounts as of a specified date39Example Resource Type Query SELECT Description, UnitOfMeasure, ListPrice FROM InventoryType WHERE ListPrice < 70;40Example Resource Type QuerySELECT *FROM CashWHERE DateAccountEstablished < April 20, 2010;41Event Query TypesDetailed information about each event instance e.g., what happened, when and where did it happen, etc.Detailed information about each event instance that meets specified criteria e.g. events of a specified type that occurred during a specified time period or that occurred at a specified locationSummarized information for all instances of an event type for a specified time period e.g. total of the event instances during a specified time periodSummarized information for only those instances of an event type for a specified time period that meet specified criteria e.g. average dollar value of the event instances for a specified location during a specified time period42Revenue Cycle Event QueriesLocation of a sales callTotal number of sales calls, sale orders, sales, etc., that occurred at a specified location or during a specified time periodTotal dollar amount for a specific sale order, sale, cash receipt, or sale returnTotal or average dollar amount of all sale orders, sales, cash receipts, or sale returns for one or more specified time periods Total or average dollar amount of sale orders, sales, cash receipts, or sale returns in a specific location for one or more specified time periodsSales tax applicable to a specified sale eventShipper’s tracking number for a shipment sale eventDate a sale event occurredLength of a sales call (end time minus start time) 43Event Tables44  Which table(s) do we need?Query to identify date and location of Sales Call #44Which relational algebra operators apply?What is SQL statement?Project, SelectSELECT SalesCallID, Date, Location FROM SalesCall WHERE SalesCallID=44;What is Access QBE?What is the result?45Sales for a specified time period (e.g. for an income statement)Which table(s) do we need?Which relational algebra operators apply?What is SQL statement?Project, SelectSELECT Sum(DollarTotal) FROM Sale WHERE Date BETWEEN 5/1/2010 AND 5/7/2010;What is Access QBE?What is the result?46Agent Query TypesDetailed status information at one or more points in time for each agent instanceDetailed status information at one or more points in time for only those agent instances meeting specified criteriaSummarized status information at one or more points in time for all agent instancesSummarized status information at one or more points in time for only those agent instances meeting specified criteria47Revenue Cycle Agent QueriesA list of all salespeople, cashiers, inventory clerks, or credit managers for an enterprise A list of all employees that possess certain characteristics e.g. all waiters and waitresses who are at least 21 years old, all staff auditors who have passed the CPA exam, all salespeople whose pay is commission-based, etc.48Example Agent Tables49Identify Customers with Credit Rating of “A” or “A+”Which table(s) do we need?Which relational algebra operators apply?What is SQL statement?SelectSELECT * FROM Customer WHERE CreditRating=A OR CreditRating=A+;What is Access QBE?What is the result?50Duality Relationship Query TypesIdentification as to whether a specified exchange is completedIdentification of completed exchanges for a specified time periodIdentification of incomplete exchanges for a specified time periodCalculation of the amount of claims, e.g. prepaid expenses, payables, unearned revenues, or receivables, either in total or for a specified exchange eventCalculation of the total or average length of the timing difference(s) between the events involved in one or more exchanges 51Revenue Cycle Duality QueriesCalculation of the outstanding receivable balance for a sale (or service engagement) invoiceCalculation of total accounts receivable at a point in timeCalculation of prepaid revenue at a point in timeAging of accounts receivableCalculation of the average number of days it takes to collect receivables 52Example Duality & Reversal Relationship Tables53Accounts Receivable Query StepsConceptuallyDollar value of sales or service engagements for which cash receipt has not yet occurred in full, adjusted for any sale returns and sale discounts.Practically Total sales/service engagements through balance sheet date minus total cash receipts through balance sheet date that applied to sales/service engagements. Also subtract sales returns and discounts through balance sheet date.MUST include ONLY ending date constraint (balance sheet item reflects cumulative data from beginning of company through balance sheet date).Use sale/service engagement date to determine which revenues to include.Use cash receipt date and duality relationship to determine which cash receipts to include. Be sure to aggregate cash receipts that apply to same sale BEFORE subtracting from sale amountLikely need for outer join and Nz function in Access (for sales with no matching cash receiptsNote: Sales minus Cash receipts from Customers not acceptable (because you might get non-sale related CR from customers)54Accounts Receivable Step 1: Total Sales through Balance Sheet Date“hard-wired” datedate as parameter55Accounts Receivable Step 2: Total Cash Receipts for Sales through Balance Sheet Date2a: identify them2b: sum them56Accounts Receivable Step 3: Total Sale Returns through Balance Sheet DateIdentify and sum them57Accounts Receivable Step 4: Calculate A/R as sales minus applicable cash receipts and sale returns58Accounts Receivable Result without Nz As of May 31, 2010As of May 15, 201059Adjusted Accounts Receivable Step 4 (using Nz to control null values)60Stockflow Relationship Query TypesWhat resources or resource types were increased or decreased by an economic event?What quantity of a resource or resource type was increased or decreased by an economic event?What dollar value of a resource or resource type was increased or decreased by an economic event?When did an event increase or decrease a specific resource or resource type?Where did an event increase or decrease a specific resource or resource type?61Revenue Cycle Stockflow Query TypesWhich inventory types were decreased by a specific sale event?What quantity of each inventory type was decreased by a specific sale event?Which inventory types were increased by a sale return event?What quantity of each inventory type was increased by a specific sale return event?What selling price was charged for an inventory type on a specific sale event?What selling price was granted as credit for an inventory type on a specific sale return event?What was the total dollar value of sales for a specified time period?(Note: if total sale amount is stored in the sale event table, then it is not necessary to use the stockflow relationship to meet this information need)What is the average dollar value of sales of a specified inventory type for a specified time period? 62Tables to useQuery to find dollar value of sales of a specified inventory type for a specified time period63Query to find dollar value of sales of a specified inventory type for a specified time period64Query to find dollar value of sales of a specified inventory type for a specified time period65Query to