Accounting Information System 12e Solutions - Chapter End Problems 12e by Marshall B. Romney Paul J. Steinbart

Accounting Information System 12e Solutions - Chapter End Problems 12e by Marshall B. Romney Paul J. Steinbart

All 22 Chapter's End Questions Solved

All Solution are in .doc format. Instant Download

For Order This Contact Us At

Whisperhills@Gmail.com

Identify at least three weaknesses in O’Brien Corporation’s revenue cycle activities. Describe the problem resulting from each weakness. Recommend control procedures that should be added to the system to correct the weakness.  

                                                                                   

12.8      Parktown Medical Center, Inc. is a small health care provider owned by a publicly held corporation. It employs seven salaried physicians, ten nurses, three support staff, and three clerical workers. The clerical workers perform such tasks as reception, correspondence, cash receipts, billing, and appointment scheduling. All are adequately bonded.

Most patients pay for services rendered by cash or check on the day of their visit. Sometimes, however, the physician who is to perform the respective services approves credit based on an interview. When credit is approved, the physician files a memo with one of the clerks to set up the receivable using data the physician generates.

The servicing physician prepares a charge slip that is given to one of the clerks for pricing and preparation of the patient’s bill. At the end of the day, one of the clerks uses the bills to prepare a revenue summary and, in cases of credit sales, to update the accounts receivable subsidiary ledger.

The front office clerks receive cash and checks directly from patients and give each patient a prenumbered receipt. The clerks take turns opening the mail. The clerk who opens that day’s mail immediately stamps all checks “for deposit only.” Each day, just before lunch, one of the clerks prepares a list of all cash and checks to be deposited in Parktown’s bank account. The office is closed from 12 noon until 2:00 p.m. for lunch. During that time, the office manager takes the daily deposit to the bank. During the lunch hour, the clerk who opened the mail that day uses the list of cash receipts and checks to update patient accounts.

The clerks take turns preparing and mailing monthly statements to patients with unpaid balances. One of the clerks writes off uncollectible accounts only after the physician who performed the respective services believes the account will not pay and communicates that belief to the office manager. The office manager then issues a credit memo to write off the account, which the clerk processes.

The office manager supervises the clerks, issues write-off memos, schedules appointments for the doctors, makes bank deposits, reconciles bank statements, and performs general correspondence duties.

Additional services are performed monthly by a local accountant who posts summaries prepared by the clerks to the general ledger, prepares income statements, and files the appropriate payroll forms and tax returns.

Identify at least three control weaknesses at Parktown. Describe the potential threat and exposure associated with each weakness, and recommend how to best correct them

12.9      Figure 12-18 depicts the activities performed in the revenue cycle by the Newton Hardware Company.                                                 

a.   Identify at least 7 weaknesses in Newton Hardware’s revenue cycle. Explain the resulting threat and suggest methods to correct the weakness.

b.   Identify ways to use IT to streamline Newton’s revenue cycle activities.  Describe the control procedures required in the new system.

12.10    The Family Support Center is a small charitable organization. It has only four full-time employees: two staff, an accountant, and an office manager. The majority of its funding comes from two campaign drives, one in the spring and one in the fall. Donors make pledges over the telephone. Some donors pay their pledge by credit card during the telephone campaign, but many prefer to pay in monthly installments by check. In such cases, the donor pledges are recorded during the telephone campaign and they are then mailed pledge cards. Donors mail their contributions directly to the charity. Most donors send a check, but occasionally some send cash. Most donors return their pledge card with their check or cash donation, but occasionally the Family Support Center receives anonymous cash donations. The procedures used to process donations are as follows:

Sarah, one of the staff members who has worked for the Family Support Center for 12 years, opens all mail. She sorts the donations from the other mail and prepares a list of all donations, indicating the name of the donor (or anonymous), amount of the donation, and the pledge number (if the donor returned the pledge card). Sarah then sends the list, cash, and checks to the accountant.

The accountant enters the information from the list into the computer to update the Family Support Center’s files. The accountant then prepares a deposit slip (in duplicate) and deposits all cash and checks into the charity’s bank account at the end of each day. No funds are left on the premises overnight. The validated deposit slip is then filed by date. The accountant also mails an acknowledgment letter thanking each donor. Monthly, the accountant retrieves all deposit slips and uses them to reconcile the Family Support Center’s bank statement. At this time, the accountant also reviews the pledge files and sends a follow-up letter to those people who have not yet fulfilled their pledges.

Each employee has a computer workstation that is connected to the internal network. Employees are permitted to surf the Web during lunch hours. Each employee has full access to the charity’s accounting system, so that anyone can fill in for someone else who is sick or on vacation. Each Friday, the accountant makes a backup copy of all computer files. The backup copy is stored in the office manager’s office.

a.   Identify two major control weaknesses in the Family Support Center’s cash receipts procedures. For each weakness you identify, suggest a method to correct that weakness. Your solution must be specific—identify which specific employees should do what.  . Assume that no new employees can be hired.

b.   Describe the IT control procedures that should exist in order to protect the Family Support Center from loss, alteration, or unauthorized disclosure of data.

12.11   Match the threats in the first column to the appropriate control procedures in the second column (more than one control may address the same threat).

Threat	Applicable Control Procedures
1.       1.      Uncollectible sales 2.      a.   Restrict access to master data. A.     2.      Mistakes in shipping orders to customers. B.     b.   Encrypt customer information while in storage.                         i.            3.      Crediting customer payments to the wrong account.                      ii.            c.    Backup and disaster recovery procedures. a.      4.      Theft of customer payments. b.      d.   Digital signatures. a.      5.      Theft of inventory by employees. b.      e.    Physical access controls on inventory a.      6.      Excess inventory. b.      f.    Segregation of duties of handling cash and maintaining accounts receivable. a.      7.      Reduced prices for sales to friends. b.      g.   Reconciliation of packing lists with sales orders. a.      8.      Orders later repudiated by customers who deny placing them. b.      h.   Reconciliation of invoices with packing lists and sales orders. a.      9.      Failure to bill customers. b.      i.     Use of bar-codes or RFID tags. a.      10.  Errors in customer invoices b.      j.     Periodic physical counts of inventory a.      11.  Cash flow problems b.      k.   Perpetual inventory system. a.      12.  Loss of accounts receivable data b.      l.     Use of either EOQ, MRP, or JIT inventory control system. a.      13.  Unauthorized disclosure of customer personal information.
	m. Lockboxes or electronic lockboxes.
1.       14.  Failure to ship orders to customers. 2.      n.   Cash flow budget
	1.       o.   Mail monthly statements to customers.
	1.       p.   Credit approval by someone not involved in sales.
	1.       q.   Segregation of duties of shipping and billing.
	1.       r.    Periodic reconciliation of prenumbered sales orders with prenumbered shipping documents.

12.12   Excel problem

Use EXCEL’s regression tools to analyze and forecast future sales.

 (Hint: The article “Forecasting with Excel,” by James A. Weisel in the February 2009 issue of the Journal of Accountancy (available at www.aicpa.org) explains how to perform the following tasks using either Excel 2003 or Excel 2007).

a.   Create a spreadsheet with the following data about targeted emails, click ads, and unit sales:

b.   Create a scattergraph to illustrate the relationship between targeted emails and unit sales. Display the regression equation and the R² between the two variables on the chart.

c.   Create a scattergraph to illustrate the relationship between click ads and unit sales. Display the regression equation and the R² between the two variables on the chart.

d.   Which variable (targeted emails or click ads) has the greater influence on unit sales? How do you know?

e.   Use the “ =Forecast “function to display the forecasted sales for 200,000 targeted emails and for 200 click ads.

12.13    Give two specific examples of nonroutine transactions that may occur in processing cash receipts and updating accounts receivable. Also specify the control procedures that should be in place to ensure the accuracy, completeness, and validity of those transactions.

CHAPTER 13

THE EXPENDITURE CYCLE:

PURCHASING AND CASH DISBURSEMENTS

13.1     In this chapter and in Chapter 12 the controller of AOE played a major role in evaluating and recommending ways to use IT to improve efficiency and effectiveness. Should the company’s chief information officer make these decisions instead? Should the controller be involved in making these types of decisions? Why or why not?         

13.2        Companies such as Wal-Mart have moved beyond JIT to VMI systems. Discuss the potential advantages and disadvantages of this arrangement. What special controls, if any, should be developed to monitor VMI systems?

13.3        Procurement cards are designed to improve the efficiency of small noninventory purchases. What controls should be placed on their use? Why?

13.4     In what ways can you apply the control procedures discussed in this chapter to paying personal debts (e.g., credit card bills)?

               

13.5        Should every company switch from the traditional 3-way matching process (purchase orders, receiving reports, and supplier invoices) to the 2-way match (purchase orders and receiving reports) used in Evaluate Receipt Settlement (ERS)? Why (not)?

13.6      Should companies allow purchasing agents to start their own businesses that produce goods the company frequently purchases? Why? Would you change your answer if the purchasing agent’s company was rated by an independent service, like Consumer Reports, as providing the best value for price? Why?

13.1        a.    A purchasing agent orders materials from a supplier that he partially owns.

b.    Receiving-dock personnel steal inventory and then claim the inventory was sent to the warehouse.

c.     An unordered supply of laser printer paper delivered to the office is accepted and paid for because the “price is right.” After jamming all of the laser printers, however, it becomes obvious that the “bargain” paper is of inferior quality.

d.    The company fails to take advantage of a 1% discount for promptly paying a vendor invoice.

e.    A company is late in paying a particular invoice. Consequently, a second invoice is sent, which crosses the first invoice’s payment in the mail. The second invoice is submitted for processing and also paid.

f.     Inventory records show that an adequate supply of copy paper should be in stock, but none is available on the supply shelf.

g.    The inventory records are incorrectly updated when a receiving-dock employee enters the wrong product number at the terminal.

h.    A clerical employee obtains a blank check and writes a large amount payable to a fictitious company. The employee then cashes the check.

1.       i.        A fictitious invoice is received and a check is issued to pay for goods that were never ordered or delivered.

2.      j.        The petty cash custodian confesses to having “borrowed” $12,000 over the last five years.

3.      k.       A purchasing agent adds a new record to the supplier master file. The company does not exist. Subsequently, the purchasing agent submits invoices from the fake company for various cleaning services. The invoices are paid.

4.      l.        A clerk affixes a price tag intended for a low-end flat panel TV to a top-of-the-line model. The clerk’s friend then purchases that item, which the clerk scans at the checkout counter.

13.2     Match the terms in the left column with their appropriate definition in the right column.

Terms	Definitions
1.       economic order quantity 2.      A document that creates a legal obligation to buy and pay for goods or services. 3.      materials requirements planning (MRP) 4.      The method used to maintain the cash balance in the petty cash account. 5.      Just-in-time (JIT) inventory system 6.      The time to reorder inventory based on the quantity on hand falling to predetermined level. 7.      purchase requisition 8.     A document used to authorize a reduction in accounts payable when merchandise is returned to a supplier. 9.      imprest fund 10.  An inventory control system that triggers production based upon actual sales. 11.   purchase order 12.  An inventory control system that triggers production based on forecasted sales. 13.  kickbacks 14.  A document only used internally to initiate the purchase of materials, supplies, or services. 15.   procurement card 16.  A process for approving supplier invoices based on a two-way match of the receiving report and purchase order. 17.    blanket purchase order 18.   A process for approving supplier invoices based on a three-way match of the purchase order, receiving report, and supplier invoice. 19.  evaluated receipts settlement (ERS) 20. A method of maintaining accounts payable in which each supplier invoice is tracked and paid for separately. 21.  disbursement voucher 22. A method of maintaining accounts payable which generates one check to pay for a set of invoices from the same supplier. 23.  receiving report 24. Combination of a purchase order, receiving report, and supplier invoice that all relate to the same transaction. 25.   debit memo 26. A document used to list each invoice being paid by a check. 27.  vendor managed inventory 28. An inventory control system that seeks to minimize the sum of ordering, carrying, and stockout costs. 29.  voucher package 30. A system whereby suppliers are granted access to point-of-sale (POS) and inventory data in order to automatically replenish inventory levels. 31.  non-voucher system 32. An agreement to purchase set quantities at specified intervals from a specific supplier. 33. voucher system 34. A document used to record the quantities and condition of items delivered by a supplier.
	1.       A special purpose credit card used to purchase supplies.
	1.       A fraud in which a supplier pays a buyer or purchasing agent in order to sell its products or services.

13.3     Excel Project. Using Benford’s Law to Detect Potential Disbursements Fraud.

1.       a.      Read the article “Using Spreadsheets and Benford’s Law to Test Accounting Data,” by Mark G. Simkin in the ISACA Journal, Vol. 1, 2010, available at www.isaca.org.

2.      b.      Follow the steps in the article to analyze the following set of supplier invoices:

13.4     Match threats in the first column to appropriate control procedures in the second column. More than one control may be applicable.

Threat	Control Procedure
1.       Failing to take available purchase discounts for prompt payment. 2.      Only accept deliveries for which an approved purchase order exists. A.     Recording and posting errors in accounts payable. B.     Document all transfers of inventory.                         i.            Paying for items not received. C.     Restrict physical access to inventory.                         i.             Kickbacks. D.    File invoices by due date.                         i.            Theft of inventory. E.     Maintain a cash budget.                         i.            Paying the same invoice twice. F.     Automated comparison of total change in cash to total changes in accounts payable.                         i.            Stockouts. G.    Adopt a perpetual inventory system.                         i.             Purchasing items at inflated prices. H.    Require purchasing agents to disclose financial or personal interests in suppliers.                         i.            Misappropriation of cash. I.       Require purchases to be made only from approved suppliers.                         i.             Purchasing goods of inferior quality. J.      Restrict access to the supplier master data.                         i.            Wasted time and cost of returning unordered merchandise to suppliers. K.     Restrict access to blank checks.                         i.            Accidental loss of purchasing data. L.     Only issue checks for a complete voucher package (receiving report, supplier invoice, and purchase order).                         i.            Disclosure of sensitive supplier information (e.g., banking data). M.   Cancel or mark “Paid” all supporting documents in a voucher package when a check is issued.
	1.       Regular backup of the expenditure cycle database.
	1.       Train employees how to respond properly to gifts or incentives offered by suppliers.
	1.       Hold purchasing managers responsible for costs of scrap and rework.
	1.       Reconciliation of bank account by someone other than the cashier.

13.5     Use Table 13-2 to create a questionnaire checklist that can be used to evaluate controls for each of the basic activities in the expenditure cycle (ordering goods, receiving, approving supplier invoices, and cash disbursements).

1.       a.      For each control issue, write a Yes/No question such that a “No” answer represents a control weakness. For example, one question might be “Are supporting documents, such as purchase orders and receiving reports, marked “paid” when a check is issued to the vendor?”

b.    For each Yes/No question, write a brief explanation of why a “No” answer represents a control weakness.

13.6               EXCEL Project

a.   Expand the cash budget you created in Problem 12.4 to include a row for expected cash outflows equal to 77% of the current month’s sales.

b.   Also add a row to calculate the amount of cash that needs to be borrowed, in order to maintain a minimum cash balance of $50,000 at the end of each month.

c.   Add another row to show the cash inflow from borrowing.

d.   Add another row to show the cumulative amount borrowed.

e.   Add another row to show the amount of the loan that can be repaid, being sure to maintain a minimum ending balance of $50,000 each month.

13.7          For each of the following activities, identify the data that must be entered by the employee performing that activity and list the appropriate data entry controls:

1.       a.      Purchasing agent generating a purchase order

1.       b.      Receiving clerk completing a receiving report

13.8     The following list identifies several important control features. For each control, (1) describe its purpose and (2) explain how it could be best implemented in an integrated ERP system.

a.   Cancellation of the voucher package by the cashier after signing the check.

b.   Separation of duties of approving invoices for payment and signing checks.

c.   Prenumbering and periodically accounting for all purchase orders.

d.   Periodic physical count of inventory.

e.   Requiring two signatures on checks for large amounts.

f.    Requiring that a copy of the receiving report be routed through the inventory stores department prior to going to accounts payable.

g.   Requiring a regular reconciliation of the bank account by someone other than the person responsible for writing checks.

h.   Maintaining an approved supplier list and checking that all purchase orders are issued only to suppliers on that list

.

13.9     For good internal control, which of the following duties can be performed by the same individual?

1.       Approve purchase orders

2.      Negotiate terms with suppliers

3.      Reconcile the organization’s bank account

4.      Approve supplier invoices for payment

5.      Cancel supporting documents in the voucher package

6.      Sign checks

7.      Mail checks

8.     Request inventory to be purchased

9.      Inspect quantity and quality of inventory received

13.10   Last year the Diamond Manufacturing Company purchased over $10 million worth of office equipment under its “special ordering” system, with individual orders ranging from $5,000 to $30,000. Special orders are for low-volume items that have been included in a department manager’s budget. The budget, which limits the types and dollar amounts of office equipment a department head can requisition, is approved at the beginning of the year by the board of directors. The special ordering system functions as follows:

