Overview
Anonymous group
This Professor HATES AI, so this must be in APA; however,, it cannot look AI-generated in any way. See attached template as well as the data sets required and the relevant chapter discussing the work. Description
In accounting analytics, the accuracy and reliability of financial data are crucial for making informed decisions. However, before any meaningful analysis can take place, the data must first be properly prepared. Data preparation involves cleaning, transforming, and structuring raw financial data to ensure that they are accurate, complete, and usable. During this process, identifying and addressing issues such as missing values, inconsistencies, or outliers is essential for maintaining data integrity.
In this context, recognizing common data preparation patterns and understanding how to resolve common dataset issues will help ensure the effectiveness of financial analysis, enabling more precise forecasting, budgeting, and financial reporting.
In the past, you had to use V-LOOKUP and other ways to pull massive amounts of data together into one spreadsheet from a variety of sources. Power Query is a new way to organize and extract data to make informed business decisions. Power Query is commonly used in industries that operate with multiple accounting systems or software programs. For example, a hospital might use one system for billing and another for recording cash receipts or payments, making it difficult to generate a single report that shows outstanding invoices. By using Power Query, data from both systems can be combined to match billing invoices with received payments, allowing accountants to clearly identify which invoices remain unpaid. In this week’s assignments, you will be exploring the Power Query tool and how it is used to extract data.
The following video shows how to use Power Query and explores why using it can be helpful (Stratvert, 2020): How to Use Microsoft Power Query.
Instructions
Go to question PAC 5.2 on page 5-74 of your textbook, and review the 2 .csv tables. You can also access the Sales Order .csv and Cash Receipts .csv files below:
In the real-world, these tables will be larger than the dataset that you are using, but the process is the same. The sales order chart shows the following:
- Order number
- Date
- Amount of order
- Corresponding payments
The cash receipts table includes the following:
- Cash receipts number
- Cash receipt date
- Cash receipt amount
- Type of payments
Follow the steps below or the steps in the template. Using the information from the .csv files and your textbook, open the U3IP template and complete it to address the following in a 3–5-page paper:
- Describe the issues you notice about the .csv data sets.
- What did you learn from creating your first Power Query?
- What does your second Power Query show?
- What is the balance for each sale?
- For which orders have no payments been received?
- Which sales orders are paid off in installments?
You can use the following instructions to generate your first and second Power Queries (Dzuranin et al, 2022)
Step 1: Create a new Excel file, and import the data from both .csv files.
- Use the Data tab in Excel’s main menu, and click "From Text/CSV" in the Get & Transform Data group.
- Open the file with the sales orders data, and click "Load."
- Complete the same process with the cash receipts data.
Step 2: Start the Power Query.
- Open Power Query by going to the Data tab.
- Select the down arrow next to "Get Data" in the Get & Transform Data group.
- Click "Launch Power Query Editor."
Step 3: Restructure the content of the tables.
- In the Queries pane, select and right-click the SalesOrder table.
- Select "Duplicate."
- Rename the newly created table "Accounts Receivable."
- In the old SalesOrder table, select the Payments column and remove this column
- In the Accounts Receivable table, delete all columns except for Order# and Payments.
Step 4: Convert the multivalued column into a single-valued column.
- Select the Accounts Receivable table.
- In Power Query, select the Payments column in the Accounts Receivable table.
- In the Home ribbon, in the Transform group, click the down arrow next to "Split Column."
- Select "By Delimiter."
- In the Split Column by Delimiter window, select "Semicolon" in the field beneath "Select" or "Enter delimiter."
- Select each occurrence of the delimiter underneath "Split at."
Step 5: Group the data into one column. Connect the payments in the Accounts Receivable table with the payments in the CashReceipt table.
- Select the Order# column in the Accounts Receivable table.
- In the Transform ribbon in the Any Column group, click the down arrow next to "Unpivot Columns."
- Select "Unpivot Other Columns."
- Delete the Attribute column, and rename the Value column as "Payments."
Please submit your assignment.
For assistance with your assignment, please use your textbook and all course resources.
Individual Project Rubric
The Individual Project (IP) Grading Rubric is a scoring tool that represents the performance expectations for the IP. This Individual Project Grading Rubric is divided into components that provide a clear description of what should be included within each component of the IP. It’s the roadmap that can help you in the development of your IP.
| Expectation | Points Possible | Points Earned | Comments |
| The student identified and explained the issue with the dataset. | 30 | ||
| The student constructed Power Query 1 and answered the questions. | 30 | ||
| The student constructed Power Query 2. | 30 | ||
| Answer the questions for Power Query 2. | 25 | ||
| Professional Language: Assignment contains accurate grammar, spelling, and punctuation, with few or no errors. | 10 | ||
Total Points | 125 | ||
Total Points Earned |
References
Dzuranin, A. C., Geerts, G, Lenk, M. (2022) Data and analytics in accounting: An integrated approach. (1st edition). Wiley.
Stratvert, K. [Kevin Stratvert]. (2020, November 24). How to use Microsoft Power Query [Video]. YouTube. https://www.youtube.com/watch?v=0aeZX1l4JT4
Requirements: 4-5 pages