ACCOUNTING-REA Homework #2 Pegasus Paper Products

.msn.com/redir/gredir.asp?pageid=commentary-taxes&target=/content/Taxes/Cutyourtaxes/P71102.asp&Namespace=0MCUSENINTERPRESS&HL=10+big+deductions+too+many+people+miss”> REA Homework #2

Pegasus Paper Products
.0/msohtmlclip1/01/clip_image002.gif”>.0/msohtmlclip1/01/clip_image004.gif”>

Pegasus Paper Products Inc.

Your co-worker,
Athletic Annie, was working on the database design project for Pegasus Paper
Products (PPP) when she suffered a slight concussion while playing
racquetball. A survey of all ACG
personnel revealed that you are the only one on your team not currently
assigned to a project and thus your supervisor has instructed you to complete
the project and provided you with your co-worker’s notes, a list of the
attributes PPP wishes to track, as well as a partially completed REA diagram. You are to:
·
Complete the REA diagram and
·
Convert your business process level model into a
set of minimal relational database tables using the following format:

Table

Primary Key

Attributes

Foreign Key

Athletic Annie’s Notes

ü
The products carried by Pegasus Paper
Products are mass-produced, and therefore each instance of a product is not
specifically identified (e.g. with a unique serial number) but instead is
tracked with a product stock number that is the same for all instances of that
product.
ü
Sales to some customers are made in
person, at the store’s South Jersey location.
For those sales, Pegasus Paper Products has a policy of accepting cash
only. Its “in-store” customers tend to
be small volume customers and the marketing department has decided it has no
need to track which specific in-store customers are purchasing which
products.
ü
However, Pegasus Paper Products also
has customers nationwide who use the company’s website. Customers may order products electronically from
the website and customer information for all sale orders placed by via
Pegasus’s internet website must be tracked especially the customer’s billing
and shipping addresses.
ü
A recent sales analysis revealed that
in-store sales transactions were approximately 15% of the revenue and 10% of
the total sales transactions; internet sales comprised approximately 85% of the
revenue and 90% of the sales transactions.
ü
Customers have the option of having Pegasus
Paper Products ship the products as they become available or waiting until the
order is complete (the former requires them to pay extra shipping costs).
ü
Multiple customer orders are never combined
into the same shipment and for internal control purposes, each shipment will be
assigned to exactly one shipper.
ü
Pegasus Paper Products uses We Deliver
for all deliveries however; because We Deliver is a union shop, PPP also has
delivery agreements with two other shippers in case We Deliver’s employees
decide to strike.
ü
Payment terms offered by Pegasus Paper
Products are n/15 and checks are the only acceptable form of payment (debit and
credit card payments are not accepted). Sometimes customers will pay for
multiple sales with a single check, and Pegasus Paper Products needs the
information system to be able to accept partial payments even though those are very
rare (less than 5%).
ü
The internal controls are very tight,
so of course, every return is related to exactly one employee for
accountability purposes.
ü
Pegasus Products does not allow any
sales returns on in-store sales; that is, all in-store sales are final. However, sometimes the incorrect products are delivered to
Pegasus Paper Products’ internet customers and these customers have 30 days to process
a return.
ü Only about 10% of products sold are returned; any return could
consist of multiple products however it would only be related to one sales
shipment, although occasionally (0.1% of the time) Pegasus Products’ receives
multiple returns for the same shipment.
When that happens, the employee involved with that shipment has to
provide a
written explanation for the multiple discrepancies.
ü On-site
sales and cash receipts are processed by cashiers, who enter product
information into an electronic cash register, put the merchandise into bags,
and accept the customer’s payment (providing change to the customer if
necessary).
ü
Store clerks, access the internet-order
information and fill all off-site orders in-between helping on-location
customers, by packing the merchandise and the related invoice into boxes,
labeling the boxes with the customer shipping address, mailing the boxes, and recording
the shipment information. Of course for
internal control purposes each shipment is processed by exactly one store clerk.
ü
When cash receipts are received from off-site
customers, they are also processed by one of the cashiers. They record the cash
receipt and put the checks into the cash register drawer, which serves as an
“on-hand cash account”.
ü Pegasus
Paper Products also has cash accounts at a local bank, where the cash register
drawer contents as well as checks received from occasional non-sale sources
(such as loans) are deposited. At the
end of each day, Pegasus Paper Products determines which cash account has the
lowest balance and make’s that day’s deposit into that account.
ü Pegasus
Paper Products also has a group of purchasing agents who order products from
various vendors, of course each purchase
order is related to exactly one agent.
ü All
purchases must be preceded by an order; purchase agents are never allowed to
just go out and buy something without going through the formal ordering
process.
ü Because
the manager approves ALL purchase orders, her participation is not tracked in
the database. Exactly one purchase agent
is responsible for any one order and orders are accepted by the vendor in their
entirety or not at all.
ü
Vendors accept nearly all (99%) of
Pegasus Paper Products’ orders, and only completed orders are shipped. Multiple orders accepted by the same vendor
are sometimes combined into a single shipment to Pegasus Paper Products.
ü One of Pegasus
Paper Products’ inventory clerks receives the products, unpacks and counts
them, enters the appropriate information into the information system, and
places them on the store display shelves.
ü Pegasus
Paper Products pays for all purchases made from a vendor during a given month
on the last day of that month with one check, and intends never to make partial
payments for purchases of inventory.
ü Each
and every check (including checks written for other things such as loan
payments, wages, etc.) is written by one of Pegasus Paper Products’ accounts
payable clerks and is drawn on one of its cash accounts. Approximately 90% of all checks written are
for purchases.
ü Pegasus
Paper Products wants a well-designed database and plans to enter information
about its employees, cash accounts, products, customers, and vendors before
entering information about any events for which those things need to be
tracked.
ü Regarding
employees, all employee information is categorized by job category type (e.g.
management, salaried non-management, hourly etc.)
ü Purchases
and purchase orders of different types of things (for example, fixed assets or
services) will NOT be included in the same entities with purchases and purchase
orders of product inventory; rather they would be considered as separate
entities (and you are not modeling those).
ü
Pegasus Paper Products does not sell anything other than product inventory. Below, Pegasus
Paper Products has identified attributes that need to be included in its
information system (don’t add or subtract any attributes).
ü
For simplification purposes the zip
code table is not included in this exercise.

1. Date of
sale

30. Quantity
of a product ordered from the Vendor

2. Shipping
address for a customer

31. Manufacturers’
suggested retail price

3. Stock
number for a product

32. Date
products were received from vendor

4. Vendor ID

33. Employee
ID

5. Location of
cash account

34. Date
products were ordered from vendor

6. Vendor name

35. Date
products were ordered by a customer

7. Purchase
Order number

36. Quoted unit
cost of a product ordered from a vendor

8. Date of
cash receipt

37. Quantity
of a product received from a vendor

9. Customer ID

38. Customer
telephone number

10. Product
quantity on hand

39. Quantity
of a product ordered by a customer

11. Total
dollar amount of a sale

40. Total
dollar amount of a cash disbursement

12. Employee
name

41. Actual
quantity of a product sold in a sale

13. Description
of a product

42. Accounts
payable due vendor

14. Cash
receipt ID

43 Total
dollar amount of a cash receipt

15. Receiving
report number

44 Vendor
address

16. Type of
cash account

45 Total
purchase order dollar amount

17. Date of
cash disbursement

46 Cash
disbursement voucher ID

18. Invoice
number

47 Vendor
email address

19. Customer
name

48 Actual unit