Purchasing     A purchase requisition form is prepared and sent to the purchasing department. Upon receiving a purchase requisition, one of the five purchasing agents (buyers) verifies that the requester is indeed a department head. The buyer next selects the appropriate supplier by searching the various catalogs on file. The buyer then phones the supplier, requests a price quote, and places a verbal order. A prenumbered purchase order is processed, with the original sent to the supplier and copies to the department head, receiving, and accounts payable. One copy is also filed in the open-requisition file. When the receiving department verbally informs the buyer that the item has been received, the purchase order is transferred from the open to the filled file. Once a month, the buyer reviews the unfilled file to follow up on open orders.

Receiving        The receiving department gets a copy of each purchase order. When equipment is received, that copy of the purchase order is stamped with the date and, if applicable, any differences between the quantity ordered and the quantity received are noted in red ink. The receiving clerk then forwards the stamped purchase order and equipment to the requisitioning department head and verbally notifies the purchasing department that the goods were received.

Accounts Payable      Upon receipt of a purchase order, the accounts payable clerk files it in the open purchase order file. When a vendor invoice is received, it is matched with the applicable purchase order, and a payable is created by debiting the requisitioning department’s equipment account. Unpaid invoices are filed by due date. On the due date, a check is prepared and forwarded to the treasurer for signature. The invoice and purchase order are then filed by purchase order number in the paid invoice file.

Treasurer       Checks received daily from the accounts payable department are sorted into two groups: those over and those under $10,000. Checks for less than $10,000 are machine signed. The cashier maintains the check signature machine’s key and signature plate and monitors its use. Both the cashier and the treasurer sign all checks over $10,000.

a.   Describe the weaknesses relating to purchases and payments of “special orders” by the Diamond Manufacturing Company.

b.   Recommend control procedures that must be added to overcome weaknesses identified in part a.

c.   Describe how the control procedures you recommended in part b should be modified if Diamond reengineered its expenditure cycle activities to make maximum use of current IT (e.g., EDI, EFT, bar-code scanning, and electronic forms in place of paper documents).                   (CPA Examination, adapted)

13.11   The ABC Company performs its expenditure cycle activities using its integrated ERP system as follows:

§  Employees in any department can enter purchase requests for items they note as being either out of stock or in small quantity.

§  The company maintains a perpetual inventory system.

§  Each day, employees in the purchasing department process all purchase requests from the prior day. To the extent possible, requests for items available from the same supplier are combined into one larger purchase order in order to obtain volume discounts. Purchasing agents use the Internet to compare prices in order to select suppliers. If an Internet search discovers a potential new supplier, the purchasing agent enters the relevant information in the system, thereby adding the supplier to the approved supplier list. Purchase orders above $10,000 must be approved by the purchasing department manager. EDI is used to transmit purchase orders to most suppliers, but paper purchase orders are printed and mailed to suppliers who are not EDI capable.

§  Receiving department employees have read-only access to outstanding purchase orders. Usually, they check the system to verify existence of a purchase order prior to accepting delivery, but sometimes during rush periods they unload trucks and place the items in a corner of the warehouse where they sit until there is time to use the system to retrieve the relevant purchase order. In such cases, if no purchase order is found, the receiving employee contacts the supplier to arrange for the goods to be returned.

§  Receiving department employees compare the quantity delivered to the quantity indicated on the purchase order. Whenever a discrepancy is greater than 5%, the receiving employee sends an email to the purchasing department manager. The receiving employee uses an online terminal to enter the quantity received before moving the material to the inventory stores department.

§  Inventory is stored in a locked room. During normal business hours an inventory employee allows any employee wearing an identification badge to enter the storeroom and remove needed items. The inventory storeroom employee counts the quantity removed and enters that information in an online terminal located in the storeroom.

§  Occasionally, special items are ordered that are not regularly kept as part of inventory, from a specialty supplier who will not be used for any regular purchases. In these cases, an accounts payable clerk creates a one-time supplier record.

§  All supplier invoices (both regular and one-time) are routed to accounts payable for review and approval. The system is configured to perform an automatic 3-way match of the supplier invoice with the corresponding purchase order and receiving report.

§  Each Friday, approved supplier invoices that are due within the next week are routed to the treasurer’s department for payment. The cashier and treasurer are the only employees authorized to disburse funds, either by EFT or by printing a check. Checks are printed on dedicated printer located in the treasurer’s department, using special stock paper that is stored in a locked cabinet accessible only to the treasurer and cashier. The paper checks are sent to accounts payable to be mailed to suppliers.

§  Monthly, the treasurer reconciles the bank statements and investigates any discrepancies with recorded cash balances.

Identify weaknesses in ABC’s expenditure cycle procedures, explain the resulting problems, and suggest how to correct those problems.

13.12   Alden, Inc. has hired you to review its internal controls for the purchase, receipt, storage, and issuance of raw materials. You observed the following:

§  ·    Raw materials, which consist mainly of high-cost electronic components, are kept in a locked storeroom. Storeroom personnel include a supervisor and four clerks. All are well trained, competent, and adequately bonded. Raw materials are removed from the storeroom only upon written or oral authorization by a production supervisor.

§  ·    No perpetual inventory records are kept; hence, the storeroom clerks do not keep records for goods received or issued. To compensate, the storeroom clerks perform a physical inventory count each month.

§  ·    After the physical count, the storeroom supervisor matches the quantities on hand against a predetermined reorder level. If the count is below the reorder level, the supervisor enters the part number on a materials requisition list that is sent to the accounts payable clerk. The accounts payable clerk prepares a purchase order for each item on the list and mails it to the supplier from whom the part was last purchased.

§  ·    The storeroom clerks receive the ordered materials upon their arrival. The clerks count all items and verify that the counts agree with the quantities on the bill of lading. The bill of lading is then initialed, dated, and filed in the storeroom to serve as a receiving report.

a.   Describe the weaknesses that exist in Alden’s expenditure cycle.

b.   Suggest control procedures to overcome the weaknesses noted in part a.

c.     Discuss how those control procedures would be best implemented in an integrated ERP system using the latest developments in IT.

CHAPTER 14

THE PRODUCTION CYCLE

14.1.    When activity-based cost reports indicate that excess capacity exists, management should either find alternative revenue-enhancing uses for that capacity or eliminate it through downsizing.  What factors influence management’s decision?  What are the likely behavioral side effects of each choice?  What implications do those side effects have for the long-run usefulness of activity-based cost systems?

14.2.      Why should accountants participate in product design?  What insights about costs can accountants contribute that differ from the perspectives of purchasing managers and engineers?

14.3.      Some companies have eliminated the collection and reporting of detailed analyses on direct labor costs broken down by various activities.  Instead, first-line supervisors are responsible for controlling the total costs of direct labor.  The justification for this argument is that labor costs represent only a small fraction of the total costs of producing a product and are not worth the time and effort to trace to individual activities.  Do you agree or disagree with this argument?  Why?     

14.4.      Typically, McDonald’s produces menu items in advance of customer orders based on anticipated demand.  In contrast, Burger King produces menu items only in response to customer orders.  Which system (MRP-II or lean manufacturing) does each company use?  What are the relative advantages and disadvantages of each system?

14.5     Some companies have switched from a “management by exception” philosophy to a “continuous improvement” viewpoint.  The change is subtle, but significant.  Continuous improvement focuses on comparing actual performance to the ideal (i.e., perfection).  Consequently, all variances are negative (how can you do better than perfect?).  The largest variances indicate the areas with the greatest amount of “waste,” and, correspondingly, the greatest opportunity for improving the bottom line.  What are the advantages and disadvantages of this practice?

14.1.    Match the terms in the left column with their definitions from the right column:

1 Bill of materials	a.  A factor that causes costs to change.
2.  Operations list	b.  A measure of the number of good units produced in a period of time.
3.  Master Production Schedule	c.  A list of the raw materials used to create a finished product.
4.  Lean manufacturing	d.  A document used to authorize removal of raw materials from inventory.
5.  Production order	e.  A cost-accounting method that assigns costs to products based on specific processes performed.
6.  Materials requisition	f.  A cost accounting method that assigns costs to specific batches or production runs and is used when the product or service consists of uniquely identifiable items.
7.  Move ticket	g.  A cost accounting method that assigns costs to each step or work center and then calculates the average cost for all products that passed through that step or work center.
8.  Job-time ticket	h.  A document that records labor costs associated with manufacturing a product.
9.  Job-order costing	i.   A document that tracks the transfer of inventory from one work center to another.
10.  Cost driver	j.  A document that authorizes the manufacture of a finished good.
11.  Throughput	k.  A document that lists the steps required to manufacture a finished good.
12.  Computer-integrated manufacturing	l.  A document that specifies how much of a finished good is to be produced during a specific time period.
	m.  A production planning technique that is an extension of the just-in-time inventory control method.
	n.  A production planning technique that is an extension of the Materials Requirement Planning inventory control method.
	o.  A term used to refer to the use of robots and other IT techniques as part of the production process.

14.2     What internal control procedure(s) would best prevent or detect the following problems?

a.   A production order was initiated for a product that was already overstocked in the company’s warehouse.

b.   A production employee stole items of work-in-process inventory.

c.   The “rush-order” tag on a partially completed production job became detached from the materials and lost, resulting in a costly delay.

d.   A production employee entered a materials requisition form into the system in order to steal $300 worth of parts from the raw materials storeroom.

e.   A production worker entering job-time data on an online terminal mistakenly entered 3,000 instead of 300 in the “quantity-completed” field.

f.    A production worker entering job-time data on an online terminal mistakenly posted the completion of operation 562 to production order 7569 instead of production order 7596.

g.   A parts storeroom clerk issued parts in quantities 10% lower than those indicated on several materials requisitions and stole the excess quantities.

h.   A production manager stole several expensive machines and covered up the loss by submitting a form to the accounting department indicating that the missing machines were obsolete and should be written off as worthless.

i.    The quantity-on-hand balance for a key component shows a negative balance.

j.    A factory supervisor accessed the operations list file and inflated the standards for work completed in his department.  Consequently, future performance reports show favorable budget variances for that department.

k.  A factory supervisor wrote off a robotic assembly machine as being sold for salvage, but actually sold the machine and pocketed the proceeds.

l.    Overproduction of a slow-moving product resulted in excessive inventory that had to eventually be marked down and sold at a loss.

14.3     Use Table 14-1 to create a questionnaire checklist that can be used to evaluate controls for each of the basic activities in the production cycle (product design, planning and scheduling, production operations, and cost accounting).

a.   For each control issue, write a Yes/No question such that a “No” answer represents a control weakness.

b.   For each Yes/No question, write a brief explanation of why a “No” answer represents a control weakness.

14.4     You have recently been hired as the controller for a small manufacturing firm that makes high-definition televisions.  One of your first tasks is to develop a report measuring throughput.

Describe the data required to measure throughput and the most efficient and accurate method of collecting that data.

14.5     The Joseph Brant Manufacturing Company makes athletic footwear.  Processing of production orders is as follows: At the end of each week, the production planning department prepares a master production schedule (MPS) that lists which shoe styles and quantities are to be produced during the next week.  A production order preparation program accesses the MPS and the operations list (stored on a permanent disk file) to prepare a production order for each shoe style that is to be manufactured.  Each new production order is added to the open production order master file stored on disk.

Each day, parts department clerks review the open production orders and the MPS to determine which materials need to be released to production.  All materials are bar-coded.  Factory workers work individually at specially designed U-shaped work areas equipped with several machines to assist them in completely making a pair of shoes.  Factory workers scan the bar-codes as they use materials.  To operate a machine, the factory workers swipe their ID badge through a reader.  This results in the system automatically collecting data identifying who produced each pair of shoes and how much time it took to make them.

Once a pair of shoes is finished, it is placed in a box.  The last machine in each work cell prints a bar-code label that the worker affixes to the box.  The completed shoes are then sent to the warehouse.

14.6     The XYZ company’s current production processes have a scrap rate of 15% and a return rate of 3%.  Scrap costs (wasted materials) are $12 per unit; warranty/repair costs average $60 per unit returned.  The company is considering the following alternatives to improve its production processes:

§  Option A: Invest $400,000 in new equipment.  The new process will also require an additional $1.50 of raw materials per unit produced.  This option is predicted to reduce both scrap rates return rates by 40% from current levels.

§  Option B: Invest $50,000 in new equipment, but spend an additional $3.20 on higher quality raw materials per unit produced.  This option is predicted to reduce both scrap and return rates by 90% from current levels.

§  Option C: Invest $2,000,000 in new equipment.  The new process will require no change in raw materials.  This option is predicted to reduce both scrap and return rates by 50% from current levels.

1.       Assume that current production levels of 1,000,000 units will continue.  Which option do you recommend?  Why?

1.       Assume that because all of the proposed changes will increase product quality, that production will jump to 1,500,000 units.  Which option do you recommend?  Why?

14.7     Excel Problem

1.       Create the following spreadsheet

1.       Create formulas to calculate

§  Accumulated depreciation (all assets use the straight line method; all assets acquired any time during the year get a full year’s initial depreciation)

§  Current year’s depreciation (straight-line method, full amount for initial year in which  asset acquired)

§  Ending accumulated depreciation

§  Net book value at end of period

§  Current year in the cell to the right of the phrase “Depreciation schedule for year”

§  Column totals for acquisition cost, beginning depreciation, current depreciation, ending accumulated depreciation, net book value

§  In the cell to the right of the arrow following the text “Cross-footing test” create a formula that checks whether the sum of the net book value column equals the sum of acquisition costs minus the sum of ending accumulated depreciation.  If the two values match, the formula should display the text “Okay” otherwise it should display the text “Error”

c.   Create a table at the bottom of your worksheet that consists of two columns:

§  Asset name (values should be chair, desk, laptop, monitor, software, and workstation)

§  Net book value (create a formula to calculate this number) assuming that the current date is 06/30/2010

§  Create a formula that sums the total net book values for all classes of assets

§  In the cell to the right of the total net book values for all asset classes, create a formula that compares the total net book values for all classes of assets to the sum of all net book values in the top portion of the spreadsheet.  The formula should return “Okay” if the two totals match or “Error: Sum of net book values by asset class does not equal sum of all net book values” if the two totals do not equal one another.

1.       Enter your name in row 1 in the cell to the right of the text “Name”

14.8     Excel Problem

Task: Use Excel and the Solver add-in to explore the effect of various resource constraints on the optimal product mix.

