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. \begin{tabular}{|l|} \hline \multicolumn{1}{|c|}{ Customer_Master_Listing } \\ \hline Customer_Account_ID \\ Customer_Account_Name \\ Customer_Physical_Street_Address1 \\ Customer_Physical_Street_Address? \\ 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 \\ \hline \end{tabular} \begin{tabular}{|l|l|} \hline \multicolumn{1}{|c|}{ Sales_Orders } \\ \hline Sales_Order_ID Sales_Order_Date Customer_Account_ID Sales_Order_Amount Entered_By Entered_Date Entered_Time Approved_By Approved_Date Approved_Time Last_Modified_By Last_Modified_Date Last_Modified_Time \\ \hline 1.1 & \\ \hline \end{tabular} \begin{tabular}{l} User_Listing \\ 1.1. User_ID \\ User_Active_Status \\ First_Name \\ Last_Name \\ Department \\ Role_Responsibility \\ Entered_By \\ Entered_Date \\ Entered_Time \\ \hline \end{tabular} \begin{tabular}{|l|} \hline \multicolumn{1}{|c|}{ Invoice_Generated } \\ \hline Invoice_ID \\ Invoice_Date \\ Invoice_Due_Date \\ Sales_Order_ID \\ Entered_By \\ Entered_Date \\ Entered_Time \\ Invoice_Amount \\ \hline \end{tabular} \begin{tabular}{|l|} \hline \multicolumn{1}{|c|}{ AR_Adjustments } \\ \hline Adjustment_ID \\ Adjustment_Type \\ Invoice_ID \\ Adjustment_Date \\ Adjustment_Amount \\ Entered_By \\ Entered_Date \\ Entered_Time \\ Approved_By \\ Approved_Date \\ Approved_Time \\ Last_Modified_By \\ Last_Modified_Date \\ Last_Modified_Time \\ \hline \end{tabular} EXHIBIT 10-1 Order-to-Cash Data 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,100039404100042192,100045353,100045254100045254,100044629,100044583100042192,100041628,100041650 OQ2. How many orders have been shipped but not yet invoiced? ExHIBIT 10-1 Ordohocual Das Click here for full size Exhibit 10-1. Data: 10-1 O2C 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? Click here for full size Exhibit 10-1. Data: 10-1 O2C Data.zip - 1.9MB Zip / 2.0MB Excel Ne've discussed a few different ways to analyze O2C data to understand the processes and controls. Now it's your chance to find inswers to your own questions. dentify five questions that you think management, auditors, or financial analysts would want to know about the O2C process. If you leed help, search for some common questions asked by accountants on the Internet. Jsing the data you have already loaded into Power BI or Tableau, generate at least three visualizations that will help you find the inswers to your five questions. Load them into a report or dashboard, and li take a screenshot of your dashboard analyses (label it 0-1D). mportant! To receive credit, you must click the button below to confirm you have completed these steps. Then, click Next to ontinue 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. 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. \begin{tabular}{|l|} \hline \multicolumn{1}{|c|}{ Customer_Master_Listing } \\ \hline Customer_Account_ID \\ Customer_Account_Name \\ Customer_Physical_Street_Address1 \\ Customer_Physical_Street_Address? \\ 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 \\ \hline \end{tabular} \begin{tabular}{|l|l|} \hline \multicolumn{1}{|c|}{ Sales_Orders } \\ \hline Sales_Order_ID Sales_Order_Date Customer_Account_ID Sales_Order_Amount Entered_By Entered_Date Entered_Time Approved_By Approved_Date Approved_Time Last_Modified_By Last_Modified_Date Last_Modified_Time \\ \hline 1.1 & \\ \hline \end{tabular} \begin{tabular}{l} User_Listing \\ 1.1. User_ID \\ User_Active_Status \\ First_Name \\ Last_Name \\ Department \\ Role_Responsibility \\ Entered_By \\ Entered_Date \\ Entered_Time \\ \hline \end{tabular} \begin{tabular}{|l|} \hline \multicolumn{1}{|c|}{ Invoice_Generated } \\ \hline Invoice_ID \\ Invoice_Date \\ Invoice_Due_Date \\ Sales_Order_ID \\ Entered_By \\ Entered_Date \\ Entered_Time \\ Invoice_Amount \\ \hline \end{tabular} \begin{tabular}{|l|} \hline \multicolumn{1}{|c|}{ AR_Adjustments } \\ \hline Adjustment_ID \\ Adjustment_Type \\ Invoice_ID \\ Adjustment_Date \\ Adjustment_Amount \\ Entered_By \\ Entered_Date \\ Entered_Time \\ Approved_By \\ Approved_Date \\ Approved_Time \\ Last_Modified_By \\ Last_Modified_Date \\ Last_Modified_Time \\ \hline \end{tabular} EXHIBIT 10-1 Order-to-Cash Data 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,100039404100042192,100045353,100045254100045254,100044629,100044583100042192,100041628,100041650 OQ2. How many orders have been shipped but not yet invoiced? ExHIBIT 10-1 Ordohocual Das Click here for full size Exhibit 10-1. Data: 10-1 O2C 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? Click here for full size Exhibit 10-1. Data: 10-1 O2C Data.zip - 1.9MB Zip / 2.0MB Excel Ne've discussed a few different ways to analyze O2C data to understand the processes and controls. Now it's your chance to find inswers to your own questions. dentify five questions that you think management, auditors, or financial analysts would want to know about the O2C process. If you leed help, search for some common questions asked by accountants on the Internet. Jsing the data you have already loaded into Power BI or Tableau, generate at least three visualizations that will help you find the inswers to your five questions. Load them into a report or dashboard, and li take a screenshot of your dashboard analyses (label it 0-1D). mportant! To receive credit, you must click the button below to confirm you have completed these steps. Then, click Next to ontinue 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