Part 1 OQ1: What value would be recorded for total sales revenue on the income statement...

Transcribed Image Text:

Part 1 OQ1: What value would be recorded for total sales revenue on the income statement for 2020? 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? OQ4: If management estimates about 5% sales to be written-off each month, which month exceeded the estimate? OQ5: If management estimates about 3% of accounts receivable balance to be written off, what would be the adjustment amount for bad debts expense at the end of the year? OQ6: Which months accounts receivable balance is most likely to be written off? Required information The order-to-cash (02C) 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. EXIT 10-1 Click here for full size Exhibit 10-1. Data: 10-102C Date.zip - 1.9MB Zip/2.0MB Excel 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 Bl 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 02C 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 O, 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 02C Analysis, answer the lab questions, then continue to Part 2. Part 2 OQ1. What are the total sales orders for November? OQ2. Which day had the highest sales order amount in November? OQ3. Which customer did the company sell the most to in November? OQ4. How much did the company sell to that customer in November? OQ5. What is the value of the oldest outstanding invoice in November? OQ6. What is the age of the oldest outstanding invoice? OQ7. What is the current days sales outstanding KPI value for 2020? Part 1 OQ1: What value would be recorded for total sales revenue on the income statement for 2020? 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? OQ4: If management estimates about 5% sales to be written-off each month, which month exceeded the estimate? OQ5: If management estimates about 3% of accounts receivable balance to be written off, what would be the adjustment amount for bad debts expense at the end of the year? OQ6: Which months accounts receivable balance is most likely to be written off? Required information The order-to-cash (02C) 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. EXIT 10-1 Click here for full size Exhibit 10-1. Data: 10-102C Date.zip - 1.9MB Zip/2.0MB Excel 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 Bl 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 02C 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 O, 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 02C Analysis, answer the lab questions, then continue to Part 2. Part 2 OQ1. What are the total sales orders for November? OQ2. Which day had the highest sales order amount in November? OQ3. Which customer did the company sell the most to in November? OQ4. How much did the company sell to that customer in November? OQ5. What is the value of the oldest outstanding invoice in November? OQ6. What is the age of the oldest outstanding invoice? OQ7. What is the current days sales outstanding KPI value for 2020?