Reference no: EM131311101

Project

Falcor Supplies is a distributor of high tech teaching equipment. Its primary target customers are schools in the region who purchase the teaching equipment for classrooms and online use. The company's main competitors in the industry are other high tech teaching supply companies.

Falcor Supplies started in 2011 with an office in Carbondale, Illinois, initially targeting local universities and colleges. The company realized there was a high demand for reasonably priced equipment and the convenience of using the internet to shop for this type of equipment. After its second year, with a constant increase in customer orders, Falcor decided to focus on selling to more colleges and universities throughout the Midwest. The move has had a positive effect on net profits and demand, supporting the decision to continue the growth of the company. Falcor recently expanded its facility to be able to fulfill a higher demand for its products. Its customer base ranges from areas as close as Southern Illinois University to as far as universities in the surrounding states. As of 2015, there were 60 employees. Their prior year's gross sales were $12 million.

Falcor's market share is on the rise but is not yet comparable to some of the larger school supply wholesalers. However, the corporation's goals for the upcoming years include establishing itself as an industry player through increased customer satisfaction and loyalty. Falcor is also considering the installation of a new information-processing system. This system will reengineer the company's current business functions by reducing loopholes in its internal control problems. It will also make the overall sales process more efficient.

Falcor purchases the products it sells from various wholesale suppliers and distributors, totaling 22 suppliers nationwide. The office has its own warehouse, stores its own merchandise, and is responsible for replenishing the inventory. Falcor has had no substantial problems in the past with their suppliers.

On the other hand, it has encountered problems with excess inventory, stock-outs, and discrepancies with inventory records.

Revenue Cycle

Becoming a customer of Falcor involves calling the toll-free number and speaking with one of the sales representatives, who establishes a new customer account. A customer's account record contains the information about the school including, a contact person's name, address, phone number, previous orders made with the company, and a sequentially assigned unique customer account number. Falcor does not sell directly to teachers, only to the school. All sales are wholesale sales, so no sales taxes apply.

Customers place orders via the Internet. A sales representative then prepares a sales order record. John, in the billing department, reviews the sales orders, adds prices and shipping charges, and prints a copy (invoice) that is sent to the customer. John then records the sale in the sales journal.

Chris, a warehouse employee, verifies the information on the sales order, picks the goods, prints the packing slip, and updates the inventory subsidiary ledger. Chris then prepares the bill of lading for the carrier. The goods are then shipped.

Sandy in AR updates the customer accounts and general ledger control accounts. When customers make a payment on account, they send both the remittance advice (that was attached to the invoice) and a check with the account number on it.

Scott, a mail room clerk, opens all the mail. He separates the checks, cash, and remittance advices and prepares a remittance list, which, along with the checks and cash, is sent to the cash receipts department.

Laura, the cash receipts clerk, reconciles the checks with the remittance list, updates the customer's account and the general ledger, and then deposits the checks in the bank. She sends the deposit slip to Sandy in the accounting department.

Upon receiving the bank receipt, Sandy files it and updates the cash receipts journal to record the amount deposited. The customer has a 15-day grace period, beginning upon the receipt of the equipment ordered. If, at the end of that period, a payment is received, it is understood that the goods have been accepted. If, on the other hand, the customer is dissatisfied with the product for any reason, they can return it to Falcor at no charge. However, to return the equipment, the customer must contact Falcor to obtain an authorization number. When the returned goods arrive, Chris prepares the return record and updates the inventory subsidiary ledger. Printed copies of the return record are sent to John and Sandy. John reviews the return record and updates the sales journal. Sandy credits the customer's account and updates the general ledger to reverse the transaction.

Expenditure Cycle

The purchases system and the cash disbursements system compose Falcor's expenditure cycle. The three departments within the purchasing system are warehouse, purchasing, and accounting. The purchasing function begins in the warehouse, which stores the inventory of equipment. Jim, the warehouse manager, compares inventory records with the various demand forecasts of each week, which the market research analyst team provides, to determine the necessary orders to make. At the end of the week, Jim prepares the purchase requisition record.

Sara, the purchasing clerk, reviews the purchase requisitions, selects the suppliers, and prepares the purchase orders. Copies of the purchase orders are sent to the supplier and to accounting. When the shipment arrives, Chris, the warehouse clerk, working from a blind copy of the purchase order, counts and inspects the goods for damage. He then prepares a receiving report and updates the inventory records.

Upon receipt of the supplier's invoice, Diana, the accounting clerk, compares it to the respective purchase order and receiving report. If the invoice is accurate, Diana creates an AP record, sets a due date for payment, and updates general ledger accounts.

On the due date, Evan, the cash disbursements clerk, closes the AP record, cuts a check, and sends it to the supplier. He then updates the check register and the general ledger.

Required

Assume that the manual system described is to be automated using a relational database system. Perform the following tasks. You may need to make assumptions about how certain automated activities will be performed.

a. Prepare the systems flowcharts of the existing system as described in the narrative.

b. Analyze the physical internal control weaknesses in the current system (both cycles). Model your response according to the six categories of physical control activities specified in the COSO internal control model.

c. Prepare a new set of systems flowcharts for each of the cycles with your recommended changes based on concerns you have with control weaknesses in the current system. Include recommended controls in the new flowcharts. These flowcharts would be your recommendation to Falcor of the way the manual system should be operating.

d. Prepare a data flow diagram for each of the cycles described in the narrative. This DFD will be your understanding of the logical flow of data through the system. Prepare, at a minimum, the Context level, Level 0, and Level 1 diagrams.

e. Prepare the REA model of the new system (both cycles) that you recommend. Show cardinalities for all associations.

f. List all tables necessary for the REA model. (include any linking tables)

• List all attributes for each table
• Identify the primary and foreign keys.

g. Using Access, implement the tables listed in f. above.

• List all attributes in the Access tables
• Identify the primary keys in the Access tables
• Enter a minimum of five rows of sample data in each table
• Create the relationships for the Access tables

h. BONUS: Create a few queries to retrieve or analyze data you stored in the tables in your Access DBMS.