a.   Read the article “Boost Profits With Excel,” by James A.  Weisel in the December 2003 issue of the Journal of Accountancy (available online at the AICPA’s Web site, www.aicpa.org

b.   Download the sample spreadsheet discussed in the article and print out the screenshots showing that you used the Solver tool as discussed in the article.

c.   Rerun the Solver program to determine the effect of the following actions on income (print out the results of each option):

§  double market share limitations for all three products

14.9     EXCEL PROBLEM

Create the spreadsheet shown in Figure 14-11.  Write formulas to calculate the total depreciation expense and to display the correct values in the following three columns: Age, Depreciation Rate, and Depreciation Expense.  (Hint: You will need to use the VLOOKUP and MATCH functions to do this.  You may also want to read the article “Double-Teaming In Excel,” by Judith K.  Welch, Lois S.  Mahoney, and Daniel R.  Brickner, in the November 2005 issue of the Journal of Accountancy, from which this problem was adapted).

CHAPTER 15

THE HUMAN RESOURCES MANAGEMENT/PAYROLL CYCLE

15.1      This chapter noted many of the benefits that can arise by integrating the HRM and payroll databases. Nevertheless, many companies maintain separate payroll and HRM information systems. Why do you think this is so? (Hint: Think about the differences in employee background and the functions performed by the HRM and payroll departments.)

15.2        Some accountants have advocated that a company’s human assets be measured and included directly in the financial statements. For example, the costs of hiring and training an employee would be recorded as an asset that is amortized over the employee’s expected term of service. Do you agree or disagree? Why?

15.3        You are responsible for implementing a new employee performance measurement system that will provide factory supervisors with detailed information about each of their employees on a weekly basis. In conversation with some of these supervisors, you are surprised to learn they do not believe these reports will be useful. They explain that they can already obtain all the information they need to manage their employees simply by observing the shop floor. Comment on that opinion.

How could formal reports supplement and enhance what the supervisors learn by direct observation?

15.4      One of the threats associated with having employees telecommute is that they may use company-provided resources (e.g., laptop, printer, etc.) for a side business. What are some other threats?

What controls can mitigate the risk of these threats?

15.5         How would you respond to the treasurer of a small charity who tells you that the organization does not use a separate checking account for payroll because the benefits are not worth the extra monthly service fee?

15.6        This chapter discussed how the HR department should have responsibility for updating the HRM/payroll database for hiring, firing, and promotions. What other kinds of changes may need to be made?

What controls should be implemented to ensure the accuracy and validity of such changes?

15.1      Match the terms in the left column with the appropriate definition from the right column.

1. Payroll service bureau	a. A list of each employee’s gross pay, payroll deductions, and net pay in a multicolumn format.
2. Payroll clearing account	b. Used to record the activities performed by a salaried professional for various clients.
3. Earnings statement	c. Used to record time worked by an hourly-wage employee.
4. Payroll register	d. An organization that processes payroll and provides other HRM services.
5. Time card	e. An organization that processes payroll.
6. Time sheet	f. A list of all the deductions for each employee.
	g. A document given to each employee that shows gross pay, net pay, and itemizes all deductions both for the current pay period and for the year-to-date.
	h. Special general ledger account used for payroll processing.

15.2     What internal control procedure(s) would be most effective in preventing the following errors or fraudulent acts?

a.   An inadvertent data entry error caused an employee’s wage rate to be overstated in the payroll master file.

b.   A fictitious employee payroll record was added to the payroll master file.

c.   During data entry, the hours worked on an employee’s time card for one day were accidentally entered as 80 hours, instead of 8 hours.

d.   A computer operator used an online terminal to increase her own salary.

e.   A factory supervisor failed to notify the HRM department that an employee had been fired. Consequently, paychecks continued to be issued for that employee. The supervisor pocketed and cashed those paychecks.

f.    A factory employee punched a friend’s time card in at 1:00 p.m. and out at 5:00 p.m. while the friend played golf that afternoon.

g.   A programmer obtained the payroll master file and increased his salary.

h.   Some time cards were lost during payroll preparation; consequently, when paychecks were distributed, several employees complained about not being paid.

               

i.    A large portion of the payroll master file was destroyed when the disk pack containing the file was used as a scratch file for another application.

j.    The organization was fined $5000 for making a late quarterly payroll tax payment to the IRS.

15.3     You have been hired to evaluate the payroll system for the Skip-Rope Manufacturing Company. The company processes its payroll in-house. Use Table 15-1 as a reference to prepare a list of questions to evaluate Skip-Rope’s internal control structure as it pertains to payroll processing for its factory employees. Each question should be phrased so that it can be answered with either a yes or a no; all no answers should indicate potential internal control weaknesses. Include a third column listing the potential problem that could arise if that particular control were not in place.                                                              

15.4     Although most medium and large companies have implemented sophisticated payroll and HRM systems like the one described in this chapter, many smaller companies still maintain separate payroll and HRM systems that employ many manual procedures. Typical of such small companies is the Kowal Manufacturing Company, which employs about 50 production workers and has the following payroll procedures:

§  ·     The factory supervisor interviews and hires all job applicants. The new employee prepares a W-4 form (Employee’s Withholding Exemption Certificate) and gives it to the supervisor. The supervisor writes the hourly rate of pay for the new employee in the corner of the W-4 form and then gives the form to the payroll clerk as notice that a new worker has been hired. The supervisor verbally advises the payroll department of any subsequent pay raises.

§  ·     A supply of blank time cards is kept in a box near the entrance to the factory. All workers take a time card on Monday morning and fill in their names. During the week they record the time they arrive and leave work by punching their time cards in the time clock located near the main entrance to the factory. At the end of the week the workers drop the time cards in a box near the exit. A payroll clerk retrieves the completed time cards from the box on Monday morning. Employees are automatically removed from the payroll master file when they fail to turn in a time card.

§  ·     The payroll checks are manually signed by the chief accountant and then given to the factory supervisor, who distributes them to the employees. The factory supervisor arranges for delivery of the paychecks to any employee who is absent on payday.

§  ·     The payroll bank account is reconciled by the chief accountant, who also prepares the various quarterly and annual tax reports.

a.   Identify weaknesses in current procedures, and explain the threats that they may allow to occur.

b.   Suggest ways to improve the Kowal Manufacturing Company’s internal controls over hiring and payroll processing.                     

15.5     Arlington Industries manufactures and sells engine parts for large industrial equipment. The company employs over 1,000 workers for three shifts, and most employees work overtime when necessary. Figure 15-10 depicts the procedures followed to process payroll. Additional information about payroll procedures follows:

§  ·     The HRM department determines the wage rates of all employees. The process begins when a form authorizing the addition of a new employee to the payroll master file is sent to the payroll coordinator for review and approval. Once the information about the new employee is entered in the system, the computer automatically calculates the overtime and shift differential rates for that employee.

§  ·     A local accounting firm provides Arlington with monthly payroll tax updates, which are used to modify the tax rates.

§  ·     Employees record time worked on time cards. Every Monday morning the previous week’s time cards are collected from a bin next to the time clock, and new time cards are left for employees to use. The payroll department manager reviews the time cards to ensure that hours are correctly totaled; the system automatically determines if overtime has been worked or a shift differential is required.

§  ·     The payroll department manager performs all the other activities depicted in Figure 15-10.

§  ·     The system automatically assigns a sequential number to each payroll check. The checks are stored in a box next to the printer for easy access. After the checks are printed, the payroll department manager uses an automatic check-signing machine to sign the checks. The signature plate is kept locked in a safe. After the checks have been signed, the payroll manager distributes the paychecks to all first-shift employees. Paychecks for the other two shifts are given to the shift supervisor for distribution.

§  ·     The payroll master file is backed up weekly, after payroll processing is finished.

a.   Identify and describe at least three weaknesses in Arlington Industries’ payroll process.

b.    Identify and describe at least two different areas in Arlington’s payroll processing system where controls are satisfactory.  

15.6     Excel Problem

Objective: Learn how to find and correct errors in complex spreadsheets used for payroll.

a.   Read the article “Ferret Out Spreadsheet Errors” by Mark G. Simkin, in the Journal of Accountancy (February 2004). You can find a copy online by accessing www.aicpa.org.

b.   Download the worksheet referenced in the article.

c.   Enter the following erroneous data in the worksheet you downloaded in step b:

§  Change hours worked for Adams to 400,

§  Change hours worked for Englert to 4, and

§  Change hours worked for Hartford to –40.

Create a chart like that shown in Exhibit 2 of the article. Which of the errors are easily found by the chart? What are the strengths and limitations of creating such charts to detect errors? Print out your chart and save your work.

d.   Create the three data validation rules described in the article (Exhibits 4–7 illustrate how to create the first rule). Print out screen shots of how you create each rule, and save your work. (Note: The article “Block That Spreadsheet Error” by Theo Callahan, in the Journal of Accountancy (August 2002) provides additional examples of data validation rules.)

e.   Follow the instructions for using the formula auditing tool. Print out a screen shot showing use of the tool to circle invalid data (yours should be similar to Exhibit 9 in the article).

f.    Follow the instructions to run the “trace precedents” audit tool. Print screen shots that show the results, and save your work. How useful is this tool? What are its limitations, if any?

g.   Enter the following data for new employees (inserting new rows in proper order to maintain alphabetical listing of employees):

§  ·     Name = Able, payrate = 11.11, regular hours = 40, overtime hours = 5

§  ·     Name = Easton, payrate = 10.00, regular hours = 40, overtime hours = 0

§  ·     Name = Johnson, payrate = 12.00, regular hours = 35, overtime hours = 10

Which audit tests and validation rules change? Why? Print screen shots, and save your work.

15.7     Excel Problem

Objective: Learn how to use the VLOOKUP function for payroll calculations.

a.   Read the article “Make Excel a Little Smarter” by Lois S. Mahoney and Charles Kelliher in the Journal of Accountancy (July 2003). You can find a copy at www.aicpa.org.

b.   Read the section titled “Data in Different Places” and create the spreadsheet illustrated in Exhibit 6. Print a screen shot of your work, and save your spreadsheet.

c.   Create a formula that calculates total bonuses. Also create a cell entry that indicates what that number represents. Print a screen shot of your work, and save it.

d.   Add the following data validation controls to your spreadsheet, including explanatory error messages. Save your work.

§  ·     Sales must be positive.

§  ·     Sales cannot exceed 125.

§  ·     Amount of bonus must be nonnegative.

§  ·     Amount of bonus cannot exceed 20% of unit sales.

e.   Modify your worksheet by placing the sales data and resulting bonus on a different worksheet from the bonus table. Name your table array, and modify the VLOOKUP function accordingly. Then add another employee: Johnson, who sold 150 units. Print a screen shot of your new worksheet showing the bonuses for each employee, including Johnson. Save your work.

15.8      The local community feels that secondary school education is a necessity in our society and that lack of education leads to a number of social problems. As a result, the local school board has decided to take action to reverse the rising dropout rate. The board has voted to provide funds to encourage students to remain in school and earn their high school diplomas. The idea is to treat secondary education like a job and pay students. The board, however, could not agree on the details for implementing this new plan. Consequently, you have been hired to devise a system to compensate students for staying in school and earning a diploma.

As you devise your compensation scheme, be sure it meets the following general control objectives for the payroll cycle:

§  ·     All personnel and payroll transactions are properly authorized.

§  ·     All employees are assigned to do productive work, and they do it efficiently and effectively.

§  ·     All transactions are accurately recorded and processed.

§  ·     Accurate records are maintained.

§  ·     All disbursements are proper.

Write a proposal that addresses these five questions:

a.   How should the students be compensated (e.g., for attendance, grades)?

b.   How and by whom will the payments be authorized?

c.   How will the payments be processed?

d.   How should the payments be made (e.g., in cash or other means)?

e.   When will the payments be made?

15.9     What is the purpose of each of the following control procedures (i.e., what threats is it designed to mitigate)?

a.   Compare a listing of current and former employees to the payroll register.

b.   Reconciliation of labor costs (based on job-time ticket data) with payroll (based on time card data).

c.   Direct deposit of paychecks.

d.   Validity checks on Social Security numbers of all new employees added to the payroll master file.

e.   Cross-footing the payroll register.

f.    Limit checks on hours worked for each time card.

g.   Use of a fingerprint scanner in order for employees to record the time they started and the time they quit working each day.

h.   Encryption of payroll data both when it is electronically sent to a payroll service bureau and while at rest in the HR/payroll database.

i.    Establishing a separate payroll checking account and funding it as an imprest account.

j.    Comparison of hash totals of employee numbers created prior to transmitting time-worked data to payroll provider with hash totals of employee numbers created by payroll provider when preparing paychecks.

k.   Periodic reports of all changes to payroll database sent to each department manager.

l.    Providing employees with earnings statements every pay period.

15.10   Excel Problem

Objective: Learn how to use text and array formulas to locate potential payroll problems.

a.   Download the spreadsheet for this problem from the course Web site.

b.   In column I, under the label “Ghost Employee?” write a function that compares the employee# in the timecards column to the employee# in the payroll master data column and displays the message: “Timecard employee# does not exist in master data” for any employee in the timecards columns who is not listed in the payroll master data columns. The function should leave the cell blank if the employee# in the timecards worksheet does exist in the payroll master file worksheet. (Hint: Use the ISNA and MATCH functions.)

c.     In column L, titled “Invalid SSN?” write a function to identify invalid Social Security numbers. Assume that Social Security numbers that begin with the digit 9 or that have the digits 00 for the middle two numbers are invalid. Your function should display a message that flags either of these two conditions or which displays nothing otherwise. (Hint: there are text functions that examine specific portions of a string, such as the left 3 characters, and there are also functions that convert text to numeric values.)

d.    In column P, titled “Missing Paycheck?” write a function to check whether a timecard exists for each employee in the master payroll data section of the worksheet. The formula should return either the message “No paycheck created for this employee” or display nothing.

CASE 15-1 Research Report: HRM/Payroll Opportunities for CPAs

Payroll has traditionally been an accounting function and some CPAs have provided payroll processing services to their clients. Today, CPAs are finding additional new lucrative opportunities to provide not only payroll processing but also various HR services. Write a brief report that compares the provision of payroll and HR services by CPAs with that of national payroll providers. Perform the following research to collect the data for your report:

1.       1.      Read the articles “Be an HR Resource for Your Clients,” by Michael Hayes and “Hired Help: Finding the Right Consultant,” by Joanne Sammer, both of which were published in the November, 2006 issue of the Journal of Accountancy.

2.      2.      Contact a local CPA firm that provides payroll and HR services and find out what types of services they perform and what types of clients they serve.

                                                                                                                

CHAPTER 16

GENERAL LEDGER AND REPORTING SYSTEM

16.1     Although XBRL facilitates the electronic exchange of financial information, some external users do not think it goes far enough. They would like access to the entire general ledger, not just to XBRL-tagged financial reports that summarize general ledger accounts. Should companies provide external users with such access? Why or why not?

16.2     How can responsibility accounting and flexible budgets improve morale?

16.3     Why is the audit trail an important control?

16.4     The balanced scorecard measures organizational performance along four dimensions. Is it possible that measures on the customer, internal operations, and innovation and learning dimensions could be improving without any positive change in the financial dimension? If so, what are the implications of such a pattern?

16.5     Do you think that mandatory standards should be developed for the design of graphs of financial data that are included in annual reports and other periodic communications to investors? Why or why not?

16.1     Match the term in the left column with its appropriate definition from the right column:

1. journal voucher file	a. an individual financial statement item
2. instance document	b. evaluating performance based on controllable costs
3. XBRL element	c. evaluating performance by computing standards in light of actual activity levels
4. Balanced Scorecard	d. the set of journal entries that updated the general ledger
5. XBRL extension taxonomy	e. a set of files that defines XBRL elements and specifies the relationships among them
6. audit trail	f. a multi-dimensional performance report
7. XBRL taxonomy	g. a file that defines relationships among XBRL elements
8. XBRL linkbase	h. a file that defines the attributes of XBRL elements
9. XBRL schema	i.  a detective control that can be used to trace changes in general ledger account balances back to source documents
10. XBRL style sheet	j.  a file that explains how to display an XBRL instance document
11. responsibility accounting	k.  a file that contains specific data values for a set of XBRL elements for a specific time period or point in time
12. flexible budget	l.  a file containing a set of customized tags to define new XBRL elements that are unique to a specific organization

16.2     Which control procedure would be most effective in addressing the following problems?

a.   When entering a journal entry to record issuance of new debt, the treasurer inadvertently transposes two digits in the debit amount.

b.   The spreadsheet used to calculate accruals had an error in a formula. As a result, the controller’s adjusting entry was for the wrong amount.

c.   The controller forgot to make an adjusting entry to record depreciation.

d.   A sales manager tipped off friends that the company’s financial results, to be released tomorrow, were unexpectedly good.

e.   The general ledger master file is stored on disk. For some reason, the disk is no longer readable. It takes the accounting department a week to reenter the past month’s transactions from source documents in order to create a new general ledger master file.

f.    The controller sent a spreadsheet containing a preliminary draft of the income statement to the CFO by e-mail. An investor intercepted the e-mail and used the information to sell his stock in the company before news of the disappointing results became public.

g.   A company’s XBRL business report was incorrect because the controller selected the wrong element from the taxonomy.

h.   Instead of a zero, the letter o was entered when typing in data values in an XBRL instance document.

16.3     Explain the components of an audit trail for verifying changes to accounts payable. Your answer should specify how those components can be used to verify the accuracy, completeness, and validity of all purchases, purchase returns, purchase discounts, debit memos, and cash disbursements.

16.4     As manager of a local pizza parlor, you want to develop a balanced scorecard so you can more effectively monitor the restaurant’s performance.

a.   Propose at least two goals for each dimension, and explain why those goals are important to the overall success of the pizza parlor. One goal should be purely performance-oriented and the other should be risk-related.

b.   Suggest specific measures for each goal developed in part a.

c.   Explain how to gather the data needed for each measure developed in part b.

16.5     Use Table 16-1 to create a questionnaire checklist that can be used to evaluate controls in the general ledger and reporting cycle.

1.       a.      For each control issue, write a Yes/No question such that a “No” answer represents a control weakness. For example, one question might be “Is access to the general ledger restricted?”

1.       b.      For each Yes/No question, write a brief explanation of why a “No” answer represents a control weakness.

16.6     Visit the SEC website (www.sec.gov) and explore what is available in terms of interactive data (the SEC’s term for XBRL reports). Use the SEC’s viewer software and examine the annual reports for two companies.

16.7     Obtain the annual report of a company assigned by your professor. Read the management discussion and analysis section, and develop a balanced scorecard that reflects that company’s vision, mission, and strategy. Create both performance-oriented and risk-based goals and measures for each section of the balanced scorecard.

16.8     Excel Problem   Objective: Practice graph design principles.

Use the data in Table 16-3 to create the following graphs:

1.       a.      Sales

1.       b.      Sales and Gross Margin

1.       c.       Earnings per share

1.       d.      Which principles of graph design, if any, did you have to manually implement to over-ride the default graphs created by Excel?

16.9     Excel Problem   Objective: Create pivot tables for what-if analysis

.

Read the article “Make Excel an Instant Know-It-All” by Roberta Ann Jones in the March 2004 issue of the Journal of Accountancy. (Available at www.aicpa.org)

).

a.   Follow the instructions in the article to create a spreadsheet with pivot tables.

c.   Assume that Brown and David are in sales group 1 and the other three salespeople are in sales group 2. Print out a report that shows monthly sales for each group.

16.10   Excel Problem   Objective: How to do what-if analysis with graphs.

a.   Read the article “Tweaking the Numbers,” by Theo Callahan in the June 2001 issue of the Journal of Accountancy (either the print edition, likely available at your school’s library, or access the Journal of Accountancy archives at www.aicpa.org). Follow the instructions in the article to create a spreadsheet with graphs that do what-if analysis.

1.       b.      Now create a spreadsheet to do graphical what-if analysis for the “cash gap.” Cash gap represents the number of days between when a company has to pay its suppliers and when it gets paid by its customers. Thus, Cash gap = Inventory days on hand + Receivables collection period – Accounts payable period.

The purpose of your spreadsheet is to display visually what happens to cash gap when you “tweak” policies concerning inventory, receivables, and payables. Thus, you will create a spreadsheet that looks like Figure 16-11.

1.       c.       Set the three spin buttons to have the following values:

CHAPTER 17

DATA MODELING AND DATABASE DESIGN

17.1        Why is it not necessary to model activities such as entering information about customers or suppliers, mailing invoices to customers, and recording invoices received from suppliers as events in an REA diagram?

17.2        The basic REA template includes links between two events and links between events and resources and between events and agents. Why do you think the basic REA template does not include direct links between (a) two resources, (b) two agents, or (c) between resources and agents?

17.3     How can REA diagrams help an auditor understand a client’s business processes?

17.4        Which parts of Figure 17-6 would accurately depict almost every organization’s revenue cycle? Which parts would change?

17.5        What is the relationship between the things that would be represented as resources in an REA diagram and the different categories of assets found on an organization’s balance sheet?  (Hint: Are there any assets that would not be modeled as resources? Are there any resources in an REA diagram that are not listed as assets on a balance sheet?)

17.6        How would accounts payable be reflected in an REA diagram?  Why?

17.7        What are the five stages of the database design process?  In which stages should accountants participate?  Why?

17.8        What is the difference between an Entity-Relationship (E-R) diagram and an REA diagram?

17.1        Joe’s is a small ice-cream shop located near the local university’s baseball field. Joe’s serves walk-in customers only. The shop carries 26 flavors of ice cream. Customers can buy cones, sundaes, or shakes. When a customer pays for an individual purchase, a sales transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sales transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe’s maintains several banking accounts but deposits all sales receipts into its main checking account.

                Draw an REA Diagram, complete with cardinalities, for Joe’s revenue cycle,.

17.2        Joe, the owner of the ice-cream shop, purchases ice cream from two vendors. Over the years, he has developed good relationships with both vendors so that they allow Joe to pay them biweekly for all purchases made during the preceding two-week period. Joe calls in ice-cream orders on Mondays and Thursdays. The orders are delivered the next day. Joe buys ice-cream toppings from one of several local stores and pays for each such purchase at the time of sale with a check from the company’s main checking account.

                Draw an REA Diagram, complete with cardinalities, for Joe’s expenditure cycle.

17.3        Sue’s Gallery sells original paintings by local artists. All sales occur in the store. Sometimes customers purchase more than one painting. Individual customers must pay for purchases in full at the time of sale. Corporate customers, such as hotels, however, may pay in installments if they purchase more than 10 paintings. Although Sue’s Gallery has several bank accounts, all sales monies are deposited intact into the main checking account.

                Draw an REA Diagram for the  gallery’s revenue cycle.  Be sure to include cardinalities.

17.4        Sue’s Gallery only purchases finished paintings (it never commissions artists). It pays each artist 50% of the agreed price at the time of purchase, and the remainder after the painting is sold. All purchases are paid by check from Sue’s main checking account.

                Draw an REA Diagram, complete with cardinalities, of the gallery’s expenditure cycle.

17.5        Develop a data model of Fred’s Train Shop’s expenditure cycle activities related to the acquisition of office equipment and other fixed assets. Fred sometimes orders multiple pieces of equipment. Vendors usually ship the entire order, but sometimes are out of stock of some items. In such cases, they immediately ship to Fred what they have in stock, and then send a second shipment when they obtain the other items. Conversely, several orders placed within a short time period with the same vendor might be filled with one delivery. Assume that Fred makes installment payments for most fixed-asset acquisitions, but occasionally pays for some equipment in full at the time of purchase.

                Draw an REA Diagram of your data model.  Be sure to include cardinalities.

17.6        Provide an example (in terms of companies with which you are familiar) for each of the business situations described by the following relationship cardinalities:

17.7        Model the cardinalities of the following business policies:

1.       a.      The relationship between the Sale and Receive Cash events for installment sales.

2.      b.      The relationship between the Sale and Receive Cash events at a convenience store.

c.     The Take Customer Order–Sale relationship in a situation when occasionally several shipments are required to fill an order because some items were out of stock.

1.       d.       The Sale-Inventory relationship for a custom homebuilder.

2.      e.       The relationship between the Sale and Receive Cash events for Dell computers, which requires customers to pay the entire amount of their purchase in advance, prior to Dell shipping the merchandise.

3.      f.          The relationship between the Sale and Receive Cash events for a retail store that has some in-store sales paid in full by customers at the time of the sale but that also makes some in-store sales to customers on credit, billing them later and permitting them to make installment payments.

4.      g.        The relationship between the Receive Inventory and Disburse Cash events in the case where suppliers require payment in advance, in full.

5.      h.       The relationship between the Call on Customers event (i.e., the visit by a salesperson to a potential customer) and the Take Customer Order event for a business that is only conducted door-to-door (e.g., kitchen knives, certain books, etc.) so that the only way to order the items is when a salesperson visits the customer.                                   (Hint: do you think every call results in an order?)

i.     The relationship between the Call on Customers and Take Customer Orders events for a manufacturer which also accepts orders on its Web site.

1.       j.         The relationship between the Receive Inventory and Disburse Cash events for a company which receives monthly bills from its suppliers for all purchases made the previous month; some suppliers require payment of the entire bill, in full, within 30 days or they will not accept any subsequent orders, but other suppliers accept installment payments.

17.8                The Computer Warehouse sells computer hardware, software, and supplies (such as paper). Individual customers just walk into the store, select merchandise, and must pay for their purchases in full before leaving the store. Corporate customers, however, call in orders in advance, so that the items are waiting to be picked up. Corporate customers may charge their purchases to their account. The Computer Warehouse mails corporate customers monthly statements that summarize all purchases made the prior month. Corporate customers pay the entire balance, as listed on the monthly statement, with one check or EFT transaction.

                Draw an REA Diagram for Computer Warehouse revenue cycle, complete with cardinalities.

17.9        The Computer Warehouse purchases its inventory from more than a dozen different vendors. Orders are placed via telephone, fax, or on the supplier’s Web site. Most orders are delivered the next day. Most orders are filled completely in one shipment, but sometimes a supplier is out of stock of a particular item. In such situations, the bulk of the order is shipped immediately and the out-of-stock item is shipped separately as soon as it arrives (such shipments of back orders are never combined with any new orders placed by the Computer Warehouse). The Computer Warehouse pays for some of its purchases COD but usually pays by the 10th of the month for all purchases made the prior month. None of its suppliers allows it to make installment payments.

                Draw an REA Diagram for Computer Warehouse expenditure cycle, complete with cardinalities

17.10     Stan’s Southern Barbeque Supply Store orders mass-produced barbecue products from various suppliers. Stan’s maintains information about a contact person at each supplier along with all required address information. Each purchase order has the order number, date, tax, and total. Purchase orders also contain the following information for each product ordered: stock number, description, and price. The manager of Stan’s places orders by fax several times a day, whenever he notices that an item is running low. Some suppliers fill each individual order separately. Others, however, consolidate orders and fill all of them in one weekly delivery. Stan's suppliers never make partial shipments; if they are out of stock of a certain item, they wait until they obtain that item and then ship the entire order. Some suppliers require payment at the time of delivery, but others send Stan’s a monthly statement detailing all purchases during the current period. Two suppliers allow Stan’s to make installment payments for any individual purchase orders that exceed $20,000.

                Draw an REA Diagram for Stan’s Southern Barbecue expenditure cycle, complete with cardinalities

17.1        Sparky’s Amusement Park is an entertainment park run by recent college graduates. It caters to young people and others who are young at heart. The owners are very interested in applying what they have learned in their information systems and marketing classes to operate a park better than any other in the area. To accomplish these goals, guests of the park are given a personal “membership card” as they enter. This card will be used to identify each guest. Assume that a new card is issued each time a guest comes to the park. As a result, the system does not have to track one person over a period of time. As at other parks, guests pay a flat fee for the day and then are able to ride all of the attractions (such as a double-looping roller coaster and the merry-go-round) for no extra charge. The owners, however, want to track the rides each guest takes and the attractions the guests use. They plan to have guests swipe their membership card through a computerized card reader, which automatically enters information into the computer system. This should allow the owners to gather data about the following:

§  Number of people who use each piece of equipment. (How many people rode the Ferris wheel today?)

§  Number of times each piece of equipment is operated daily.

§  Times of day the attraction is busy or slow. (When was the carousel the busiest?)

§  Number of attractions each guest uses. (How many different pieces of equipment did customer 1122 ride?)

§  Number of rides each guest enjoys. (How many different rides did customer 1122 enjoy? Did each guest go on any rides more than once?)

Draw an REA diagram for Sparky’s revenue cycle only. Be sure to include cardinalities. State any assumptions you had to make.

CHAPTER 18

IMPLEMENTING AN REA MODEL IN A RELATIONAL DATABASE

18.1        How would the process of generating a cash disbursements journal from the REA data model presented in Figure 18-4 and Table 18-1 differ from the process for creating a sales journal?

18.2        Why take the time to develop separate REA diagrams for each business cycle if the ultimate objective is to combine them into one integrated enterprise-wide data model? Why not just focus on the integrated model from the start?

16

17

18

18.3        Building separate tables for every relationship (1:1, 1:N, and M:N) does not violate any of the rules for building a well-structured database. Why then do you think that REA data modelers recommend building separate tables only for M:N relationships and using foreign keys to implement 1:1 and 1:N relationships?

18.4        Assume that there exists a 1:1 relationship between the Receive Inventory and Disburse Cash events. How does the manner in which the relationship between the two events is implemented (i.e., in which table a foreign key is placed) affect the process used to record payments made to suppliers?

18.5        Refer to Figure 18-4 and Table 18-1. How would you determine the amount of cash that Fred’s Train Shop has at any point in time?

18.6        Why does Figure 18-4 show only one cash disbursement entity if Fred’s Train Shop uses a general operating checking account for purchases of inventory, supplies, and operating expenses such as rent but also uses a separate checking account for payroll?

18.7        Examine Figure 18-4 and Table 18-1. Why do the Inventory, Customers, and Suppliers tables all have an attribute that contains data about the balance at the beginning of the current fiscal period?

18.1        Refer to Problems 17.1 and 17.2 for information about the revenue and expenditure cycle activities for Joe’s ice-cream shop in order to draw an integrated REA diagram of both cycles.

18.2        Develop a set of tables to implement the integrated REA diagram you developed in Problem 18.1 for Joe’s ice-cream shop in a relational database. Specify a primary key for each table, and suggest at least one other attribute that should be included in each table.

18.3            Refer to Problems 17.3 and 17.4 for information about Sue’s Gallery’s revenue and expenditure cycle activities in order to draw an integrated REA diagram of both cycles.

18.4            Develop a set of tables to implement the integrated REA diagram you developed in Problem 18.3 for Sue’s Gallery in a relational database. Specify a primary key for each table, and suggest at least one other attribute that should be included in each table.

18.5            The following tables and attributes exist in a relational database:

Draw an REA diagram for this database. State any additional assumptions you need to make about cardinalities.

18.6        Refer to Problems 17.8 and 17.9 for information about the revenue and expenditure cycles for the Computer Warehouse and use that information to draw an integrated REA diagram for both cycles.

18.7        Develop a set of tables to implement the integrated REA diagram you developed in Problem 17.6 for the Computer Warehouse in a relational database. Specify a primary key for each table, and suggest at least one other attribute that should be included in each table.

18.8        Explain how to calculate the total amount of Accounts Payable:

18.9        Refer to Figure 18.4 and Table 18-1 to write the query logic needed to answer the following questions. (Optional: If requested by your instructor, write your queries in SQL or a Query-By-Example graphical interface.) Some answers may require more than one query—try to write the most efficient queries possible.

1.       a.       Accounts payable for all suppliers in Arizona

b.    Total amount of sales to a customer named Smith

c.     Total wage expense

d.    Total wages payable

e.    Net increase (decrease) in quantity-on-hand for a particular inventory item

f.     The proportion of sales made to walk-in customers (i.e., no order)

g.    The salesperson who made the largest amount of sales in October

h.    The salesperson who made the most sales in October

i.     The most popular item, in terms of total units sold

8.10        Refer to Problem 17.10 and develop a set of tables to implement the REA diagram you developed for Stan’s Southern Barbeque Supply Store. Identify the primary and foreign keys for each table, and don’t forget to address any M:N relationships.

CHAPTER 19

SPECIAL TOPICS IN REA MODELING

19.1     Often it takes several sales calls to obtain the first order from a new customer. Why then does Figure 19-1 depict the relationship between the Call on Customer and Take Customer Order events as being 1:1?

19.2     How could an automobile dealer model the use of loaner cars, which it gives to customers for free whenever they drop off a vehicle for maintenance that will take longer than one day to complete?

19.3     In what situations would you expect to model a relationship between an agent and a resource?

19.4     Why is depreciation not represented as an event in the REA data model?

19.5     How would you model the acquisition of a digital asset, such as the purchase of software online (the software is downloaded and then installed on the purchaser’s computer)?

19.6     How are the similarities and differences between the purchase of services, such as telephone service, and the purchase of raw materials reflected in an REA data model?

19.7     How would you modify the expenditure cycle REA diagram in Figure 19-4 to include the return of defective products to suppliers for credit?

19.1     We-Fix-Computers, Inc. provides spare parts and service for a wide variety of computers. Customers may purchase parts to take home for do-it-yourself repairs, or they may bring their systems in for repair, in which case they pay for both the parts and the labor associated with the type of service required. Some services do not include any new parts, just a labor charge for that service. Individual customers must pay for all parts purchases in full at the time of sale. Individual customers must pay 50% down when they bring their computers in for servicing and pay the balance at pickup. Corporate customers, however, are billed monthly for all sales (parts or service). Although We-Fix-Computers, Inc. has several different banking accounts, all sales are deposited intact into its main checking account.

We-Fix-Computers, Inc. purchases its inventory of parts from more than a dozen different vendors. Orders are usually delivered the next day; sometimes, however, suppliers ship only partial orders. We-Fix-Computers pays for some of its purchases COD, but usually pays by the 10th of the month for all purchases made the prior month. None of its suppliers allows it to make installment payments.

Required

Draw an integrated REA diagram for We-Fix-Computers’ revenue and expenditure cycles.

19.2     The Mesa Veterinary Hospital is run by Dr. Brigitte Roosevelt. She has two employees in the office and has asked you to develop a database to help better track her data. Dr. Roosevelt currently uses her personal computer only for word processing, but she is interested in also using it to maintain pet histories and accounting information. She is excited about the transition and is counting on you to help her through the process. She describes her daily activities as follows:

When new customers come to Mesa Veterinary Hospital, the “owners” of the pets are required to complete an introductory form. This form includes the following:

• Owner name

• Address

• Day phone

• Night phone

They are also required to provide the following information about each pet, as some people own many pets:

• Pet name

• Breed

• Color

• Birth date

Dr. Roosevelt would like to enter this information once and then have the system retrieve it for all subsequent visits.

When customers call to make appointments, one of the office clerks asks what kind of services they require (e.g., is it a routine exam, a surgery, etc.). Dr. Roosevelt sees only one pet during each 

appointment. If she is going to see one owner’s two pets, then two separate appointments are necessary (but scheduled back-to-back). For each appointment, Dr. Roosevelt records the pet’s weight, notes the reason for the appointment, and records her diagnosis. Depending on the diagnosis, the doctor will possibly prescribe any number of medications to cure the pet.

Owners are charged $25 for each appointment and must pay additionally for any medications prescribed for their pets. Dr. Roosevelt requires all pets to be brought back for another examination prior to refilling any prescriptions. Customers must pay for services and medication in full at the conclusion of their visits.

You also learn that Dr. Roosevelt orders drugs and medications from several different suppliers. She places orders weekly, on Fridays. Suppliers usually make one shipment to fill each order, but sometimes have to make additional shipments if they are currently out of stock of one or more items. In such cases, they always ship the back-ordered item as soon as they receive it from the manufacturer; they never combine such back orders with subsequent orders by Dr. Roosevelt.

Suppliers bill Dr. Roosevelt monthly and expect payment in full by the 15th of the following month. A few suppliers do permit Dr. Roosevelt to make installment payments. The prices charged by suppliers for a given product may change several times during the year, so it is important to accurately store the cost of each item each time it is purchased.

Dr. Roosevelt concludes the interview by requesting that in addition to the facts mentioned,

she wants the system to store the following attributes:

• Number of pets owned by each customer

• Total charge for the appointment

• Prescription price

• Drug name

• Length of appointment

• Diagnosis

• Date of appointment

• Service requested

REQUIRED

a. Given this brief overview, draw an integrated REA diagram for the Mesa Veterinary Hospital and include cardinalities.

b. As directed by your instructor, either draw the tables necessary to implement the integrated REA diagram you developed for the Mesa Veterinary Hospital or build the tables in a relational DBMS to which you have access. Be sure to include all attributes from the narrative plus the additional ones explicitly listed by Dr. Roosevelt at the conclusion of the interview. Create additional attributes only if necessary.

19.3     Your university hires you to implement a database system for the library network. You have interviewed several librarians, and the following summarizes these discussions:

