Capstone Project
ACC 640 Accounting Analytics II Capstone Project, Spring 2023 This document should be used to submit your capstone project. It is designed to provide structure and consistent labeling for your submission to facilitate grading and reduce misunderstandings. Instructions: · Use Microsoft Power BI to complete this project. · There are two question sets, and you must complete both. Question Set 1 focuses on order-to-cash (O2C) and Question Set 2 focuses on procure-to-pay (P2P). You will follow the instructions provided in Chapter 10 of your textbook with one exception. The textbook instructions tell you to submit the question sets separately, but you will submit them both in this single document. · There are labels and spaces for all screenshots and answers within this document. · Save this file as “Capstone Project – Your Name.” · Submit this completed file in the appropriate location in Blackboard by the due date listed in the course schedule. Notes: There are two types of questions in most parts of this project: objective questions and analysis questions. For the objective questions, you may give short, single word or number answers because they ask short, objective questions. For example, a question that asks what month recorded the highest sales revenue can be answered in a single word (the month) with no further explanation needed. However, for the analysis questions, you must write your answers in proper sentence form with good grammar and punctuation. The analysis questions are not objective questions with a single right answer. These questions ask you to analyze situations and provide a written analysis, often including your educated opinion. Your answers should be written well enough for the reader (your instructor) to understand your response and award appropriate credit. There are no lab walkthrough videos available for this assignment. QUESTION SET 1: ORDER-TO-CASH QS1 PART 1 FINANCIAL Screenshot 10-1A: QS1 Part 1 Objective Questions: OQ1. OQ2. OQ3. OQ4. OQ5. OQ6. QS1 Part 1 Analysis Questions: AQ1. AQ2. AQ3. QS1 PART 2 Managerial Screenshot 10-1B: QS1 Part 2 Objective Questions: OQ1. OQ2. OQ3. OQ4. OQ5. OQ6. OQ7. QS1 Part 2 Analysis Questions: AQ1. AQ2. AQ3. AQ4. AQ5. AQ6. QS1 PART 3 Audit Screenshot 10-1C: QS1 Part 3 Objective Questions: OQ1. OQ2. QS1 Part 3 Analysis Questions: AQ1. AQ2. AQ3. AQ4. AQ5. QS1 PART 4 What Else Can You Determine about the O2C Process? Screenshot 10-1D: QS1 Part 4 Analysis Questions: AQ1. AQ2. AQ3. AQ4. AQ5. QUESTION SET 2: PROCURE-TO-PAY QS2 PART 1 FINANCIAL Screenshot 10-2A: QS2 Part 1 Objective Questions: OQ1. OQ2. OQ3. OQ4. OQ5. QS2 Part 1 Analysis Questions: AQ1. AQ2. AQ3. AQ4. AQ5. QS2 PART 2 MANAGERIAL Screenshot 10-2B: QS2 Part 2 Objective Questions: OQ1. OQ2. OQ3. OQ4. QS2 Part 2 Analysis Questions: AQ1. AQ2. AQ3. AQ4. AQ5. AQ6. AQ7. AQ8. QS2 PART 3 AUDIT Screenshot 10-2C: QS2 Part 3 Objective Questions: OQ1. OQ2. QS2 Part 3 Analysis Questions: AQ1. AQ2. AQ3. AQ4. AQ5. QS2 PART 4 What Else Can You Determine about the P2P Process? Screenshot 10-2D: QS2 Part 4 Analysis Questions: AQ1. AQ2. AQ3. AQ4. AQ5. Part 1 of 2 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 by calculating account balances used to prepare financial statements. The order-to-cash process provides data to evaluate the revenue, which the company bases on the invoice amount, and accounts receivable balances, which the company calculates based on the difference between the invoice and cash collections and write-offs. The revenue and accounts receivable balances are essential for inclusion in the income statement and balance sheet, respectively. 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 (Tableau) or page (Power BI) called Financial that includes the following: 1. Create a new workbook, connect to 10-1 O2C Data.xlsx, and import all seven tables. Double-check the data model to ensure relationships are correctly defined as shown in Exhibit 10-1. 2. Add a table to your worksheet or page called Sales and Receivables that shows the invoice month in each row and the invoice amount, receipt amount, adjustment amount, AR balance, and write-off percentage in the columns. Tableau Hint: Use Measure Names in the columns and Measure Values in the marks to create your table. Then once your table is complete, use Analytics > Summarize > Totals to calculate column totals. a. You will need to create a new measure or calculated field showing the account AR Balance, or the total invoice amount minus the total receipt amount minus the total adjustment amount. Tableau Hint: To minimize erroneous values from appearing in Tableau due to blank or missing values, use the IFNULL() function to replace blank values with 0, for example, IFNULL([Receipt Amount],0). b. You will need to calculate the write-off percentage as the total AR adjustment divided by total invoice amount. Hint: Format the write-off percentage as a percent or to four decimals. c. Filter this visual to show only values from January 2020 to December 2020. 3. Add a new bar chart called Bad Debts that shows the invoice amount and adjustment amount along with a tooltip for write-off percentage. Tableau Hint: Choose Dual Axis and Synchronize Axis to combine the two values. a. Filter this visual to show only values from January 2020 to December 2020. 4. Clean up the formatting and titles of your visuals and combine them into a single dashboard or page labeled Financial. 5. Take a screenshot of your dashboard showing the account balances (label it 10-1A). 6. Save your workbook as 10-1 O2C Analysis, answer the lab questions, then continue to Part 2. Part 2 of 4 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. For this part, put yourself in a manager’s shoes and create an analysis that will help answer questions about the order-to-cash process. In this case, management focuses on sales orders, rather than invoices as in Part 1, to determine the contract amount and evaluates the age of an open account based on the invoice due date. 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 dashboard (Tableau) or page (Power BI) called Management with the following: 1. Add a filter to show only sales orders from November. 2. Add a table to your page called Total Sales by Day that shows the total sales order amount by sales order date. Power BI Hint: Use the date hierarchy to drill down to specific days of the month. Tableau Hint: Set the sales order date to DAY() and place the total sales order amount as a text mark. 3. Add a bar chart to your page called Sales by Customer that shows the total sales order amount by customer account name in descending order. 4. Add a new matrix table to your page called AR by Customer that shows the customer and invoices in rows, and earliest invoice due date, age, and balance as values. a. Create a parameter showing the Report Date as 12/31/2020. Power BI Hint: Create a new column and use the DATE() function. b. Create a new measure showing the Age as the difference between the Invoice Due Date and the Report Date. Power BI Hint: Use the DATEDIFF() function to calculate the age and the MIN() function on the date fields to load specific dates. c. Use the AR Balance you created in Part 1. d. Filter the table to show only outstanding balances that are greater than 0. 5. Add a new card to your page called Days Sales Outstanding to show the current KPI value. Hint: Create a new measure showing the DSO as the accounts receivable balance divided by the total sales amount multiplied by 30 days. 6. In Tableau, combine all of these visuals into one dashboard. 7. Take a screenshot of your dashboard (label it 10-1B). 8. Save your workbook, answer the lab questions, then continue to Part 3. Part 3 of 4 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. 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. Part 4 of 4 Required information The order-to-cash (O2C) process, or sales cycle, involves three main processes: Sales order processing Order fulfillment and shipping 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. 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