Objective: Learn how to use the VLOOKUP function for payroll calculations. a. Read the article “Make Excel a Little Smarter” by Lois S. Mahoney and Charles Kelliher in the Journal of Accountancy (July 2003). You can find a copy at www.aicpa.org. b. Read the section titled “Data in Different Places” and create the spreadsheet illustrated in Exhibit 6. Print a screen shot of your work, and save your spreadsheet. c. Create a formula that calculates total bonuses. Also create a cell entry that indicates what that number represents. Print a screen shot of your work, and save it. d. Add the following data validation controls to your spreadsheet, including explanatory error messages. Save your work. Sales must be positive. Sales cannot exceed 125. Amount of bonus must be nonnegative. Amount of bonus cannot exceed 20% of unit sales. e. Modify your worksheet by placing the sales data and resulting bonus on a different worksheet from the bonus table. Name your table array, and modify the VLOOKUP function accordingly. Then add another employee: Johnson, who sold 150 units. Print a screen shot of your new worksheet showing the bonuses for each employee, including Johnson. Save your work.
> Match the terms in the left column with the appropriate definition from the right column.
> 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 maint
> 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 tha
> 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. Prepare an input controls matrix
> 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, start
> The following table lists the actions that various employees are permitted to perform:
> 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 reimburs
> 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.
> 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.
> 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.
> 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.
> 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.
> Prepare a program flowchart to help Melanie program this process.
> 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 p
> 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 skill employee date of birt
> 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
> 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
> 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 w
> 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 listene
> 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, informat
> 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 manag
> 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
> 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 val
> 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 th
> 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/deliver
> 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 w
> The specific solution will vary depending upon the DBMS and REA data model used. Therefore, we present only the query logic here for the model depicted in Figure 18.4. 1. Calculate total Accounts Receivable. 2. Calculate Accounts Receivable for a speci
> 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 de
> 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 depa
> 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 impleme
> 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 tak
> 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
> 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 ord
> 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. Sta
> 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 boo
> 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 on
> Explain how to calculate the total amount of Accounts Payable:
> 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 cl
> 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. Table Attributes Vendor Vendor#, name, street address, city, state Purc
> 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 O
> Provide an example (in terms of companies with which you are familiar) for each of the business situations described by the following relationship cardinalities: a b. c. d. e. f. g. h. i. j. Sales Receive Cash Sales Inventory Receive Disburse Invento
> 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. b. Print o
> Objective: Practice graph design principles. 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? Use the data
> 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 suc
> 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, deb
> 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 cal
> 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 Jo
> 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 dat
> 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 serv
> Objective: Learn how to find and correct errors in complex spreadsheets used for payroll. a. Read the article “Ferret Out Spreadsheet Errors” by Mark G. Simkin, in the Journal of Accountancy (February 2004). You can find a copy online by accessing www.a
> 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 pay
> 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
> 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 pay
> 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
> 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 V
> 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 (availabl
> a. Create the following spreadsheet b. 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 depreci
> 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 t
> 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 f
> 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 quanti
> 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 effi
> 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 in
> Alden, Inc. has hired you to review its internal controls for the purchase, receipt, storage, and issuance of raw materials. You observed the following: aw materials, which consist mainly of high-cost electronic components, are kept in a locked store
> 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 i
> For good internal control, which of the following duties can be performed by the same individual? 1. Approve purchase orders 2. Negotiate terms with suppliers 3. Reconcile the organization’s bank account 4. Approve supplier invoices for payment 5. C
> 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 sign
> 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 receivin
> 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
> 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 articl
> 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.2 Impact Assessment, Pr
> 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 ac
> 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
> 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
> Figure 12-18 depicts the activities performed in the revenue cycle by the Newton Hardware Company. (CPA Examination, adapted) a. Identify at least 7 weaknesses in Newton Hardware’s revenue cycle. Explain the resulting threat b. Identify ways to use IT t
> 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
> 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
> 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
> 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. Main
> 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 tra
> 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 th
> 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: 1. Gerber, J. A., and Feldman, E. R. 2002. “Is Your Business Prepared for th
> 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 pa
> 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 reci
> 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: Responsib
> 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 progra
> 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: For each situation, sta
> 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
> 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
> 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. a. The data processing manager b. The computer operat
> 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 in
> The fixed-asset master file at Thermo-Bond includes the following data items: Asset number ………………………….. Date of retirement (99/99/2099 for assets still in service) Description …………………….………. Depreciation method code Type code …………………….…………. De
> 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. Wha
> 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 he
> 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
> 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) modu
> 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
> 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
> 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 fo
> 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?
> 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
