Brock Ltd sells home furniture purchased from various furniture manufacturers to customers through the companys sales force in the GTA, Ontario, Canada Purchases The purchasing clerk runs an inventory report everyday to identify the items that should be purchased based on the reorder point of each inventory item When employees from various departments need to purchase something, they fill in a purchase requisition form and send it to the purchasing clerk Brock Ltd creates a purchase order for each purchase of inventory items, office supplies, or services For each purchase order, there is one receipt For each receipt, there is one purchase order All the vendors are required to create one invoice for each purchase order after goods are delivered and received in good condition Accounts Payable The accounts payable clerk prepares checks for payments to various venders Brock normally pays for purchases at the end of each month for all the purchases from each vendor during the month If multiple purchases from the same vendor were made, the accounts payable clerk will combine payments However, each purchase invoice is either unpaid or paid in full If an invoice is received after the checks are prepared, they will hold it until the end of the following month before sending payment The accounts payable clerk stamps each invoice with the check number, the check amount, and the date paid After the check is prepared and sent out to the vendor, they package all of the source documents and file them Some cash disbursements involve parties other than vendors (e g bank loans) Sales Each type of furniture inventory is given a unique identification number and a suggested selling price Each sale includes one or more pieces of furniture and can be paid for in one of the following three ways (1) immediately in cash (2) on the 15th of the following month (3) over the period of six months When customer data is initially entered into Brocks information system, the customer is immediately assigned to a salesperson When a salesperson is hired, he or she is immediately assigned to a group of customers No more than one salesperson participates in making a particular sale A salesperson may negotiate with a customer and agree on a selling price that is lower than that furnitures suggested selling price, especially if the customer is a high volume customer or if that furniture is a slow seller (i e , it has been in stock for a long time) Salesperson commission rate is determined per contractual arrangement with each salesperson and, for a particular salesperson, it is the same percentage rate no matter what items he or she sells Salespersons responsibilities include determining whether credit card sales are approved, approving credit sales, and approving sales returns Cash Receipts Although the vast majority of cash receipts come from customers (any particular cash receipt would be from only one customer) for sales, some cash receipts come from other sources (e g interests received from banks) Every cash receipt is processed by exactly one of Brocks several cashiers and is deposited into one of Brocks bank accounts Other Information about furniture, employees, and customers often needs to be entered into the database before any transactions involving them have occurred Based on your UML class diagram, build a database with MS Access (The solution to Phase I is provided above ) Create a table for each class and each many to many relationship Establish appropriate relationships among the tables using foreign keys Create a sales invoice form (use a form with a sub form) that includes at least the following fields invoice date, invoice number, total amount, inventory type, quantity, price Prepare a query to meet each of the following requirements Query 1 determine which inventory category generated a gross profit of greater than $10,000 Query 2 determine which customers have an outstanding balance of over $1,000 Query 3 determine the commissions earned by each salesperson for Q2 Query 4 create an employee table for those who were born before 1990 Query 5 for all the inventory types with unit cost greater than $500, update the suggested selling price by 10 0 0 Purchases 1 1 0 1 1 3 Vendors 0 1 0 1 0 0 Cash 1 Disbursement 1 1 0 1 1 Furniture Bank Account 1 1 Inventory Employees 1 1 1 1 1 1 1 1 1 0 Cash Receipts 0 0 1 1 0 Customers 0 0 Sales 0 1 1 0

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Mar 21, 2021

Brock Ltd. sells home furniture purchased from various furniture manufacturers to customers through the companys sales force in the GTA, Ontario, Canada. Purchases The purchasing

1. Brock Ltd. sells home furniture purchased from various furniture manufacturers to customers through the company’s sales force in the GTA,
  Ontario, Canada. Purchases The purchasing clerk runs an inventory report everyday to identify the items that should be purchased based on the reorder point of each inventory item. When employees from various departments need to purchase something, they fill in a purchase requisition form and send it to the purchasing clerk. Brock Ltd. creates a purchase order for each purchase of inventory items, office supplies, or services. For each purchase order, there is one receipt. For each receipt, there is one purchase order. All the vendors are required to create one invoice for each purchase order after goods are delivered and received in good condition.
  Accounts Payable The accounts payable clerk prepares checks for payments to various venders. Brock normally pays for purchases at the end of each month for all the purchases from each vendor during the month. If multiple purchases from the same vendor were made, the accounts payable clerk will combine payments. However, each purchase invoice is either unpaid or paid in full. If an invoice is received after the checks are prepared, they will hold it until the end of the following month before sending payment. The accounts payable clerk stamps each invoice with the check number, the check amount, and the date paid.
  After the check is prepared and sent out to the vendor, they package all of the source documents and file them. Some cash disbursements involve parties other than vendors (e.g. bank loans). Sales Each type of furniture inventory is given a unique identification number and a suggested selling price. Each sale includes one or more pieces of furniture and can be paid for in one of the following three ways: (1) immediately in cash; (2) on the 15th of the following month; (3) over the period of six months.
  When customer data is initially entered into Brock’s information system, the customer is immediately assigned to a salesperson. When a salesperson is hired, he or she is immediately assigned to a group of customers. No more than one salesperson participates in making a particular sale. A salesperson may negotiate with a customer and agree on a selling price that is lower than that furniture’s suggested selling price, especially if the customer is a high-volume customer or if that furniture is a slow seller (i.e., it has been in stock for a long time).
  Salesperson commission rate is determined per contractual arrangement with each salesperson and, for a particular salesperson, it is the same percentage rate no matter what items he or she sells. Salespersons’ responsibilities include determining whether credit card sales are approved, approving credit sales, and approving sales returns. Cash Receipts Although the vast majority of cash receipts come from customers (any particular cash receipt would be from only one customer) for sales, some cash receipts come from other sources (e.g. interests received from banks). Every cash receipt is processed by exactly one of Brock’s several cashiers and is deposited into one of Brock’s bank accounts. Other Information about furniture, employees, and customers often needs to be entered into the database before any transactions involving them have occurred.

Based on your UML class diagram, build a database with MS Access.(The solution to Phase I is provided above.)
1. Create a table for each class and each many-to-many relationship.
2. Establish appropriate relationships among the tables using foreign keys.
Create a sales invoice form (use a form with a sub-form) that includes at least the following fields: invoice date, invoice number, total amount, inventory type, quantity, price.
Prepare a query to meet each of the following requirements:
1. Query 1: determine which inventory category generated a gross profit of greater than $10,000
2. Query 2: determine which customers have an outstanding balance of over $1,000.
3. Query 3: determine the commissions earned by each salesperson for Q2
4. Query 4: create an employee table for those who were born before 1990.
5. Query 5: for all the inventory types with unit cost greater than $500, update the suggested selling price by 10%.