The order-to-cash (020) process, or sales cycle, involves three main processes: 1. Sales order processing 2....

Transcribed Image Text:

The order-to-cash (020) 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. Click here for full size Exhibit 10-1. Data: 10-102C Dats.zip-1.9MB Zip / 2.0MB Excel QS 10-1 Part 3 (Static) Instruction: Audit: Is the Delivery Process Following the Expected Procedure? (LO 10-1) 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 Bl or Tableau Desktop to complete the generic tasks presented below: Build a new dashboard (In Tablesu) or page (In Power B) 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. 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 fiter 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. The order-to-cash (020) 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. Click here for full size Exhibit 10-1. Data: 10-102C Dats.zip-1.9MB Zip / 2.0MB Excel QS 10-1 Part 3 (Static) Instruction: Audit: Is the Delivery Process Following the Expected Procedure? (LO 10-1) 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 Bl or Tableau Desktop to complete the generic tasks presented below: Build a new dashboard (In Tablesu) or page (In Power B) 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. 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 fiter 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.