• The library’s main goal is to provide students and professors with access to books and other publications. The library, therefore, maintains an extensive collection of materials that are available to anyone with a valid university identification card.

• The standard procedure for lending materials is that the student or faculty member comes to one of the three campus libraries and locates the book or journal on the shelves.

• Each book is assigned three unique numbers. First, the book is assigned a number by the publisher, called the International Standard Book Number (ISBN). This number allows the publishers to track each title and the number changes with each new edition. The second number is the Dewey decimal number, which is assigned to the title and written on the outside spine of the book. This number is used to organize the library shelves and is thus helpful to the students and faculty. It is therefore critical that this number be available to users on the online inquiry screens. The last number is a university book ID number. A different number is assigned to every book that is received so the library can track all copies of each book. This number is different from the other two numbers such that if the library has three copies of one book, each will have a unique university book ID number.

• When students or faculty check out books, the system must be able to track the specific copy that is being borrowed. Each book has a magnetic strip inserted in its spine, which is used as a security measure. If someone tries to take a book without checking it out, an alarm sounds.

• In general, students and faculty have equal clout in the library. Both are able to check

out most books and to check out several books at one time. No one is allowed to remove periodicals from any library. The length of time that the book may be borrowed varies, however, depending on who checks it out. Students are allowed to check out a book for several weeks; faculty may borrow books for several months.

• When patrons check out books, they take their materials to the circulation desk. At that time, the librarian scans in each item’s university book ID number and the borrower’s ID number. The system records a separate loan event for each book being checked out, assigning each a separate loan number. At this time, each book’s due date is calculated and marked on a slip located inside each book’s front cover. Simultaneously, the magnetic strip is deactivated so the book may be removed from the library.

• After borrowers check out a book, they are expected to return it by its due date. In reality, everyone is allowed 30 days after the due date recorded on the checkout slip before the book is officially overdue. At that point, the book must be returned, and the borrower is assessed a $10 fine. If the book is permanently lost, then the borrower is fined $75 for the book’s replacement. All fines must be paid in cash, in full. Students are not allowed to enroll for subsequent semesters until all library fines are paid; they also do not receive a diploma until all library fines are paid. Faculty must pay all outstanding fines by June 30 of each year.

• When a book is returned, the return must be entered into the system, and a unique return number is used to log the transaction. At that time, the loan record is updated to show that the book has been returned.

The following attributes have been identified as critical for the new system:

§  University book ID

§  Book publisher

§  Due date

§  Loan number

§  Checkout date

§  Borrower phone number

§  Cash account number

§  Librarian name

§  Book status (on the shelf or checked out)

§  Type of borrower (faculty or student)

§  Librarian college degree

§  Actual return date

§  Borrower ID

§  Borrower name

§  Book title

§  Fine receipt number

§  Amount received

§  Library name

§  Amount of fine

§  Default library where book is shelved

§  Borrower’s fine balance owed

§  ISBN number

§  Book return number

§  Dewey decimal number

§  Borrower address

§  Book copyright date

§  Borrower e-mail address

§  Library borrowed from

§  Librarian number

§  Account balance

§  Total number of books in a specific library

§  Loan status (still outstanding, or returned)

§  Author name

REQUIRED

1.       Draw an REA diagram for the library system. Remember to include cardinalities.

2.      As directed by your instructor, either create the tables on paper that would be required to implement your REA diagram or actually build those tables in a relational DBMS to which you have access. Only use the attributes listed, unless others are absolutely necessary.

19.4   The XYZ Company sells tools and parts to automotive repair shops. Shops call in orders; all orders received by noon are delivered the same day. Between 12:00 and 1:00, the system prints out schedules. From 1:00 to 5:00, drivers make deliveries according to the printed schedules. Typically, each driver makes between 25 and 30 deliveries each day. Each delivery is signed for by a repair shop manager; the portable laptop then uses wireless communications to transmit information about the delivery back to the XYZ Company and the information is recorded as another row in the sales event table. The XYZ Company uses its own trucks to make local deliveries to its customers. It wants to track information about the use of those trucks: which employee drove which truck, to which customers did a particular truck make deliveries, which deliveries are made on which days, what was the starting and stopping mileage each day?

REQUIRED

a. Draw a partial REA diagram of the XYZ Company’s revenue cycle to model these

events: Taking Customer Orders, Deliveries, and the Use of Vehicles. Be sure to

include cardinalities.

b. Create a set of tables (either on paper or in a relational DBMS to which you have

access) to implement the REA model you developed for the XYZ Company.

19.5   Assume that Stained Glass Artistry, a new shop that specializes in making stained glass artwork, has hired you to design an integrated database that will provide the owners with the accounting information they need to effectively manage the business. Stained Glass Artistry makes a wide variety of stained glass windows for sale in its store.

A unique job order is assigned to each production run, which includes creating multiple copies of the same basic design. When raw materials are issued to employees, the issuance is documented on a prenumbered raw material issue form. The different kinds of glass needed for the product, and other materials such as copper foil or lead, are issued at one time, so that employees can efficiently produce the design.

Creating a piece of stained glass art involves several different steps, including cutting, foiling, and soldering. The owners want to track how much time each employee spends each day performing each of those various tasks.

The owners have developed raw material and direct labor standards for each design they offer. They want their AIS to track actual costs and standard costs so that they can generate reports that provide price and quantity variance information.

The owners also have provided you with the following list of facts that they want

stored in the database. (Note: You must create appropriate primary keys for each table;

this is the list of other attributes.) Attributes in Standard Glass Artistry AIS:

§  Date hired

§  Time started task

§  Time completed task

§  Style of glass (name or description)

§  Quantity on hand

§  Color of glass

§  Quantity to be produced

§  Actual cost of design

§  Design name

§  Standard quantity of glass use in design

§  Quantity issued

§  Standard hours to make design

§  Standard cost of design

§  Date design produced

§  Date of birth

§  Wage rate

§  Employee name

§  Standard cost of glass

REQUIRED

a. Draw an integrated REA diagram for Stained Glass Artistry. Include both minimum

and maximum cardinalities.

b. Create the set of relational tables required to implement your REA diagram for Stained

Glass Artistry in a relational database.

19.6  Bernie’s Pet Store sells pet food, toys, and supplies. Bernie, the owner, is the only person who places orders with suppliers. He is also the only person who writes checks. Suppliers ship each order individually; if they are out of an item, they back order it and ship it separately as soon as it arrives. Bernie pays each supplier monthly for all purchases made the previous month. Suppliers do not allow him to make installment payments.

Bernie has eight employees, each of whom can check in materials received from suppliers and sell merchandise to customers. Bernie pays his employees weekly from a separate checking account used only for payroll purposes.

All sales are made in-store and are paid for immediately by cash, check, or credit card.

When employees are not working the cash register or checking in merchandise, they restock shelves and clean up the premises. Bernie does not want to track each individual restock or clean-up event, but does want to know how much time each employee spends each day doing those tasks. He also wants to track how much time each employee spends each day receiving inventory and how much time they spend working at the cash register.

He wants to be able to write queries that would show time spent by job task (restocking, cleaning, receiving, or sales) for each employee. It is not practical, however, to try to measure the time spent on individual tasks (e.g., Bernie does not want employees to track the time they start and finished unloading a shipment from supplier X, then repeat for supplier Y; similarly, he does not want to track how long it takes to ring up each individual customer at the cash register). All he wants is to know how much time each day (e.g., 3.75 hours) each employee spent performing each different type of job.

REQUIRED

Draw an integrated REA diagram for Bernie’s Pet Shop. Be sure to include both payroll processing and the ability to track how employees use their time.

19.7  At Big Time University (BTU) students are allowed to purchase two basketball tickets for each home game. Each ticket contains the date of the game, and the seat information, such as section, row, and individual seat number. Students pay for each game individually; that is, student sporting event passes are not used at BTU. BTU deposits the proceeds from each game into its bank.

REQUIRED

a. Prepare an REA diagram with cardinalities for the revenue cycle for BTU’s basketball games. State any assumptions you may have to make concerning BTU’s business policies and practices.

b. Implement your model in a set of relational tables. Be sure to specify primary keys, foreign keys, and identify at least one other attribute that should be included in each table.

19.8  Small contractors often rent special equipment for specific jobs. They need to track the equipment that is rented, when it is returned, and payments made to the rental company.

REQUIRED

1.       Draw a partial REA diagram for the acquisition, payment, and return of rental equipment. Be sure to include cardinalities and state any assumptions you made when specifying those cardinalities.

b. Create a set of tables (either on paper or in a relational DBMS to which you have

access) to implement the REA model you developed.

Case 19.1        This case involves creating a database from an integrated REA diagram and then using the REA diagram to guide the writing of queries to prepare financial statements.

Required

a. Create the tables necessary to implement Figure 19-9 in a relational database. Be sure to include primary keys and other relevant attributes in each table.

2.      Write the query, or set of queries, necessary to generate as many elements of financial statements as possible. For example, write the query or set of queries that would be used to calculate the amount of cash on hand, the total of accounts receivable, the total value of raw materials, inventory on hand, etc.

CHAPTER 20

INTRODUCTION TO SYSTEMS DEVELOPMENT; SYSTEMS ANALYSIS

20.1  The approach to long-range AIS planning described in this chapter is important for large organizations with extensive investments in computer facilities.  Should small organizations with far fewer information systems employees attempt to implement planning programs?  Why or why not?  Be prepared to defend your position to the class.

20.2  You are a consultant advising a firm on the design and implementation of a new system.  Management has decided to let several employees go after the system is implemented.  Some have many years of company service. 

How would you advise management to communicate this decision to the affected employees?  To the entire staff?

20.3  While reviewing a list of benefits from a computer vendor’s proposal, you note an item that reads, “Improvements in management decision making—$50,000 per year.”  How would you interpret this item?  What influence should it have on the economic feasibility and the computer acquisition decision?

20.4    For each of the following, discuss which data-gathering method(s) are most appropriate and why:

        

1.       Examining the adequacy of internal controls in the purchase requisition procedure -

2.      Identifying the controller’s information needs

3.      Determining how cash disbursement procedures are actually performed

d.      Surveying employees about the move to a total quality management program

e.      Investigating an increase in uncollectible accounts

20.5 The following problem situations occurred in a manufacturing firm.  What questions should you ask to understand the problem?

</para>Accounting sees an increase in the number and dollar value of bad debt write-offs

Operating margins have declined each of the past four years due to higher-than-expected production costs from idle time, overtime, and reworking products

20.6  Give some examples of systems analysis decisions that involve a trade-off between each of the following pairs of objectives:

1.       economy and usefulness

2.      economy and reliability

c.      economy and customer service

d.      simplicity and usefulness

e.      simplicity and reliability

f.       economy and capacity

g.      economy and flexibility

20.7  For years, Jerry Jingle’s dairy production facilities led the state in sales volume but recent declines worry him.  Customers are satisfied with his products but are troubled by the dairy’s late deliveries and incomplete orders.  Production employees (not the cows) are concerned about bottlenecks in milk pasteurization and homogenization due to poor job scheduling, mix-ups in customers’ orders, and improperly labeled products.  How should Jerry address the problems?  What data-gathering techniques would be helpful at this early stage?

20.8 A manufacturing firm needed a specialized software program to identify and monitor cost overruns.  After an extensive analysis, the company purchased prepackaged software and assigned three programmers to modify it to meet its individual circumstances and processes.  After six months of work, during final testing, the company told them to stop all work until further notice.  While reading the software vendor’s sales agreement, the manufacturing manager found a clause stating that the software could not be changed without the prior written consent of the vendor.  The firm had to pay the software vendor an additional fee so it could use the modified software in its manufacturing process.  Which aspect(s) of feasibility did the manufacturing firm failed to consider prior to purchasing the software.</para></problem>

20.9    Ajax Manufacturing installed a new bar code based inventory tracking system in its warehouse.  To close the books each month on a timely basis, the six people who work in the warehouse must scan each item in a 36-hour period while still performing their normal duties.  During certain months, when inventory expands to meet seasonal demands, the scan takes as many as 30 hours to complete.  In addition, the scanners do not accurately record some inventory items that require low operating temperatures.  A recent audit brought to management’s attention that the inventory records are not always accurate.  Which aspect(s) of feasibility did Ajax fail to consider prior to installing the inventory tracking system.</para></problem>

20.1  How do you get a grizzled veteran police officer who is used to filling out paper forms to use a computer to process his arrests and casework—especially when he has little or no experience using a computer? That was the problem facing the Chicago Police Department when it decided to implement a relational database system. The system is capable of churning through massive amounts of data to give officers the information they need to fight crime more effectively.

         Initially, the department rolled out the case component of the CLEAR (Citizen Law Enforcement Analysis and Reporting) system that provided criminal history and arrest records. The officers hated it, complaining that the system was not user-friendly, that approval from supervisors was complex and involved multiple screens, and that they did not feel properly trained on the system. After listening to the officers’ complaints for a year, the department clearly had to do something. (Adapted from Todd Datz, “No Small Change,” CIO (February 15, 2004): 66–72)

a.      Identify as many system analysis and design problems as you can.

1.       b.            What could the department have done differently to prevent the officers’ complaints?

1.       c.             What principles of system analysis and design were violated in this case?

20.2  Mary Smith is the bookkeeper for Dave’s Distributing Company, a distributor of soft drinks and juices. Because the company is rather small, Mary performs all daily accounting tasks herself. Dave, the owner of the company, supervises the warehouse/delivery and front office staff, but he also spends much of his time jogging and skiing.

         For several years, profits were good, and sales grew faster than industry averages. Although the accounting system was working well, bottlers were pressuring Dave to computerize. With a little guidance from a CPA friend and with no mention to Mary, Dave bought a new computer system and some accounting software. Only one day was required to set up the hardware, install the software, and convert the files. The morning the vendor installed the computer system, Mary’s job performance changed dramatically. Although the software company provided two full days of training, Mary resisted learning the new system. As a result, Dave decided she should run both the manual and computer systems for a month to verify the new system’s accuracy.

         Mary continually complained that she lacked the time and expertise to update both systems by herself. She also complained that she did not understand how to use the new computer system. To keep accounts up to date, Dave spent two to three hours a day running the new system himself. Dave found that much of the time spent running the system was devoted to identifying discrepancies between the computer and manual results. When the error was located, it was usually in the manual system. This significantly increased Dave’s confidence in the new system.

         At the end of the month, Dave was ready to scrap the manual system, but Mary said she was not ready. Dave went back to skiing and jogging, and Mary went on with the manual system. When the computer system fell behind, Dave again spent time catching it up. He also worked with Mary to try to help her understand how to operate the computer system.

         Months later, Dave was very frustrated because he was still keeping the computer system up to date and training Mary. He commented, “I’m sure Mary knows how to use the system, but she doesn’t seem to want to. I can do all the accounting work on the computer in two or three hours a day, but she can’t even do it in her normal eight-hour workday. What should I do?”

a.      What do you believe is the real cause of Mary’s resistance to computers?

                  b.   What events may have contributed to the new system’s failure?

c.      In retrospect, how should Dave have handled the accounting system computerization?

d.      At what point in the decision-making process should Mary have been informed?  Should she have had some say in whether the computer was purchased?  If so, what should have been the nature of her input?  If Mary had not agreed with Dave’s decision to acquire the computer, what should Dave have done?

e.      A hard decision must be made regarding Mary.  Significant efforts have been made to train her, but they have been unsuccessful.  What would you recommend at this point?  Should she be fired?  Threatened with the loss of her job?  Moved somewhere else in the business?  Given additional training?

20.3  Wright Company’s information system was developed in stages over the past five years. During the design process, department heads specified the information and reports they needed. By the time development began, new department heads were in place, and they requested additional reports. Reports were discontinued only when requested by a department head. Few reports were discontinued, and a large number are generated each period.

            Management, concerned about the number of reports produced, asked internal auditing to evaluate system effectiveness. They determined that more information was generated than could be used effectively and noted the following reactions:

§  Many departments did not act on reports during peak activity periods. They let them accumulate in the hope of catching up later.

§  Some had so many reports they did not act at all or misused the information.

§  Frequently, no action was taken until another manager needed a decision made. Department heads did not develop a priority system for acting on the information.

§  Department heads often developed information from alternative, independent sources. This was easier than searching the reports for the needed data.

a.      Explain whether each reaction is a functional or dysfunctional behavioral response. 

b.   Recommend procedures to eliminate dysfunctional behavior and prevent its recurrence.

20.4 The controller of Tim’s Travel (TT) is deciding between upgrading the company’s existing computer system or replacing it with a new one. Upgrading the four-year-old system will cost $97,500 and extend its useful life for another seven years. The book value is $19,500, although it would sell for $24,000. Upgrading will eliminate one employee at a salary of $19,400; the new computer will eliminate two employees. Additional annual operating costs are estimated at $15,950 per year. Upgrading is expected to increase profits 3.5% above last year’s level of $553,000.

The BetaTech Company has quoted a price of $224,800 for a new computer with a useful life of seven years. Annual operating costs are estimated to be $14,260. The average processing speed of the new computer is 12% faster than that of other systems in its price range, which would increase TT’s profits by 4.5%.

