Part A - The order-to-cash (O2C) process, or sales cycle
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.
Click here for full size Exhibit 10-1.
Click here to download full size Exhibit 10-1 as a PDF.
Data: 10-1 O2C Data.zip - 1.9MB Zip / 2.0MB Excel
Part 1 (Static) What is the Total Revenue and Balance in Accounts Receivable?
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:
⦁ 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.
⦁ 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.
⦁ 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).
⦁ 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.
⦁ Filter this visual to show only values from January 2020 to December 2020.
⦁ 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.
⦁ Filter this visual to show only values from January 2020 to December 2020.
⦁ Clean up the formatting and titles of your visuals and combine them into a single dashboard or page labeled Financial.
Take a screenshot of your dashboard showing the account balances (label it 10-1A).
⦁ Save your workbook as 10-1 O2C Analysis, answer the lab questions, then continue to Part 2.
⦁ OQ1. What value would be recorded for total sales revenue on the income statement for 2020?
⦁ Note: Round your answer to the nearest dollar.
⦁ OQ2. Which month recorded the highest sales revenue?
⦁ OQ3. What value would be recorded for total accounts receivable on the balance sheet for 2020, excluding the allowance for doubtful accounts?
⦁ Note: Round your answer to the nearest dollar.
⦁ OQ4. If management estimates about 5 percent of sales to be written off each month, which month(s) exceeded that estimate?
⦁ Note: You may select more than one answer (i.e. more than one month).
⦁ OQ5. If management estimates about 3 percent of the accounts receivable balance to be written off, what would be the adjustment amount for bad debts expense at the end of the year?
⦁ Note: Round your answer to the nearest dollar.
⦁ OQ6. Which month’s accounts receivable balance is most likely to be written off?
Part 1 (Static): Analysis Questions
⦁ AQ1. Why do you think there are no write-offs in November and December?
⦁ AQ2. How could management estimate the write-offs for November and December?
⦁ AQ3. How could analytics provide additional insight into financial information beyond calculating balances?
Part 2 (Static): How Efficiently is the Company Collecting Cash? (LO 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.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
