Presentation Transcript

1. The Sales/Collection Business Process

   Accounts Receivable QueryJoin & Null to Zero (NZ) considerations
2. Summary Accounts Receivable Query Steps Practically Total sales/service engagements through balance sheet date on account minus total cash receipts through balance sheet date that applied to sales/service engagements on account. Also subtract sales returns and discounts through balance sheet date. Use cash receipt date and duality relationship to determine which cash receipts to include. Be sure to aggregate cash receipts that apply to same sale BEFORE subtracting from sale amount Likely need for Outer Join and NZ function in Access (for sales with no matching cash receipts Note: Sales minus Cash receipts from Customers may not be acceptable (because you might get non-sale related CR from customers)
3. Customer table Cash receipts table Sale table Sale Inventory table Sale Return table
4. Summary Accounts Receivable Query Data sources Sales table Date and summary cash sales data Note if summary cash sales data not stored, it would be necessary to calculate sales figure pulling data from the sales –inventory table Cash receipts table Sales return table
5. Summary Accounts Receivable Step 1: Total Sales through Balance Sheet Date For repetitive queries requiring date criteria, date parameter should be used instead of hard coding date criteria. date as parameter<=[bsdate]
6. When you run the query, a pop up box will appear prompting for criteria input Result of query
7. Summary Accounts Receivable Step 2: Total Cash Receipts related to Sales through Balance Sheet Date 2a: identify them 2b: sum them
8. Summary Accounts Receivable Step 3:Total Sale Returns through Balance Sheet Date Identify and sum them
9. Summary Accounts Receivable Step 4: Calculate A/R as sales minus applicable cash receipts and sale returns AcctsReceivable: [sales total]-[cash receipts]-[returns]
10. When you run query, you will; be prompted to enter date
11. Summary accounts receivable as of 05-31-2010
12. Accounts Receivable Result without Nz As of May 31, 2010 As of May 15, 2010 (notice receivable balance is missing)
13. Adjusted Accounts Receivable Step 4 (using NZ to control for null values) AcctsReceivable: NZ([sales total])-NZ([cash receipts])-NZ([returns]) As of May 15, 2010
14. A/R by Customer Same basic procedure that was used for summary figure would be used for A/R by customer You would need to use grouping feature in QBE Include customer in query
15. Data sources Sales table Date and summary cash sales data Note if summary cash sales data not stored, it would be necessary to calculate sales figure pulling data from the sales –inventory table Cash receipts table Sales return table Customer table
16. 1. Gross Sales By Customer
17. 1. Gross Sales By Customer
18. 2. Sales return by customer
19. 2. Sales return by customer
20. 3. Net Sales by Customer outer Join and NZ used
21. 3. Net Sales by Customer
22. 4. Cash receipts by Customer
23. 4. Cash receipts by Customer
24. 5. A/R balance The following queries will be used to get the A/R balance Net Sales by customer Cash receipts by customer associated with sales events
25. 5. A/R Balance Note: Inner Join has been used
26. 5. A/R balance Results are missing 1 customers Balance missing for customer Outer join and NZ needed
27. Balance: nz([sales])-nz([returns])-nz([cash receipts])
28. 5. A/R balance Correct results