Tim’s present tax rate is 35%, and the cost of financing (minimum desired rate of return) is 11%. After seven years, the salvage value, net of tax, would be $12,000 for the new computer and $7,500 for the present system. For tax purposes, computers are depreciated over five full years  (six calendar years; a half year the first and last years), and the depreciation percentages are as follows:

Year    Percent (%)

1          20.00

2          32.00

3          19.20

4          11.52

5          11.52

6            5.76

<para>Using a spreadsheet package, prepare an economic feasibility analysis to determine if Tim’s Travel should rehabilitate the old system or purchase the new computer.  As part of the analysis, compute the after-tax cash flows for years 1 through 7 and the payback, NPV, and IRR of each alternative.

20.5.  Rossco is considering the purchase of a new computer with the following estimated costs: initial systems design, $54,000; hardware, $74,000; software, $35,000, one-time initial training, $11,000; system installation, $20,000; and file conversion, $12,000. A net reduction of three employees is expected, with average yearly salaries of $40,000. The system will decrease average yearly inventory by $150,000. Annual operating costs will be $30,000 per year.

         The expected life of the machine is four years, with an estimated salvage value of zero. The effective tax rate is 40%. All computer purchase costs will be depreciated using the straight-line method over its four-year life. Rossco can invest money made available from the reduction in inventory at its cost of capital of 11%. All cash flows, except for the initial investment and start-up costs, are at the end of the year. Assume 365 days in a year.

        

         Use a spreadsheet to perform a feasibility analysis to determine if Rossco should purchase the computer.  Compute the following as part of the analysis: initial investment, after-tax cash flows for years 1 through 4, payback period, net present value, and internal rate of return.

20.6  A recently completed feasibility study to upgrade XYZ’s computer system shows the following benefits. Compensation figures in parentheses include wages, benefits, and payroll taxes.

1.   Production

a.   Market forecasts, which take two $400 person-days a month, will be more accurate with software making the calculations.

b.   Effective inventory control will prevent part stockouts and reduce inventory by $1,000,000. XYZ’s cost of capital is 20%.

c.   Detailed evaluations of plan changes will increase production flexibility, reduce sales losses, and eliminate two clerks ($75,000 each).

2.   Engineering

a.   Computerized updating of bills of material and operations lists will save 40% of an engineer’s ($100,000) and 25% of a clerk’s ($60,000) time.

b.   Computerized calculations of labor allocations, rates, and bonus details will save 40% of a clerk’s ($80,000) time.

3.   Sales. Improved reporting will enable the five-person sales staff to react more quickly to the market, producing a $10,000 per person sales increase.

4.   Marketing. Revised reports and an improved forecasting system will increase net income by $50,000.

5.   Accounting

a.   Quickly determining new product costs will save 30% of the accountant’s ($100,000) time.

b.   An incentive earnings system will save 40% of the payroll clerk’s ($60,000) time.

         <para>As a board member, which of the benefits can you defend as relevant to the system’s cost justification?  Calculate how much XYZ will save with the new system.</para>

         Adapted from the SMAC Exam

20.7  The following list presents specific project activities and their scheduled starting and completion times:

        Activity         Starting Date      Ending Date

A                     Jan. 5               Feb. 9

B                      Jan. 5               Jan. 19

C                      Jan. 26                         Feb. 23

D                     Mar. 2              Mar. 23

E                      Mar. 2              Mar. 16

F                      Feb. 2              Mar. 16

G                     Mar. 30            Apr. 20

H                     Mar. 23            Apr. 27

a.      Using a format similar to that in <link linkend="ch18fig03" preference="0">Figure 18-3<xref linkend="ch18fig03" label="18-3"/></link>, prepare a Gantt chart for this project.  Assume that each activity starts on a Monday and ends on a Friday. 

b.      Assume today is February 16 and activities A and B have been completed, C is half completed, F is a quarter completed, and the other activities have not yet commenced.  Record this information on your Gantt chart.  Is the project behind schedule, on schedule, or ahead of schedule?  Explain. 

c.   Discuss the relative merits of the Gantt chart and PERT as project planning and control tools.

20.8  Recent years have brought an explosive growth in electronic communication. Laptops, netbooks, e-readers, personal digital assistants, sophisticated cell phones, fax machines, e-mail, teleconferencing, office productivity software, and sophisticated management information systems have changed the way information is received, processed, and transmitted. With the decreasing costs of computer equipment and the increasing power of automation, the full impact of computerization has yet to be felt. Although the development of computer applications is directed at being user friendly or user oriented, the integration of computers into the organization has had both positive and negative effects on employees.

a.      Describe the benefits companies and employees receive from electronic communications.

1.       c.             Discuss the organizational impact of introducing new electronic communication systems.

2.      d.            Explain

A.     Why an employee might resist the introduction of electronic communication systems

B.     The steps an organization can take to alleviate this resistance.

20.9  PWR manufactures precision nozzles for fire hoses. Ronald Paige, an engineer, started the corporation and it has experienced steady growth. Reporting to Ronald are six vice presidents representing marketing, production, research and development, information services, finance, and human resources. The information services department was established last year when PWR began developing a new information system consisting of a server connected to each employee’s personal computer. The PCs can download and upload data to the server. PWR is still designing and developing applications for its new system. Ronald received a letter from the external auditor and called a meeting with his vice presidents to review the recommendation that PWR form an information systems steering committee.

                                                                                         

a.      Explain why the auditors would recommend an information systems steering committee and discuss its specific responsibilities.  What advantages can the committee offer PWR?  What advantages can such a steering committee offer PWR?

b.      Identify the PWR managers most likely to serve on the committee.

20.10   Businesses often modify or replace their financial information system to keep pace with their growth and take advantage of improved IT. This requires a substantial time and resource commitment. When an organization changes its AIS, a systems analysis takes place.

a.   Explain the purpose and reasons for surveying an organization’s existing system.

b.   Explain the activities commonly performed during systems analysis.

c.   Systems analysis is often performed by a project team composed of a systems analyst, a management accountant, and other knowledgeable and helpful people.  What is the management accountant’s role in systems analysis?

20.11  Don Richardson, JEM Corporation’s vice president of marketing, is part of a management team that for several months has been discussing plans to develop a new line of business. Rumors about the major organizational changes that may be required to implement the strategic plan have been circulating for months.

         Several employees who are anxious about the expected changes confronted Don. The sales manager said, “It is imperative that we speak to you right away. The employees are very apprehensive about the proposed changes, and their job performance has slacked off.” The accounting manager added, “That’s right. My staff are asking me all sorts of questions about this new line of business, and I don’t have any answers for them. They’re not buying the ‘We will make an official announcement soon’ line any longer. I suspect that some of them are already looking for jobs in case the department changes phase out their positions.”

Implementing organizational change is one of the most demanding assignments an executive faces. It has been suggested that every change requires three steps: unfreezing the current situation, implementing the change, and refreezing the effected change. This view, however, lacks the specific details needed by an operating manager who must initiate the change.

1.       a.            Explain why employees resist organizational change.

b.      Discuss ways JEM Corporation can alleviate employee resistance to change.

20.12   Remnants, Inc., with headquarters in St. Louis, manufactures designer clothing. The company markets and services its products by region, with each functioning as a profit center. Each region has a manager, an accounting department, a human resources department, and several area offices to market and service the products. Each area office has sales, service, and administrative departments whose managers report to an area manager.

                The New York area office departed from the standard organizational structure by establishing a branch office to market and service the firm’s products in Boston. A branch manager who reports directly to the New York area manager heads the local office.

The Boston branch manager is encouraging the New York area manager to consider a new information system to handle the local branch’s growing information needs. The NewYork area manager and the eastern region manager want to establish a project team with employees from the region, area, and branch office. The team will assess the information needs at the Boston branch office and develop system recommendations. The following employees have been appointed to the project team, with Keith Nash as chairperson:

Eastern Region Office

Kurt Johnson, Budget Supervisor

Sally Brown, Training Director

New York Area Office

Keith Nash, Administrative Director

Boston Branch

Heidi Meyer, Branch and Sales Manager

Bobby Roos, Assistant Branch and Service Manager

Joe Gonzalez, Salesperson

Juana Martinez, Serviceperson

a.         P</inst>roject team members contribute their skills to help accomplish a given objective.  Characteristics of group members can influence the functioning and effectiveness of a project team.  Identify some of these characteristics.

b.   Due to the team’s composition, what sources of conflict can you see arising among its members?  Do you think the group will succeed in its objective to develop an information system for the Boston branch office?  Why or why not?

c.   What contribution would a person who holds a position as budget supervisor make in a project team such as this one?

20.13   Managers at some companies face an ongoing systems development crisis: IS departments develop systems that businesses cannot or will not use. At the heart of the problem is a “great divide” that separates the world of business and the world of IS. Few departments seem able or ready to cross this gap.

            One reason for the crisis is that many companies are looking for ways to improve existing, out-of-date systems or to build new ones. Another is the widespread use of PC-based systems that have spawned high user expectations that IS departments are not meeting. Users seek more powerful applications than are available on many older systems.

            The costs of the great divide can be devastating. An East Coast chemical company spent over $1 million on a budgeting and control system that was never used. The systems department’s expertise was technical excellence, not budgets. As a result, the new system completely missed the mark when it came to meeting business needs. A Midwestern bank used an expensive computer-aided software engineering (CASE) tool to develop a system that users ignored because there had been no design planning. A senior analyst for the bank said, “They built the system right; but unfortunately they didn’t build the right system.”

a.         What is the great divide in the systems development process?  What causes the gap?

            b.   What would you suggest to solve this great divide information crisis?  

c.   Discuss the role a systems designer, business manager, and end user can take to narrow the great divide.

      d.   Who plays the most vital role in the effective development of the system?

20.14   <para>Joanne Grey, a senior consultant, and David Young, a junior consultant, are conducting a systems analysis for a client to determine the feasibility of integrating and automating clerical functions. Joanne had previously worked for the client, but David was a recent hire.

The first morning on the job, Joanne directed David to interview a departmental supervisor and learn as much as possible about department operations. David introduced himself and said, “Your company has hired us to study how your department works so we can make recommendations on how to improve its efficiency and lower its cost. I would like to interview you to determine what goes on in your department.”

David questioned the supervisor for 30 minutes but found him to be uncooperative. David gave Joanne an oral report on how the interview went and what he learned about the department.

<para>Describe several flaws in David’s approach to obtaining information.  How should this task have been performed?</para></problem>

</para></problem>

SUGGESTED ANSWERS TO THE CASES

20-1  Audio Visual Corporation (AVC) manufactures and sells visual display equipment. Headquartered in Boston, it has seven sales offices with nearby warehouses that carry its inventory of new equipment and replacement parts. AVC has a departmentalized manufacturing plant with assembly, maintenance, engineering, scheduling, and cost accounting departments as well as several component parts departments.

When management decided to upgrade its AIS, they installed a mainframe at headquarters and local area networks at each sales office. The IS manager and four systems analysts were hired shortly before they integrated the new computer and the existing AIS. The other IS employees have been with the company for years.

During its early years, AVC had a centralized decision-making organization. Top management formulated all plans and directed all operations. As the company expanded, decision making was decentralized, although data processing was highly centralized. Departments coordinated their plans with the corporate office but had the freedom to develop their own sales programs. However, information problems developed, and the IS department was asked to improve the company’s information processing system once the new equipment was installed.

Before acquiring the new computer, the systems analysts studied the existing AIS, identified its weaknesses, and designed applications to solve them. In the 18 months since the new equipment was acquired, the following applications were redesigned or developed: payroll, production scheduling, financial statement preparation, customer billing, raw materials usage, and finished goods inventory. The departments affected by the changes were rarely consulted until the system was operational.

Recently the president stated, “The systems people are doing a good job, and I have complete confidence in their work. I talk to them frequently, and they have encountered no difficulties in doing their work. We paid a lot of money for the new equipment, and the systems people certainly cost enough, but the new equipment and new IS staff should solve all our problems.”

Two additional conversations regarding the new AIS took place.

BILL TAYLOR, IS MANAGER AND JERRY ADAMS, PLANT MANAGER

JERRY: Bill, you’re trying to run my plant for me. I’m the manager, and you keep interfering. I wish you would mind your own business.

BILL: You’ve got a job to do, and so do I. As we analyzed theinformation needed for production scheduling and by top management, we saw where we could improve the workflow. Now that the system is operational, you can’t reroute work and change procedures, because that would destroy the value of the information we’re processing. And while I’m on that subject, we can’t trust the information we’re getting from production. The documents we receive from production contain a lot of errors.

JERRY: I’m responsible for the efficient operation of production. I’m the best judge of production efficiency. The system you installed reduced my workforce and increased the workload of the remaining employees, but it hasn’t improved anything. In fact, it might explain the high error rate in the documents.

BILL: This new computer cost a lot of money, and I’m trying to make sure the company gets its money’s worth.

JERRY ADAMS, PLANT MANAGER AND TERRY WILLIAMS, HUMAN RESOURCES MANAGER

JERRY: My best production assistant, the one I’m grooming to be a supervisor, told me he was thinking of quitting.  When I asked why, he said he didn’t enjoy the work anymore. He’s not the only one who is unhappy. The supervisors and department heads no longer have a voice in establishing production schedules. This new computer system took away the contribution we made to company planning and direction. We’re going back to when top management made all the decisions. I have more production problems now than I ever had. It boils down to my management team’s lack of interest. I know the problem is in my area, but I thought you could help me.

TERRY: I have no recommendations, but I’ve had similar complaints from purchasing and shipping. We should explore your concerns during tomorrow’s plant management meeting.       Adapted from the CMA Examination

<para><emphasis role="strong">Evaluate the preceding information, and answer the following questions:</emphasis></para>

1.      Identify the problems the new computer system created </inst> and discuss what caused them.

2.      How could AVC have avoided the problems?  How can they prevent them in the future?

                                                                                                CHAPTER 21

                                                                                AIS DEVELOPMENT STRATEGIES

21.1    What is the accountant’s role in the computer acquisition process?  Should the accountant play an active role, or should all the work be left to computer experts?  In what aspects of computer acquisition might an accountant provide a useful contribution?

21.2  In a Midwest city of 45,000, a computer was purchased and in-house programmers began developing programs.  Four years later, only one incomplete and poorly functioning application had been developed, none of software met users’ minimum requirements, and the hardware and the software frequently failed.  Why do you think the city was unable to produce quality, workable software?  Would the city have been better off purchasing software?  Could the city have found software that met its needs?  Why or why not?

21.3    You are a systems consultant for Ernst, Price, and Deloitte, CPAs.  At your country club’s annual golf tournament, Frank Fender, an automobile dealer, describes a proposal from Turnkey Systems and asks for your opinion.  The system will handle inventories, receivables, payroll, accounts payable, and general ledger accounting.  Turnkey personnel would install the $40,000 system and train Fender’s employees.  Identify the major themes you would touch on in responding to Fender.  Identify the advantages and disadvantages of using a turnkey system to

21.4    Sara Jones owns a rapidly growing retail store that faces stiff competition due to poor customer service, late and error-prone billing, and inefficient inventory control.  To continue its growth, its AIS must be upgraded but Sara is not sure what it wants the AIS to accomplish.  Sara has heard about prototyping, but does not know what it is or whether it would help.  How would you explain prototyping to Sara?  Include an explanation of its advantages and disadvantages as well as when its use is appropriate.

21.5    Clint Grace has been business over 30 years and has definite ideas about how his ten retail stores should be run.  He is financially conservative and is reluctant to make expenditures that do not have a clear financial payoff.  Store profitability has declined sharply and customer dissatisfaction is high.  Store managers never know how much inventory is on hand and when purchases are needed until a shelf is empty.  Clint asks you to determine why profitability has declined and to recommend a solution.  You determine that the current AIS is inefficient and unreliable and that company processes and procedures are out of date.</para> <para>You believe the solution is to redesign the systems and business processes using BPM.  What are some challenges you might face in redesigning the system?  How will you present your recommendations Clint?</para></question></general-problem>

21.1    Don Otno has been researching software options but cannot decide among three alternatives. Don started his search at Computers Made Easy (CME) and almost wished he had looked no further. Steve Young, the manager of CME, appeared knowledgeable and listened attentively to Don’s problems, needs, and concerns. Steve had software and hardware that would, with a few exceptions, meet Don’s needs. Don could start using the system almost immediately. The system’s price was unexpectedly reasonable.

            After three hours at Custom Designed Software (CDS), Don left convinced that they could produce exactly what he needed. Cost and time estimates were not established, but CDS assured him that the cost would be reasonable and that the software would be complete in a few months.

            At Modified Software Unlimited (MSU), the owner said that customized software was very good but expensive and that canned software was inexpensive but rarely met more than a few needs. The best of both worlds could be achieved by having MSU modify the package that came closest to meeting Don’s needs.

            Don returned to CME and asked Steve about customized and modified software. Steve expressed enough concerns about both that Don came full circle—to thinking canned software was best. That night, Don realized he could not make an objective decision. He was swayed by whichever vendor he was talking with at the time. The next morning he called you for help.

a.        List the advantages and disadvantages of each vendor’s approach.

Advantages of canned (packaged software)

b.        Recommend a course of action for Don and support your decision.</para></listitem></orderedlist></problem>

