attached file
ACCT 384 – Final Exam Sections A & B Accounting 384 Midterm Exam Fall 2020 Jim Kurtenbach Iowa State University ACADEMIC INTEGRITY PLEDGE You are NOT permitted to do the following: · You may NOT communicate with anyone else about this assessment · You may NOT post questions about the assessment to the Help Forum on Canvas · You may NOT consult unauthorized aids, including paid-for subscriptions to Chegg, Scribd, or tutoring services Communication with instructors during the assessment: · You may seek clarification on any aspect of the assessment by sending an email to me at
[email protected]. I will read and respond to all email messages received by 10:00 AM CDT. If necessary, a Canvas announcement will be posted by 11:00 AM CDT to address any widespread misunderstandings. Academic integrity pledge I understand that academic integrity is expected of all Iowa State University students, at all times. My submission of this assessment for grading certifies that I have read and understood the ground rules. By my signature below, I pledge that I have neither given nor received unauthorized aid on this assessment. Signature:_________________________________________________________ Your exam will not be graded unless you identify at least two steps you took to follow appropriate academic integrity rules. Please identify at least two steps you took to make sure you followed appropriate academic integrity rules: DIRECTIONS Type in answers in the red boxes or on the red lines, save file with your last name in the file name, and upload to Canvas when completed. 8 PROBLEM 1 – INFORMATION NEEDS, BUSINESS PROCESSES, ERP (8 points) P1 (a) Identify examples of each of the five business processes discussed in Chapter 1 in the following scenario: (4 points) Super Cy is the owner of a business consulting service to develop new mascots for universities. The service has rented office space in downtown Ames and has procured appropriate computer equipment and supplies for making mascots. Independent designers work with university clients to develop the new mascots and the consulting service used independent contractors to assemble the costumes for the new mascots. A group of college presidents provided the initial investment in the consulting service. Super Cy has agreed to repay this investment in installment payments over 10 years. P1 (b) Define an ERP system and evaluate whether or not an ERP system would be most appropriate for the above scenario. (4 points) PROBLEM 2 – REA MODEL (12 points) P2. Prepare an REA model with both minimum and maximum cardinality for the following expenditure scenario. If in doubt about a relationship, please state your assumptions. Cy Apples sells delicious locally grown apples throughout the Midwest. Its accounting information systems and data analytics were designed and implemented by ISU alumni. The system has the ability to store data about preapproved customers but does not store information about any apple equipment until the equipment is purchased. Employee information is added when the employees are hired even if they spend their first few days training. Each month, the purchase department manager accumulates all related purchase requisitions for equipment and places orders with preapproved vendors. Most vendors fulfill the orders in a timely manner. However, Slow Hawkeye Supplies rarely has all items requested and often fulfils orders with multiple shipments. Sometimes, Slow Hawkeye Supplies combines multiple orders into one shipment. All vendors except those in Iowa City grant Cy Apples 30 days credit and allow them to accumulate invoices and make one monthly payment at the beginning of each month. Cy Apples pays all invoices in full. The accounts payable department handles all payments and the payments are made from one bank account. Cy Apples has a separate payroll bank account. PROBLEM 3 – IMPLEMENTING REA MODEL INTO RELATIONAL DATABASE (12 points) P3 (a) Briefly describe how a one to many relationship in a REA model is handled when converting the REA model to a physical database. (4 points) P3 (b)Explain the business rules diagrammed below for both minimum and maximum cardinality. (4 points) Employee Pay Labor P3 (c)Explain the business rules diagrammed below for both minimum and maximum cardinality. (4 points) Perform Work Pay Labor PROBLEM 4 – RELATIONAL TABLES & SQL QUERIES (30 points) Cy Magic Chairs, Inc. sells cardinal and gold chairs to various retail outlet stores throughout the Midwest. These chairs include a machine learning algorithm to automatically provide Cyclone students with good luck when taking exams. The algorithm also identifies students to the other university to our east who try to masquerade as Cyclone students. The chairs are available in three sizes: small, medium, and large. Tables used in its revenue database are shown below. Use this information to answer questions . Table Attributes Inventory ProductWeight, ReorderPoint, ProductNumber, Description, UnitCost, UnitPrice, QuantityOnHand Cash BankID, Balance, AccountNumber SalesOrder OrderAmount, EmployeeNumber, Terms, OrderNumber, OrderDate, CustomerNumber ShipGoods EmployeeNumber, CustomerNumber, ShipmentReportNumber, OrderNumber, ShipmentDate CashReceipt CustomerNumber, EmployeeNumber, AccountNumber, ReceiptNumber, ReceiptDate, ReceiptAmount Employee EmployeeName, DateHired, BirthDate, EmployeeNumber, Salary, ManagerNumber, EmployeeStreetAddress, EmployeeCity, EmployeeState, EmployeeZip Customer CustomerName, Street, City, State, Zip, CustomerNumber, CustomerPhone Inventory-SalesOrder OrderQuantity, ProductNumber, OrderNumber Inventory-ShipGoods QuantityShipped, ProductNumber, ShipmentReportNumber ShipGoods-CashReceipt ActualPrice, ShipmentReportNumber, ReceiptNumber P4 (a) Identify the best primary key(s) for each table. (5 points) Inventory ___________________________________ ShipGoods-CashReceipt___________________________________ Cash___________________________________ Inventory-SaleOrder___________________________________ ShipGoods___________________________________ P4 (b) Identify the foreign keys (if any) in the following tables: (3 points) Customer ShipGoods CashReceipt P4 (c) Write an SQL query to calculate the total dollar amount of sales made in September 2020 (hint: September has 30 days). (8 points) P4 (d) Write an SQL query to identify the average order quantity of small cardinal and gold chairs purchased by each customer in September 2020. (hint: September has 30 days). Be sure to list the customer number and customer name. Also, assume you know that the product number for small cardinal and gold chairs is 10012. (7 points) P4 (e) Write an SQL query to find the date that Cy Magic Chairs Inc. shipped the two small cardinal and gold chairs that Dr. Anne Clem ordered on September 1, 2020. Be sure to include quantity ordered in your restriction as Dr. Clem also placed an order for one small cardinal and gold chair for her office on September 1, 2020. Include the customer number, customer name, and customer city in your results. Assume inventory description is ‘small cardinal and gold chair’. (7 points) PROBLEM 5 – DATA ANALYTICS (14 points) P5 (a)Assume the internal audit manager is concerned that dividend payments are being paid to shareholders who no longer own stock in Super Cy Tulip Company. The manager suggests that your first step is to separate the names into two columns: one for first names and one for last names. Write a formula to separate the names into one column for first names and one column for last names. Using LEFT, RIGHT, and MID functions, write formulas to develop these formulas. Assume column E will hold the first name information and column F will hold last name information. (4 points) Formula for First Name Formula for Last Name Below is basic descriptive statistics orders for Ivana Ice Cream Shops for the first six months of 2020. As indicated in assignment 5, Ivana Ice Cream Shop has two products both available in two flavors. P5 (b) Identify at least three concerns you have with the data and discuss what steps you would take for each concern identified. (6 points) P5 (c) Describe the Excel function needed to count the number of null values. (4 points) PROBLEM 6 – DATA VISUALIZATION (14 points) P6The Board of Regents oversees Iowa’s three public universities (Iowa State, Iowa, and Northern Iowa) and two special schools (Iowa School for the Deaf and Iowa Braille and Sight Savings School). The graphs shown below appear on page 1 of the 2019 Iowa Board of Regents Comprehensive Fiscal Report (see https://www.iowaregents.edu/media/cms/1119_IF4__Comp_Fiscal_9B30D8F9B59ED.pdf). P6 (a) Indicate the purpose of these graphs based on information from Chapter 7 in your textbook. Is a pie chart most appropriate? Why or why not? (5 points) P6 (b) Based on information in page 204 to 205 in the textbook, what techniques does the Board use to increase the visual weight of specific information in these graphs? (5 points) P6 (c) Based on Figure 7-8 in the textbook, what methods would you suggest to change these graphs if the Board wants to emphasis the amount of tuition received. Explain your choice clearly. (4 points)