Need Solution Email me:
topsolutions8@gmail.com
accounting information systems: An overview
1.1 The value of information is the difference between the benefits realized from using that information and the costs of producing it. Would you, or any organization, ever produce information if its expected costs exceeded its benefits? If so, provide some examples. If not, why not?
1.2 Can the characteristics of useful information listed in Table 1-1 be met simultaneously? Or does achieving one mean sacrificing another?
1.3 You and a few of your classmates decided to become entrepreneurs. You came up with a great idea for a new mobile phone application that you think will make lots of money. Your business plan won second place in a local competition, and you are using the $10,000 prize to support yourselves as you start your company.
a. Identify the key decisions you need to make to be successful entrepreneurs, the information you need to make them, and the business processes you will need to engage in.
b. Your company will need to exchange information with various external parties. Identify the external parties, and specify the information received from and sent to each of them.
1.4 How do an organization’s business processes and lines of business affect the design of its AIS? Give several examples of how differences among organizations are reflected in their AIS.
1.5 Figure 1-4 shows that organizational culture and the design of an AIS influence one another. What does this imply about the degree to which an innovative system developed by one company can be transferred to another company?
1.6 Figure 1-4 shows that developments in IT affect both an organization’s strategy and the design of its AIS. How can a company determine whether it is spending too much, too little, or just enough on IT?
1.7 Apply the value chain concept to S&S. Explain how it would perform the various primary and support activities.
1.8 Information technology enables organizations to easily collect large amounts of information about employees. Discuss the following issues:
To what extent should management monitor employees’ e-mail?
To what extent should management monitor which Web sites employees visit?
c. To what extent should management monitor employee performance by, for example, using software to track keystrokes per hour or some other unit of time? If such information is collected, how should it be used?
d. Should companies use software to electronically “shred” all traces of e-mail?
e. Under what circumstances and to whom is it appropriate for a company to distribute information it collects about the people who visit its Web site?
Problems
1.1 Information technology is continually changing the nature of accounting and the role of accountants. Write a two-page report describing what you think the nature of the accounting function and the accounting information system in a large company will be like in the year 2020.
1.2 Adapted from the CMA Examination
a. Identify and discuss the basic factors of communication that must be considered in the presentation of the annual report.</para></listitem>
b. Discuss the communication problems a corporation faces in preparing the annual report that result from the diversity of the users being addressed.
c. Select two types of information found in an annual report, other than the financial statements and accompanying footnotes, and describe how they are helpful to the users of annual reports.
d. </inst>Discuss at least two advantages and two disadvantages of stating well-defined corporate strategies in the annual report.</para></listitem>
e. Evaluate the effectiveness of annual reports in fulfilling the information needs of the following current and potential users: shareholders, c</para></listitem>reditors</para></listitem>, <listitem><para><inst></inst>employees</para></listitem>, c<ustomers, and f</para></listitem>inancial analysts</para></listitem></orderedlist></listitem>
f. Annual reports are public and accessible to anyone, including competitors. Discuss how this affects decisions about what information should be provided in annual reports.
1.3 The use of IT at USAA
a. Why should USAA collect data on which auto parts are fixed most frequently? What could it do with this data?</para></listitem>
b. Even though USAA offered to waive the deductible, the repair shops still managed to convince 95% of the owners to replace rather than repair their damaged windshields. How could USAA use its AIS to persuade more shop owners to repair rather than replace their windows?</para></listitem>
a. How does the image-processing system at USAA add value to the organization?
b. How do the remote deposit capture and mobile banking system at USAA add value to the organization?
c. Do an Internet search and find out what other advancements USAA has introduced. Write a brief paragraph on each new application or other newsworthy item you find (maximum limit of three applications or items).
1.4 Match the description in the right column with the information characteristic in the left column.
1. Relevant a. The report was carefully designed so that the data contained on the report became information to the reader
2. Reliable b. The manager was working one weekend and needed to find some information about production requests for a certain customer. He was able to find the report on the company’s network.
3. Complete c. The data on a report was checked by two clerks working independently
4. Timely d. An accounts receivable aging report that included all customer accounts
5. Understandable e. A report checked by 3 different people for accuracy
6. Verifiable f. An accounts receivable aging report used in credit granting decisions
7. Accessible g. An accounts receivable aging report was received before the credit manager had to make a decision whether to extend customer credit
1.5 The Howard Leasing Company
a. What is an accounts receivable aging report?
Why is an accounts receivable aging report needed for an audit?
What is an accounts receivable aging report used for in normal company operations?
What data will you need to prepare the report?
Where will you collect the data you need to prepare the report?
How will you collect the necessary data for the report?
What will the report look like (i.e., how will you organize the data collected to create the information your supervisor needs for the audit)? Prepare an accounts receivable aging report in Excel or another spreadsheet package.
How will you distribute the report? How many copies will you make? Who should receive the copies? What security features will you implement?
1.6 The use of IT at Tesco
a. What kind of information do you think Tesco gathers?
a. How do you think Tesco has motivated over 12 million customers to sign up for its Clubcard program?
b. What can Tesco accomplish with the Clubcard data it collects? Think in term of strategy and competitive advantage.
c. What are some of the disadvantages to the Clubcard program?
d. Do an Internet search to find out how Tesco is doing in comparison to Wal Mart and other grocers and retailers. Write a few paragraphs explaining your findings.
1.7 Have you ever imagined having one electronic device that does everything you would ever need? Mobile phone makers in Japan have gone beyond the imagining phase. Cell phones in Japan are becoming more versatile than ever. Newer models of cell phones contain a myriad of applications and can do many of the things that a personal computer (PC) can do. PCs are also able to function as phones. A small but growing number of professionals are trading in their laptops for handheld computers. Cell phone manufacturers in the United States and elsewhere are quickly catching up to their Japanese counterparts.
a. What commercial activities can be done with a cell phone? With a cell phone/PC combination device? What do you do when you’re on your cell phone? What do you expect to be doing in five years?
b. How can businesses utilize this technology to attract more customers, sell more products, advertise their products, facilitate the sale of products, and conduct and manage their businesses more efficiently and effectively?
c. What are some problems or drawbacks you can see with using these devices in business?
1.8 Classify each of the following items as belonging in the revenue, expenditure, human resources/payroll, production, or financing cycle.
Purchase raw materials
Pay off mortgage on factory
Hire a new assistant controller
Establish a $10,000 credit limit for a new customer
Pay for raw materials
Disburse payroll checks to factory workers
Record goods received from vendor
Update the allowance for uncollectible accounts
Decide how many units to make next month
Complete picking ticket for customer order
Record factory employee timecards
Sell concert tickets
Draw on line-of-credit
Send new employees to a business ethics course
Pay utility bills
Pay property taxes on office building
Pay federal payroll taxes
Sell DVD player
Collect payment on customer accounts
Obtain a bank loan
Pay sales commissions
Send an order to a vendor
Put purchased goods into the warehouse
Cases
1-1 The Web site for this book contains an adaption of Russell L. Ackoff’s classic article “Management Misinformation Systems” from Management Science. In the article, Ackoff identified five common assumptions about information systems and then explained why he disagreed with them.
Read the five assumptions, contentions, and Ackoff’s explanations. For each of the five assumptions, decide whether you agree or disagree with Ackoff’s contentions. Prepare a report in which you defend your stand and explain your defense.
CHAPTER 2
Overview of Business Processes
2.1 Table 2-1 lists some of the documents used in the revenue, expenditure, and human resources cycle. What kinds of input or output documents or forms would you find in the production (or conversion) cycle?
2.2 With respect to the data processing cycle, explain the phrase “garbage in, garbage out.” How can you prevent this from happening?
2.3 </inst><para>What kinds of documents are most likely to be turnaround documents? Do an internet search to find the answer and to find example turnaround documents.
2.4 The data processing cycle in Figure 2-1 is an example of a basic process found throughout nature. Relate the basic input/process/store/output model to the functions of the human body.
2.5 Some individuals argue that accountants should focus on producing financial statements and leave the design and production of managerial reports to information systems specialists. What are the advantages and disadvantages of following this advice? To what extent should accountants be involved in producing reports that include more than just financial measures of performance? Why?</para></question></general-problem></problemset>
2.1 The chart of accounts must be tailored to an organization’s specific needs. Discuss how the chart of accounts for the following organizations would differ from the one presented for S&S in <link linkend=”ch02table04? preference=”0?>Table 2-2<xref linkend=”ch02table04? label=”2-4?/></link>.</para>
2.2 <para>Design a chart of accounts for SDC. Explain how you structured the chart of accounts to meet the company’s needs and operating characteristics. Keep total account code length to a minimum, while still satisfying all of Mace’s desires.</para><source>
2.3 An audit trail enables a person to trace a source document to its ultimate effect on the financial statements or work back from amounts in the financial statements to source documents. Describe in detail the audit trail for the following:</para>
2.4 Your nursery sells various types and sizes of trees, bedding plants, vegetable plants, and shrubs. It also sells fertilizer and potting soil. Design a coding scheme for your nursery.
2.5 Match the following terms with their definitions
TERM
DEFINITION
a. data processing
Contains summary-level data for every asset, liability, equity, revenue, and expense account
b. source documents
2. Items are numbered consecutively to account for all items; missing items cause a gap in the numerical sequence
c. turnaround documents
3. Path of a transaction through a data processing system from point of origin to final output, or backwards from final output to point of origin
d. source data automation
4. List of general ledger account numbers; allows transaction data to be coded, classified, and entered into proper accounts; </para <para>facilitates preparation of financial statements and reports
e. general ledger
5. Contents of a specific field, such as “George” in a name field
f. subsidiary ledger
6. Portion of a data record that contains the data value for a particular attribute, like a cell in a spreadsheet
g. control account
7. Company data sent to an external party and then returned to the system as input
h. coding
8. Used to record infrequent or non-routine transactions
i. sequence code
9. Characteristics of interest that need to be stored
j. block code
10. The steps a company must follow to efficiently and effectively process data about its transactions
k. group code
11. Something about which information is stored
l. mnemonic code
12. Stores cumulative information about an organization; like a ledger in a manual AIS.
m. chart of accounts
13. Contains detailed data for any general ledger account with many individual subaccounts
n. general journal
14. Contains records of individual business transactions that occur during a specific time period
o. specialized journal
15. Updating each transaction as it occurs
p. audit trail
16. Devices that capture transaction data in machine-readable form at the time and place of their origin
q. entity
17. Used to record large numbers of repetitive transactions
r. attribute
18. Set of interrelated, centrally coordinated files
s. field
19. Two or more subgroups of digits are used to code items
t. record
20. Updating done periodically, such as daily
u. data value
21. Systematic assignment of numbers or letters to items to classify and organize them
v. master file
22. Letters and numbers, derived from the item description, are interspersed to identify items; usually easy to memorize
w. transaction file
23. Initial record of a transaction that takes place; usually recorded on preprinted forms or formattted screens
x. database
24. Fields containing data about entity attributes; <keyterm linkend=”ch02kt29? role=”strong” preference=”1?>like a row in a spreadsheet
y. batch processing
25. Sets of numbers are reserved for specific categories of data
z. online, real-time processing
26. The general ledger account corresponding to a subsidiary ledger, where the sum of all subsidiary ledger entries should equal the amount in the general ledger account
2.6 For each of the following scenarios identify which data processing method (batch or online, real-time) would be the most appropriate.
2.7 <para>After viewing the Web sites, and based on your reading of the chapter, write a 2 page paper that describes how an ERP can connect and integrate the revenue, expenditure, human resources/payroll, and financing cycles of a business.
2.8 Which of the following actions update a master file and which would be stored as a record in a transaction file?
Update customer address change
Update unit pricing information
Record daily sales
Record payroll checks
Change employee pay rates
Record production run variances
Record Sales Commissions
Change employee office location
Update accounts payable balance
Change customer credit limit
Change vendor payment discount terms
Record purchases
2.9 You were hired to assist Ashton Fleming in designing an accounting system for S&S. Ashton has developed a list of the journals, ledgers, reports, and documents that he thinks S&S needs (see <link linkend=”ch02table06? preference=”1?>Table 2-6<xref linkend=”ch02table06? label=”2-8?/></link>). He asks you to complete the following tasks:</para>
a. Specify what data you think should be collected on each of the following four documents:
b. Design a report to manage inventory.</para></listitem>
Design a report to assist in managing credit sales and cash collections.
Visit a local office supply store and identify what types of journals, ledgers, and blank forms for various documents (sales invoices, purchase orders, etc.) are available. Describe how easily they could be adapted to meet S&S’s needs.
2.1 Bar Harbor Blueberry Farm
Data from Case
Date Supplier Invoice Supplier Name Supplier Address Amount
March 7 AJ34 Bud’s Soil Prep, Inc. PO Box 34
$2,067.85
March 11 14568 Osto Farmers Supply 45 Main
$ 67.50
March 14 893V Whalers Fertilizer, Inc. Route 34
$5,000.00
March 21 14699 Osto Farmers Supply 45 Main
$3,450.37
March 21 10102 IFM Package Wholesale 587 Longview
$4,005.00
March 24 10145 IFM Package Wholesale 587 Longview
$ 267.88
CHAPTER 3
SYSTEMS DEVELOPMENT AND DOCUMENTATION TECHNIQUES
3.1 Identify the DFD elements in the following narrative: A customer purchases a few items from a local grocery store. Jill, a salesclerk, enters the transaction in the cash register and takes the customer’s money. At closing, Jill gives both the cash and the register tape to her manager.
3.2 Do you agree with the following statement: “Any one of the systems documentation procedures can be used to adequately document a given system”? Explain.
3.3 Compare the guidelines for preparing flowcharts and DFDs. What general design principles and limitations are common to both documentation techniques?
3.4 Your classmate asks you to explain flowcharting conventions using real-world examples. Draw each of the major flowchart symbols from memory, placing them into one of four categories: input/output, processing, storage, and flow and miscellaneous. For each symbol, suggest several uses.
3.1 Prepare flowcharting segments for each of the following operations:
a. processing transactions stored on magnetic tape to update a master file stored on magnetic tape
b. processing transactions stored on magnetic tape to update a database stored on a magnetic disk
c. converting source documents to magnetic tape using a computer-based optical character reader (OCR)
d. processing OCR documents online to update a database on magnetic disk
e. reading data from a magnetic disk into the computer to be printed on a report
f. using a computer or terminal to </inst>key data from source documents to a file stored on a magnetic disk
g. manually sorting and filing invoices numerically
h. using a terminal to enter source document data and send it to a </inst>remote location where an online processing system records it in a database stored on magnetic disk
i. a scheduled automatic backup of an internal hard drive to an external hard drive
j. using a terminal to </inst>query customer sales data maintained on a magnetic disk
k. enter employee hours recorded on time cards in the payroll transaction file maintained on disk and update wage data maintained on the payroll master file
l. use a terminal to </inst>access a price list maintained on disk to complete a purchase order. An electronic copy of the purchase order is sent to the vendor and a backup copy is printed and filed by vendor name
m. update an airline reservation on a Web-based airline reservation system from a home computer
3.2 Happy Valley Utility Company
a. Draw a system flowchart of the billing operations, commencing with the computer preparation of the meter reading forms and ending with the mailing of customer bills.
b. Draw a system flowchart depicting customer payments processing, starting with the mail room operations and ending with the two printed reports.
3.3 <para>Prepare a system flowchart of the process described.</para></problem>
3.4 Prepare a document flowchart to reflect how ANGIC Insurance Company processes its casualty claims.
3.5
a. Prepare a document flowchart that indicates the interaction and use of these documents among all departments at Beccan Company’s central facility. It should provide adequate internal control over the receipt, issuance, replenishment, and payment of tires and supplies. You may assume that there is a sufficient number of document copies to ensure that the perpetual inventory system has the necessary basic internal controls.</para><source>
b. Use the instructor proUsing the flowcharting conventions discussed in Focus 3.2, critique the instructor provided CMA solution. List all the ways the CMA solution violates those flowcharting guidelines.
3.6
a. Prepare a context diagram and level 0 DFD to document the payroll processing system at No-Wear Products.
b. Prepare a document flowchart to document the payroll processing system at No-Wear Products.
3.7
a. Prepare a context diagram and a level 0 DFD to document accounts payable processing at S&S.
b. Prepare a document flowchart to document accounts payable processing at S&S.
3.8
a. Develop a context diagram and a level 0 DFD of the acquisition/payment system at Oriental Trading.
b. Prepare a document flowchart to document the acquisition/payment system at Oriental Trading.
3.9
a. Develop a context diagram and a level 0 DFD for the cash receipts system at S&S.</para></listitem>
b. Prepare a document flowchart to document the cash receipts system at S&S.
3.10 <para>Draw a context diagram and at least two levels of DFDs for the preceding
3.11
a. Prepare a context diagram and at least two levels of DFDs for this operation.
b. Prepare a document flowchart to document this operation.
3.12 You recognize weaknesses in the existing system and believe a document flowchart would be beneficial in evaluating this client’s internal control in preparing for your examination of the financial statements.</para>
a. Complete the flowchart given in <link linkend=”ch03fig12? preference=”1?>Figure 3-12<xref linkend=”ch03fig12? label=”3-12?/></link>, for sales and cash receipts of Charting, Inc., by labeling the appropriate symbols and indicating information flows. Adapted from the 1969 CPA Exam
b. Using the guidelines for preparing flowcharts in <link linkend=”ch03sb02? preference=”0?>Focus 3-2<xref linkend=”ch03sb02? label=”3-2?/></link> and the flowcharting symbols shown in <link linkend=”ch03fig08? preference=”0?>Figure 3-8<xref linkend=”ch03fig08? label=”3-8?/></link>, critique the flowchart shown in <link linkend=”ch03fig12? preference=”0?>Figure 3-12<xref linkend=”ch03fig12? label=”3-12?/></link>. List the ways the flowchart violates the guidelines or uses improper symbols.
3.13 Bottom Manufacturing Corporation Charge Sales System
a. List the procedures or the internal documents that are labeled letters <emphasis>c</emphasis> to <emphasis>r</emphasis> in the flowchart of Bottom Manufacturing Corporation’s charge sales system. Organize your answer as follows (Note that the explanations of the letters <emphasis>a</emphasis> and <emphasis>b</emphasis> in the flowchart are entered as examples):
</para></listitem>
<informaltable id=”informaltable2? frame=”none” float=”0? type=”untbl1?><tgroup cols=”2? colsep=”0? rowsep=”0? align=”left”><colspec colnum=”1? colname=”c01? colwidth=”500?/><colspec colnum=”2? colname=”c02? colwidth=”500?/><thead><row><entry valign=”top”><para>Flowchart Symbol Letter</para></entry> <entry valign=”top”><para>Procedures or Internal Document</para></entry></row></thead>
<tbody><row><entry valign=”top”><para>a</para></entry> <entry valign=”top”><para>Prepare six-part sales order.</para></entry></row>
<row><entry valign=”top”><para>b</para></entry> <entry valign=”top”><para>File by order number.</para></entry></row></tbody></tgroup></informaltable>
b. Using the guidelines for preparing flowcharts in <link linkend=”ch03sb02? preference=”0?>Focus 3-2<xref linkend=”ch03sb02? label=”3-2?/></link> and the flowcharting symbols shown in <link linkend=”ch03fig08? preference=”0?>Figure 3-8<xref linkend=”ch03fig08? label=”3-8?/></link>, critique the flowchart shown in <link linkend=”ch03fig13? preference=”0?>Figure 3-13<xref linkend=”ch03fig13? label=”3-13?/></link>. List the ways the flowchart violates the guidelines or uses improper symbols.
3.14
a. Prepare and file a tax return with the tax owed to the Internal Revenue Service.
b. A customer pays an invoice with a check. Accounts receivable is updated to reflect the payment. The check is recorded and deposited into the bank.
c. A customer places an online order to purchase merchandise. The order is approved, filled, and sent to the customer with an invoice.
d. An inventory request is received by the purchasing department. The purchasing
e. A vendor invoice is received, reviewed, and compared against the appropriate purchase order, then paid and filed.</para></listitem>
f. A bill of lading for ordered inventory is received from a vendor, recorded, checked against the appropriate purchase order, and filed.
3 .15 <para>Prepare a program flowchart to help Melanie program this process.</para></problem>
3.16
1. Statements are prepared and sent to customers from data contained in the accounts receivable data store.
2. A customer sends a sales invoice to the accounts payable process.
3. A check is manually prepared from data on a vendor invoice.
4. The cash receipt process updates the cash receipts data store.
5. A sales invoice is manually prepared and sent to a customer.
6. A report is prepared from data stored on magnetic tape.
7. Billing data are entered online and used to update the sales order file and the customer master file.
8. Data from a cancelled invoice are used to update the cash disbursements ledger.
9. A sales order is prepared manually. Copy 1 is sent to the warehouse and copy 2 is filed.
10. An accounts receivable aging report is prepared from the accounts receivable master file and the cash receipts master file, both stored on disk.
11. An error listing and batch total are compared and filed.
3-1 <para>You are the systems analyst for the Wee Willie Williams Widget Works (also known as Dub 5, which is a shortened version of 5 Ws). Dub 5 produces computer keyboard components. It has been producing keyboards for more than 20 years and has recently signed an exclusive 10-year contract to provide the keyboards for all Dell personal computers. As the systems analyst, you have been assigned the task of developing a level 0 DFD for Dub 5’s order processing system. You have finished gathering all the information you need to develop the first-pass DFD and now want to complete the diagram.</para>
CHAPTER 4
RELATIONAL DATABASES
4.1 Contrast the logical and the physical view of data and discuss why separate views are necessary in database applications. Describe which perspective is most useful for each of the following employees: a programmer, a manager, and an internal auditor. How will understanding logical data structures assist you when designing and using database systems?</para></question><question id=”ch04ques02? label=”4.2?>
4.2 The relational data model represents data as being stored in tables. Spreadsheets are another tool that accountants use to employ a tabular representation of data. What are some similarities and differences in the way these tools use tables? How might an accountant’s familiarity with the tabular representation of spreadsheets facilitate or hinder learning how to use a relational DBMS?
4.3 Some people believe database technology may eliminate the need for double-entry accounting. This creates three possibilities: (1) the double-entry model will be abandoned; (2) the double-entry model will not be used directly, but an external-level schema based on the double-entry model will be defined for accountants’ use; or (3) the double-entry model will be retained in database systems. Which alternative do you think is most likely to occur? Why?
4.4 Relational DBMS query languages provide easy access to information about the organization’s activities. Does this mean that online, real-time processing should be used for all transactions? Does an organization need real-time financial reports? Why or why not?</para></question></general-problem></problemset>
4.5 Why is it so important to have good data?
4.6 What is a data dictionary, what does it contain, and how is it used?
4.7 Compare and contrast the file-oriented approach and the database approach. Explain the main advantages of database systems.
4.1
a. Identify three potential users and design a subschema for each. Justify your design by explaining why each user needs access to the subschema data elements.
b. Use Microsoft Access or some other relational database product to create the schema tables. Specify the primary key(s), foreign key(s), and other data for each table. Test your model by entering sample data in each table.
4.2 Most DBMS packages contain data definition, data manipulation, and data query languages. For each of the following, indicate which language would be used and why.
A database administrator defines the logical structure of the database
b. The controller requests a cost accounting report containing a list of all employees being paid for more than 10 hours overtime in a given week.
c. A programmer develops a program to update the fixed-assets records stored in the database.
d. The human resources manager requests a report noting all employees who are retiring within five years.
e. The inventory serial number field is extended in the inventory records to allow for recognition of additional inventory items with serial numbers containing more than 10 digits.
f. A user develops a program to print out all purchases made during the past two weeks.
g. An additional field is added to the fixed-asset records to record the estimated salvage value of each asset.
4.3 Ashton wants to store the following data about S&S’s purchases of inventory:
a. Design a set of relational tables to store this data. Do all of the data items need to be stored in a table? If not, which ones do not need to be stored and why do they not need to be stored?
b. Identify the primary key for each table.
c. Identify the foreign keys needed in the tables to implement referential integrity.
d. Implement your tables using any relational database product to which you have access.
e. Test your specification by entering sample data in each table.
f. Create a few queries to retrieve or analyze the data you stored.
4.4 Retrieve the S&S In-Chapter Database (in Microsoft Access format) from the text’s Web site (or create the tables in <link linkend=”ch04table05? preference=”0?>Table 4-5<xref linkend=”ch04table05? label=”4-5?/></link> in a relational DBMS product). Write queries to answer the following questions. <emphasis>Note</emphasis>: For some questions, you may have to create two queries—one to calculate an invoice total and the second to answer the question asked.
a. How many different kinds of inventory items does S&S sell?
b. How many sales were made during October?
c. What were total sales in October?
d. What was the average amount of a sales transaction?
e. Which salesperson made the largest sale?
f. How many units of each product were sold?
g. Which product was sold most frequently?</para></listitem></orderedlist></problem>
4.5
Enter the tables in <link linkend=”ch04table15? preference=”1?>Table 4-15<xref linkend=”ch04table15? label=”4-15?/></link> into a relational DBMS package. Write queries to answer the following questions. <emphasis>Note</emphasis>: For some questions, you may have to create two queries—one to calculate a total and the second to answer the question asked.<inlinemediaobject role=”titleicon” id=”ch04dg03? type=”dg01?><imageobject><imagedata/></imageobject><textobject/></inlinemediaobject></para>
a. Which customers (show their names) made purchases from Martinez?
b. Who has the largest credit limit?
c. How many sales were made in October?
d. What were the item numbers, price, and quantity of each item sold on invoice number 103?
e. How much did each salesperson sell?
f. How many customers live in Arizona?
g. How much credit does each customer still have available?
h. How much of each item was sold? (Include the description of each item in your answer.)
Which customers still have more than $1,000 in available credit?
j. For which items are there at least 100 units on hand?
4.6 The BusyB Company wants to store data about employee skills. Each employee may possess one or more specific skills and several employees may have the same skill. Include the following facts in the database:
date hired
date of birth
date skill acquired
employee name
employee number
pay rate </pa
a. Design a set of relational tables to store these data.<inlinemediaobject role=”titleicon” id=”ch04dg04? type=”dg01?><imageobject><imagedata/></imageobject><textobject/></inlinemediaobject></para></listitem>
<listitem><para><inst>b. </inst>Identify the primary key for each table, and identify any needed foreign keys.</para></listitem>
<listitem><para><inst>c. </inst>Implement your schema using any relational DBMS. Specify primary and foreign keys, and enforce referential integrity. Demonstrate the soundness of your design by entering sample data in each table.</para></listitem></orderedlist></problem>
4.7 You want to extend the schema shown in <link linkend=”ch04table16? preference=”0?>Table 4-16<xref linkend=”ch04table16? label=”4-16?/></link> to include information about customer payments. Some customers make installment payments on each invoice. Others write a check to pay for several different invoices. </para>
<listitem><para><inst>a. </inst>Modify the set of tables in <link linkend=”ch04table16? preference=”1?>Table 4-16<xref linkend=”ch04table16? label=”4-16?/></link> to store this additional data.<inlinemediaobject role=”titleicon” id=”ch04dg05? type=”dg01?><imageobject><imagedata/></imageobject><textobject/></inlinemediaobject></para></listitem>
<listitem><para><inst>b. </inst>Identify the primary key for each new table you create.</para></listitem>
<listitem><para><inst>c. </inst>Implement your schema using any relational DBMS package. Indicate which attributes are primary and foreign keys, and enter sample data in each table you create.
</para></listitem></orderedlist></problem
4.8 Create relational tables that solve the update, insert, and delete anomalies in <link linkend=”ch04table17? preference=”1?>Table 4-17.
4.9 Create relational tables that solve the update, insert, and delete anomalies in <link linkend=”ch04table18? preference=”1?>Table 4-18.
4.10 From the database created in the comprehensive problem, perform queries based on the tables and query grid shown in <link linkend=”ch04table19? preference=”1?>Table 4-19<xref linkend=”ch04table19? label=”4-19?/></link>.
a. Which borrowers use Advent Appraisers?
b. What is the average amount borrowed from National Mortgage?
c. List all of the property appraisers.
d. List all of the lenders.
List the lenders that lent more than $100,000.
f. Which borrower requested the largest mortgage?
g. Which borrower requested the smallest mortgage?
4.1 As in all areas of information technology, DBMSs are constantly changing and improving. Research how businesses are using DBMSs, and write a report of your findings. Address the following issues:
1. Which popular DBMS products are based on the relational data model?
2. Which DBMS products are based on a logical model other than the relational data model?
3. What are the relative strengths and weaknesses of the different types (relational versus other logical models) of DBMSs
CHAPTER 5
COMPUTER FRAUD
5.1 Do you agree that the most effective way to obtain adequate system security is to rely on the integrity of company employees? Why or why not? Does this seem ironic? What should a company do to ensure the integrity of its employees?
5.2 You are the president of a multinational company where an executive confessed to kiting $100,000. What is kiting and what can your company do to prevent it? How would you respond to the confession? What issues must you consider before pressing charges?
5.3 Discuss the following statement by Roswell Steffen, a convicted embezzler: “For every foolproof system, there is a method for beating it.” Do you believe a completely secure computer system is possible? Explain. If internal controls are less than 100% effective, why should they be employed at all?
5.4 Revlon hired Logisticon to install a real-time invoice and inventory processing system. Seven months later, when the system crashed, Revlon blamed the Logisticon programming bugs they discovered and withheld payment on the contract. Logisticon contended that the software was fine and that it was the hardware that was faulty. When Revlon again refused payment, Logisticon repossessed the software using a telephone dial-in feature to disable the software and render the system unusable. After a three-day standoff, Logisticon reactivated the system. Revlon sued Logisticon, charging them with trespassing, breach of contract, and misappropriation of trade secrets (Revlon passwords). Logisticon countersued for breach of contract. The companies settled out of court.
Would Logisticon’s actions be classified as sabotage or repossession? Why? Would you find the company guilty of committing a computer crime? Be prepared to defend your position to the class.
5.5 Because improved computer security measures sometimes create a new set of problems—user antagonism, sluggish response time, and hampered performance—some people believe the most effective computer security is educating users about good moral conduct. Richard Stallman, a computer activist, believes software licensing is antisocial because it prohibits the growth of technology by keeping information away from the neighbors. He believes high school and college students should have unlimited access to computers without security measures so that they can learn constructive and civilized behavior. He states that a protected system is a puzzle and, because it is human nature to solve puzzles, eliminating computer security so that there is no temptation to break in would reduce hacking.
<para>Do you agree that software licensing is antisocial? Is ethical teaching the solution to computer security problems? Would the removal of computer security measures reduce the incidence of computer fraud? Why or why not?
5.1 You were asked to investigate extremely high, unexplained merchandise shortages at a department store chain. Classify each of the five situations as a fraudulent act, an indicator of fraud, or an event unrelated to the investigation. Justify your answers.
a. The receiving department supervisor owns and operates a boutique carrying many of the same labels as the chain store. The general manager is unaware of the ownership interest.
b. The receiving supervisor signs receiving reports showing that the total quantity shipped by a supplier was received and then diverts 5% to 10% of each shipment to the boutique.
c. The store is unaware of the short shipments because the receiving report accompanying the merchandise to the sales areas shows that everything was received.
d. Accounts Payable paid vendors for the total quantity shown on the receiving report.
e. Based on the receiving department supervisor’s instructions, quantities on the receiving reports were not counted by sales personnel.
5.2 A client heard through its hot line that John, the purchases journal clerk, periodically enters fictitious acquisitions. After John creates a fictitious purchase, he notifies Alice, the accounts payable ledger clerk, so she can enter them in her ledger. When the payables are processed, the payment is mailed to the nonexistent supplier’s address, a post office box rented by John. John deposits the check in an account he opened in the nonexistent supplier’s name.
a. Define fraud, fraud deterrence, fraud detection, and fraud investigation.
a. List four personal (as opposed to organizational) fraud symptoms, or red-flags, that indicate the possibility of fraud.</para></listitem> Do not confine your answer to this example.
b. List two procedures you could follow to uncover John’s fraudulent behavior.
5.3 The computer frauds that are publicly revealed represent only the tip of the iceberg. Although many people perceive that the major threat to computer security is external, the more dangerous threats come from insiders. Management must recognize these problems and develop and enforce security programs to deal with the many types of computer fraud.
Explain how each of the following six types of fraud is committed. Using the format provided, also identify a different method of protection for each and describe how it works
5.4 Environmental, institutional, or individual pressures and opportune situations, which are present to some degree in all companies, motivate individuals and companies to engage in fraudulent financial reporting. Fraud prevention and detection require that pressures and opportunities be identified and evaluated in terms of the risks they pose to a company. Adapted from the CMA Examination.
a. Identify two company pressures that would increase the likelihood of fraudulent financial reporting.
b. Identify three corporate opportunities that make fraud easier to commit and detection less likely.
c. For each of the following, identify the external environmental factors that should be considered in assessing the risk of fraudulent financial reporting
· The company’s industry
· The company’s business environment
· The company’s legal and regulatory environment
d. What can top management do to reduce the possibility of fraudulent financial reporting?
5.5 For each of the following independent cases of employee fraud, recommend how to prevent similar problems in the future.
a. Due to abnormal inventory shrinkage in the audiovisual department at a retail chain store, internal auditors conducted an in-depth audit of the department. They learned that a customer frequently bought large numbers of small electronic components from a certain cashier. The auditors discovered that they had colluded to steal electronic components by not recording the sale of items the customer took from the store.
b. During an unannounced audit, auditors discovered a payroll fraud when they distributed paychecks instead of department supervisors. When the auditors investigated an unclaimed paycheck, they discovered that the employee quit four months previously after arguing with the supervisor. The supervisor continued to turn in a time card for the employee and pocketed his check.
c. Auditors discovered an accounts payable clerk who made copies of supporting documents and used them to support duplicate supplier payments. The clerk deposited the duplicate checks in a bank account she had opened using a name similar to the supplier’s.</para></listitem></orderedlist><source>
5.6 An auditor found that Rent-A-Wreck management does not always comply with its stated policy that sealed bids be used to sell obsolete cars. Records indicated that several vehicles with recent major repairs were sold at negotiated prices. Management vigorously assured the auditor that performing limited repairs and negotiating with knowledgeable buyers resulted in better sales prices than the sealed-bid procedures. Further investigation revealed that the vehicles were sold to employees at prices well below market value. Three managers and five other employees pleaded guilty to criminal charges and made restitution.
a. List the fraud symptoms that should have aroused the auditor’s suspicion.
b. What audit procedures would show that fraud had in fact occurred.
5.7 A bank auditor met with the senior operations manager to discuss a customer’s complaint that an auto loan payment was not credited on time. The customer said the payment was made on May 5, its due date, at a teller’s window using a check drawn on an account in the bank. On May 10, when the customer called for a loan pay-off balance so he could sell the car, he learned that the payment had not been credited to the loan. On May 12, the customer went to the bank to inquire about the payment and meet with the manager. The manager said the payment had been made on May 11. The customer was satisfied because no late charge would have been assessed until May 15. The manager asked whether the auditor was comfortable with this situation.
The auditor located the customer’s paid check and found that it had cleared on May 5. The auditor traced the item back through the computer records and found that the teller had processed the check as being cashed. The auditor traced the payment through the entry records of May 11 and found that the payment had been made with cash instead of a check.
What type of embezzlement scheme does this appear to be, and how does that scheme operate?
5.8 AICPA adapted
a. Prepare a schedule showing how much the cashier embezzled.
a. Describe how the cashier attempted to hide the theft.
5.9 An accountant with the Atlanta Olympic Games was charged with embezzling over $60,000 to purchase a Mercedes-Benz and to invest in a certificate of deposit. Police alleged that he created fictitious invoices from two companies that had contracts with the Olympic Committee: International Protection Consulting and Languages Services. He then wrote checks to pay the fictitious invoices and deposited them into a bank account he had opened under the name of one of the companies. When he was apprehended, he cooperated with police to the extent of telling them of the bogus bank account and the purchase of the Mercedes-Benz and the CD. The accountant was a recent honors graduate from a respected university who, supervisors stated, was a very trusted and loyal employee.
a. How does the accountant fit the profile of a fraudster?
How does he not fit the profile?
b. What fraud scheme did he use to perpetrate his fraud?
c. What controls could have prevented his fraud?
d. What controls could have detected his fraud?
5.10 Lexsteel, a manufacturer of steel furniture, has facilities throughout the United States. Problems with the accounts payable system have prompted Lexsteel’s external auditor to recommend a detailed study to determine the company’s exposure to fraud and to identify ways to improve internal control. Lexsteel’s controller assigned the study to Dolores Smith. She interviewed Accounts Payable employees and created the flowchart of the current system shown in Figure 5-3.
Lexsteel’s purchasing, production control, accounts payable, and cash disbursements functions are centralized at corporate headquarters. The company mainframe at corporate headquarters is linked to the computers at each branch location by leased telephone lines.
The mainframe generates production orders and the bills of material needed for the production runs. From the bills of material, purchase orders for raw materials are generated and e-mailed to vendors. Each purchase order tells the vendor which manufacturing plant to ship the materials to. When the raw materials arrive, the manufacturing plants produce the items on the production orders received from corporate headquarters.
The manufacturing plant checks the goods received for quality, counts them, reconciles the count to the packing slip, and e-mails the receiving data to Accounts Payable. If raw material deliveries fall behind production, each branch manager can send emergency purchase orders directly to vendors. Emergency order data and verification of materials received are e-mailed to Accounts Payable. Since the company employs a computerized perpetual inventory system, periodic physical counts of raw materials are not performed.
Vendor invoices are e-mailed to headquarters and entered by Accounts Payable when received. This often occurs before the branch offices transmit the receiving data. Payments are due 10 days after the company receives the invoices. Using information on the invoice, Data Entry calculates the final day the invoice can be paid, and it is entered as the payment due date.
Once a week, invoices due the following week are printed in chronological entry order on a payment listing, and the corresponding checks are drawn. The checks and payment listing are sent to the treasurer’s office for signature and mailing to the payee. The check number is printed by the computer, displayed on the check and the payment listing, and validated as the checks are signed. After the checks are mailed, the payment listing is returned to Accounts Payable for filing. When there is insufficient cash to pay all the invoices, the treasurer retains certain checks and the payment listing until all checks can be paid. When the remaining checks are mailed, the listing is then returned to Accounts Payable. Often, weekly check mailings include a few checks from the previous week, but rarely are there more than two weekly listings involved.
When Accounts Payable receives the payment listing from the treasurer’s office, the expenses are distributed, coded, and posted to the appropriate cost center accounts. Accounts Payable processes weekly summary performance reports for each cost center and branch location. Adapted from the CMA Examination
1. Discuss three ways Lexsteel is exposed to fraud and recommend improvements to correct these weaknesses.
2. Describe three ways management information could be distorted and recommend improvements to correct these weaknesses.
3. Identify and explain three strengths in Lexsteel’s procedures
5.11 The Association of Certified Fraud Examiners periodically prepares an article called “What Is Your Fraud IQ?” It consists of 10 or more multiple choice questions dealing with various aspects of fraud. The answers, as well as an explanation of each answer, are provided at the end of the article. Visit the Journal of Accountancy site (http://www.journalofaccountancy.com) and search for the articles. Read and answer the questions in three of these articles, and then check your answers.
5.12 Explore the Anti-Fraud and Forensic Accounting portion of the AICPA Web site (http://www.aicpa.org/INTERESTAREAS/FORENSICANDVALUATION/RESOURCES/Pages/default.aspx), and write a two-page report on the three most interesting things you found on the site.
5.1 1. How does Miller fit the profile of the average fraud perpetrator?
2. Explain the three elements of the opportunity triangle (commit, conceal, convert) and discuss how Miller accomplished each when embezzling funds from Associated Communications. What specific concealment techniques did Miller use?
3. What pressures motivated Miller to embezzle? How did Miller rationalize his actions?
4. Miller had a framed T-shirt in his office that said, “He who dies with the most toys wins.” What does this tell you about Miller? What lifestyle red flags could have tipped off the company to the possibility of fraud?
Why do companies hesitate to prosecute white-collar criminals?
What could the victimized companies have done to prevent Miller’s embezzlement?
5.2
1. Figure 5-4 shows the employees and external parties that deal with Heirloom. Explain how Heirloom could defraud the bank and how each internal and external party except the bank could defraud Heirloom.
2. What risk factor, unusual item, or abnormality would alert you to each fraud?
3. What control weaknesses make each fraud possible?
4. Recommend one or more controls to prevent or detect each means of committing fraud.
CHAPTER 6
COMPUTER FRAUD AND ABUSE TECHNIQUES
6.1 When U.S. Leasing (USL) computers began acting sluggishly, computer operators were relieved when a software troubleshooter from IBM called. When he offered to correct the problem they were having, he was given a log-on ID and password. The next morning, the computers were worse. A call to IBM confirmed USL’s suspicion: Someone had impersonated an IBM repairman to gain unauthorized access to the system and destroy the database. USL was also concerned that the intruder had devised a program that would let him get back into the system even after all the passwords were changed.
What techniques might the impostor have employed to breach USL’s internal security?
What could USL do to avoid these types of incidents in the future?</para></question><question id=”ch05ques04? label=”5.4?>
6.2 What motives do people have for hacking? Why has hacking become so popular in recent years? Do you regard it as a crime? Explain your position.
6.3 The UCLA computer lab was filled to capacity when the system slowed and crashed, disrupting the lives of students who could no longer log into the system or access data to prepare for finals. IT initially suspected a cable break or an operating system failure, but diagnostics revealed nothing. After several frustrating hours, a staff member ran a virus detection program and uncovered a virus on the lab’s main server. The virus was eventually traced to the computers of unsuspecting UCLA students. Later that evening, the system was brought back online after infected files were replaced with backup copies.
<para>What conditions made the UCLA system a potential breeding ground for the virus?
What symptoms indicated that a virus was present?
6.1 A few years ago, news began circulating about a computer virus named Michelangelo that was set to “ignite” on March 6, the birthday of the famous Italian artist. The virus attached itself to the computer’s operating system boot sector. On the magical date, the virus would release itself, destroying all of the computer’s data. When March 6 arrived, the virus did minimal damage. Preventive techniques limited the damage to isolated personal and business computers. Though the excitement surrounding the virus was largely illusory, Michelangelo helped the computer-using public realize its systems’ vulnerability to outside attack.
a. What is a computer virus? Cite at least three reasons why no system is completely safe from a computer virus.
b. Why do viruses represent a serious threat to information systems? What damage can a virus do to a computer system?
c. How does a virus resemble a Trojan horse?
d. What steps can be taken to prevent the spread of a computer virus?
6.2 The controller of a small business received the following e-mail with an authentic-looking e-mail address and logo:
From: Big Bank [antifraud@bigbank.com]
To: Justin Lewis, Controller, Small Business USA
Subject: Official Notice for all users of Big Bank!
Due to the increased incidence of fraud and identity theft, we are asking all bank customers to verify their account information on the following Web page: www.antifraudbigbank.com
Please confirm your account information as soon as possible. Failure to confirm your account information will require us to suspend your account until confirmation is made.
A week later, the following e-mail was delivered to the controller:
From: Big Bank [antifraud@bigbank.com]
To: Justin Lewis, Controller, Small Business USA
Subject: Official Notice for all users of Big Bank!
Dear Client of Big Bank,
Technical services at Big Bank is currently updating our software. Therefore, we kindly ask that you access the website shown below to confirm your data. Otherwise, your access to the system may be blocked.
web.da-us.bigbank.com/signin/scripts/login2/user_setup.jsp
We are grateful for your cooperation.
a. What should Justin do about these e-mails?
b. What should Big Bank do about these e-mails?
c. Identify the computer fraud and abuse technique illustrated.
6.3 A purchasing department received the following e-mail.
Dear Accounts Payable Clerk,
You can purchase everything you need online—including peace of mind—when you shop using Random Account Numbers (RAN). RAN is a free service for Big Credit Card customers that substitutes a random credit card number in place of your normal credit card number when you make online purchases and payments. This random number provides you with additional security. Before every online purchase, simply get a new number from RAN to use at each new vendor. Sign up for an account at www.bigcreditcard.com. Also, take advantage of the following features:
Automatic Form automatically completes a vendor’s order form with the RAN, its expiration date, and your shipping and billing addresses.
Set the spending limit and expiration date for each new RAN.
Use RAN once or use it for recurring payments for up to one year.
<para>Explain which computer fraud and abuse techniques could be prevented using a random account number that links to your corporate credit card.
</para></problem>
6.4 <para>Match the internet related computer fraud and abuse technique in the left column with the scenario in the right column. Terms may be used once, more than once, or not at all.</para>
6. 5 <para>Match the data communications-related computer fraud and abuse technique in the left column with the scenario in the right column. Terms may be used once, more than once, or not at all.
</para>
6.6 <para>Match the data related computer fraud and abuse technique in the left column with the scenario in the right column. Terms may be used once, more than once, or not at all.</para>
6.7 <para>Match the data security computer fraud and abuse technique in the left column with the scenario in the right column. Terms may be used once, more than once, or not at all.</para>
6.8 Match the data security computer fraud and abuse technique in the left column with the scenario in the right column. Terms may be used once, more than once, or not at all.</para>
6.9 Identify the computer fraud and abuse technique used in each the following actual examples of computer wrongdoing.
a. A teenage gang known as the “414s” broke into the Los Alamos National Laboratory, Sloan-Kettering Cancer Center, and Security Pacific Bank. One gang member appeared in Newsweek with the caption “Beware: Hackers at play.”
b. Daniel Baas was the systems administrator for a company that did business with Acxiom, who manages customer information for companies. Baas exceeded his authorized access and downloaded a file with 300 encrypted passwords, decrypted the password file, and downloaded Acxiom customer files containing personal information. The intrusion cost Acxiom over $5.8 million.
c. Cyber-attacks left high-profile sites such as Amazon.com, eBay, Buy.com, and CNN Interactive staggering under the weight of tens of thousands of bogus messages that tied up the retail sites’ computers and slowed the news site’s operations for hours.
d. Susan Gilmour-Latham got a call asking why she was sending the caller multiple adult text messages per day. Her account records proved the calls were not coming from her phone. Neither she nor her mobile company could explain how the messages were sent. After finding no way to block the unsavory messages, she changed her mobile number to avoid further embarrassment by association.
e. A federal grand jury in Fort Lauderdale claimed that four executives of a rental-car franchise modified a computer-billing program to add five gallons to the actual gas tank capacity of their vehicles. Over three years, 47,000 customers who returned a car without topping it off ended up paying an extra $2 to $15 for gasoline.
f. A mail-order company programmer truncated odd cents in sales-commission accounts and placed them in the last record in the commission file. Accounts were processed alphabetically, and he created a dummy sales-commission account using the name of Zwana. Three years later, the holders of the first and last sales-commission accounts were honored. Zwana was unmasked and his creator fired.
g. MicroPatent, an intellectual property firm, was notified that their proprietary information would be broadcast on the Internet if they did not pay a $17 million fee. The hacker was caught by the FBI before any damage was done.
h. When Estonia removed a Russian World War II war memorial, Estonian government and bank networks were knocked offline in a distributed DoS attack by Russian hackers. A counterfeit letter of apology for removing the memorial statue was placed on the Web site of Estonia’s prime minister.
i. eBay customers were notified by e-mail that their accounts had been compromised and were being restricted unless they re-registered using an accompanying hyperlink to a Web page that had eBay’s logo, home page design, and internal links. The form had a place for them to enter their credit card data, ATM PINs, Social Security number, date of birth, and their mother’s maiden name. Unfortunately, eBay hadn’t sent the e-mail.
j. A teenager hijacked the eBay.de domain name and several months later the domain name for a large New York ISP. Both hijacked Web sites pointed to a site in Australia.
k. Travelers who logged into the Alpharetta, Georgia, airport’s Internet service had personal information stolen and picked up as many as 45 viruses. A hacker had set up a rogue wireless network with the same name as the airport’s wireless access network.
l. Criminals in Russia used a vulnerability in Microsoft’s server software to add a few lines of Java code to users’ copies of Internet Explorer. The code recorded the users’ keyboard activities, giving the criminals access to usernames and passwords at many banking Web sites. The attacks caused $420 million in damage.
m. America Online subscribers received a message offering free software. Users who opened the attachments unknowingly unleashed a program hidden inside another program that secretly copied the subscriber’s account name and password and forwarded them to the sender.
n. Rajendrasinh Makwana, an Indian citizen and IT contractor who worked at Fannie Mae’s Maryland facility, was terminated at 1:00 P.M. on October 24. Before his network access was revoked, he created a program to wipe out all 4,000 of Fannie Mae’s servers on the following January 31.
o. A man accessed millions of ChoicePoint files by claiming in writing and on the phone to be someone he was not.
p. A 31-year-old programmer unleashed a Visual Basic program by deliberately posting an infected document to an alt.sex Usenet newsgroup using a stolen AOL account. The program evaded security software and infected computers using the Windows operating system and Microsoft Word. On March 26, the Melissa program appeared on thousands of e-mail systems disguised as an important message from a colleague or friend. The program sent an infected e-mail to the first 50 e-mail addresses on the users’ Outlook address book. Each infected computer would infect 50 additional computers, which in turn would infect another 50 computers. The program spread rapidly and exponentially, causing considerable damage. Many companies had to disconnect from the Internet or shut down their e-mail gateways because of the vast amount of e-mail the program was generating. The program caused more than $400 million in damages.
q. Microsoft filed a lawsuit against two Texas firms that produced software that sent incessant pop-ups resembling system warnings. The messages stated “CRITICAL ERROR MESSAGE! REGISTRY DAMAGED AND CORRUPTED” and instructed users to visit a Web site to download Registry Cleaner XP at a cost of $39.95.
r. As many as 114,000 Web sites were tricked into running database commands that installed malicious HTML code redirecting victims to a malicious Web server that tried to install software to remotely control the Web visitors’ computers.
s. Zeus records log-in information when the user of the infected computer logs into a list of target Web sites, mostly banks and other financial institutions. The user’s data is sent to a remote server where it is used and sold by cyber-criminals. The new version of Zeus will significantly increase fraud losses, given that 30% of Internet users bank online.
t. It took Facebook 15 hours to kill a Facebook application that infected millions of PCs with software that displays a constant stream of pop-up ads. The program posted a “Sexiest Video Ever” message on Facebook walls that looked like it came from a friend. Clicking the link led to a Facebook installation screen, where users allowed the software to access their profiles and walls. Once approved, the application told users to download an updated, free version of a popular Windows video player. Instead, it inserted a program that displayed pop-up ads and links. A week later a “Distracting Beach Babes” message did the same thing.
u. Robert Thousand, Jr. discovered he lost $400,000 from his Ameritrade retirement account shortly after he began receiving a flood of phone calls with a 30-second recording for a sex hotline. An FBI investigation revealed that the perpetrator obtained his Ameritrade account information, called Ameritrade to change his phone number, created several VoIP accounts, and used automated dialing tools to flood the dentist’s phones in case Ameritrade called his real number. The perpetrator requested multiple monetary transfers, but Ameritrade would not process them until they reached Thousand to verify them. When the transfers did not go through, the attacker called Ameritrade, gave information to verify that he was Thousand, claimed he had been having phone troubles, and told Ameritrade he was not happy that the transfers had not gone through. Ameritrade processed the transfers, and Thousand lost $400,000.
v. The Internet Crime Complaint Center reports a “hit man” scam. The scammer claims that he has been ordered to assassinate the victim and an associate has been ordered to kill a family member. The only way to prevent the killings is to send $800 so an Islamic expatriate can leave the United States.
w. In an economic stimulus scam, individuals receive a phone call from President Obama telling them to go to a Web site to apply for the funds. To receive the stimulus money, victims have to enter personal identification information, complete an online application, and pay a $28 fee.
6.10 On a Sunday afternoon at a hospital in the Pacific Northwest, computers became sluggish, and documents would not print. Monday morning, the situation became worse when employees logged on to their computers. Even stranger things happened—operating room doors would not open, pagers would not work, and computers in the intensive care unit shut down. By 10:00 A.M., all 50 IT employees were summoned. They discovered that the hospital was under attack by a botnet that exploited a Microsoft operating system flaw and installed pop-up ads on hospital computers. They got access to the first computer on Sunday and used the hospital’s network to spread the infection to other computers. Each infected computer became a zombie that scanned the network looking for new victims. With the network clogged with zombie traffic, hospital communications began to break down. The IT staff tried to halt the attack by shutting off the hospital’s Internet connection, but it was too late. The bots were inside the hospital’s computer system and infecting other computers faster than they could be cleaned. Monday afternoon IT figured out which malware the bots were installing and wrote a script, which was pushed out hourly, directing computers to remove the bad code. The script helped to slow the bots down a bit.
a. What could the hospital do to stop the attack and contain the damage?
b. Which computer fraud and abuse technique did the hackers use in their attack on the hospital?
c. What steps should the hospital have taken to prevent the damage caused by the attack?
Aftermath:
6.1 1. How did Shadowcrew members concealed their identities?
2. How has the Internet made detecting and identifying identity fraudsters difficult?
3. What are some of the most common electronic means of stealing personal
4. What is the most common way that fraudsters use personal data?
5. What measures can consumers take to protect against the online brokering of their personal data?
6. What are the most effective means of detecting identity theft?
7. What pieces of personal information are most valuable to identity fraudsters?
The rest of the story:
CHAPTER 7 CONTROL AND ACCOUNTING INFORMATION SYSTEMS
7.1 Answer the following questions about the audit of Springer’s Lumber & Supply
a. What deficiencies existed in the internal environment at Springer’s?
b. Do you agree with the decision to settle with the Springers rather than to prosecute them for fraud and embezzlement? Why or why not?
c. Should the company have told Jason and Maria the results of the high-level audit? Why or why not?
7.2 Effective segregation of duties is sometimes not economically feasible in a small business. What internal control elements do you think can help compensate for this threat?
7.3 One function of the AIS is to provide adequate controls to ensure the safety of organizational assets, including data. However, many people view control procedures as “red tape.” They also believe that, instead of producing tangible benefits, business controls create resentment and loss of company morale. Discuss this position.
7.4 In recent years, Supersmurf’s external auditors have given clean opinions on its financial statements and favorable evaluations of its internal control systems. Discuss whether it is necessary for this corporation to take any further action to comply with the Sarbanes–Oxley Act.
7.5 When you go to a movie theater, you buy a prenumbered ticket from the cashier. This ticket is handed to another person at the entrance to the movie. What kinds of irregularities is the theater trying to prevent? What controls is it using to prevent these irregularities? What remaining risks or exposures can you identify?
7.6 Some restaurants use customer checks with prenumbered sequence codes. Each food server uses these checks to write up customer orders. Food servers are told not to destroy any customer checks; if a mistake is made, they are to void that check and write a new one. All voided checks are to be turned in to the manager daily. How does this policy help the restaurant control cash receipts?
7.7 Compare and contrast the following three frameworks: COBIT, COSO Integrated Control, and ERM.
7.8 Explain what an event is. Using the Internet as a resource, create a list of some of the many internal and external factors that COSO indicated could influence events and affect a company’s ability to implement its strategy and achieve its objectives.
7.9 Explain what is meant by objective setting and describe the four types of objectives used in ERM.
7.10 Discuss several ways that ERM processes can be continuously monitored and modified so that deficiencies are reported to management.
7.1 You are an audit supervisor assigned to a new client, Go-Go Corporation, which is listed on the New York Stock Exchange. You visited Go-Go’s corporate headquarters to become acquainted with key personnel and to conduct a preliminary review of the company’s accounting policies, controls, and systems. During this visit, the following events occurred:
a. You met with Go-Go’s audit committee, which consists of the corporate controller, treasurer, financial vice president, and budget director.
b. You recognized the treasurer as a former aide to Ernie Eggers, who was convicted of fraud several years ago.
c. Management explained its plans to change accounting methods for depreciation from the accelerated to the straight-line method. Management implied that if your firm does not concur with this change, Go-Go will employ other auditors.
d. You learned that the financial vice president manages a staff of five internal auditors.
e. You noted that all management authority seems to reside with three brothers, who serve as chief executive officer, president, and financial vice president.
f. You were told that the performance of division and department managers is evaluated on a subjective basis, because Go-Go’s management believes that formal performance evaluation procedures are counterproductive.
g. You learned that the company has reported increases in earnings per share for each of the past 25 quarters; however, earnings during the current quarter have leveled off and may decline.
h. You reviewed the company’s policy and procedures manual, which listed policies for dealing with customers, vendors, and employees.
i. Your preliminary assessment is that the accounting systems are well designed and that they employ effective internal control procedures.
j. Some employees complained that some managers occasionally contradict the instructions of other managers regarding proper data security procedures.
k. After a careful review of the budget for data security enhancement projects, you feel the budget appears to be adequate.
l. The enhanced network firewall project appeared to be on a very aggressive implementation schedule. The IT manager mentioned that even if he put all of his personnel on the project for the next five weeks, he still would not complete the project in time. The manager has mentioned this to company management, which seems unwilling to modify the schedule.
m. Several new employees have had trouble completing some of their duties, and they do not appear to know who to ask for help.
n. Go-Go’s strategy is to achieve consistent growth for its shareholders. However, its policy is not to invest in any project unless its payback period is no more than 48 months and yields an internal rate of return that exceeds its cost of capital by 3%.
o. You observe that company purchasing agents wear clothing and exhibit other paraphernalia from major vendors. The purchasing department manager proudly displays a picture of himself holding a big fish on the deck of a luxury fishing boat that has the logo of a major Go-Go vendor painted on its wheelhouse.
7.2 Explain how the principle of separation of duties is violated in each of the following situations. Also, suggest one or more procedures to reduce the risk and exposure highlighted in each example.
a. A payroll clerk recorded a 40-hour workweek for an employee who had quit the previous week. He then prepared a paycheck for this employee, forged her signature, and cashed the check.
b. While opening the mail, a cashier set aside, and subsequently cashed, two checks payable to the company on account.
c. A cashier prepared a fictitious invoice from a company using his brother-in-law’s name. He wrote a check in payment of the invoice, which the brother-in-law later cashed.
d. An employee of the finishing department walked off with several parts from the storeroom and recorded the items in the inventory ledger as having been issued to the assembly department.
e. A cashier cashed a check from a customer in payment of an account receivable, pocketed the cash, and concealed the theft by properly posting the receipt to the customer’s account in the accounts receivable ledger
f. Several customers returned clothing purchases. Instead of putting the clothes into a return bin to be put back on the rack, a clerk put the clothing in a separate bin under some cleaning rags. After her shift, she transferred the clothes to a gym bag and took them home.
g. A receiving clerk noticed that four cases of MP3 players were included in a shipment when only three were ordered. The clerk put the extra case aside and took it home after his shift ended.
h. An insurance claims adjuster had check signing authority of up to $6,000. The adjuster created three businesses that billed the insurance company for work not performed on valid claims. The adjuster wrote and signed checks to pay for the invoices, none of which exceeded $6,000.
i. An accounts payable clerk recorded invoices received from a company that he and his wife owned and authorized their payment.
j. A cashier created false purchase return vouchers to hide his theft of several thousand dollars from his cash register.
k. A purchasing agent received a 10% kickback of the invoice amount for all purchases made from a specific vendor.
7.3 The following description represents the policies and procedures for agent expense reimbursements at Excel Insurance Company.
Agents submit a completed expense reimbursement form to their branch manager at the end of each week. The branch manager reviews the expense report to determine whether the claimed expenses are reimbursable based on the company’s expense reimbursement policy and reasonableness of amount. The company’s policymanual states that agents are to document any questionable expense item and that the branch manager must approve in advance expenditures exceeding $500.
After the expenses are approved, the branch manager sends the expense report to the home office. There, accounting records the transaction, and cash disbursements prepares the expense reimbursement check. Cash disbursements sends the expense reimbursement checks to the branch manager, who distributes them to the agents.
To receive cash advances for anticipated expenses, agents must complete a Cash Advance Approval form. The branch manager reviews and approves the Cash Advance Approval form and sends a copy to accounting and another to the agent. The agent submits the copy of the Cash Advance Approval form to the branch office cashier to obtain the cash advance.
At the end of each month, internal audit at the home office reconciles the expense reimbursements. It adds the total dollar amounts on the expense reports from each branch, subtracts the sum of the dollar totals on each branch’s Cash Advance Approval form, and compares the net amount to the sum of the expense reimbursement checks issued to agents. Internal audit investigates any differences.
Identify the internal control strengths and weaknesses in Excel’s expense reimbursement process. Look for authorization, recording, safeguarding, and reconciliation strengths and weaknesses.
7.3 The Gardner Company, a client of your firm, has come to you with the following problem. It has three clerical employees who must perform the following functions:
a. Maintain the general ledger
b. Maintain the accounts payable ledger
c. Maintain the accounts receivable ledger
d. Prepare checks for signature
e. Maintain the cash disbursements journal
f. Issue credits on returns and allowances
g. Reconcile the bank account
h. Handle and deposit cash receipts
Assuming equal abilities among the three employees, the company asks you to assign the eight functions to them to maximize internal control. Assume that these employees will perform no accounting functions other than the ones listed.
a. List four possible unsatisfactory pairings of the functions
b. State how you would distribute the functions among the three employees. Assume that with the exception of the nominal jobs of the bank reconciliation and the issuance of credits on returns and allowances, all functions require an equal amount of time.
7.5 During a recent review, ABC Corporation discovered that it has a serious internal control problem. It is estimated that the impact associated with this problem is $1 million and that the likelihood is currently 5%. Two internal control procedures have been proposed to deal with this problem. Procedure A would cost $25,000 and reduce likelihood to 2%; procedure B would cost $30,000 and reduce likelihood to 1%. If both procedures were implemented, likelihood would be reduced to 0.1%.
7.6 The management at Covington, Inc., recognizes that a well-designed internal control system provides many benefits. Among the benefits are reliable financial records that facilitate decision making and a greater probability of preventing or detecting errors and fraud. Covington’s internal auditing department periodically reviews the company’s accounting records to determine the effectiveness of internal controls. In its latest review, the internal audit staff found the following eight conditions:
1. Daily bank deposits do not always correspond with cash receipts.
2. Bad debt write-offs are prepared and approved by the same employee.
3. There are occasional discrepancies between physical inventory counts and perpetual inventory records.
4. Alterations have been made to physical inventory counts and to perpetual inventory records.
5. There are many customer refunds and credits.
6. Many original documents are missing or lost. However, there are substitute copies of all missing originals.
7. An unexplained decrease in the gross profit percentage has occurred.
8. Many documents are not approved.
For each of the eight conditions detected by the Covington internal audit staff:
<a. Describe a possible cause of the condition.
b. Recommend actions to be taken and/or controls to be implemented that would correct the condition.
7.7 Consider the following two situations:
For the situations presented, dDescribe the recommendations the internal auditors should make to prevent the following problems.
Situation 1: Many employees of a firm that manufactures small tools pocket some of the tools for their personal use. Since the quantities taken by any one employee are immaterial, the individual employees do not consider the act as fraudulent or detrimental to the company. The company is now large enough to hire an internal auditor. One of the first things she did was to compare the gross profit rates for industrial tools to the gross profit for personal tools. Noting a significant difference, she investigated and uncovered the employee theft.
Situation 2: A manufacturing firm’s controller created a fake subsidiary. He then ordered goods from the firm’s suppliers, told them to ship the goods to a warehouse he rented, and approved the vendor invoices for payment when they arrived. The controller later sold the diverted inventory items, and the proceeds were deposited to the controller’s personal bank account. Auditors suspected something was wrong when they could not find any entries regarding this fake subsidiary office in the property, plant, and equipment ledgers or a title or lease for the office in the real-estate records of the firm
7.8 Tralor Corporation manufactures and sells several different lines of small electric components. Its internal audit department completed an audit of its expenditure processes. Part of the audit involved a review of the internal accounting controls for payables, including the controls over the authorization of transactions, accounting for transactions, and the protection of assets. The auditors noted the following items:
1. Routine purchases are initiated by inventory control notifying the purchasing department of the need to buy goods. The purchasing department fills out a prenumbered purchase order and gets it approved by the purchasing manager. The original of the five-part purchase order goes to the vendor. The other four copies are for purchasing, the user department, receiving for use as a receiving report, and accounts payable.
2. For efficiency and effectiveness, purchases of specialized goods and services are negotiated directly between the user department and the vendor. Company procedures require that the user department and the purchasing department approve invoices for any specialized goods and services before making payment.
3. Accounts payable maintains a list of employees who have purchase order approval authority. The list was updated two years ago and is seldom used by accounts payable clerks.
4. Prenumbered vendor invoices are recorded in an invoice register that indicates the receipt date, whether it is a special order, when a special order is sent to the requesting department for approval, and when it is returned. A review of the register indicated that there were seven open invoices for special purchases, which had been forwarded to operating departments for approval over 30 days previously and had not yet been returned.
5. Prior to making entries in accounting records, the accounts payable clerk checks the mathematical accuracy of the transaction, makes sure that all transactions are properly documented (the purchase order matches the signed receiving report and the vendor’s invoice), and obtains departmental approval for special purchase invoices.
6. All approved invoices are filed alphabetically. Invoices are paid on the 5th and 20th of each month, and all cash discounts are taken regardless of the terms.
7. The treasurer signs the checks and cancels the supporting documents. An original document is required for a payment to be processed.
8. Prenumbered blank checks are kept in a locked safe accessible only to the cash disbursements department. Other documents and records maintained by the accounts payable section are readily accessible to all persons assigned to the section and to others in the accounting function.
RRReview the eight items listed and decide whether they represent an internal control strength or weakness
a. For each internal control strength you identified, explain how the procedure helps achieve good authorization, accounting, or asset protection control.
For each internal control weakness you identified, explain why it is a weakness and recommend a way to correct the weakness
7.7 Lancaster Company makes electrical parts for contractors and home improvement retail stores. After their annual audit, Lancaster’s auditors commented on the following items regarding internal controls over equipment:
1. The operations department that needs the equipment normally initiates a purchase requisition for equipment. The operations department supervisor discusses the proposed purchase with the plant manager. If there are sufficient funds in the requesting department’s equipment budget, a purchase requisition is submitted to the purchasing department once the plant manager is satisfied that the request is reasonable.
2. When the purchasing department receives either an inventory or an equipment purchase requisition, the purchasing agent selects an appropriate supplier and sends them a purchase order.
3. When equipment arrives, the user department installs it. The property, plant, and equipment control accounts are supported by schedules organized by year of acquisition. The schedules are used to record depreciation using standard rates, depreciation methods, and salvage values for each type of fixed asset. These rates, methods, and salvage values were set 10 years ago during the company’s initial year of operation.
4. When equipment is retired, the plant manager notifies the accounting department so the appropriate accounting entries can be made.
5. There has been no reconciliation since the company began operations between the accounting records and the equipment on hand.
Identify the internal control weaknesses in Lancaster’s system, and recommend ways to correct them.
7.10 The Langston Recreational Company (LRC) manufactures ice skates for racing, figure skating, and hockey. The company is located in Kearns, Utah, so it can be close to the Olympic Ice Shield, where many Olympic speed skaters train.
Given the precision required to make skates, tracking manufacturing costs is very important to management so it can price the skates appropriately. To capture and collect manufacturing costs, the company acquired an automated cost accounting system from a national vendor. The vendor provides support, maintenance, and data and program backup service for LRC’s system.
LRC operates one shift, five days a week. All manufacturing data are collected and recorded by Saturday evening so that the prior week’s production data can be processed. One of management’s primary concerns is how the actual manufacturing process costs compare with planned or standard manufacturing process costs. As a result, the cost accounting system produces a report that compares actual costs with standards costs and provides the difference, or variance. Management focuses on significant variances as one means of controlling the manufacturing processes and calculating bonuses.
Occasionally, errors occur in processing a week’s production cost data, which requires the entire week’s cost data to be reprocessed at a cost of $34,500. The current risk of error without any control procedures is 8%. LRC’s management is currently considering a set of cost accounting control procedures that is estimated to reduce the risk of the data errors from 8% to 3%. This data validation control procedure is projected to cost $1,000 per week.
7.11 Spring Water Spa Company is a 15-store chain in the Midwest that sells hot tubs, supplies, and accessories. Each store has a full-time, salaried manager and an assistant manager. The sales personnel are paid an hourly wage and a commission based on sales volume.
The company uses electronic cash registers to record each transaction. The salesperson enters his or her employee number at the beginning of his/her shift. For each sale, the salesperson rings up the order by scanning the item’s bar code, which then displays the item’s description, unit price, and quantity (each item must be scanned). The cash register automatically assigns a consecutive number to each transaction. The cash register prints a sales receipt that shows the total, any discounts, the sales tax, and the grand total.
The salesperson collects payment from the customer, gives the receipt to the customer, and either directs the customer to the warehouse to obtain the items purchased or makes arrangements with the shipping department for delivery. The salesperson is responsible for using the system to determine whether credit card sales are approved and for approving both credit sales and sales paid by check. Sales returns are handled in exactly the reverse manner, with the salesperson issuing a return slip when necessary.
At the end of each day, the cash registers print a sequentially ordered list of sales receipts and provide totals for cash, credit card, and check sales, as well as cash and credit card returns. The assistant manager reconciles these totals to the cash register tapes, cash in the cash register, the total of the consecutively numbered sales invoices, and the return slips. The assistant manager prepares a daily reconciled report for the store manager’s review.
Cash sales, check sales, and credit card sales are reviewed by the manager, who prepares the daily bank deposit. The manager physically makes the deposit at the bank and files the validated deposit slip. At the end of the month, the manager performs the bank reconciliation. The cash register tapes, sales invoices, return slips, and reconciled report are mailed daily to corporate headquarters to be processed with files from all the other stores. Corporate headquarters returns a weekly Sales and Commission Activity Report to each store manager for review.
Please respond to the following questions about Spring Water Spa Company’s operations:
7.12 PriceRight Electronics (PEI) is a small wholesale discount supplier of electronic instruments and parts. PEI’s competitive advantage is its deep-discount, three-day delivery guarantee, which allows retailers to order materials often to minimize in-store inventories. PEI processes its records with stand-alone, incompatible computer systems except for integrated enterprise resource planning (ERP) inventory and accounts receivable modules. PEI decided to finish integrating its operations with more ERP modules, but because of cash flow considerations, this needs to be accomplished on a step-by-step basis.
It was decided that the next function to be integrated should be sales order processing to enhance quick response to customer needs. PEI implemented and modified a commercially available software package to meet PEI’s operations. In an effort to reduce the number of slow-paying or delinquent customers, PEI installed Web-based software that links to the Web site of a commercial credit rating agency to check customer credit at the time of purchase. The following are the new sales order processing system modules:
Sales. Sales orders are received by telephone, fax, e-mail, Web site entry, or standard mail. They are entered into the sales order system by the Sales department. If the order does not cause a customer to exceed his credit limit, the system generates multiple copies of the sales order.
Credit. When orders are received from new customers, the system automatically accesses the credit rating Web site and suggests an initial credit limit. On a daily basis, the credit manager reviews new customer applications for creditworthiness, reviews the suggested credit limits, and accepts or changes the credit limits in the customer database. On a monthly basis, the credit manager reviews the accounts receivable aging report to identify slow-paying or delinquent accounts for potential revisions to or discontinuance of credit. As needed, the credit manager issues credit memos for merchandise returns based on requests from customers and forwards copies of the credit memos to Accounting for appropriate account receivable handling.
Warehousing. Warehouse personnel update the inventory master file for inventory purchases and sales, confirm availability of materials to fill sales orders, and establish back orders for sales orders that cannot be completed from stock on hand. Warehouse personnel gather and forward inventory to Shipping and Receiving along with the corresponding sales orders. They also update the inventory master file for merchandise returned to Receiving.
Shipping and receiving. Shipping and Receiving accepts inventory and sales orders from Warehousing, packs and ships the orders with a copy of the sales order as a packing slip, and forwards a copy of the sales order to Billing. Customer inventory returns are unpacked, sorted, inspected, and sent to Warehousing.
Accounting. Billing prices all sales orders received, which is done approximately 5 days after the order ships. To spread the work effort throughout the month, customers are placed in one of six 30-day billing cycles. Monthly statements, prepared by Billing, are sent to customers during the cycle billing period. Outstanding carry forward balances reported by Accounts Receivable and credit memos prepared by the credit manager are included on the monthly statement. Billing also prepares electronic sales and credit memos for each cycle. Electronic copies of invoices and credit memos are forwarded to Accounts Receivable for entry into the accounts receivable master file by customer account. An aging report is prepared at the end of each month and forwarded to the credit manager. The general accounting office staff access the accounts receivable master file that reflects total charges and credits processed through the accounts receivable system for each cycle. General accounting runs a query to compare this information to the electronic sales and credit memo and posts the changes to the general ledger master file.
7.1 Nino Moscardi, president of Greater Providence Deposit & Trust (GPD&T), received an anonymous note in his mail stating that a bank employee was making bogus loans. Moscardi asked the bank’s internal auditors to investigate the transactions detailed in the note. The investigation led to James Guisti, manager of a North Providence branch office and a trusted 14-year employee who had once worked as one of the bank’s internal auditors. Guisti was charged with embezzling $1.83 million from the bank using 67 phony loans taken out over a three-year period.
Court documents revealed that the bogus loans were 90-day notes requiring no collateral and ranging in amount from $10,000 to $63,500. Guisti originated the loans; when each one matured, he would take out a new loan, or rewrite the old one, to pay the principal and interest due. Some loans had been rewritten five or six times.
The 67 loans were taken out by Guisti in five names, including his wife’s maiden name, his father’s name, and the names of two friends. These people denied receiving stolen funds or knowing anything about the embezzlement. The fifth name was James Vanesse, who police said did not exist. The Social Security number on Vanesse’s loan application was issued to a female, and the phone number belonged to a North Providence auto dealer.
Lucy Fraioli, a customer service representative who cosigned the checks, said Guisti was her supervisor and she thought nothing was wrong with the checks, though she did not know any of the people. Marcia Perfetto, head teller, told police she cashed checks for Guisti made out to four of the five persons. Asked whether she gave the money to Guisti when he gave her checks to cash, she answered, “Not all of the time,” though she could not recall ever having given the money directly to any of the four, whom she did not know.
Guisti was authorized to make consumer loans up to a certain dollar limit without loan committee approvals, which is a standard industry practice. Guisti’s original lending limit was $10,000, the amount of his first fraudulent loan. The dollar limit was later increased to $15,000 and then increased again to $25,000. Some of the loans, including the one for $63,500, far exceeded his lending limit. In addition, all loan applications should have been accompanied by the applicant’s credit history report, purchased from an independent credit rating firm. The loan taken out in the fictitious name would not have had a credit report and should have been flagged by a loan review clerk at the bank’s headquarters.
News reports raised questions about why the fraud was not detected earlier. State regulators and the bank’s internal auditors failed to detect the fraud. Several reasons were given for the failure to find the fraud earlier. First, in checking for bad loans, bank auditors do not examine all loans and generally focus on loans much larger than the ones in question. Second, Greater Providence had recently dropped its computer services arrangement with a local bank in favor of an out-of-state bank. This changeover may have reduced the effectiveness of the bank’s control procedures. Third, the bank’s loan review clerks were rotated frequently, making follow-up on questionable loans more difficult.
Guisti was a frequent gambler and used the embezzled money to pay gambling debts. The bank’s losses totaled $624,000, which was less than the $1.83 million in bogus loans, because Guisti used a portion of the borrowed money to repay loans as they came due. The bank’s bonding company covered the loss.
The bank experienced other adverse publicity prior to the fraud’s discovery. First, the bank was fined $50,000 after pleading guilty to failure to report cash transactions exceeding $10,000, which is a felony. Second, bank owners took the bank private after a lengthy public battle with the State Attorney General, who alleged that the bank inflated its assets and overestimated its capital surplus to make its balance sheet look stronger. The bank denied this charge.
CHAPTER 8
INFORMATION SYSTEM CONTROLS for SYSTEMS RELIABILITY
Part 1: Information Security
8.1 Explain why an organization would want to use all of the following information security controls: firewalls, intrusion prevention systems, intrusion detection systems, and a CIRT.
8.2 What are the advantages and disadvantages of having the person responsible for information security report directly to the chief information officer (CIO), who has overall responsibility for all aspects of the organization’s information systems?
8.3 Reliability is often included in service level agreements (SLAs) when outsourcing. The toughest thing is to decide how much reliability is enough. Consider an application like e-mail. If an organization outsources its e-mail to a cloud provider, what is the difference between 95%, 99%, 99.99%, and 99.9999% reliability?
8.4 What is the difference between authentication and authorization?
8.5 What are the limitations, if any, of relying on the results of penetration tests to assess the overall level of security?
8.6 Security awareness training is necessary to teach employees “safe computing” practices. The key to effectiveness, however, is that it changes employee behavior. How can organizations maximize the effectiveness of their security awareness training programs?
8.7 What is the relationship between COSO, COBIT, and the AICPA’s Trust Services frameworks?
8.1 Match the following terms with their definitions:
Term
Definition
1. Vulnerability
a. Code that corrects a flaw in a program.
2. Exploit
b. Verification of claimed identity.
3. Authentication
c. The firewall technique that filters traffic by comparing the information in packet headers to a table of established connections.
4. Authorization
d. A flaw or weakness in a program.
5. Demilitarized zone (DMZ)
e. A test to determine the time it takes to compromise a system.
6. Deep packet inspection
f. A subnetwork that is accessible from the Internet but separate from the organization’s internal network.
7. router
g. The device that connects the organization to the Internet.
8. social engineering
h. The rules (protocol) that govern routing of packets across networks.
9. firewall
i. The rules (protocol) that govern the division of a large file into packets and subsequent reassembly of the file from those packets.
10. hardening
j. An attack that involves deception to obtain access.
11. CIRT
k. A device that provides perimeter security by filtering packets.
12. patch
l. The set of employees assigned responsibility for resolving problems and incidents.
13. virtualization
m. Restricting the actions that a user is permitted to perform.
14. Transmission Control Protocol (TCP)
n. Improving security by removal or disabling of unnecessary programs and features.
15. static packet filtering
o. A device that uses the Internet Protocol (IP) to send packets across networks.
16. border router
p. A detective control that identifies weaknesses in devices or software.
17. vulnerability scan
q. A firewall technique that filters traffic by examining the packet header of a single packet in isolation.
18. penetration test
r. The process of applying code supplied by a vendor to fix a problem in that vendor’s software.
s. patch management
s. Software code that can be used to take advantage of a flaw and compromise a system.
t. cloud computing
t. A firewall technique that filters traffic by examining not just packet header information but also the contents of a packet.
u. The process of running multiple machines on one physical server.
v. An arrangement whereby a user remotely accesses software, hardware, or other resources via a browser.
8.2 Install and run the latest version of the Microsoft Baseline Security Analyzer on your home computer or laptop. Write a report explaining the weaknesses identified by the tool and how to best correct them. Attach a copy of the MBSA output to your report.
1. Then there is a section about other system information
8.3 The following table lists the actions that various employees are permitted to perform:
8.4 Which preventive, detective, and/or corrective controls would best mitigate the following threats?
An employee’s laptop was stolen at the airport. The laptop contained personally identifying information about the company’s customers that could potentially be used to commit identity theft.
A salesperson successfully logged into the payroll system by guessing the payroll supervisor’s password.
A criminal remotely accessed a sensitive database using the authentication credentials (user ID and strong password) of an IT manager. At the time the attack occurred, the IT manager was logged into the system at his workstation at company headquarters.
An employee received an email purporting to be from her boss informing her of an important new attendance policy. When she clicked on a link embedded in the email to view the new policy, she infected her laptop with a keystroke logger.
A company’s programming staff wrote custom code for the shopping cart feature on its web site. The code contained a buffer overflow vulnerability that could be exploited when the customer typed in the ship-to address.
A company purchased the leading “off-the-shelf” e-commerce software for linking its electronic storefront to its inventory database. A customer discovered a way to directly access the back-end database by entering appropriate SQL code.
Attackers broke into the company’s information system through a wireless access point located in one of its retail stores. The wireless access point had been purchased and installed by the store manager without informing central IT or security.
An employee picked up a USB drive in the parking lot and plugged it into their laptop to “see what was on it,” which resulted in a keystroke logger being installed on that laptop.
Once an attack on the company’s website was discovered, it took more than 30 minutes to determine who to contact to initiate response actions.
To facilitate working from home, an employee installed a modem on his office workstation. An attacker successfully penetrated the company’s system by dialing into that modem.
An attacker gained access to the company’s internal network by installing a wireless access point in a wiring closet located next to the elevators on the fourth floor of a high-rise office building that the company shared with seven other companies.
8.5 What are the advantages and disadvantages of the three types of authentication credentials (something you know, something you have, and something you are)?
8.6 a. Apply the following data to evaluate the time-based model of security for the XYZ Company. Does the XYZ Company satisfy the requirements of the time-based model of security? Why?
Estimated time for attacker to successfully penetrate system = 25 minutes
Estimated time to detect an attack in progress and notify appropriate information security staff = 5 minutes (best case) to 10 minutes (worst case)
Estimated time to implement corrective actions = 6 minutes (best case) to 20 minutes (worst case)
Which of the following security investments to you recommend? Why?
1. Invest $50,000 to increase the estimated time to penetrate the system by 4 minutes
2. Invest $50,000 to reduce the time to detect an attack to between 2 minutes (best case) and 6 minutes (worst case)
3. Invest $50,000 to reduce the time required to implement corrective actions to between 4 minutes (best case) and 14 minutes (worst case).
8.7 Explain how the following items individually and collectively affect the overall level of security provided by using a password as an authentication credential.
a. Length
b. Complexity requirements (which types of characters are required to be used: numbers, alphabetic, case-sensitivity of alphabetic, special symbols like $ or !) –
c. Maximum password age (how often password must be changed)
d. Minimum password age (how long a password must be used before it can be changed)
e. Maintenance of password history (how many prior passwords does system remember to prevent reselection of the same password when required to change passwords)
f. Account lockout threshold (how many failed login attempts before the account is locked)
g. Time frame during which account lockout threshold is applied (i.e., if lockout threshold is five failed login attempts, time frame is whether those 5 failures must occur within 15 minutes, 1 hour, 1 day, etc.).
h. Account lockout duration (how long the account remains locked after exceeding the maximum allowable number of failed login attempts)
8.8 The chapter briefly discussed the following three common attacks against applications
a. Buffer overflows
b. SQL injection
c. Cross-site scripting
Required
Research each of these three attacks and write a report that explains in detail how each attack actually works and that describes suggested controls for reducing the risks that these attacks will be successful.
b. SQL injection
c. Cross-site scripting
8.9 Physical security is extremely important. Read the article “19 Ways to Build Physical Security into a Data Center,” which appeared in the CSO Magazine November 2005. (You can find the article at
http://www.csoonline.com/read/110105/datacenter.html).
Which methods would you expect to find used by almost any major corporation?
Which might likely only be justified at a financial institution?
Case 8.1 Costs of Preventive Security
Firewalls are one of the most fundamental and important security tools. You are likely familiar with the software-based host firewall that you use on your laptop or desktop. Such firewalls should also be installed on every computer in an organization. However, organizations also need corporate-grade firewalls, which are usually, but not always, dedicated special-purpose hardware devices. Conduct some research to identify three different brands of such corporate-grade firewalls and write a report that addresses the following points:
Cost
Technique (deep packet inspection, static packet filtering, or stateful packet filtering)
Ease of configuration and use
Case 8.2 Developing an Information Security Checklist
Obtain a copy of COBIT (available at www.isaca.org) and read section DS5.
Design a checklist for assessing each of the 11 detailed information security control objectives. The checklist should contain questions to which a Yes response represents a control strength, a No response represents a control weakness, plus a possible N/A response.
Provide a brief reason for asking each question. Organize your checklist as follows:
Question
Yes
No
N/A
Reason for asking
1. Is there regular security awareness training?
Training is one of the most important preventive controls because many security incidents happen due to either human error or social engineering.
CHAPTER 9
INFORMATION SYSTEMS CONTROLS FOR SYSTEMS RELIABILITY – Part 2: Confidentiality and Privacy
9.1 From the viewpoint of the customer, what are the advantages and disadvantages to the opt-in versus the opt-out approaches to collecting personal information? From the viewpoint of the organization desiring to collect such information?
9.2 What risks, if any, does offshore outsourcing of various information systems functions pose to satisfying the principles of confidentiality and privacy?
9.3 Should organizations permit personal use of e-mail systems by employees during working hours?
9.4 What privacy concerns might arise from the use of biometric authentication techniques? What about the embedding of RFID tags in products such as clothing? What other technologies might create privacy concerns?
9.5 What do you think an organization’s duty or responsibility should be to protect the privacy of its customers’ personal information? Why?
9.6 Assume you have interviewed for a job online and now receive an offer of employment. The job requires you to move across the country. The company sends you a digital signature along with the contract. How does this provide you with enough assurance to trust the offer so that you are willing to make the move?
9.1 Match the terms with their definitions:
Virtual Private Network (VPN)
a. A hash encrypted with the creator’s private key
Data Loss Prevention (DLP)
b. A company that issues pairs of public and private keys and verifies the identity of the owner of those keys.
Digital signature
c. A secret mark used to identify proprietary information.
Digital certificate
d. An encrypted tunnel used to transmit information securely across the Internet.
Data masking
e. Replacing real data with fake data.
Symmetric encryption
f. Unauthorized use of facts about another person to commit fraud or other crimes.
Spam
g. The process of turning ciphertext into plaintext.
Plaintext
h. Unwanted e-mail.
Hashing
i. A document or file that can be read by anyone who accesses it.
Ciphertext
j. Used to store an entity’s public key, often found on web sites.
Information rights management (IRM)
k. A procedure to filter outgoing traffic to prevent confidential information from leaving.
Certificate authority
l. A process that transforms a document or file into a fixed length string of data.
Non-repudiation
m. A document or file that must be decrypted to be read.
Digital watermark
n. A copy of an encryption key stored securely to enable decryption if the original encryption key becomes unavailable.
Asymmetric encryption
o. An encryption process that uses a pair of matched keys, one public and the other private. Either key can encrypt something, but only the other key in that pair can decrypt it.
Key escrow
p. An encryption process that uses the same key to both encrypt and decrypt.
q. The inability to unilaterally deny having created a document or file or having agreed to perform a transaction.
r. Software that limits what actions (read, copy, print, etc.) that users granted access to a file or document can perform.
9.2 Cost-effective controls to provide confidentiality require valuing the information that is to be protected. This involves classifying information into discrete categories. Propose a minimal classification scheme that could be used by any business, and provide examples of the type of information that would fall into each of those categories.
9.3 Download a hash calculator that can create hashes for both files and text input. Use it to create SHA-256 (or any other hash algorithm your instructor assigns) hashes for the following:
a. A document that contains this text: “Congratulations! You earned an A+”
b. A document that contains this text: “Congratulations! You earned an A-”
c. A document that contains this text: “Congratulations! You earned an a-”
d. A document that contains this text: “Congratulations! You earned an A+” (this message contains two spaces between the exclamation point and the capital letter Y).
e. Make a copy of the document used in step a, and calculate its hash value.
f. Hash any multiple-page text file on your computer.
9.4 Accountants often need to print financial statements with the words “CONFIDENTIAL” or “DRAFT” appearing in light type in the background.
a. Create a watermark with the word “CONFIDENTIAL” in a Word document. Print out a document that displays that watermark.
b. Create the same watermark in Excel and print out a spreadsheet page that displays that watermark.
c. Can you make your watermark “invisible” so that it can be used to detect whether a document containing sensitive information has been copied to an unauthorized location? How? How could you use that “invisible” watermark to detect violation of copying policy?
9.5 Create a spreadsheet to compare current monthly mortgage payments versus the new monthly payments if the loan were refinanced, as shown (you will need to enter formulas into the two cells with solid borders like a box: D9 and D14)
a. Restrict access to the spreadsheet by encrypting it.
Further protect the spreadsheet by limiting users to only being able to select and enter data in the six cells without borders.
9.6 Research the information rights management software that may be available for your computer. What are its capabilities for limiting access rights? Write a report of your findings.
Optional: If you can download and install IRM software, use it to prevent anyone from being able to copy or print your report.
9.7 The principle of confidentiality focuses on protecting an organization’s intellectual property. The flip side of the issue is ensuring that employees respect the intellectual property of other organizations. Research the topic of software piracy and write a report that explains:
a. What software piracy is.
b. How organizations attempt to prevent their employees from engaging in software piracy.
c. How software piracy violations are discovered.
d. The consequences to both individual employees and to organizations who commit software piracy.
9.8 Practice encryption.
Required:
a. Use your computer operating system’s built-in encryption capability to encrypt a file.
In Windows, if you are working with an open document, you can encrypt it by choosing that option under the “Prepare” menu:
b. TrueCrypt is one of several free software programs that can be used to encrypt files stored on a USB drive. Download and install a copy of TrueCrypt (or another program recommended by your professor). Use it to encrypt some files on a USB drive. Compare its functionality to that of the built-in encryption functionality provided by your computer’s operating system.
9.9 Research the problem of identity theft and write a report that explains:
a. Whether the problem of identity theft is increasing or decreasing
b. What kind of identity theft protection services or insurance products are available. Compare and contrast at least two products.
9.10 Certificate authorities are an important part of a public key infrastructure (PKI). Research at least two certificate authorities and write a report that explains the different types of digital certificates that they offer.
9.11 Obtain a copy of COBIT (available at www.isaca.org) and read the control objectives that relate to encryption (DS5.8 and DS5.11). What are the essential control procedures that organizations should implement when using encryption?
SUGGESTED SOLUTIONS TO THE CASES
Case 9-1 Protecting Privacy of Tax Returns
The department of taxation in your state is developing a new computer system for processing individual and corporate income-tax returns. The new system features direct data input and inquiry capabilities. Identification of taxpayers is provided by using the Social Security number for individuals and federal tax identification number for corporations. The new system should be fully implemented in time for the next tax season.
The new system will serve three primary purposes:
1 Data will either be automatically input directly into the system if the taxpayer files electronically or by a clerk at central headquarters scanning a paper return received in the mail.
2 The returns will be processed using the main computer facilities at central headquarters. Processing will include four steps:
a. Verifying mathematical accuracy
b. Auditing the reasonableness of deductions, tax due, and so on, through the use of edit routines, which also include a comparison of current and prior years’ data.
c. Identifying returns that should be considered for audit by department revenue agents
d. Issuing refund checks to taxpayers
3 Inquiry services. A taxpayer will be allowed to determine the status of his or her return or get information from the last three years’ returns by calling or visiting one of the department’s regional offices, or by accessing the department’s web site and entering their social security number.
The state commissioner of taxation and the state attorney general are concerned about protecting the privacy of personal information submitted by taxpayers. They want to have potential problems identified before the system is fully developed and implemented so that the proper controls can be incorporated into the new system.
Required
Describe the potential privacy problems that could arise in each of the following three areas of processing, and recommend the corrective action(s) to solve each problem identified:
a. Data input
b. Processing of returns
c. Data inquiry
Case 9-2 Generally Accepted Privacy Principles
Obtain the practitioner’s version of Generally Accepted Privacy Principles from the AICPA’s web site (www.aicpa.org). You will find it located under professional resources and then information technology. Use it to answer the following questions:
1. What is the difference between confidentiality and privacy?
2. How many categories of personal information exist? Why?
3. In terms of the principle of choice and consent, what does GAPP recommend concerning opt-in versus opt-out?
4. Can organizations outsource their responsibility for privacy?
5. What does principle 1 state concerning top management’s and the Board of Directors’ responsibility for privacy?
6. What does principle 1 state concerning the use of customers’ personal information when testing new applications?
7. Obtain a copy of your university’s privacy policy statement. Does it satisfy GAPP criterion 2.2.3? Why?
8. What does GAPP principle 3 say about the use of cookies?
9. What are some examples of practices that violate management criterion 4.2.2?
10. What does management criterion 5.2.2 state concerning retention of customers’ personal information? How can organizations satisfy this criterion?
11. What does management criterion 5.2.3 state concerning the disposal of personal information? How can organizations satisfy this criterion?
12. What does management criterion 6.2.2 state concerning access? What controls should organizations use to achieve this objective?
13. According to GAPP principle 7, what should organizations do if they wish to share personal information they collect with a third party?
14. What does GAPP principle 8 state concerning the use of encryption?
15. What is the relationship between GAPP principles 9 and 10?
CHAPTER 10
INFORMATION SYSTEMS CONTROLS FOR SYSTEMS RELIABILITY – PART 3: PROCESSING INTEGRITY AND AVAILABILITY
10.1 Two ways to create processing integrity controls in Excel spreadsheets are to use the built-in Data Validation tool or to write custom code with IF statements. What are the relative advantages and disadvantages of these two approaches?
10.2 What is the difference between using check digit verification and a validity check to test the accuracy of an account number entered on a transaction record?
10.3 For each of the three basic options for replacing IT infrastructure (cold sites, hot sites, and real-time mirroring) give an example of an organization that could use that approach as part of its DRP. Be prepared to defend your answer.
10.4 Use the numbers 10–19 to show why transposition errors are always divisible by 9.
10.5 What are some business processes for which an organization might use batch processing?
10.6 Why do you think that surveys continue to find that a sizable percentage of organizations either do not have formal disaster recovery and business continuity plans or have not tested and revised those plans for more than a year?
10.1 Match the following terms with their definitions:
1. business continuity plan (BCP)
a. A file used to store information for long periods of time.
2. completeness check
b. A plan that describes how to resume IT functionality after a disaster.
3. hash total
c. An application control that verifies that the quantity ordered is greater than 0.
4. incremental daily backup
d. A control that verifies that all data was transmitted correctly by counting the number of odd or even bits.
5. archive
e. An application control that tests whether a customer is 18 or older.
6. field check
f. A daily backup plan that copies all changes since the last full backup.
7. sign check
g. A disaster recovery plan that contracts for use of an alternate site that has all necessary computing and network equipment, plus Internet connectivity.
8. change control
h. A disaster recovery plan that contracts for use of another company’s information system.
9. cold site
i. A disaster recovery plan that contracts for use of an alternate site that is pre-wired for Internet connectivity but has no computing or network equipment.
10. limit check
j. An application control that ensures that a customer’s ship-to address is entered in a sales order.
11. zero-balance test
k. An application control that makes sure an account does not have a balance after processing.
12. recovery point objective (RPO)
l. An application control that compares the sum of a set of columns to the sum of a set of rows.
13. recovery time objective (RTO)
m. A measure of the length of time that an organization is willing to function without its information system.
14. record count
n. The amount of data an organization is willing to re-enter or possibly lose in the event of a disaster.
15. validity check
o. A batch total that does not have any intrinsic meaning.
16. check digit verification
p. A batch total that represents the number of transactions processed.
17. closed-loop verification
q. An application control that validates the correctness of one data item in a transaction record by comparing it to the value of another data item in that transaction record.
18. parity checking
r. An application control that verifies that an account number entered in a transaction record matches an account number in the related master file.
19. reasonableness test
s. A plan that describes how to resume business operations after a major calamity, like Hurricane Katrina, that destroys not only an organization’s data center but also its headquarters.
20. financial total
t. A data-entry application control that verifies the accuracy of an account number by recalculating the last number as a function of the preceding numbers.
21. turnaround document
u. A daily backup procedure that copies only the activity that occurred on that particular day.
v. A data-entry application control that could be used to verify that only numeric data is entered into a field.
w. A plan to ensure that modifications to an information system do not reduce its security.
x. A data-entry application control that displays the value of a data item and asks the user to verify that the system has accessed the correct record.
y. A batch total that represents the total dollar value of a set of transactions.
z. A document sent to an external party and subsequently returned so that preprinted data can be scanned rather than manually reentered.
10.2 Excel Problem
Enter the following data into a spreadsheet and then perform the following tasks:
Employee Number
Pay rate
Hours worked
Gross Pay
Deductions
Net pay
12355
10.55
38
400.90
125.00
275.90
2178g
11.00
40
440.00
395.00
45.00
24456
95.00
90
8550.00
145.00
8405.00
34567
10.00
40
400.00
105.00
505.00
a. Calculate examples of these batch totals:
A hash total
A financial total
A record count
b. Assume the following rules govern normal data:
Employee numbers are five-digits in length and range from 10000 through 99999.
Maximum pay rate is $25, and minimum is $9.
Hours worked should never exceed 40.
Deductions should never exceed 40% of gross pay.
Give a specific example of an error or probable error in the data set that each of the following controls would detect:
Field check
Limit check
Reasonableness test
Cross-footing balance test
c. Create a control procedure that would prevent, or at least detect, each of the errors in the data set.
Employee number not numeric
10.3 Excel Problem
The Moose Wings Cooperative Flight Club owns a number of airplanes and gliders. It serves fewer than 2,000 members, who are numbered sequentially from the founder, Tom Eagle (0001), to the newest member, Jacques Noveau (1368). Members rent the flying machines by the hour, and all must be returned on the same day. The following six records were among those entered for the flights taken on September 1, 2010:
Member #
Flight Date
MM/DD/YY
Plane Used
Takeoff time
Landing time
1234
09/10/10
G
6:25
8:46
4111
09/01/10
C
8:49
10:23
1210
09/01/10
P
3:42
5:42
0023
09/01/10
X
1:59
12:43
012A
09/01/10
P
12:29
15:32
0999
09/01/10
L
15:31
13:45
Valid plane codes (plane used column): C = Cessna, G = glider, L = Lear Jet, P = Piper Cub)
a. Identify and describe any errors in the data.
b. For each of the five data fields, suggest one or more input edit controls that could be used to detect input errors.
Enter the data in a spreadsheet and create appropriate controls to prevent or at least detect the input errors.
d. Suggest other controls to minimize the risk of input errors.
10.4 The first column in Table 10-3 lists transaction amounts that have been summed to obtain a batch total. Assume that all data in the first column are correct. Cases a through d each contain an input error in one record, along with a batch total computed from that set of records.
For each case (a-d), compute the difference between the correct and erroneous batch totals and explain how this difference could help identify the cause of the error.
10.5 Excel Problem
Create a spreadsheet with the following columns:
Plaintext character
ASCII code (7-bits, binary number)
First bit
Second bit
Third bit
Fourth bit
Fifth bit
Sixth bit
Seventh bit
Number of bits with value = 1
Parity bit for odd parity coding
Parity bit for even parity coding
a. Enter the 26 letters a-z (lowercase) and the ten digits (0-9) in the plaintext column
b. The ASCII column should convert the plaintext character to the binary code used by your computer.
c. The next seven columns should each display one bit of the ASCII code, beginning with the leftmost digit. (Hint: Excel provides text functions that can select individual characters from a string).
d. The tenth column should sum the number of bits that have the value ‘1’. (Hint: the text functions used to populate columns 3-9 return a text string that you will need to convert to a numeric value).
e. The eleventh column should have a 1 if the number in the tenth column is odd and 0 if the number in the tenth column is even.
f. The twelfth column should have a 1 if the number in the tenth column is even and a 0 if the number in the tenth column is odd.
10.6 The ABC Company is considering the following options for its backup plan:
1. Daily full backups:
Time to perform backup = 60 minutes
Size of backup = 50 GB
Time to restore from backup = 30 minutes
2. Weekly full backups plus daily incremental backup:
Same time, storage, and restoration as above to do a weekly backup on Friday, plus
Time to perform daily backup = 10 minutes
Size of daily backup = 10 GB
Time to restore each daily backup file = 5 minutes
3. Weekly full backups plus daily differential backup:
Same time, storage, and restoration as above to do a weekly backup on Friday, plus
Time to perform daily backup = 10 minutes first day, growing by 5 minutes each day thereafter
Size of daily backup = 10 GB first day, growing by 10 GB each day
Time to restore differential backup file = 5 minutes first day, increasing by 2 minutes each subsequent day
Which approach would you recommend? Why?
10.7 Which control(s) would best mitigate the following threats?
a. The hours worked field in a payroll transaction record contained the value 400 instead of 40. As a result, the employee received a paycheck for $6,257.24 instead of $654.32.
b. The accounts receivable file was destroyed because it was accidentally used to update accounts payable.
c. During processing of customer payments, the digit 0 in a payment of $204 was mistakenly typed as the letter “O.” As a result, the transaction was not processed correctly and the customer erroneously received a letter that the account was delinquent.
d. </inst>A salesperson mistakenly entered an online order for 50 laser printers instead of 50 laser printer toner cartridges.
e. A 20-minute power brownout caused a mission-critical database server to crash, shutting down operations temporarily.
f. A fire destroyed the data center, including all backup copies of the accounts receivable files.
g. After processing sales transactions, the inventory report showed a negative quantity on hand for several items.
h. A customer order for an important part did not include the customer’s address. Consequently, the order was not shipped on time and the customer called to complain.
i. When entering a large credit sale, the clerk typed in the customer’s account number as 45982 instead of 45892. That account number did not exist. The mistake was not caught until later in the week when the weekly billing process was run. Consequently, the customer was not billed for another week, delaying receipt of payment.
i. A visitor to the company’s Web site entered 400 characters into the five-digit Zip code field, causing the server to crash.
j. Two traveling sales representatives accessed the parts database at the same time. Salesperson A noted that there were still 55 units of part 723 available and entered an order for 45 of them. While salesperson A was keying in the order, salesperson B, in another state, also noted the availability of 55 units for part 723 and entered an order for 33 of them. Both sales reps promised their customer next-day delivery. Salesperson A’s customer, however, learned the next day that the part would have to be back-ordered. The customer canceled the sale and vowed to never again do business with the company.
k. The warranty department manager was upset because special discount coupons were mailed to every customer who had purchased the product within the past 3 years, instead of to only those customers who had purchased the product within the past 3 months.
The clerk entering details about a large credit sale mistakenly typed in a nonexistent account number. Consequently, the company never received payment for the items.
l. A customer filled in the wrong account number on the portion of the invoice being returned with payment. Consequently, the payment was credited to another customer’s account.
m. A batch of 73 time sheets was sent to the payroll department for weekly processing. Somehow, one of the time sheets did not get processed. The mistake was not caught until payday, when one employee complained about not receiving a paycheck.
q. Sunspot activity resulted in the loss of some data being sent to the regional office. The problem was not discovered until several days later when managers attempted to query the database for that information.
10.8 MonsterMed Inc. (MMI) is an online pharmaceutical firm. MMI has a small systems staff that designs and writes MMI’s customized software. The data center is installed in the basement of its two-story headquarters building. The data center is equipped with halon-gas fire suppression equipment and an uninterruptible power supply system.
The computer operations staff works a two-shift schedule, five days per week. MMI’s programming staff, located in the same building, has access to the data center and can test new programs and program changes when the operations staff is not available. Programmers make changes in response to oral requests by employees using the system. Since the programming staff is small and the work demands have increased, systems and programming documentation is developed only when time is available. Backups are made whenever time permits. The backup files are stored in a locked cabinet in the data center. Unfortunately, due to several days of heavy rains, MMI’s building recently experienced serious flooding that destroyed not only the computer hardware but also all the data and program files that were on-site.
a. Identify at least five weaknesses in MonsterMed Inc.’s backup and DRP procedures.
b. Evaluate change controls at MonsterMed Inc.
10.9 Excel Problem
Create data validation rules in a spreadsheet to perform each of the following controls:
a. Limit check – that values in the cell are < 70
b. Range check – that values in the cell are between 15 and 65
c. Sign check – that values in the cell are positive
d. Field check – that values in a cell are only numeric
e. Size check – that cell accepts no more than 40 characters of text
f. Reasonableness check – that cell’s value is less than 75% of cell to its left
g. Validity check – that a value exists in a list of allowable values
10.10 Excel Problem
Creating and testing check digits.
a. Create a spreadsheet that will take as input a five-digit account number and calculate a check digit using this formula: (5 x left-most digit + 4 x next digit + 3 x third digit + 2 x fourth digit + fifth digit) modulus division by 7. (Modulus division returns the remainder – for example: 11 modulus division by 3 = 2). The check digit then becomes the 6th (right-most) digit in the account number. Your spreadsheet should look like this:
b. Add another panel to the spreadsheet that takes as input a six-digit account number and uses the check digit formula in part a to test whether or not the account number is valid. Your solution should look like this:
10. 11 For each of the following scenarios, determine whether the company’s current backup procedures enable it to meet its recovery objectives and explain why:
a. Scenario 1:
Recovery point objective = 24 hours
Daily backups at 3:00 am, process takes 2 hours
Copy of backup tapes picked up daily at 8:00 am for storage off-site
b. Scenario 2: Company makes daily incremental backups Monday-Saturday at 7:00 pm each night. Company makes full backup weekly, on Sunday at 1:00 pm.
Recovery time objective = 2 hours
Time to do full backup = 3 hours
Time to restore from full backup = 1 hour
Time to make incremental daily backup = 1 hour
Time to restore each incremental daily backup = 30 minutes
c. Scenario 3: Company makes daily differential backups Monday-Friday at 8:00 p.m each night. Company makes full backup weekly, on Saturdays, at 8:00 am.
Recovery time objective = 6 hours
Time to do full backup = 4 hours
Time to restore from full backup = 3 hours
Time to do differential daily backups = 1 hour on Monday, increasing by 30 minutes each successive day
Time to restore differential daily backup = 30 minutes for Monday, increasing by 15 minutes each successive day
Case 10-1 Ensuring Systems Availability
The Journal of Accountancy (available at www.aicpa.org) has published a series of articles that address different aspects of disaster recovery and business continuity planning:
Gerber, J. A., and Feldman, E. R. 2002. “Is Your Business Prepared for the Worst?” Journal of Accountancy (April): 61-64.
McCarthy, E. 2004. “The Best-Laid Plans,” Journal of Accountancy (May): 46-54.
Myers, R. 2006. “Katrina’s Harsh Lessons,” Journal of Accountancy (June): 54-63.
Phelan, S., and Hayes, M. 2003. “Before the Deluge – and After,” Journal of Accountancy (April): 57-66.
Read one or more of the following articles that your professor assigns plus section DS4 of COBIT version 4.1 (available at www.isaca.org) to answer the following questions:
1. What does COBIT suggest as possible metrics for evaluating how well an organization is achieving the objective of DS4? Why do you think that metric is useful?
2. For each article assigned by your professor, complete the following table, summarizing what each article said about a specific COBIT control objective (an article may not address all 10 control objectives in DS4):
Case 10-2 Change Controls
Read section AI6 in version 4.1 of COBIT (available at www.isaca.org) and answer the following questions:
1. What is the purpose of each detailed control objective – why is it important?
AI6.1 Change Standards and Procedures
AI6.3 Emergency Changes
AI6.4 Change Status Tracking and Reporting
AI6.5 Change Closure and Documentation
2. How is each of the suggested metrics useful?
CHAPTER 11
AUDITING COMPUTER-BASED INFORMATION SYSTEMS
11.1 Auditing an AIS effectively requires that an auditor have some knowledge of computers and their accounting applications. However, it may not be feasible for every auditor to be a computer expert. Discuss the extent to which auditors should possess computer expertise to be effective auditors.
11.2 Should internal auditors be members of systems development teams that design and implement an AIS? Why or why not?
11.3 <para>At present, no Berwick employees have auditing experience. To staff its new internal audit function, Berwick could (a) train some of its computer specialists in auditing, (b) hire experienced auditors and train them to understand Berwick’s information system, (c) use a combination of the first two approaches, or (d) try a different approach. Which approach would you support, and why?
</para></question><question id=”ch09ques14? label=”9.4?>
11.4 The assistant finance director for the city of Tustin, California, was fired after city officials discovered that she had used her access to city computers to cancel her daughter’s $300 water bill. An investigation revealed that she had embezzled a large sum of money from Tustin in this manner over a long period. She was able to conceal the embezzlement for so long because the amount embezzled always fell within a 2% error factor used by the city’s internal auditors. What weaknesses existed in the audit approach? How could the audit plan be improved? What internal control weaknesses were present in the system? Should Tustin’s internal auditors have discovered this fraud earlier?
11.5 Lou Goble, an internal auditor for a large manufacturing enterprise, received an anonymous note from an assembly-line operator who has worked at the company’s West Coast factory for the past 15 years. The note indicated that there are some fictitious employees on the payroll as well as some employees who have left the company. He offers no proof or names. What computer-assisted audit technique could Lou use to help him substantiate or refute the employee’s claim?
11.6. Explain the four steps of the risk-based audit approach, and discuss how they apply to the overall security of a company.
11.7. Compare and contrast the frameworks for auditing program development/acquisition and for auditing program modification.
11.1 You are the director of internal auditing at a university. Recently, you met with Issa Arnita, the manager of administrative data processing, and expressed the desire to establish a more effective interface between the two departments. Issa wants your help with a new computerized accounts payable system currently in development. He recommends that your department assume line responsibility for auditing suppliers’ invoices prior to payment. He also wants internal auditing to make suggestions during system development, assist in its installation, and approve the completed system after making a final review.
<para>Would you accept or reject each of the following? Why?</para>
a. The recommendation that your department be responsible for the pre-audit of supplier’s invoices.
b. The request that you make suggestions during system development.
c. The request that you assist in the installation of the system and approve the system after making a final review.
11.2 As an internal auditor for the Quick Manufacturing Company, you are participating in the audit of the company’s AIS. You have been reviewing the internal controls of the computer system that processes most of its accounting applications. You have studied the company’s extensive systems documentation. You have interviewed the information system manager, operations supervisor, and other employees to complete your standardized computer internal control questionnaire. You report to your supervisor that the company has designed a successful set of comprehensive internal controls into its computer systems. He thanks you for your efforts and asks for a summary report of your findings for inclusion in a final overall report on accounting internal controls.
<para>Have you forgotten an important audit step? Explain. List five examples of specific audit procedures that you might recommend before reaching a conclusion.
</para></problem>
11.3 As an internal auditor, you have been assigned to evaluate the controls and operation of a computer payroll system. To test the computer systems and programs, you submit independently created test transactions with regular data in a normal production run.
<orderedlist numeration=”loweralpha” inheritnum=”ignore” type=”ll” continuation=”restarts”><listitem><para><inst></inst>List four advantages and two disadvantages of this technique.</para></listitem>
<listitem><para><ins</i</para></listitem></orderedlist>
11.4 You are involved in the audit of accounts receivable, which represent a significant portion of the assets of a large retail corporation. Your audit plan requires the use of the computer, but you encounter the following reactions:
<para><para>For each situation, state how the auditor should proceed with the accounts receivable audit.</para>
a. The computer operations manager says the company’s computer is running at full capacity for the foreseeable future and the auditor will not be able to use the system for audit tests.</para></listitem>
b. The computer scheduling manager suggests that your computer program be stored in the computer program library so that it can be run when computer time becomes available.
c. You are refused admission to the computer room.</para></listitem>
d. The systems manager tells you that it will take too much time to adapt the auditor’s computer audit program to the computer’s operating system and that company programmers will write the programs needed for the audit.
11.5 You are a manager for the CPA firm of Dewey, Cheatem, and Howe (DC&H). While reviewing your staff’s audit work papers for the state welfare agency, you find that the test data approach was used to test the agency’s accounting software. A duplicate program copy, the welfare accounting data file obtained from the computer operations manager, and the test transaction data file that the welfare agency’s programmers used when the program was written were processed on DC&H’s home office computer. The edit summary report listing no errors was included in the working papers, with a notation by the senior auditor that the test indicates good application controls. You note that the quality of the audit conclusions obtained from this test is flawed in several respects, and you decide to ask your subordinates to repeat the test.
Identify three existing or potential problems with the way this test was performed. For each problem, suggest one or more procedures that might be performed during the revised test to avoid flaws in the audit conclusions.
</para></problem>
11.6 You are performing an information system audit to evaluate internal controls in Aardvark Wholesalers’ (AW) computer system. From an AW manual, you have obtained the following job descriptions for key personnel:
Director of information systems: Responsible for defining the mission of the information systems division and for planning, staffing, and managing the IS department.
Manager of systems development and programming: Reports to director of informationsystems. Responsible for managing the systems analysts and programmerswho design, program, test, implement, and maintain the data processing systems.Also responsible for establishing and monitoring documentation standards.
Manager of operations: Reports to director of information systems. Responsible for management of computer center operations, enforcement of processing standards, and systems programming, including implementation of operating system upgrades.
Data entry supervisor: Reports to manager of operations. Responsible for supervisionof data entry operations and monitoring data preparation standards.
Operations supervisor: Reports to manager of operations. Responsible for supervisionof computer operations staff and monitoring processing standards.
Data control clerk: Reports to manager of operations. Responsible for logging and distributing computer input and output, monitoring source data control procedures, and custody of programs and data files.
Name two positive and two negative aspects (from an internal control standpoint) of this organizational structure.
c. What additional information would you require before making a final judgment on the adequacy of AW’s separation of functions in the information systems division?
11.7 Robinson’s Plastic Pipe Corporation uses a data processing system for inventory. The input to this system is shown in Table 11-7. You are using an input controls matrix to help audit the source data controls.
<para>Prepare an input controls matrix using the format and input controls shown in <link linkend=”ch09fig03? preference=”0?>Figure 11-3<xref linkend=”ch09fig03? label=”9-3?/></link>; however, replace the field names shown in <link linkend=”ch09fig03? preference=”0?>Figure 11-3<xref linkend=”ch09fig03? label=”9-3?/></link> with those shown in <link linkend=”ch09table08? preference=”0?>Table 11-7<xref linkend=”ch09table08? label=”9-8?/></link>. Place checks in the matrix cells that represent input controls you might expect to find for each field.
</para></problem>
11.8 As an internal auditor for the state auditor’s office, you are assigned to review the implementation of a new computer system in the state welfare agency. The agency is installing an online computer system to maintain the state’s database of welfare recipients. Under the old system, applicants for welfare assistance completed a form giving their name, address, and other personal data, plus details about their income, assets, dependents, and other data needed to establish eligibility. The data are checked by welfare examiners to verify their authenticity, certify the applicant’s eligibility for assistance, and determine the form and amount of aid.
Under the new system, welfare applicants enter data on the agency’s Web site or give their data to clerks, who enter it using online terminals. Each applicant record has a “pending” status until a welfare examiner can verify the authenticity of the data used to determine eligibility. When the verification is completed, the examiner changes the status code to “approved,” and the system calculates the aid amount.
Periodically, recipient circumstances (income, assets, dependents, etc.) change, and the database is updated. Examiners enter these changes as soon as their accuracy is verified, and the system recalculates the recipient’s new welfare benefit. At the end of each month, payments are electronically deposited in the recipient’s bank accounts.
Welfare assistance amounts to several hundred million dollars annually. You are concerned about the possibilities of fraud and abuse.
a. Describe how to employ concurrent audit techniques to reduce the risks of fraud and abuse.
b. Describe how to use computer audit software to review the work welfare examiners do to verify applicant eligibility data. Assume that the state auditor’s office has access to other state and local government agency databases.</para></listitem></orderedlist></problem>
11.9 Melinda Robinson, the director of internal auditing at Sachem Manufacturing Company, believes the company should purchase software to assist in the financial and procedural audits her department conducts. Robinson is considering the following software packages:
A generalized audit software package to assist in basic audit work, such as the retrieval of live data from large computer files. The department would review this information using conventional audit investigation techniques. The department could perform criteria selection, sampling, basic computations for quantitative analysis, record handling, graphical analysis, and print output (i.e., confirmations).
An ITF package that uses, monitors, and controls dummy test data processed by existing programs. It also checks the existence and adequacy of data entry and processing controls.
A flowcharting package that graphically presents the flow of information through a system and pinpoints control strengths and weaknesses.
A parallel simulation and modeling package that uses actual data to conduct the same tests using a logic program developed by the auditor. The package can also be used to seek answers to difficult audit problems (involving many comparisons) within statistically acceptable confidence limits.
a. Without regard to any specific computer audit software, identify the general advantages of using computer audit software to assist with audits.
b. Describe the audit purpose facilitated and the procedural steps to be followed by the internal auditor in using the following:</para>
<itemizedlist mark=”bull” type=”bl”><listitem><inst><listitem><inst> </inst><para>Generalized audit software package.
Flowcharting package
Parallel simulation and modeling package
11.10 The fixed-asset master file at Thermo-Bond includes the following data items: <para>
Asset number Date of retirement (99/99/2099 for assets still in service)
Description Depreciation method code
Type code Depreciation rate
Location code Useful life (years)
Date of acquisition Accumulated depreciation at beginning of year
Original cost Year-to-date depreciation
E<para>xplain several ways auditors can use computer audit software in performing a financial audit of Thermo-Bond’s fixed assets.</para></problem>
11.11 You are auditing the financial statements of a cosmetics distributor that sells thousands of individual items. The distributor keeps its inventory in its distribution center and in two public warehouses. At the end of each business day, it updates its inventory file, whose records contain the following data:
Item number Cost per item
Item description Date of last purchase
Quantity-on-hand Date of last sale
Item location Quantity sold during year
You will use audit software to examine inventory data as of the date of the distributor’s physical inventory count. You will perform the following audit procedures:
1. Observe the distributor’s physical inventory count at year-end and test a sample for accuracy.
2. Compare the auditor’s test counts with the inventory records.
3. Compare the company’s physical count data with the inventory records.
4. Test the mathematical accuracy of the distributor’s final inventory valuation.
5. Test inventory pricing by obtaining item costs from buyers, vendors, or other sources.
6. Examine inventory purchase and sale transactions on or near the year-end date to verify that all transactions were recorded in the proper accounting period.
7. Ascertain the propriety of inventory items located in public warehouses.
8. Analyze inventory for evidence of possible obsolescence.
9. Analyze inventory for evidence of possible overstocking or slow-moving items.
10. Test the accuracy of individual data items listed in the distributor’s inventory master file.
<para>Describe how the use of the audit software package and a copy of the inventory file data might be helpful to the auditor in performing each of these auditing procedures.</para>
11.12 Which of the following should have the primary responsibility to detect and correct data processing errors? Explain why that function should have primary responsibility and why the others should not.
The data processing manager
The computer operator
The corporate controller
The independent public accountant
11.1 You are performing a financial audit of the general ledger accounts of Preston Manufacturing. As transactions are processed, summary journal entries are added to the general ledger file at the end of the day. At the end of each day, the general journal file is processed against the general ledger control file to compute a new current balance for each account and to print a trial balance.
The following resources are available as you complete the audit:
Your firm’s generalized computer audit software
A copy of the general journal file for the entire year
A copy of the general ledger file as of fiscal year-end
(current balance = year-end balance)
A printout of Preston’s year-end trial balance listing the account number, account name, and balance of each account on the general ledger control file
Create an audit program for Preston Manufacturing. For each audit step, list the audit objectives and the procedures you would use to accomplish the audit program step.
CHAPTER 12
THE REVENUE CYCLE: SALES AND CASH COLLECTIONS
12.1 Customer relationship management systems hold great promise, but their usefulness is determined by the amount of personal data customers are willing to divulge. To what extent do you think concerns about privacy-related issues affect the use of CRM systems?
12.2 Some products, like music and software, can be digitized. How does this affect each of the four main activities in the revenue cycle?
12.3 Many companies use accounts receivable aging schedules to project future cash inflows and bad-debt expense. Review the information typically presented in such a report (see Figure 12-8). Which specific metrics can be calculated from those data that might be especially useful in providing early warning about looming cash flow or bad-debt problems?
12.4 Table 12-1 suggests that restricting physical access to inventory is one way to reduce the threat of theft. How can information technology help accomplish that objective?
12.5 Invoiceless pricing has been adopted by some large businesses for B2B transactions. What are the barriers, if any, to its use in B2C commerce?
12.6 The use of some form of electronic “cash” that would provide the same kind of anonymity for e-commerce that cash provides for traditional physical business transactions has been discussed for a long time. What are the advantages and disadvantages of electronic cash to customers? To businesses? What are some of the accounting implications of using electronic cash?
SUGGESTED ANSWERS TO THE PROBLEMS
12.1 Match the term in the left column with its definition in the right column.
1. CRM system
a. Document used to authorize reducing the balance in a customer account
2. Open-invoice method
b. Process of dividing customer account master file into subsets and preparing invoices for one subset at a time
3. Credit memo
c. System that integrates EFT and EDI information
4. Credit limit
d. System that contains customer-related data organized in a manner to facilitate customer service, sales, and retention
5. Cycle billing
e. Electronic transfer of funds
6. FEDI
f. Method of maintaining accounts receivable that generates one payments for all sales made the previous month
7Remittance advice
g. Method of maintaining customer accounts that generates payments for each individual sales transaction
8. Lockbox
h. Maximum possible account balance for a customer
9. Back order
i. Electronic invoicing
10. Picking ticket
j. Post office box to which customers send payments
11. Bill of lading
k. Document used to indicate stock outs exist
l. Document used to establish responsibility for shipping goods via a third party
m. Document that authorizes removal of merchandise from inventory
n. Turnaround document returned by customers with payments
12.2 What internal control procedure(s) would provide protection against the following threats?
a. Theft of goods by the shipping dock workers, who claim that the inventory shortages reflect errors in the inventory records.
b. Posting the sales amount to the wrong customer account because a customer account number was incorrectly keyed into the system.
c. Making a credit sale to a customer who is already four months behind in making payments on his account.
d. Authorizing a credit memo for a sales return when the goods were never actually returned.
e. Writing off a customer’s accounts receivable balance as uncollectible to conceal the theft of subsequent cash payments from that customer.
f. Billing customers for the quantity ordered when the quantity shipped was actually less due to back ordering of some items.
g. Theft of checks by the mailroom clerk, who then endorsed the checks for deposit into the clerk’s personal bank account.
h. Theft of funds by the cashier, who cashed several checks from customers.
i. Theft of cash by a waiter who destroyed the customer sales ticket for customers who paid cash.
j. Shipping goods to a customer but then failing to bill that customer.
k. Lost sales because of stockouts of several products for which the computer records indicated there was adequate quantity on hand.
l. Unauthorized disclosure of buying habits of several well-known customers.
m. Loss of all information about amounts owed by customers in New York City because the master database for that office was destroyed in a fire.
n. The company’s Web site was unavailable for seven hours because of a power outage.
o. Interception and theft of customers’ credit card numbers while being sent to the company’s Web site.
p. A sales clerk sold a $7,000 wide-screen TV to a friend and altered the price to $700.
q. A shipping clerk who was quitting to start a competing business copied the names of the company’s 500 largest customers and offered them lower prices and better terms if they purchased the same product from the clerk’s new company.
r. A fire in the office next door damaged the company’s servers and all optical and magnetic media in the server room. The company immediately implemented its disaster recovery procedures and shifted to a backup center several miles away. The company had made full daily backups of all files and stored a copy at the backup center. However, none of the backup copies were readable.
12.3 For good internal control, which of the following duties can be performed by the same individual?
1. Approve changes to customer credit limits
2. Sales order entry
3. Shipping merchandise
4. Billing customers
5. Depositing customer payments
6. Maintaining accounts receivable
7. Issuing credit memos
8. Reconciling the organization’s bank accounts
9. Checking inventory availability
12.4 Excel Project. (Hint: For help on steps b and c, see the article “Dial a Forecast,” by James A. Weisel, in the December 2006 issue of the Journal of Accountancy. The Journal of Accountancy is available in print or online at the AICPA’s Web site: www.aicpa.org
Required:
a. Create a 12-month cash flow budget in Excel using the following assumptions:
· Initial sales of $5,000,000 with forecasted monthly growth of 1%
· 40% of each month’s sales for cash; 30% collected the following month; 20% collected 2 months later; 8% collected 3 months later; and 2% never collected
· Initial cash balance of $350,000
b. Add a “spinner” to your spreadsheet that will enable you to easily change forecasted monthly sales growth to range from 0.5% to 1.5% in increments of 0.1%.
d. Design appropriate data entry and processing controls to ensure spreadsheet accuracy.
12.5 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:
a. Sales order entry clerk taking a customer order
b. Shipping clerk completing a bill of lading for shipment of an order to a customer
12.6 Create a questionnaire checklist that can be used to evaluate controls for each of the four basic activities in the revenue cycle (sales order entry, shipping, billing, and cash collections).
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 customer credit limits set and modified by a credit manager with no sales responsibility?”
b. For each Yes/No question, write a brief explanation of why a “No” answer represents a control weakness.
12.7 O’Brien Corporation is a midsize, privately owned, industrial instrument manufacturer supplying precision equipment to manufacturers in the Midwest. The corporation is 10 years old and uses an integrated ERP system. The administrative offices are located in a downtown building and the production, shipping, and receiving departments are housed in a renovated warehouse a few blocks away.
Customers place orders on the company’s website, by fax, or by telephone. All sales are on credit, FOB destination. During the past year sales have increased dramatically, but 15% of credit sales have had to written off as uncollectible, including several large online orders to first-time customers who denied ordering or receiving the merchandise.
Customer orders are picked and sent to the warehouse, where they are placed near the loading dock in alphabetical sequence by customer name. The loading dock is used both for outgoing shipments to customers and to receive incoming deliveries. There are ten to twenty incoming deliveries every day, from a variety of sources.
The increased volume of sales has resulted in a number of errors in which customers were sent the wrong items. There have also been some delays in shipping because items that supposedly were in stock could not be found in the warehouse. Although a perpetual inventory is maintained, there has not been a physical count of inventory for two years. When an item is missing, the warehouse staff writes the information down in log book. Once a week, the warehouse staff uses the log book to update the inventory records.
The system is configured to prepare the sales invoice only after shipping employees enter the actual quantities sent to a customer, thereby ensuring that customers are billed only for items actually sent and not for anything on back order.
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. Uncollectible sales
a. Restrict access to master data.
2. Mistakes in shipping orders to customers.
b. Encrypt customer information while in storage.
3. Crediting customer payments to the wrong account.
c. Backup and disaster recovery procedures.
4. Theft of customer payments.
d. Digital signatures.
5. Theft of inventory by employees.
e. Physical access controls on inventory
6. Excess inventory.
f. Segregation of duties of handling cash and maintaining accounts receivable.
7. Reduced prices for sales to friends.
g. Reconciliation of packing lists with sales orders.
8. Orders later repudiated by customers who deny placing them.
h. Reconciliation of invoices with packing lists and sales orders.
9. Failure to bill customers.
i. Use of bar-codes or RFID tags.
10. Errors in customer invoices
j. Periodic physical counts of inventory
11. Cash flow problems
k. Perpetual inventory system.
12. Loss of accounts receivable data
l. Use of either EOQ, MRP, or JIT inventory control system.
13. Unauthorized disclosure of customer personal information.
m. Lockboxes or electronic lockboxes.
14. Failure to ship orders to customers.
n. Cash flow budget
o. Mail monthly statements to customers.
p. Credit approval by someone not involved in sales.
q. Segregation of duties of shipping and billing.
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 R2 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 R2 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.
i. A fictitious invoice is received and a check is issued to pay for goods that were never ordered or delivered.
j. The petty cash custodian confesses to having “borrowed” $12,000 over the last five years.
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.
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
economic order quantity
A document that creates a legal obligation to buy and pay for goods or services.
materials requirements planning (MRP)
The method used to maintain the cash balance in the petty cash account.
Just-in-time (JIT) inventory system
The time to reorder inventory based on the quantity on hand falling to predetermined level.
purchase requisition
A document used to authorize a reduction in accounts payable when merchandise is returned to a supplier.
imprest fund
An inventory control system that triggers production based upon actual sales.
purchase order
An inventory control system that triggers production based on forecasted sales.
kickbacks
A document only used internally to initiate the purchase of materials, supplies, or services.
procurement card
A process for approving supplier invoices based on a two-way match of the receiving report and purchase order.
blanket purchase order
A process for approving supplier invoices based on a three-way match of the purchase order, receiving report, and supplier invoice.
evaluated receipts settlement (ERS)
A method of maintaining accounts payable in which each supplier invoice is tracked and paid for separately.
disbursement voucher
A method of maintaining accounts payable which generates one check to pay for a set of invoices from the same supplier.
receiving report
Combination of a purchase order, receiving report, and supplier invoice that all relate to the same transaction.
debit memo
A document used to list each invoice being paid by a check.
vendor managed inventory
An inventory control system that seeks to minimize the sum of ordering, carrying, and stockout costs.
voucher package
A system whereby suppliers are granted access to point-of-sale (POS) and inventory data in order to automatically replenish inventory levels.
non-voucher system
An agreement to purchase set quantities at specified intervals from a specific supplier.
voucher system
A document used to record the quantities and condition of items delivered by a supplier.
A special purpose credit card used to purchase supplies.
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.
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.
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
Failing to take available purchase discounts for prompt payment.
Only accept deliveries for which an approved purchase order exists.
Recording and posting errors in accounts payable.
Document all transfers of inventory.
Paying for items not received.
Restrict physical access to inventory.
Kickbacks.
File invoices by due date.
Theft of inventory.
Maintain a cash budget.
Paying the same invoice twice.
Automated comparison of total change in cash to total changes in accounts payable.
Stockouts.
Adopt a perpetual inventory system.
Purchasing items at inflated prices.
Require purchasing agents to disclose financial or personal interests in suppliers.
Misappropriation of cash.
Require purchases to be made only from approved suppliers.
Purchasing goods of inferior quality.
Restrict access to the supplier master data.
Wasted time and cost of returning unordered merchandise to suppliers.
Restrict access to blank checks.
Accidental loss of purchasing data.
Only issue checks for a complete voucher package (receiving report, supplier invoice, and purchase order).
Disclosure of sensitive supplier information (e.g., banking data).
Cancel or mark “Paid” all supporting documents in a voucher package when a check is issued.
Regular backup of the expenditure cycle database.
Train employees how to respond properly to gifts or incentives offered by suppliers.
Hold purchasing managers responsible for costs of scrap and rework.
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).
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:
a. Purchasing agent generating a purchase order
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?
Approve purchase orders
Negotiate terms with suppliers
Reconcile the organization’s bank account
Approve supplier invoices for payment
Cancel supporting documents in the voucher package
Sign checks
Mail checks
Request inventory to be purchased
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:
Need Solution Email me:
topsolutions8@gmail.com
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.
Assume that current production levels of 1,000,000 units will continue. Which option do you recommend? Why?
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
Create the following spreadsheet
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.
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, http://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 http://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 http://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. 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. 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.
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?”
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:
a. Sales
b. Sales and Gross Margin
c. Earnings per share
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 http://www.aicpa.org). Follow the instructions in the article to create a spreadsheet with graphs that do what-if analysis.
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.
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:
a. The relationship between the Sale and Receive Cash events for installment sales.
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.
d. The Sale-Inventory relationship for a custom homebuilder.
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.
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.
g. The relationship between the Receive Inventory and Disburse Cash events in the case where suppliers require payment in advance, in full.
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.
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.
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
Draw an REA diagram for the library system. Remember to include cardinalities.
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
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.
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:
Examining the adequacy of internal controls in the purchase requisition procedure -
Identifying the controller’s information needs
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:
economy and usefulness
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.
b. What could the department have done differently to prevent the officers’ complaints?
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.
c. Discuss the organizational impact of introducing new electronic communication systems.
d. Explain
Why an employee might resist the introduction of electronic communication systems
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.
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?
b. What in-house development issues are demonstrated in this case?
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. IT picked the wrong software using a very naïve request for proposal process.
2. IT did not formulate a project plan.
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.
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.
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. 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. 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. The insurance department wants data on the cost and book values of assets to include in its review of current insurance coverage.
4. The tax department has requested data to determine whether Columbia should switch depreciation methods for tax purposes.
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.
a. Identify and justify four major objectives Columbia’s automated property accounting system should possess to respond to departmental requests for information.
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. System test
2. User specifications
3. Conversion
4. Systems survey
5. Technical specifications
6. Post-implementation planning
7. Implementation planning
8. User procedures and training
9. Programming
a. Arrange the nine items in the sequence in which they should logically occur.
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?
c. What other testing methodologies could have been used by the firm?
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.
Need Solution Email me:
topsolutions8@gmail.com