21.2      A federal agency signed a 15-month contract for $445,158 for a human resources/payroll system. After 28 months and no usable software, the agency canceled the contract and withheld payment for poor performance. A negotiated settlement price of $970,000 was agreed on. The project experienced the following problems:

§  The contractor did not understand what software was desired. The RFP did not have fully developed user requirements or system specifications, and user requirements were never adequately defined and frozen. Changes delayed completion schedules and caused disagreements about whether new requirements were included in the original scope of work.

§  The contract did not specify systems requirements or performance criteria, and the terminology was vague. The contract was amended 13 times to add or delete requirements and to reimburse the contractor for the extra costs resulting from agency caused delays. The amendments increased the cost of the contract to $1,037,448.  

§  The contractor complained of inexcusable agency delays, such as taking too much time to review items submitted for approval. The agency blamed the delays on the poor quality of the documentation under review.

§  The agency did not require each separate development phase to be approved before work continued. When the agency rejected the general system design, the contractor had to scrap work already completed.

a.            What caused the problems? 

                        How could the agency have better managed the systems development project?

                        What could the contractor have done differently?

b.        Can we conclude from this case that organizations should not have custom software written for them?  Explain your answer.

21.3    Wong Engineering Corp (WEC) operates in 25 states and three countries. WEC faced a crucial decision: choosing network software that would maximize functionality, manageability, and end-user acceptance of the system. WEC developed and followed a four-step approach:

Step 1. Develop evaluation criteria. WEC organized a committee that interviewed users and developed the following evaluation criteria:

§  Ease of use

§  Scope of vendor support

§  Ease of network management and administration

§  Cost, speed, and performance

§  Ability to access other computing platforms

§  Security and control

§  Fault tolerance and recovery abilities

§  Ability to connect workstations to the network

§  Global naming services

§  Upgrade and enhancement options

§  Vendor stability

WEC organized the criteria into the following four categories and prioritized them. Criteria vital to short-term and long-term business goals were given a 5. “Wish list” criteria were weighted a 3. Inapplicable criteria were given a 1.

1.        Business criteria: overall business, economic, and competitive issues

2.        Operational criteria: tactical issues and operating characteristics

3.        Organizational criteria: networks’ impact on the information systems structure

4.        Technical criteria: hardware, software, and communications issues

Step 2. Define the operating environment. Several data-gathering techniques were used to collect information from which an information systems model was developed. The model revealed the need to share accounting, sales, marketing, and engineering data at three organizational levels: district, division, and home office. District offices needed access to centralized financial information to handle payroll. WEC needed a distributed network that allowed users throughout the organization to access company data.

Step 3. Identify operating alternatives. Using the criteria from step 1, committee members evaluated each package and then compared notes during a roundtable discussion.

Step 4. Test the software. The highest-scoring products were tested, and the product that fit the organization’s needs the best was selected. 

a.        Discuss the committee’s role in the selection process.  How should committee members be selected?  What are the pros and cons of using a committee to make the selection?

b.        What data-gathering techniques could WEC use to assess user needs?  To select a vendor? 

What data-gathering techniques could WEC use to select a vendor?

c.         What is the benefit of analyzing the operating environment before selecting the software? 

d.        In selecting a system using the point-scoring method, how should the committee resolve scoring disputes?  List at least two methods.

e.    Should a purchase decision be made on the point-scoring process alone?  What other procedure(s) should the committee employ in making the final selection?

21.4    Mark Mitton, the liaison to the IS department, has eliminated all but the best three systems. Mark developed a list of required features, carefully reviewed each system, talked to other users, and interviewed appropriate systems representatives. Mark used a point-scoring system to assign weights to each requirement. Mark developed Table 21-4 to help him select the best system.

            a.        Use a spreadsheet to develop a point-scoring matrix and determine which system Mark should select.

           

b.      </inst>Susan Shelton did not agree with Mark’s weightings and suggested the following changes:

</para><link linkend="informaltable0" preference="1" role="generated"/></listitem>

<informaltable id="informaltable0" frame="none" float="0"><tgroup cols="2" colsep="0" rowsep="0" align="left"><colspec colnum="1" colname="c01" colwidth="500"/><colspec colnum="2" colname="c02" colwidth="500"/><tbody><row><entry valign="top"><para>Flexibility</para></entry>	<entry valign="top"><para>60</para></entry></row>
<row><entry valign="top"><para>Reputation and reliability</para></entry>	<entry valign="top"><para>50</para></entry></row>
<row><entry valign="top"><para>Quality of support utilities</para></entry>	<entry valign="top"><para>10</para></entry></row>
<row><entry valign="top"><para>Graphics capability</para></entry>	<entry valign="top"><para>10</para></entry></row></tbody></tgroup></informaltable>

        

         When the changes are made, which vendor should Mark recommend?

c.         Mark’s manager suggested the following changes to Susan’s weightings:

</para><link linkend="informaltable1" preference="1" role="generated"/>

<informaltable id="informaltable1" frame="none" float="0"><tgroup cols="2" colsep="0" rowsep="0" align="left"><colspec colnum="1" colname="c01" colwidth="500"/><colspec colnum="2" colname="c02" colwidth="500"/><tbody><row><entry valign="top"><para>Reputation and reliability</para></entry>	<entry valign="top"><para>90</para></entry></row>
<row><entry valign="top"><para>Installation assistance</para></entry>	<entry valign="top"><para>40</para></entry></row>
<row><entry valign="top"><para>Experience with similar systems</para></entry>	<entry valign="top"><para>40</para></entry></row>
<row><entry valign="top"><para>Training assistance</para></entry>	<entry valign="top"><para>65</para></entry></row>
<row><entry valign="top"><para>Internal memory size</para></entry>	<entry valign="top"><para>10</para></entry></row></tbody></tgroup></informaltable>

<para>Will the manager’s changes affect the decision about which system to buy?

</para></listitem>

d.        What can you conclude about point scoring from the changes made by Susan and Mark’s manager?  Develop your own weighting scale to evaluate the software packages.  What other selection criteria would you use?  Be prepared to discuss your results with the class.

e.        What are the weaknesses of the point-scoring method?

21.5  Nielsen Marketing Research (NMR), with operations in 29 countries, produces and disseminates marketing information. Nielsen has been the primary supplier of decision support information for more than 70 years. NMR’s most recognizable product is the Nielsen television ratings. Nielsen is one of the largest users of computer capacity in the UnitedStates. Its information system consistently ranks above average in efficiency for its industry. NMR hired IBM to evaluate outsourcing its information processing. NMR wanted to know whether outsourcing would allow it to concentrate on giving its customers value-added services and insights, increase its flexibility, promote rapid growth, and provide it with more real-time information.

         What are the benefits and risks of outsourcing for NMR? 

         Do the benefits outweigh the risks?  Explain your answer.

21.6    A large organization had 18 months to replace its old customer information system with a new one that could differentiate among customer levels and provide appropriate products and services on demand. The new system, which cost $1 million and was installed by the IS staff on time, did not work properly. Complex transactions were error-prone, some transactions were canceled and others were put on hold, and the system could not differentiate among customers. The system was finally shut down, and transactions were processed manually. New IS management was hired to build a new system and mend the strained relationship between operations and IS.

            So what went wrong? IS couldn’t—or wouldn’t—say no to all the requests for systems enhancements. Eager to please top management, IS management ignored the facts and assured them they could build a scalable system that was on time and on budget. Another big mistake was a strict project schedule with little flexibility to deal with problems and unforeseen challenges. Developers never spoke up about any glitches they encountered along the way. More than a dozen people (including the CIO) lost their jobs because of their roles in this disaster.

            a.        What could IS management have done differently to make this project successful?

1.       b.            What in-house development issues are demonstrated in this case?

1.       c.             How could the in-house issues have been addressed to prevent the system’s failure?

21.7    Meredith Corporation publishes books and magazines, owns and operates television stations, and has a real estate marketing and franchising service. Meredith has 11 different systems that do not communicate with each other. Management wants an executive information system that provides them with the correct and timely information they need to make good business decisions. Meredith has decided to use prototyping to develop the system.

            a.        Identify three questions you would ask Meredith personnel to determine systems requirements.  What information are you attempting to elicit from each question?

b.        Explain how prototyping works.  What would the system developer do during the iterative process step?  Why would you want the fewest iterations possible?</para></listitem>

c.         Would you want the prototype to be operational or nonoperational?  Why?  If it were an operational prototype, what would have to happen?  If it were a nonoperational prototype, how would the prototype be used?

d.        Suppose the company decides the prototype system is not practical, abandons it, and takes some other approach to solving its information problem.  Does that mean prototyping is not a valid systems development approach?  Explain your answer.

21.8        Norcom, a division of a large manufacturer, needed a new distribution and customer service system. The project was estimated to take 18 months and cost $5 million. The project team consisted of 20 business and IT staff members. After two years, the CIO was fired, and the company hired a CIO with expertise in saving troubled projects. The new CIO said three grave errors were committed.

1.       1.      IT picked the wrong software using a very naïve request for proposal process.

2.      2.      IT did not formulate a project plan.

3.      3.      No one “owned” the project. The IT staff assumed the users owned the project, the users believed the IT staff owned it, and management believed the vendor owned it.

The CIO developed a 2,000-line plan to rescue the project. Three months later, the system failed, even with IT staff and consultants working on it day and night. The failed system was to have been the company’s preeminent system, but it could not even process customer orders correctly, resulting in complaints about late shipments and receiving the wrong goods.

After three years and $4 million, the new CIO polled the staff anonymously. Only two said the project could be saved, and they had staked their careers on the project. The message that the project was not worth saving was very hard for the CIO to give. It was likewise hard for the division president to receive it; he could not accept the idea of killing a project that cost so much money. He finally accepted the decision and all the ramifications involved, including corporate IT taking control of all IT operations at his division.

a.        List the primary components of an RFP.

1.       a.             Identify possible components or deficiencies in Norcom’s RFP that could have led the new CIO to claim that it was naïve or insufficient.

2.      b.            Identify possible approaches Norcom could have used to evaluate RFP responses.

21.9    Quickfix is rapidly losing business, and management wants to redesign its computer repair processes and procedures to decrease costs and increase customer service. Currently, a customer needing help calls one of five regional service centers. A customer service representative records the relevant customer information, finds the closest qualified technician, and calls the technician’s cell phone to see whether the repair fits into his or her schedule. If not, the representative finds the next closest technician. When a technician is located, customer repair information is provided over the phone. The technician calls the customer and arranges to pick up the computer and replace it with a loaner. Making these arrangements takes one to two days and sometimes more if technicians are not available or do not promptly return calls.  

            If a broken computer cannot be quickly repaired, it is sent to a repair depot. These repairs take another four to seven days. If problems arise, it can take up to two weeks for an item to be repaired. When a customer calls to see whether the computer is ready, the service representative calls the technician to find out the status and calls the customer back. The repair process usually takes five phone calls between the customer, the service representative, and the technician.

            There are several problems with this process that have led to a significant drop in business: (1) it is time-consuming; (2) it is inconvenient for a customer to have a computer removed, a new one installed, and then the old one reinstalled; and (3) service representatives do not have immediate access to information about items being repaired. Quickfix decides to use BPM principles to redesign its business processes.

a.        Identify the repair processes that occur and decide which should be redesigned.

b.        Describe how the repair process can be redesigned to solve the three problems identified.

c.     What benefits can be achieved by redesigning the repair process? 

21.10             Conduct a search (using written materials, the Internet, electronic databases, etc.) for successful and failed implementations of information systems.  Per your professor’s instructions, prepare an oral or written summary of a successful and a failed implementation.  Include in your summary the approach used to acquire or develop the system (purchase software, develop it, modify it, outsource it).

21-1   Steve Cowan owns Professional Salon Concepts (PSC), a hair salon products distribution company. After working for his father, a barber and beauty salon products distributor, he started his own business selling Paul Mitchell products. Business was poor until Steve conducted a free seminar demonstrating how to successfully use his products. He left with a $1,000 order and a decision to sell to salons that allowed him to demonstrate his products. 

            Steve’s strategy paid off as PSC grew to 45 employees, 3,000 customers, and sales of $7 million. PSC carries 1,000 products, compared with 10,000 for most distributors. The smaller product line allows PSC to achieve a 24-hour order turnaround, compared to over two days for the competition. Steve occasionally has to work late packing orders and driving them to the UPS hub a few towns away so he can meet the 2:00 A.M. deadline. 

            After buying a computer and installing a $3,000 accounting package, Steve thought everything was going great until Terri Klimko, a consultant from a PSC supplier, stopped by. Terri asked the following questions to find out how well he knew his business: 

§  Do you know exactly how much you ship each month and to whom?

§  Do you know how much each customer bought, by supplier?

§  Can you rank your customer sales?

§  Can you break your sales down by product?

§  Do you know how the profit per client breaks down into product lines?

§  Do you know how revenues per salesperson vary over the days of the week?

            When Steve answered no to each question, Terri told him that people who cannot answer the questions were losing money. Upset, Steve terminated the session by politely dismissing Terri. Although unimpressed with Terri’s advice, Steve was impressed with her and they were soon married. Shortly afterwards she joined the company.

            Steve asked Terri to help the salons become more profitable. She developed a template to help salon owners determine how much each hairstylist brings in per client, how many clients receive extra services, and which clients buy hair products. The Cowans soon became more like partners to their customers than trainers. If a salon had employee problems, the Cowans would help settle it. If a salon needed help with a grand opening, they lent a hand. The more PSC products the salons bought, the more time the Cowans gave.

            PSC sold turnkey systems and support services at cost to help salons answer Terri’s questions. Unfortunately, PSC’s computer could not answer those same questions. Steve asked consultant Mike Fenske for help. Mike entered all of PSC’s raw data into a database and wrote a program to produce the desired information. The system worked but had problems. It was so slow that accounts payable and purchasing information was handled manually, it did not answer Terri’s growing list of questions, and only a few months of detailed information were available at a time. To alleviate these problems, Steve hired Mike as the company controller.

            After reading an industry report, Steve realized it was time to purchase a new system. Steve and Mike decided to evaluate and select the software themselves and rely on the vendor for installation help. They spent months researching software and attending demonstrations before settling on a $20,000 system. The vendor began installing the system and training PSC personnel.

            Three days prior to conversion, Steve met a distributor who described how his system met his detailed accounting and customer reporting needs as well as his inventory management and order fulfillment needs. Steve was so impressed that they stopped the conversion, went to North Dakota to check out the distributor’s system, and flew to Minneapolis to visit DSM, the software developer.

            DSM did a great job of demonstrating the software and provided Steve and Mike with great references. The only hitch was DSM’s inability to demonstrate two features that were particularly important: adjusting orders automatically to reflect outstanding customer credits and back orders, and determining the least expensive way to pack and ship each order. DSM’s salespeople assured them that those features would be up and running by the time the package was delivered to PSC.

            Their economic feasibility analysis showed $234,000 in yearly savings:

$144,000    Most PSC orders consist of several boxes, 95% of which are sent COD. The old PSC system had no way to prepare orders for multiple-box shipments; a five-box order required five sales invoices and five COD tickets. The new system allowed PSC to generate one sales order and ship one box COD and the other four by regular delivery. Not having to ship every box COD would save $144,000 a year.

$50,000       PSC paid a CPA firm $50,000 a year to prepare its financial statements. The new software would prepare the statements automatically. 

$40,000       Because the old system did not have credit-managing capabilities, it was hard to detect past-due accounts. Earlier detection of past-due accounts would result in faster collections, fewer lost customers, and fewer write-offs. 

Unknown The major reason for acquiring the system was to improve customer service by making more detailed customer information available.

            After estimated annual maintenance costs of $10,000, there was an annual return on investment of $224,000. Because the system would pay for itself in less than a year, Steve bought it and wrote off his $20,000 investment in the other system. 

            When DSM installed the software, Steve found out that the promised features were not available and that there was no immediate plan to add them. Although Steve and Mike were upset, they had to shoulder some of the blame for not insisting on the two features before signing the deal. They found a program that automatically determined the cheapest way to pack and ship an order. DSM agreed to pay half of the $10,000 cost to integrate it into the program. DSM offered to create the module to reflect customer credits and back orders for another $20,000, but Steve declined. These problems pushed the conversion date back several months. 

PSC spent three months preparing to implement the new system. Training PSC employees to use the new system was particularly important. Adding a customer to the database required only one screen with the old system, the new software required six screens. Employees were taught to shout “Fire!” when they had a problem they could not handle. Mike or a DSM programmer explained the error and how to correct it. During implementation, the new system was tested for glitches by processing real data. Looking back, Mike admits three months were not nearly enough for the training and testing. They should have used twice as much time to identify and eliminate glitches.

 When PSC converted to the new system, telephone operators were confronted with situations they had not been trained to handle. Soon everyone was yelling “Fire!” at the same time. In less than one hour, so many operators were waiting for help that the programmers stopped explaining the correct procedures and simply ran from operator to operator correcting problems. Mistakes were repeated numerous times, and the situation intensified. Some employees, frustrated by their inability to work the new system, broke down and cried openly.  

In the warehouse, Steve was not having much fun either. On a normal day, PSC has 200 to 300 boxes ready for 3:30 P.M. shipment. On conversion day, a lone box sat ready to go. Facing the first default on his 24-hour turnaround promise, Steve, Terri, Mike, and a few others stayed past midnight packing and loading boxes on trucks. They barely made it to the UPS hub on time.

 The next day, order entry and shipping proceeded more smoothly, but Steve could not retrieve data to monitor sales. That did not make him feel too kindly about his $200,000 system or DSM. It took Steve weeks to figure out how to get data to monitor sales. When he did, he was horrified that sales had dropped 15%. They had focused so hard on getting the system up and running that they took their eyes off the customers. To make matters worse, Steve could not get information on sales by customer, salesperson, or product, nor could he figure out why or where sales were falling.   Things quickly improved after “Hell Week.” Orders were entered just as quickly, and warehouse operations improved thanks to the integrated add-in program. The new system provided pickers with the most efficient path to follow and told them which items to pack in which boxes based on destination and weight. The system selected a carrier and printed labels for the boxes. Order turnaround time was shaved to 20 minutes from five hours.  

Months after the system was installed, it still did not do everything Steve needed, including some things the old system did. Nor did it answer all of Terri’s questions. Steve is confident, however, that the system will eventually provide PSC with a distinct competitive advantage. He is negotiating with DSM to write the credit and back-order module. 

Steve believes the step up to the new system was the right move for his growing company. With the exceptions of taking the DSM salesperson’s word and not taking enough time to practice with the system, Steve feels PSC did as good a job as it could have in selecting, installing, and implementing a new system.

<para>1.        Do you agree that PSC did a good job selecting, installing, and implementing the new system?  If so, why?  Or do you feel PSC could have done a better job?  If so, what did it do wrong, and what should it have done differently?

                       

2.                How could PSC have avoided the missing features problem?

3.                How could PSC have avoided conversion and reporting problems?

4.                Evaluate Steve’s </inst>economic feasibility analysis.  Do you agree with his numbers and his conclusions?

5.                How could PSC's customers use the new multi-box shipping approach to defraud PSC?

6.                How would you rate the service PSC received from DSM?  What did it do well and what did it do poorly?

CHAPTER 22

SYSTEMS DESIGN, IMPLEMENTATION, AND OPERATION

22.1        Prism Glass is converting to a new information system.  To expedite and speed up implementation, the CEO asked your consulting team to postpone establishing standards and controls until after the system is fully operational.  How should you respond to the CEO’s request?

22.2        When a company converts from one system to another, many areas within the organization are affected.  Explain how conversion to a new system will affect the following groups, both individually and collectively.

22.3     The following notice was posted in the employee cafeteria on Monday morning:</para><opening>

<para><emphasis>To:             </emphasis>All Accounting and Clerical Employees</para>

<para><emphasis>From:        </emphasis>I.M.  Krewel, President</para>

<para><emphasis>Subject:     </emphasis>Termination of Employee Positions</para></opening>

<extract><para>Effective this Friday, all accounting and clerical employees not otherwise contacted will be terminated.  Our new computer system eliminates the need for most of these jobs.  We’re grateful for the loyal service you’ve rendered as employees and wish you success.  You may wish to pick up your final checks on Friday before you go.</para></extract>

<para>Discuss the president’s approach to human resource management. 

</para></question><question id="ch20ques14" label="2           

                What are the possible repercussions of this episode?

Assuming that job termination is the best alternative available, how should management approach the situation?

22.4        In which phase of the systems development life cycle would each of the following positions be most actively involved?  Justify your answers.

22.5        During which of the five SDLC stages is each task, labeled (a) through (m), performed? More than one answer may apply for each activity.

22.1  You were hired to manage the accounting and control functions at the Glass Jewelry Company. During your introductory meeting, the president asked you to design and implement a new AIS within six months. Company sales for the past year were $10 million, and they are expected to double in the next 18 months.

         Outline the procedures you would follow to complete the assigned project.    

a.   

                        Include a description of the following:</para>

1.    Sources of Information

2.    Methods of Recording Information

3.    Methods of Verifying the System Description

b.    The accounts payable system will contain a number of programs, including Enter Invoices and Print Payable Checks.  For each program, describe its purpose and outline application control considerations.

22.2        Wang Lab’s tremendous growth left the company with a serious problem. Customers would often wait months for Wang to fill orders and process invoices. Repeated attempts by Wang’s understaffed IS department to solve these problems met with failure. Finally, Wang hired a consulting firm to solve its revenue tracking problems and expedite prompt receipt of payments. The 18-month project turned into a doubly long nightmare. After three years and $10 million, the consultants were dismissed from the unfinished project.

                The project failed for many reasons. The systems development process was so dynamic that the failure to complete the project quickly became self-defeating as modifications took over the original design. Second, management did not have a clear vision of the new AIS and lacked a strong support staff. As a result, a number of incompatible tracking systems sprang from the company’s distributed computer system. Third, the project was too large and complex for the consulting firm, who had little experience with the complex database at the heart of the new system. Finally, the project had too many applications. Interdependencies among subprograms left consultants with few completed programs. Every program was linked to several subprograms, which in turn were linked to several other programs. Programmers eventually found themselves lost in a morass of subroutines with no completed program.

                The IS department finally developed a system to solve the problem, but their revenue tracking system suffered quality problems for years.<para>

                Wang Labs asked you, a member of the IS staff, to write a memo explaining the failure of the systems development project.</para>

a.    Why did the development project fail?  What role did the consultants play in the failure?

b.            Identify the organizational issues that management must address in the future.

c.     Recommend steps the company could take to guarantee consulting service quality.

22.3     Tiny Toddlers, a manufacturer of children’s toys and furniture, is designing and implementing a distributed system to assist its sales force. Each of the 10 sales offices in Canada and 20 in the United States maintains its own customers and is responsible for granting credit and collecting receivables. Reports used by each sales office to maintain the customer master file and to enter the daily sales orders are shown in Figures 22-4 and 22-5.

<para>Evaluate the reports shown in <link linkend="ch20fig04" preference="0">Figures 22-4<xref linkend="ch20fig04" label="20-4"/></link> and<link linkend="ch20fig05" preference="0"> 22-5<xref linkend="ch20fig05" label="20-5"/></link> using the following format:

Weakness</para></entry>                                <entry valign="top"><para>Explanation</para></entry>                                        <entry valign="top"><para>Recommendation(s)</para><link linkend="informaltable0" preference="1" role="generated"/>

22.4    Mickie Louderman is the new assistant controller of Pickens Publishers. She was the controller of a company in a similar industry, where she was in charge of accounting and had considerable influence over computer center operations. Pickens wants to revamp its information system, placing increased emphasis on decentralized data access and online systems. John Richards, the controller, is near retirement. He has put Mickie in charge of developing a new system that integrates the company’s accounting-related functions. Her promotion to controller will depend on the success of the new AIS.

            Mickie uses the same design characteristics and reporting format she used at her former company. She sends details of the new AIS to the departments that interface with accounting, including inventory control, purchasing, human resources, production control, and marketing. If they do not respond with suggestions by a prescribed date, she will continue the development process. Mickie and John have established a new schedule for many of the reports, changing the frequency from weekly to monthly. After a meeting with the director of IS, Mickie selects a programmer to help her with the details of the new reporting formats.

            Most control features of the old system are maintained to decrease the installation time, with a few new ones added for unusual situations. The procedures for maintaining the controls are substantially changed. Mickie makes all the AIS control change and program-testing decisions, including screening the control features related to payroll, inventory control, accounts receivable, cash deposits, and accounts payable.

            As each module is completed, Mickie has the corresponding department implement the change immediately to take advantage of the labor savings. Incomplete instructions accompany these changes, and specific implementation responsibility is not assigned to departmental personnel. Mickie believes operations people should learn as they go, reporting errors as they occur. 

            Accounts payable and inventory control are implemented first, and several problems arise. The semimonthly payroll runs, which had been weekly under the old system, have abundant errors, requiring numerous manual paychecks. Payroll run control totals take hours to reconcile with the computer printout. To expedite matters, Mickie authorizes the payroll clerk to prepare payroll journal entries. 

            The new inventory control system fails to improve the carrying level of many stock items. This causes critical stock outs of raw material that result in expensive rush orders. The new system’s primary control procedure is the availability of ordering and user information. The information is available to both inventory control and purchasing personnel so that both departments can issue timely purchase orders. Because the inventory levels are updated daily, Mickie discontinues the previous weekly report.

            Because of these problems, system documentation is behind schedule, and proper backup procedures have not been implemented. Mickie has requested budget approval to hire two systems analysts, an accountant, and an administrative assistant to help her implement the new system. John is disturbed by her request because her predecessor had only one part-time assistant.

a.                List the steps Mickie should have taken during while designing the AIS to ensure that end-user needs were satisfied.

b.        Identify and describe three ways Mickie violated internal control principles during the AIS implementation.

c.      Identify and describe the weaknesses in Mickie’s approach to implementing the new AIS.  How could you improve the development process for the remaining parts of the AIS?

</para></listitem></orderedlist></listitem></orderedlist>

22.5        Ryon Pulsipher, manager of Columbia’s property accounting division, has had difficulty responding to the following departmental requests for information about fixed assets.

1.       1.       The controller has requested individual fixed assets schedules to support the general    ledger balance. Although Ryon has furnished the information, it is late. The way the    records are organized makes it difficult to obtain information easily.

2.      2.       The maintenance manager wants to verify the existence of a punch press that he    thinks was repaired twice. He has asked Ryon to confirm the asset number and the    location of the press.

3.      3.       The insurance department wants data on the cost and book values of assets to include    in its review of current insurance coverage. 

4.      4.       The tax department has requested data to determine whether Columbia should switch    depreciation methods for tax purposes. 

5.      5.       The internal auditors have spent significant time in the property accounting division    to confirm the annual depreciation expense. 

                Ryon’s property account records, kept in an Excel spreadsheet, show the asset acquisition date, its account number, the dollar amount capitalized, and its estimated useful life for depreciation purposes. After many frustrations, Ryon realizes his records are inadequate and that he cannot supply data easily when requested. He discusses his problems with the controller, Gig Griffith. 

                RYON: Gig, something has to give. My people are working overtime and can’t keep up. You worked in property accounting before you became controller. You know I can’t tell the tax, insurance, and maintenance people everything they need to know from my records. Internal auditing is living in my area, and that slows down the work. The requests of these people are reasonable, and we should be able to answer their questions and provide the needed data. I think we need an automated property accounting system. I want to talk with the AIS people to see if they can help me.

                GIG: I think that’s a great idea. Just be sure you are personally involved in the design of any system so you get all the info you need. Keep me posted on the project’s progress.                                             

1.       a.       Identify and justify four major objectives Columbia’s automated property accounting system should possess to respond to departmental requests for information.

2.      b.      Identify the data that should be included in the database for each asset.

22.6        A credit union is developing a new AIS. The internal auditors suggest planning the systems development process in accordance with the SDLC concept. The following nine items are identified as major systems development activities that will have to be completed.

1.       1.       System test

2.      2.       User specifications

3.      3.       Conversion

4.      4.       Systems survey

5.      5.       Technical specifications

6.      6.       Post-implementation planning

7.      7.       Implementation planning

8.     8.       User procedures and training

9.      9.       Programming

1.       a.       Arrange the nine items in the sequence in which they should logically occur.

2.      b.      One major activity is converting data files from the old system to the new one.  List three types of file conversion documentation that would be of particular interest to an auditor.</para></listitem></orderedlist>

22.7  MetLife, an insurance company, spent $11 billion to acquire Travelers Life and Annuity from Citicorp in one of the largest insurance company acquisitions of all time. The Metlife CIO estimated it would take three years to integrate the two systems. Because the integration project was especially critical, he figured he could accomplish the integration in 18 months if he pulled out all the stops. The MetLife CEO gave him nine months to complete the task. To pull off the integration in nine months, he had to:

§  Integrate over 600 IS applications, all with their own infrastructure and business processes. The new systems had to comply with “One MetLife,” a company policy that all information systems had to have a common look and feel companywide and be able to function seamlessly with other MetLife systems.

§  Work with over 4,000 employees located in 88 offices scattered all over the globe.

§  Supervise an oversight team and 50 integration teams in seven project management offices.

§  Work with hostile, uncooperative Travelers employees for the six months it took to get regulatory approval and close the deal. The systems had to be integrated three months after the deal closed.

§  Identify integration deliverables (144 in total) and manage the process to deliver them.

§  Negotiate with Citicorp for hundreds of transition services that would not be immediately converted to MetLife’s systems.

a.   What tasks do you think MetLife would have to perform to successfully integrate the Traveler systems into MetLife’s?

b.        Search the Internet for articles that describe the integration process.  Write a two-page summary of the problems and successes that MetLife experienced while integrating the two systems</para></listitem></orderedlist></problem>.

22.8    During final testing, just before launching a new payroll system, the project manager at Reutzel Legal Services found that the purchased payroll system was doing the following:

§  Writing checks for negative amounts

§  Printing checks with names and employee numbers that did not match

§  Making errors; for example, $8 per hour became $800 per hour if a decimal point was not entered

§  Writing checks for amounts greater than a full year’s salary

            Fortunately, payroll was still installed on time, and only 1.5% of the checks had to be manually reissued every payday until the problem was solved. 

            Other problems were that no one had made sure the new system was compatible with the existing payroll database, and there appeared to be no formal transition between the development of the project and the implementation of the project. The system was never run in parallel.  

            Although the programming manager lost his job, the payroll problems helped raise awareness of the company’s growing dependence on IT. Lacking a major problem, there was a perception that the information system did not affect operations.

a.        What does “the system was never run in parallel” mean?</para></listitem>

b.        If the company had run the system in parallel, what should have occurred?

1.       c.             What other testing methodologies could have been used by the firm?

1.       d.        What other types of problems are evident from reading the case?

22.9    A new program at Jones and Carter Corporation (JCC) was supposed to track customer calls. Unfortunately, the program took 20 minutes to load on a PC, and it crashed frequently. The project did not have a traditional reporting structure, and it appeared that no one was actually in charge. The lead project manager quit halfway through the project, the in-house programmers were reassigned to other projects or let go, and two layers of management loosely supervised the systems analyst.

            Management hired consultants to fix the application, but after three months and $200,000, the project was discontinued. JCC did not check the references of the consulting firm it hired to create the new system. The consultants, who were located two states away, made many programming errors. Although the systems analyst caught some of the consultant’s mistakes, they grew increasingly distant and difficult to work with. They would not even furnish the source code to the project managers, most likely because they were afraid of revealing their incompetence.

a.        Identify potential causes for the system implementation failure.

b.        What steps should JCC have taken to successfully design and implement the call tracking system?

22.1    Citizen’s Gas Company (CGC) provides natural gas service to 200,000 customers. The customer base is divided into the following three revenue classes:

Class	Customers	Sales in Cubic Feet	Revenues
Residential	160,000	80 billion	$160 million
Commercial	38,000	15 billion	$ 25 million
Industrial	2,000	50 billion	$ 65 million
Totals		145 billion	$250 million

            Residential customer gas usage is highly correlated with the weather. Commercial customer usage is partially weather dependent. Industrial customer usage is governed almost entirely by business factors.   

            The company buys natural gas from 10 pipeline companies in the amounts specified in contracts that run for 5 to 15 years. For some contracts, the supply is in equal monthly increments; for other contracts, the supply varies according to the heating season. Supply over the contract amounts is not available, and some contracts contain take-or-pay clauses. That is, the company must pay for the gas volume specified in the contract, regardless of the amount used.

            To match customer demand with supply, gas is pumped into a storage field when supply exceeds customer demand. Gas is withdrawn when demand exceeds supply. There are no restrictions on the gas storage field except that the field must be full at the beginning of each gas year (September 1). Consequently, when the contractual supply for the remain- der of the gas year is less than that required to satisfy projected demand and fill the storage field, CGC curtails service to industrial customers (except for heating quantities). The curtailments must be carefully controlled to prevent either an oversupply at year-end or a curtailing of commercial or residential customers so the storage field can be filled at year-end.    

            In recent years, CGC’s planning efforts have not been able to control the supply during the gas year or provide the information needed to establish long-term contracts. Customer demand has been projected only as a function of the total number of customers. Commercial and industrial customers’ demand for gas has been curtailed. This has resulted in lost sales and caused an excess of supply at the end of the gas year.    

            To correct the problems, CGC has hired a director of corporate planning. She is presented with a conceptual design for an information system that will help analyze gas supply and demand. The system will provide a monthly gas plan for the next five years, with particular emphasis on the first year. The plan will provide detailed reports that assist in the decision- making process. The system will use actual data during the year to project demand for the year. The president has indicated that she will base her decisions on the effect alternative plans have on operating income.                  

1.    Discuss the criteria to consider in specifying the structure and features of CGC’s new system.

2.                Identify the data that should be incorporated into CGC’s new system to provide adequate planning capability.  Explain why each data item it is important and the level of detail needed for the data to be useful.

Complete Solutions for Accounting Information System 12e by Marshall B. Romney
Paul J. Steinbart

All 22 Chapter's End Questions Solved - Verified from Solution Manual

All Solution are in .doc format.