18.8 Explain how to calculate the total amount of accounts payable:
Total Accounts Payable can be calculated in five steps, as follows:
Step 1: Normally, only orders actually received are considered purchases for purposes of
calculating accounts payable. Therefore, begin with a query of the Receive
Query 4.
18.9 Refer to Figure 18.4 and Table 18-1 to write the query logic needed to answer the
following questions. (Optional: If requested by your instructor, write your queries in
SQL or a Query-By-Example graphical interface.) Some answers may require more
than one query—try to write the most efficient queries possible.
a. Accounts payable for all suppliers in Arizona
b. Total amount of sales to a customer named Smith
c. Total wage expense
Pay rates are likely to differ across employees. Therefore, calculate total wage expense by
summing the wage expense for each employ, as follows:
d. Total wages payable
e. Net increase (decrease) in quantity-on-hand for a particular inventory item
f. The proportion of sales made to walk-in customers (i.e., no order)
Step 1: Write a query to calculate total sales by summing the product of quantity sold
times unit price in the Sales-Inventory M:N relationship table.
g. The salesperson who made the largest amount of sales in October
Step 1: Write a query to calculate total sales by summing the product of quantity sold
times unit price in the Sales-Inventory M:N relationship table.
h. The salesperson who made the most sales in October
i. The most popular item, in terms of total units sold
18.10 Refer to Problem 17.10 and develop a set of tables to implement the REA diagram
you developed for Stan’s Southern Barbeque Supply Store. Identify the primary and
foreign keys for each table, and don’t forget to address any M:N relationships.
Table Primary key Foreign Keys Other attributes
Order Inventory Purchase
Order#
Employee#,
Vendor#,
Date
Inventory-Cash
Disbursement
Report#,
Check#
SUGGESTED ANSWERS TO THE CASES
1. Calculate total Accounts Receivable.
a. Calculate beginning Accounts Receivable by summing the beginning balance
2. Calculate Accounts Receivable for a specific customer.
This requires a similar set of queries as used to calculate total Accounts Receivable:
a. Calculate beginning Accounts Receivable by summing the beginning balance
attribute in the Customer table for the customer of interest.
3. Create a sales invoice form that references the appropriate tables and inputs
data about attributes into the proper tables.
4. Calculate as many financial statement items as possible from the data model you
implement.
Income Statement items derivable from Figure 18.4:
Only Sales and Wage Expense can be definitively calculated; cost of goods sold
requires making assumptions about inventory cost/valuation method (FIFO, LIFO,
weighted average, specific identification).
Sales
Only Cash, Accounts Receivable, Accounts Payable, and Wages Payable can be
definitively calculated. Inventory valuation requires an assumption about inventory
method (FIFO, LIFO, weighted average, specific identification).
Step 1: Calculate the beginning balance of cash by summing the amount
column in the Cash table.
quantity sold times unit price from the Sales – Inventory M:N relationship
table.
c. Calculate total cash received from customers by summing amount
received column in the Receive Cash table.
d. Total Accounts Receivable = Query A + Query B – Query C
Accounts Payable
the Suppliers table and summing the beginning balance column.
rows where the supplier number is null, as those represent other types
5. Design appropriate input controls for the sales invoice form created in step 3.
The syntax required will vary depending upon the DBMS used. Students should be
encouraged to review material from chapter 10 to identify appropriate input controls.
Solutions should include the following:
1. Auto-number the sales invoice to prevent creating duplicate or null primary keys.