Need to complete part 1 to 3

Business Finance

CUNY Brooklyn College

Question Description

Use Idea or excel to do parts 1 to 3. Include excel sheet with the answers using the excel spreadsheet provided.

I will extend additional 15 hours once the question is assigned to Tutor.

Unformatted Attachment Preview

MGA 621 – Data Analytics for Accountants 1 Background Urgent Medical Device, Inc. (the Company) is a medical device company founded in 2013 in Provo, Utah that specializes in the development and manufacturing of cutting-edge medical devices designed for all types of joint replacement surgeries. In January 2015, the FDA approved Urgent’s premier product, a hinged titanium axle designed to provide physicians with more precise placement of joints during joint replacement surgery. In early 2016, approximately one year after the new product’s approval, the Company hired a new senior vice president (SVP) of sales to oversee sales, physician training, product delivery, and customer service. The broad set of responsibilities allowed the charismatic SVP to significantly influence the Company’s revenue generation. The hiring of the new SVP was also done in large part to help guide the company’s development of an important new sales channel: third-party distributors that are each strategically located in close proximity to key hospitals in regions around the country. The move to hire the SVP was in direct response to overwhelming disappointment about the first year’s sales volume for the new surgical implant, which was lagging significantly behind expectations. Reports from the field led management to recommend the new sales channel to the board of directors that overwhelmingly approved the new strategy, the execution of which was being led by the new SVP. Execution of strategy To help execute the new strategy, the SVP hired five regional sales managers who would become his trusted cohorts. Together, they set aggressive sales targets for the Company’s surgical implants. The sales targets focused on achieving a growth pattern that was characterized by a record high sales volume for each successive quarter in each region. In fact, it is fair to say that the sales targets were intentionally created at almost unreachable levels to remove any question about possible weakness in demand for the Company’s new product. Incentive compensation Top management focuses significant attention on achieving short-term performance measures based on the audited financial statements when determining compensation and making promotion decisions. Revenue earned is the most important criterion in performance assessment throughout the organization. As part of the launch of its new surgical implant, a new bonus plan was established to provide additional incentives for the entire organization to focus on this new opportunity, with revenue earned as the key criterion used to determine incentive compensation. © 2018 KPMG LLP, a Delaware limited liability partnership and the U.S. member firm of the KPMG network of independent member firms affiliated with KPMG International Cooperative (“KPMG International”), a Swiss entity. All rights reserved. NDPPS 741662. The original case study has been edited and adopted for the classroom by Joshua Khavis – University at Buffalo. MGA 621 – Data Analytics for Accountants Preliminary results By the end of 2017, the Company had signed on a total of 73 distributors to sell its surgical implants in more than 20 different states throughout the United States. Each distributor was independently owned and operated but the company routinely shared best practices among its network. The SVP monitored sales closely from the distributor network through his regional sales managers. In fact, he even maintained a monthly sales report from each of the 73 distributors. The Company invoices customers when the goods are shipped, and invoicing triggers the recording of revenue. The Company does not include freight costs in sales revenue but does offset shipping costs with any freight charged to customers. The preliminary results were as follows: Sales revenue, year ended 12/31/2017 Gross accounts receivable, 12/31/2017 $84,867,855 $11,988,886 Internal Controls Four key application controls were tested at interim and no exceptions were noted. Additionally, IT auditors tested the system to make sure that proper segregation of duties occurred throughout the period and that controls over data input, data integrity, and the completion and accuracy of data used in the four application controls were operating effectively. The four key application controls are: 1. Automated Three-way Sales match. The control matches the details from 1) an approved sales order; 2) relevant shipping documents; and 3) the sales invoice before revenue is recorded. The control has been designed to support the existence/occurrence assertion for revenue. 2. Customer Credit Limit. The credit department obtains information from the customer that allows for a comprehensive review of the financial condition of the new customer and an assessment of the customer’s capacity to pay outstanding invoices. All new customers are assigned a credit limit by the credit department manager. 3. Automated Sales Authorization. When a sales order is entered into the system, the amount of the sale is added to the existing accounts receivable balance for that customer. The sum is then compared to the customer’s credit limit. If the sum is greater than the credit limit, the sale is not approved. If the sum is less than the credit limit, the sale is approved. A credit manager notes the approval and authorizes shipment by electronically entering their initials into the system, which gets posted into the sales order database. 4. Adequacy of the Allowance for Doubtful Accounts. On a monthly basis, the controller reviews the aging of accounts receivable report produced by the company’s information system. During the review, the controller identifies for follow-up all balances greater than 90 days past due for consideration in the allowance calculation. MGA 621 – Data Analytics for Accountants Database definitions for data provided 1. SalesOrders – Urgent has provided the SalesOrders database, which includes a master listing of all customer orders placed during the year. The client has informed you that the database also includes sales orders from the prior year that were not completed until the current year, as well as orders taken this year that were not delivered to customers as of 12/31. This database is also the client’s primary database for the three-way match control. When an order is shipped or invoiced, the system automatically posts the ShipID or InvoiceID to the SalesOrders database. MGA 621 – Data Analytics for Accountants 2. Shipments – Urgent has provided you with the Shipments database, which includes a listing of all shipments made during fiscal 2017, as well as shipments of products ordered during 2017 and not shipped until 2018. 3. CustomerInvoices – Urgent has provided you with the CustomerInvoices database, which includes a listing of all invoices issued during 2017, as well as invoices for sales orders taken during 2017 but not delivered to customers until 2018. MGA 621 – Data Analytics for Accountants 4. SalesTerritory – Urgent has provided you with this database listing the sales territories, as well as the sales goals for the fourth quarter of 2017 for all territories. 5. CustomerMaster – Urgent has provided you with the CustomerMaster database, which is a listing of all distributors. It classifies distributors by territory and also includes the most current credit limit for the customer. This credit limit is used for the automated credit limit check control. MGA 621 – Data Analytics for Accountants 6. Products – Urgent provided the Products database, which includes a listing of all products currently manufactured and sold by the company. The UnitPrice is the current verified selling price and is automatically populated in SalesOrders when a customer places an order. Similarly, the weight determines the freight charged to customers. Your firm’s Data and Analytics center has already converted the files, as provided by the client, into Excel format. As such, the contents and format of the databases you have been provided are exactly the same as they were provided by the client. MGA 621 – Data Analytics for Accountants Requirements Task 1: Import all supplied data into IDEA and then reconcile the data with the supplied trial balance and check figures from the case. Simply stated, you must check that the data has been transferred in a complete and accurate manner at each step in the process. Thus, to ensure that you are working with the set of transactions and balances that the client used to calculate Sales Revenue and Accounts Receivable, answer the following questions: a. What are the total number of valid sales that comprise the $84,867,855 sales revenue shown on the trial balance as of 12/31/2017? b. What are the total number of unpaid invoices that comprise the $11,988,886 gross accounts receivable balance shown on the trial balance as of 12/31/2017? Assume that Urgent has no outstanding accounts receivable from 2016. Task 2: Technology tools like IDEA can also be used to help audit teams identify items with potentially higher risks within an entire population of items in an efficient and effective manner. With this in mind, complete the following steps. a. The Sales number for 2017 are based on the records contained in the SalesOrder database. A sale is recognized when a customer is invoiced, and an invoice is generated only after a sales order is recorded and shipped. Thus, each SalesOrder record is presumed to have a match in the Shipments and CustomerInvoices databases. Using IDEA, verify the accuracy of the SalesOrder database by creating a three-way match using the SalesOrder, Shipments, and CustomerInvoices databases. Are there sales records that do not have Shipment/Invoice matches? If yes, list the records numbers. b. Urgent Medical Device Company has a credit limit policy, and all customers (including distributors) must have an established credit limit prior to any sale taking place. Analyze the sales revenue file using IDEA to determine whether any sales were made to distributors without an established credit limit. Are there any exceptions? If there are exceptions, what further testing can be performed on these exceptions? c. Analyze the accounts receivable and related credit authorization limits using IDEA to determine whether any customers have account balances as of 12/31/17 that exceed their existing credit limit. Are there any exceptions to the credit limit control as of 12/31/17? Do you notice any pattern in the exceptions? What further testing would you perform on these exceptions? d. For some time, Urgent has based its allowance for doubtful accounts on an aging analysis and the results have been historically accurate. Below, you have been provided with the client’s list of customer balances in excess of 90 days. These are the accounts that the client will consider when calculating the allowance for doubtful accounts as of 12/31/2017. Perform an aging analysis using IDEA to assess the completeness and accuracy of Urgent’s list of customer balances in excess of 90 days. MGA 621 – Data Analytics for Accountants Urgent Medical Devices Analysis of >90-Day Delinquent Accounts Receivable As of December 31, 2017 SalesOrderID InvoiceDate 3236 7/4/2017 3241 7/9/2017 3422 9/5/2017 3466 9/21/2017 3490 9/27/2017 TotalDue 56,251.34 73,450.67 73,450.67 59,932.09 54,851.15 317,935.92 i. Based on your analysis, does the client’s listing agree to your analysis completed in IDEA? ii. Do you have any concern with the client’s process for identifying the total amount of significantly delinquent accounts? You may think broadly, beyond mechanical accuracy, in considering the sufficiency of their process for identification of delinquent accounts. Task 3: Based on your risk analysis and findings in Tasks 1 and 2 above and the internal visual report below (initially provided to management by head of regional sales), what additional analyses can be performed using IDEA to identify specific transactions, territories, or distributors that may require additional substantive testing? Be creative in your proposal. For example, consider patterns in the data provided that might indicate management override of a control activity. Focus your additional analytics on those areas. What data/prior results suggest that additional testing is warranted? InvoiceID CustID InvoiceDate SalesOrderID PaidDate ModifiedDate ModifiedTime 100635 54 1/2/2017 2679 2/2/2017 2/2/2017 17:29 100636 62 1/3/2017 2681 2/1/2017 2/1/2017 10:05 100634 23 1/2/2017 2682 1/31/2017 1/31/2017 16:30 100637 19 1/3/2017 2683 2/1/2017 2/1/2017 11:32 100638 3 1/3/2017 2684 2/1/2017 2/1/2017 10:18 100640 65 1/4/2017 2685 2/2/2017 2/2/2017 11:33 100639 27 1/3/2017 2686 2/1/2017 2/1/2017 14:03 100641 43 1/4/2017 2687 2/2/2017 2/2/2017 9:21 100642 31 1/4/2017 2688 2/2/2017 2/2/2017 11:43 100643 73 1/5/2017 2689 2/4/2017 2/4/2017 8:01 100644 68 1/5/2017 2692 2/1/2017 2/1/2017 17:19 100645 1 1/5/2017 2690 2/4/2017 2/4/2017 14:52 100646 64 1/5/2017 2691 2/2/2017 2/2/2017 7:40 100647 18 1/6/2017 2693 2/2/2017 2/2/2017 12:35 100648 55 1/6/2017 2694 2/4/2017 2/4/2017 15:47 100649 13 1/8/2017 2696 2/4/2017 2/4/2017 17:30 100650 45 1/8/2017 2695 2/5/2017 2/5/2017 12:18 100651 4 1/9/2017 2698 2/8/2017 2/8/2017 16:30 100652 39 1/9/2017 2697 2/5/2017 2/5/2017 17:29 100653 45 1/10/2017 2699 2/6/2017 2/6/2017 10:05 100654 53 1/10/2017 2700 2/8/2017 2/8/2017 11:32 100655 36 1/11/2017 2704 2/10/2017 2/10/2017 10:18 100656 66 1/11/2017 2702 2/10/2017 2/10/2017 14:03 100657 2 1/11/2017 2703 2/10/2017 2/10/2017 11:33 100658 45 1/11/2017 2701 2/7/2017 2/7/2017 9:21 100659 7 1/12/2017 2705 2/9/2017 2/9/2017 11:43 100660 33 1/12/2017 2708 2/8/2017 2/8/2017 8:21 100661 56 1/12/2017 2709 2/10/2017 2/10/2017 11:58 100662 44 1/12/2017 2710 2/8/2017 2/8/2017 12:45 100663 26 1/12/2017 2706 2/9/2017 2/9/2017 16:08 100664 3 1/12/2017 2707 2/10/2017 2/10/2017 17:19 100665 58 1/13/2017 2712 2/10/2017 2/10/2017 9:36 100666 4 1/13/2017 2711 2/10/2017 2/10/2017 13:55 100667 3 1/13/2017 2713 2/9/2017 2/9/2017 7:52 100668 3 1/13/2017 2714 2/11/2017 2/11/2017 9:44 100669 12 1/14/2017 2715 2/10/2017 2/10/2017 7:55 100670 2 1/14/2017 2717 2/10/2017 2/10/2017 16:06 100671 44 1/14/2017 2719 2/13/2017 2/13/2017 8:11 100672 24 1/14/2017 2716 2/13/2017 2/13/2017 11:12 100673 5 1/14/2017 2718 2/13/2017 2/13/2017 17:58 100674 64 1/14/2017 2720 2/12/2017 2/12/2017 12:57 100675 67 1/15/2017 2722 2/13/2017 2/13/2017 17:52 100676 34 1/15/2017 2721 2/12/2017 2/12/2017 16:24 100677 52 1/16/2017 2723 2/14/2017 2/14/2017 15:09 100678 19 1/16/2017 2726 2/14/2017 2/14/2017 7:42 100679 22 1/16/2017 2725 2/14/2017 2/14/2017 15:00 100680 100681 100682 100683 100684 100685 100686 100687 100688 100689 100690 100691 100692 100693 100694 100695 100696 100697 100698 100699 100700 100701 100702 100703 100704 100705 100706 100707 100708 100709 100710 100711 100712 100713 100714 100715 100716 100717 100718 100719 100720 100721 100722 100723 100724 100725 100726 30 56 24 17 5 56 60 40 6 45 34 65 7 35 72 13 52 19 11 69 50 63 8 14 11 26 41 27 2 46 53 63 20 30 9 23 56 35 62 42 5 36 66 55 42 67 45 1/16/2017 1/17/2017 1/17/2017 1/17/2017 1/17/2017 1/18/2017 1/18/2017 1/19/2017 1/19/2017 1/19/2017 1/19/2017 1/22/2017 1/22/2017 1/22/2017 1/22/2017 1/23/2017 1/23/2017 1/23/2017 1/23/2017 1/23/2017 1/23/2017 1/25/2017 1/25/2017 1/26/2017 1/26/2017 1/26/2017 1/26/2017 1/27/2017 1/27/2017 1/27/2017 1/27/2017 1/28/2017 1/28/2017 1/28/2017 1/28/2017 1/29/2017 1/29/2017 1/30/2017 1/30/2017 1/30/2017 1/30/2017 1/31/2017 1/31/2017 1/31/2017 1/31/2017 2/1/2017 2/1/2017 2724 2727 2730 2728 2729 2732 2731 2736 2735 2733 2734 2737 2738 2740 2739 2744 2743 2742 2745 2746 2741 2748 2747 2749 2750 2751 2752 2755 2754 2753 2756 2758 2757 2760 2759 2761 2762 2765 2766 2763 2764 2770 2767 2768 2769 2772 2771 2/12/2017 2/15/2017 2/16/2017 2/15/2017 2/13/2017 2/15/2017 2/15/2017 2/18/2017 2/16/2017 2/18/2017 2/17/2017 2/20/2017 2/20/2017 2/19/2017 2/19/2017 2/19/2017 2/21/2017 2/19/2017 2/19/2017 2/19/2017 2/21/2017 2/22/2017 2/22/2017 2/22/2017 2/23/2017 2/25/2017 2/24/2017 2/26/2017 2/25/2017 2/23/2017 2/23/2017 2/24/2017 2/24/2017 2/26/2017 2/24/2017 2/27/2017 2/27/2017 2/28/2017 2/26/2017 3/1/2017 3/1/2017 3/2/2017 3/1/2017 2/27/2017 3/2/2017 3/3/2017 3/3/2017 2/12/2017 2/15/2017 2/16/2017 2/15/2017 2/13/2017 2/15/2017 2/15/2017 2/18/2017 2/16/2017 2/18/2017 2/17/2017 2/20/2017 2/20/2017 2/19/2017 2/19/2017 2/19/2017 2/21/2017 2/19/2017 2/19/2017 2/19/2017 2/21/2017 2/22/2017 2/22/2017 2/22/2017 2/23/2017 2/25/2017 2/24/2017 2/26/2017 2/25/2017 2/23/2017 2/23/2017 2/24/2017 2/24/2017 2/26/2017 2/24/2017 2/27/2017 2/27/2017 2/28/2017 2/26/2017 3/1/2017 3/1/2017 3/2/2017 3/1/2017 2/27/2017 3/2/2017 3/3/2017 3/3/2017 7:30 12:14 7:47 17:16 9:51 13:20 9:27 10:53 16:37 10:34 12:45 14:14 15:00 12:24 8:32 15:51 12:41 11:40 10:07 12:23 12:15 11:20 17:07 14:55 14:40 11:47 16:43 10:24 10:48 11:52 12:54 12:28 17:01 15:41 14:58 15:26 15:35 10:42 8:53 8:16 10:31 11:52 10:20 16:21 9:09 15:25 17:05 100727 100728 100729 100730 100731 100732 100733 100734 100735 100736 100737 100738 100739 100740 100741 100742 100743 100744 100745 100746 100747 100748 100749 100750 100751 100752 100753 100754 100755 100756 100757 100758 100759 100760 100761 100762 100763 100764 100765 100766 100767 100768 100769 100770 100771 100772 100773 27 56 22 64 37 64 36 38 68 38 8 26 5 39 40 69 21 21 15 47 35 10 30 22 40 6 31 4 49 41 19 43 71 43 6 72 3 41 13 28 11 44 45 69 57 6 54 2/3/2017 2/3/2017 2/3/2017 2/3/2017 2/4/2017 2/4/2017 2/5/2017 2/5/2017 2/5/2017 2/5/2017 2/5/2017 2/5/2017 2/5/2017 2/5/2017 2/6/2017 2/6/2017 2/7/2017 2/7/2017 2/7/2017 2/7/2017 2/8/2017 2/8/2017 2/8/2017 2/8/2017 2/9/2017 2/9/2017 2/9/2017 2/9/2017 2/9/2017 2/9/2017 2/10/2017 2/10/2017 2/11/2017 2/11/2017 2/12/2017 2/12/2017 2/12/2017 2/12/2017 2/14/2017 2/14/2017 2/15/2017 2/15/2017 2/15/2017 2/15/2017 2/16/2017 2/16/2017 2/16/2017 2773 2775 2776 2774 2778 2777 2779 2782 2783 2785 2781 2786 2780 2784 2787 2788 2791 2792 2790 2789 2793 2794 2796 2795 2802 2801 2798 2800 2799 2797 2803 2804 2806 2805 2808 2809 2810 2807 2811 2812 2816 2813 2814 2815 2820 2818 2822 3/3/2017 3/4/2017 3/2/2017 3/5/2017 3/6/2017 3/4/2017 3/5/2017 3/6/2017 3/4/2017 3/4/2017 3/7/2017 3/4/2017 3/7/2017 3/7/2017 3/7/2017 3/6/2017 3/7/2017 3/9/2017 3/7/2017 3/6/2017 3/9/2017 3/10/2017 3/9/2017 3/7/2017 3/8/2017 3/8/2017 3/10/2017 3/8/2017 3/11/2017 3/8/2017 3/10/2017 3/10/2017 3/13/2017 3/12/2017 3/11/2017 3/13/2017 3/13/2017 3/12/2017 3/14/2017 3/15/2017 3/15/2017 3/16/2017 3/14/2017 3/14/2017 3/16/2017 3/15/2017 3/17/2017 3/3/2017 3/4/2017 3/2/2017 3/5/2017 3/6/2017 3/4/2017 3/5/2017 3/6/2017 3/4/2017 3/4/2017 3/7/2017 3/4/2017 3/7/2017 3/7/2017 3/7/2017 3/6/2017 3/7/2017 3/9/2017 3/7/2017 3/6/2017 3/9/2017 3/10/2017 3/9/2017 3/7/2017 3/8/2017 3/8/2017 3/10/2017 3/8/2017 3/11/2017 3/8/2017 3/10/2017 3/10/2017 3/13/2017 3/12/2017 3/11/2017 3/13/2017 3/13/2017 3/12/2017 3/14/2017 3/15/2017 3/15/2017 3/16/2017 3/14/2017 3/14/2017 3/16/2017 3/15/2017 3/17/2017 11:07 17:14 15:51 16:28 15:16 15:00 17:20 13:14 17:59 12:06 9:10 8:29 17:10 7:45 10:24 12:59 8:11 16:44 10:18 11:10 8:30 10:02 17:42 13:01 17:59 10:45 13:00 14:33 12:20 15:15 17:46 10:49 9:26 14:23 13:36 7:31 15:27 15:22 10:18 14:18 9:55 8:21 12:59 11:07 16:26 12:07 16:18 100774 100775 100776 100777 100778 100779 100780 100781 100782 100783 100784 100785 100786 100787 100788 100789 100790 100791 100792 100793 100794 100795 100796 100797 100798 100799 100800 100801 100802 100803 100804 100805 100806 100807 100808 100809 100810 100811 100812 100813 100814 100815 100816 100817 100818 100819 100820 22 44 46 11 10 20 55 9 70 36 42 36 71 27 2 24 42 12 43 31 9 30 41 51 57 13 24 12 43 72 44 49 26 34 61 52 30 20 2 65 8 47 60 13 48 18 72 2/16/2017 2/16/2017 2/16/2017 2/19/2017 2/19/2017 2/21/2017 2/21/2017 2/21/2017 2/21/2017 2/24/201 ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags