! Required information The order-to-cash (O2C) process, or sales cycle, involves three main processes: 1. Sales order processing 2. Order fulfillment and shipping 3. Billing and cash collections Financial accountants are interested in determining the amount of sales revenue on the income statement and accounts receivable balance on the balance sheet as well as the calculation of bad debts expense. Managers are concerned with making the process as efficient as possible to ensure increased sales volume, sufficient profitability, and fast cash collection. Auditors should test sales transaction and master data to ensure that only authorized users are processing orders, that sales prices match master data and aren't altered, and that customers aren't exceeding approval limits. In addition, auditors use accounts receivable aging schedules to evaluate outstanding balances. The O2C tables follow the UML diagram shown in Exhibit 10-1. Over Creu EXHIBIT 10-1 Dederton Data By Droy 12 Novt Customer Master Listing Customer Account_ID Customer Account Name Customer Physical Street Address1 Customer Physical Street Address2 Customer Physical_City Customer Physical_State_Province Customer_Physical_ZipPostalCode Active Date Inactive Date Transaction_Credit Limit Overall Credit Limit Terms Discount Percentage Terms Discount Days Terms Due_Days Sales Orders Sales Order_ID Sales Order_Date Customer Account_ID Sales Order Amount Entered By Entered Date 0.. Entered Time 11 Approved By Approved Date Approved Time Last Modified By Last Modified Date Last Modified Time Shipments Made Shipment ID Shipment Date Shipping Document ID Sales Order ID Sales Order Date Entered By 0.1 Entered Date Entered Time Shipping Amount Local User ID User Listing User_Active Status First Name Last Name Department Role Responsibility 11 Entered By Entered Date Entered Time 1.1 EXHIBIT 10-1 Order-to-Cash Data Invoice ID Invoice Generated Invoice_Date Invoice Due Date 0.1 Sales Order ID 0.5 Entered By Entered Date Entered Time Invoice Amount AR_Adjustments Adjustment_ID Adjustment Type Invoice ID 0..1 Adjustment Date Adjustment Amount Entered_By Entered Date Entered Time Approved By Approved Date Approved Time Last Modified By Last Modified Date Cash Received Last Modified Time Receipt ID Receipt Date Reference Number 0.1 Receipt Type Receipt Amount Posting Status 0 Entered By Entered Date Entered Time As an auditor, you're interested in determining whether the delivery process follows the expected sequence. Specifically, does the delivery follow the sales order, and has each delivery been matched with an invoice? For this part, put yourself in an auditor's shoes and create an analysis that will help answer questions about the order-to-cash process. Microsoft or Tableau Using the skills you have gained throughout this text, use Microsoft Power BI or Tableau Desktop to complete the generic tasks presented below: Build a new dashboard (in Tableau) or page (in Power BI) called Audit that includes the following: 1. Add a table to your page called Exceptions to identify any shipments that occurred before the order was placed. It should show the Sales Order ID and the number of days to ship in ascending order. a. Create a new measure called Order To Ship Days that calculates the difference between the sales order date and the shipment date. Power BI Hint: Use the DATEDIFF() function to calculate the difference and the MIN() function on the date fields to load specific dates. b. Filter this visual on order to ship days to show only negative values. 2. Add a new matrix table called Missing Invoice to determine whether any orders have shipped but have not yet been invoiced. It should list the sales orders, earliest (minimum) shipment date, minimum shipment ID, and minimum invoice ID. a. Filter this visual on invoice ID to show only missing (blank) values. 3. You should find at least one exception here. If you don't see any exceptions, try selecting different months in the sales order date month filter. 4. Take a screenshot of your dashboard showing exceptions and missing invoices (label it 10-1C). 5. Save your workbook, answer the lab questions, then continue to Part 4. Important! To receive credit, you must click the button below to confirm you have completed these steps. Then, click Next to continue with the assignment. OQ1. Which orders were created after shipment? 100045353, 100045425, 100039404 100042192, 100045353, 100045254 100045254, 100044629, 100044583 100042192, 100041628, 100041650 0 OQ2. How many orders have been shipped but not yet invoiced? Number of orders shipped but not yet invoiced EXHIBIT 10-1 On-D Click here for full size Exhibit 10-1. Data: 10-1 02C Data.zip - 1.9MB Zip / 2.0MB Excel Note: Submit your answers for the following Analysis Questions in the text entry field below. AQ1. How do you know some orders were created after shipment? AQ1. Of the shipped orders without an invoice, which one is the most problematic? Why? AQ2. Why aren't the other shipped orders without an invoice suspicious? AQ3. While you still have your auditor hat on, what are some additional analyses you could perform to understand whether the process or processes are being followed or controls are functioning properly? AQ4. Under what circumstances might a delivery take place before a sales order? Should this happen? AQ5. What types of controls would prevent the system from skipping a process or step? Essay Toolbar navigation BIU SE = A Click here for full size Exhibit 10-1. Data: 10-1 02C Data.zip - 1.9MB Zip / 2.0MB Excel We've discussed a few different ways to analyze O2C data to understand the processes and controls. Now it's your chance to find answers to your own questions. Identify five questions that you think management, auditors, or financial analysts would want to know about the O2C process. If you need help, search for some common questions asked by accountants on the Internet. Using the data you have already loaded into Power BI or Tableau, generate at least three visualizations that will help you find the answers to your five questions. Load them into a report or dashboard, and take a screenshot of your dashboard analyses (label it 10-1D). Important! To receive credit, you must click the button below to confirm you have completed these steps. Then, click Next to continue with the assignment. I have completed these steps. Note: Submit your answers for the following Analysis Questions in the text entry field AQ1. Write your first question and provide an answer based on your analysis. AQ2. Write your second question and provide an answer based on your analysis. AQ3. Write your third question and provide an answer based on your analysis. AQ4. Write your fourth question and provide an answer based on your analysis. AQ5. Write your fifth question and provide an answer based on your analysis.