Q4. What are the primary and foreign keys that relate the two tables in this workbook?
Lab 6-2 Perform Substantive Tests of Account Balances
1. Address the question of the data needed, we will compute the accounts receivable to each customer. That is, as of 9/30/2019 how many customers have yet to pay the amount they owe?
2. Open the File SlainteAging-Sept.xlsx
3. Create a pivot Table using the Sales_Order data. Ensure that the Pivot Table will use the Internal Data Model so that you can retrieve fields from both of the tables in the spreadsheet by replacing a check mark next to Add this data to the Data Model in the Create Pivot Table Window.
4. In the Pivot Table Fields window, click All to view both tables in the workbook.
5. Create a Pivot Table that shows the Sales_Order_Total and the Receipt_Total for each Sales_Order_ID.
6. The data will look odd at first, and you will be prompted to create relationships. You can allow excel to auto-detect the relationships, and it will identify the relationship between the primary and Foreign Keys that exist between the two tables.
7. Copy the data in the Pivot Table to a new spreadsheet to convert the Pivot Table data to a range. Doing so will allow us to be able to identify which of the invoices have yet to be paid in full yet. You can ensure that you're copying the range by selecting and copying all the data in the Pivot Table, except for the last raw containing the Grand Total.
8. Add a column to your new range, and calculate the difference between the Sales_Order_Total and the Receipt_Amount.
9. Add a new filter to the Difference Column, and filter out all values that appear as 0's. This will allow you to view all of the invoices that haven't been paid in full yet.
10. This data can be made more interesting by identifying how late the payments are. Return to the Cash_Received spreadsheet in your workbook.
11. Add a column to the Cash_Received table called Sales_Order_Date. This will allow you to easily compare the date of the original Sales order to the date of the payment.
12. Use VLookup formula to look up the date that corresponds with the Sales_Order_ID that each cash receipt corresponds to.
13. Now that you have the Sales_Order_Date easily accessible, you can create another column to calculate the difference between the dates, Create a new column labeled Age, and subtract the Sales_Order_Date from the Cash_Receipt_Date.
14. Your next step is to create a True VLookup formula to assign each cash receipt to an aging bucket. Create an aging table with the following information somewhere on your spreadsheet.
15. Add another new column to the Cash_Received table labeled Bucket, and create a True VLookup formula to identify the bucket for each invoice.
16. We can quickly create a summary of how many invoices fall into each bucket using excel's COUNTIF function. In the column to the right of your aging table, create a column labeled Count.
17. In the cell to the right of you 0 - 30 bucket, type the COUNTIF function. COUNTIF requires two arguments, range and criteria. The range in this case is the Buckets column. The criteria is 3 - 30. COUNTIF will count every instance of 0 - 30 in the buckets column.
18. Repeat the steps for the remaining three buckets. The top two records in the Count column should return to following data
0 0-30 350
30 31-60 285
19. Return to your Pivot Table, and refresh the data so that you can pull in your new fields for further analysis. You can refresh your data by clicking the Refresh button in the Analyze tab from the ribbon.
20. You should now be able to add the Buckets field to the Pivot Table. Do so.
21. Collapse the fields so that you do not see the detail of each invoice within the buckets, but only the totals. The top two records of the Pivot Table will appear as the following:
Row Lables Sum of Receipt_Amount
22. Save your File
Q5. Of the not yet collected balances in each of the four buckets, which bucket is least likely to be collected? Which bucket is most likely to be collected? How would this help us come up with an allowance for doubtful accounts?
23. Now lets assume that three more months have passed, open up the spreadsheet SlainteAging_Dec.xlsx.
Q6. Based on what you have viewed with the September data, what do you expect to find as far as outstanding balances now that the year has finished at the end of December?
24. Repeat the same steps as you did above in the new dataset.
a. Create a Pivot Table that shows the Sales_Order_Total and Receipt_Amount for each Sales_Order_ID.
i Remember to use the Internal Data Model and to build relationships so that the data in your Pivot Table is accurate.
b. Create a range from your Pivot Table data and calculate the difference between the Sales_Order_Total and the Receipt_Amount. Filter the difference to show only the invoices that haven't been paid in full yet.
c. Return to the Cash_Received table and create the additional columns so that you can identify the aging buckets for each invoice.
. Create a Pivot Table to identify which invoices fall into each bucket
25. Save your file as Lab6-2December.xslx, ensuring that the Pivot Table with buckets is included in your final spreadsheet.