Rewrite accounting problem, there have right answers on it. · Chapter 5
Problem 7. Systems Design
Robert Hamilton was hired six months ago as the controller of a small oil and gas exploration and development company, Gusher, Inc., headquartered in Beaumont, Texas. Before working at Gusher, Hamilton was the controller of a larger petroleum company, Eureka Oil Company, based in Dallas. The joint interest billing and fixed asset accounting systems of Gusher are outdated, and many processing problems and errors have been occurring quite frequently. Hamilton immediately recognized these problems and informed the president, Mr. Barton, that it was crucial to install a new system. Barton concurred and met with Hamilton and Sally Jeffries, the information systems senior manager. Barton instructed Jeffries to make the new system that Hamilton wished to have a top priority in her department. Basically, he told Jeffries to deliver the system to meet Hamilton’s needs as soon as possible.
Jeffries left the meeting feeling overwhelmed because the IS department is currently working on two other very big projects, one for the production department and the other for the geological department. The next day, Hamilton sent a memo to Jeffries indicating the name of a system he had 100 percent confidence in—Amarillo Software—and he also indicated that he would very much like this system to be purchased as soon as possible. He stated that the system had been used with much success during the past four years in his previous job.
When commercial software is purchased, Jeffries typically sends out requests for proposals to at least six different vendors after conducting a careful analysis of the needed requirements. However, due to the air of urgency demonstrated in the meeting with the president and the over-worked systems staff, she decided to go along with Hamilton’s wishes and sent only one RFP (request for proposal) out, which went to Amarillo Software. Amarillo promptly returned the completed questionnaire. The purchase price ($75,000) was within the budgeted amount. Jeffries contacted the four references provided and was satisfied with their comments. Further, she felt comfortable since the system was for Hamilton, and he had used the system for four years.
The plan was to install the system during the month of July and try it for the August transaction cycle. Problems were encountered, however, during the installation phase. The system processed extremely slowly on the hardware platform owned by Gusher. When Jeffries asked Hamilton how the problem had been dealt with at Eureka, he replied that he did not remember having had such a problem. He called the systems manager from Eureka and discovered that Eureka had a much more powerful mainframe than Gusher. Further investigation revealed that Gusher has more applications running on its mainframe than Eureka did, since Eureka used a two-mainframe distributed processing platform.
Further, the data transfer did not go smoothly. A few data elements being stored in the system were not available as an option in the Amarillo system. Jeffries found that the staff at Amarillo was very friendly when she called, but they could not always identify the problem over the phone. They really needed to come out to the site and investigate. Hamilton was surprised at the delays between requesting an Amarillo consultant to come out and the time in which he or she actually arrived. Amarillo explained that it had to fly a staff member from Dallas to Beaumont for each trip. The system finally began to work somewhat smoothly in January, after a grueling fiscal year-end close in October. Hamilton’s staff viewed the project as an unnecessary inconvenience. At one point, two staff accountants threatened to quit. The extra consulting fees amounted to $35,000. Further, the systems department at Gusher spent 500 more hours during the implementation process than it had expected. These additional hours caused other projects to fall behind schedule.
Required:
Discuss what could have been done differently during the design phase. Why were most of the problems encountered? How might a detailed feasibility study have helped?
Response: The systems analysis and requirements phase was never conducted. Further, a conceptual design was never prepared. A crucial aspect, the feasibility study, was never carried out. Thus, no criteria were available to judge whether the vendor’s RFP was appropriate for Gusher. Due to time constraints, Gusher purchased the software hurriedly without conducting the proper analysis. The rush to put in a new project because the systems department was overworked caused more work, troubles, headaches, and cost outflows than would have occurred if the analysis had been appropriately conducted in the first place. Proper analysis would probably have addressed the major problems. The software purchased did not have data fields to capture some of the data captured by the old system. The mainframe, with all of the other processing, was not sufficiently powerful to process transactions using the new system. A benchmark test using Gusher’s mainframe and data would have discovered both problems.
· Chapter 6
Problem 8. SYSTEM FLOWCHART
Required: Analyze the system flowchart on for Problem and describe in detail the processes that are occurring.
Response: Time sheets are collected in a batch, and the information is manually keyed into the system. This data is now stored on a magnetic disk. An editing program is run, which verifies whether the employee number is valid by checking it against an employee master file. The validity of the cost center assigned is also verified against a master file. Logical and clerical errors should also be tested, such as an employee working an unreasonable number of hours in a day/week. Records that are found to be in error are sent to an error file. These errors need to be investigated and corrected. The good records are stored in a data file. The next program takes the edited transaction records, one at a time, and updates any corresponding fields in the master files. Finally, a report program generates paychecks and management reports.
· Chapter 7
Problem 3. Input Controls and Data Processing
You have been hired by a catalog company to computerize its sales order entry forms. Approximately 60 percent of all orders are received over the telephone, with the remainder either mailed or faxed in. The company wants the phone orders to be input as they are received. The mail and fax orders can be batched together in groups of fifty and submitted for data entry as they become ready. The following information is collected for each order:
· Customer number (if a customer does not have one, one needs to be assigned)
· Customer name
· Address
· Payment method (credit card or money order)
· Credit card number and expiration date (if necessary)
· Items ordered and quantity
· Unit price
Required:
Determine control techniques to make sure that all orders are entered accurately into the system. Also, discuss any differences in control measures between the batch and the real-time processing.
Response: For the phone orders, if a customer has a customer number, it should be verified against a master file. If a customer needs to establish a customer number, one should be assigned, and the customer’s name should be entered. A missing data check should be used to verify that a first name, last name, and street address have been entered. If the firm has a U.S. zip code database, the zip code can be entered and the city and town should appear.
The payment method should be a menu choice of credit cards that are accepted. The credit card number should be entered into an alpha-numeric field as well as the expiration date—a numeric field. Once the order is totaled, authorization with the credit card company will be provided online. The item ordered should be entered and verified against an inventory master file. The description should appear and be read to the customer and verified as accurate. The unit price should automatically appear. The quantity should be entered, and a range check performed to see if the order is reasonable.
For the batch processed data, customers without customer numbers should be placed into a batch for adding and receiving customer numbers before the order can be processed. For those orders with customer numbers, the data will be grouped into batches. Check digits will be calculated for the customer numbers and the inventory items. Any records that have an invalid customer number, invalid inventory item, check digits that do not match, or an unreasonable quantity ordered will be written to an error file, and the rest of the orders will be processed. The clean transactions should be sorted according to charge type and the credit card numbers verified. Any rejected transactions will be sent to a special file from which letters will be sent to the customer. The doubly-clean transactions will then be processed. The real-time processing technique is more efficient because any errors can be resolved easily and immediately.
· Chapter8
Problem 3. Structured Query Language
The vice president of finance has noticed in the aging of the accounts receivable that the amount of overdue accounts is substantially higher than anticipated. He wants to investigate this problem. To do so, he requires a report of overdue accounts containing the attributes shown in the top half of the table (presented in the chapter problem and on the next page). The bottom half of the table contains the data fields and relevant files in the relational database system. Further, he wants to alert the salespeople of any customers not paying their bills on time. Using the SQL commands given in this chapter, write the code necessary to generate a report of overdue accounts that are greater than $5,000 and more than 30 days overdue. Each customer has an assigned salesperson.
Response:
CREATE VIEW collect
AS SELECT salesperson_name, branch, customer_number, customer_name, overdue_balance, order_date, delivery_date, amount, amount_of_last_payment, date_of_last_payment
FROM salesperson, customer, sales_order
WHERE overdue_balance>5000 AND date_of_last_payment < mm/dd/yyyy
REPORT ATTRIBUTES
Salesperson Name, Salesperson Branch Office, Customer Number, Customer Name,
Amount Overdue, Last Purchase Date, Goods Delivered?, Amount of Last Sales Order,
Amount of Last Payment, Date of Last Payment
FILES AVAILABLE:
Salesperson Table Customer Table Sales Order Table
Salesperson Name Customer Number Sales Order Number
Salesperson Number Customer Name Customer Number
Commission Rate Customer Address 1 Order Date
Rank Customer Address 2 Amount
Branch Salesperson Number Delivery Date
Date of Hire Last Sales Order Number
Year to Date Purchases
Account Balance
Overdue Balance
Amount of Last Payment
Date of Last Payment
· Chapter 9
Problem 5. Risks and Internal Controls
Following describes the credit sales procedures for clothing wholesaler that sells name-brand clothing to department stores and boutique dress shops. The company sells to both one-time and recurring customers. A flowchart of the system is provided in the figure labeled Problem 5: Internal Control.
Customer orders are received by fax and e-mail in the sales department. The sales clerk, who works on commission, approves the credit sale, calculates commissions and discounts, and records the sale in the sales journal from the PC in the sales department. The clerk then prepares a sales order, a customer invoice, and a packing slip, which are sent to the accounting department for processing. The accounting clerk updates the AR Subsidiary ledger and sends an invoice to the customer. The clerk then forwards the sales order and packing slip to the Warehouse-Shipping Department. The warehouse-shipping clerk picks the items from inventory and sends them and the packing slip to the carrier for shipment to the customer. Finally the clerk updates the inventory subsidiary ledger and files the sales order in the department.
Cash receipts from customers go to the mailroom, which has one supervisor overseeing 32 employees performing similar tasks: a clerk opens the envelope containing the customer check and remittance advice, inspects the check for completeness, reconciles it with the remittance advice, and sends the remittance advice and check to the accounting department. The accounting department clerk reviews the remittance advice and the checks, updates the AR subsidiary ledger and records the cash receipt in the cash receipts journal. At the end of the day, the clerk updates the AR Control, Cash, and Sales accounts in the general ledger to reflect the day’s sales and cash receipts.
Required
a. Describe the uncontrolled risks associated with this system as it is currently designed.
b. For each risk describe the specific internal control weakness(s) in the system that causes or contributes to the risk.
| Risks | Control Weaknesses |
| Sales to un-creditworthy customers
Inaccurately recording the sales transactions in journals |
Sales clerk approves credit
Sale is recorded when the sales clerk takes the order rather than after it is shipped. |
| Misappropriation of cash
Shipping customers the wrong items Misappropriation of inventory |
Accounting department clerk has access to the cash, the remittance advice, the AR sub – ledger, and the General ledger. Opportunity for embezzlement such as lapping
Mailroom span of control is wide (32 employees) for a single supervisor. This inhibits close supervision. The mailroom clerks, with access to the cash and remittance advices, have an opportunity to commit mailroom fraud. Warehouse / shipping are combined allowing for no reconciliation between what is picked and what is ordered and shipped. Warehouse clerk has custody of inventory and the inventory sub – Ledger |
· Chapter 11
Problem 3. OLTP VERSUS OLAP SEVERS
For each of the following processes, state whether OLTP or OLAP is appropriate and why:
a. An order entry system that retrieves customer information, invoice information, and inventory information for local sales.
b. An order entry system that retrieves customer information, invoice information, inventory information, and several years of sales information about both the customer and the inventory items.
c. An order entry system that retrieves customer information, invoice information, inventory information, and information to compare the current sale to sales across several geographic regions.
d. An order entry system that retrieves customer information, invoice information, inventory information, and accounts receivable information for sales within one marketing region.
e. An insurance company requires a system that will allow it to determine total claims by region, determine whether a relationship exists between claims and meteorological phenomenon, and why one region seems to be more profitable than another.
f. A manufacturing company has only one factory, but that factory employs several thousand people and has nearly $1 billion in revenue each year. The company has seen no reason to make comparisons about its operations from year to year or from process to process. Its information needs focus primarily on operations, but it has maintained backup of prior-year operations activities. Examination of prior-year financial reports have shown that the company, while profitable, is not growing and return on investment is decreasing. The owners are not satisfied with this situation.
Response:
a. On-line transaction (OLTP) processing is appropriate because the amount of data accessed is limited, few business elements are analyzed, the data is not aggregated, and the time frame is finite.
b. On-line analytical processing (OLAP) is appropriate because analysis of data over several years is required.
c. OLAP because that data being analyzed spans several regions.
d. OLAP. While this system will analyze simple transactions, the volume of activity and the analytical procedures may require greater resources than OLTP can provide.
e.. OLAP. OLAP supports consolidation of data, drill-down analysis, and slicing and dicing.
f. OLAP. While OLTP has been sufficient to provide the information requirements to date, the company is not meeting its goals, and an understanding of the business processes, related phenomena, and comparisons among processes is indicated. Analyses in these areas may help the company determine better business practices. OLAP will provide the company with the analytic tools that may help management find better ways to operate.
· Chapter 12
Problem 1. Mary Jane Smith has been a highly regarded employee of the Brier Corporation for almost 20 years. Her loyalty to the company is reflected in her dedication to her job as general accounting clerk from which she has not taken a vacation in almost 12 years. Because of her dedication and long tenure, she has acquired many related responsibilities, which has allowed the Brier Corp to reduce its work force through attrition, control salary expenses and become more efficient and competitive. The following describes Mary Jane’s responsibilities.
Mary Jane receives copies of credit sales orders from the sales department. From these documents she accesses the AR subsidiary ledger from her office computer and records the AR. She then records the sale in the sales journal and posts the transactions to the general ledger accounts. Cash receipts in payment of customer accounts receivable come directly to her office. She records the cash receipts in the GL cash and AR accounts and updates the AR subsidiary ledger. She then endorses the checks “for deposit only” and deposits them in the bank at the end of each day.
Required:
a. Identify any control problems in the procedures described above.
b. What sorts of fraud are possible in this system?
c. What controls are needed to reduce the risk of fraud?
a) Mary Jane’s dedication to her job from which she has not taken a vacation in many years is a red flag that she may be engaged in something illegal. This concern is reinforced by her job description, which combines several incompatible tasks:
· Segregation of Duties: Mary Jane is responsible for recording both accounts receivable and cash receipts.
· Segregation of Duties / Accounting Records: Mary Jane has access to both the AR subsidiary ledger and the general ledger accounts.
b) The following possible fraud could be committed:
· Skimming: Mary Jane could steal check and write off customer account receivable as a bad debt.
· Cash Larceny: Mary Jane could implement a lapping scheme because she has access to both cash receipts and AR records. The complex accounting procedures needed to manage such a scheme would require her to not take a vacation for fear that a replacement clerk would uncover the fraud.
c) Controls need to reduce the risk of fraud are:
· Implement a policy that all employees must take a vacation each year.
· Separate the task of AR record keeping and cash receipts processing
· Separate the tasks of posting to the general ledger from the tasks of updating subsidiary ledgers,
