Pies by Skye is a owner operated company located in Centerville, Pennsylvania. The shop bakes mile-high pies,
Question:
Pies by Skye is a owner operated company located in Centerville, Pennsylvania. The shop bakes “mile-high pies”, to order, and has been popular in the area for years. Recently, Pies by Skye has enjoyed a surge in business that has required expansion of both facilities and employees. As part of this expansion, the owner, Skye LilDude, is also investing in a new information system to help manage parts the business.
The business runs as a kind of “catering” service, where all product is made to order. There is no store front or inventory for finished goods. The pies (finished goods) are created and sold immediately. There is, however, significant maintenance of raw materials inventories that must be time managed for freshness.
The systems to be implemented must be designed for continued expansion. Following are the parts of the system that are out of scope and the parts on which you are to focus.
Accounting Cycle:
Both accounts payables (AP) and accounts receivables (AR) are entered into the General Ledger (GL). The GL maintains various accounts. General Ledger is out of scope. Accounts Receivable and Accounts Payable ledgers are out of scope. Pick up with the incoming invoices from vendors that we pay for items that we order, and the outgoing invoices that we issue to our customers for payment of our pies.
Accounts Receivables
This includes all incoming monies, many of which are out of scope. Incoming payments from invoices that we have issued to customers for our sale of pies are the only A/R in scope.
The sales transactions are initiated through logging an internal document called a sales order. This works hand-in-hand with an invoicing process (to bill customers for our payment) that is explained under “Receiving payment: Invoicing”.
Accounts Payable
This includes all outgoing monies, many of which are out of scope. The only A/P of concern to us are the invoices that we receive for the purchase orders placed with our vendors in the procurement process.
Inventory Management:
- Procurement (purchasing)
Incoming (purchased) inventory is classified into three categories:
- Raw materials for product manufacturing (flour, sugar, shortening, fruit, etc)
- Supplies - both for the shop (shelving, décor, etc) and the office (pencils, etc)
- Packaging – aluminum pans, boxes, cellophane, ribbons, bags, etc. for finished goods.
- Manufacturing and Production
Outgoing raw materials inventory and packaging inventory.
Obviously, finished goods requires some manufacturing process that will use the raw materials. Then the finished goods are packaged for delivery or pickup. This is covered in detail under “Manufacturing and Production”.
- Finished Goods (pies)
Incoming finished goods through manufacturing. Otherwise stated, pies that are input into our system when baking is done.
Obviously, finished goods requires some manufacturing process that will use the raw materials. The details of the manufacturing process is addressed a bit later.
All product is made to order, so there is no long-standing finished goods inventory. But that doesn’t mean that there is no finished goods inventory at all! There has to be an accountability of the items produced so reconcile raw material usage (and write off losses) and sales. As such, when the batches of pies are complete and put into cooling, they are entered into the finished goods inventory so that the entire business cycle can take place. This is covered in detail under “Manufacturing and Production”.
- Sales
Outgoing Finished Goods Inventory through Sales.
When a sale is made, the employee logs the information into an internal sales order that is used to initiate both production and accounting cycles; however, customers do not pay sales orders. They pay invoices that we issue for the finished goods, which is covered under” Receiving Payment: A/R Invoicing”. When the sales/production/invoicing cycles are completed, our finished goods inventory is reduced.
Each of these four areas will be discussed.
- Procurement
The procurement process is done with vetted vendors and is completed through a purchase order process, where we send a purchase order request to a vendor for the items we need. We do not pay purchase orders – we pay incoming invoices that are issued to us, by our vendors, at the time that the products are received. This is covered under “Making Payment: A/P Invoicing”.
Pies by Skye has done business with all vendors in the system and many vendors can supply us the same product. For this reason, we must also store the vendor’s product code. When we enter a product into our inventory, it always has OUR product code assigned to it so that we can make sense of our own inventory. However, we have to know what that product is “called” by each vendor as it is different. You have to determine where it is best to store this special attribute (pay attention to this as it may not be as obvious as you think).
Some of the vendor information that we store is the vendor name, street address 1 and 2, city, state, zip, phone, contact person, e-mail and web site.
When a purchase is placed for raw materials, office supplies and packaging, the items are received with a packing slip which is manually verified (outside of scope of this project). Then, the items are entered into inventory with the proper classification. This includes:
- General inventory, including packaging: Item code (ours), item description, item type (whether it is used for production or not), price per unit, quantity per unit, quantity in stock, reorder limit, storage location (to find it in the warehouse, which is very large).
- For the items that are raw materials, the same information is stored as well as the following additional information: Entry date (this is to ensure that the FIFO method is used so that product doesn’t expire on the shelf), storage temperature, shelf life, special environmental requirements (such as humid, dry, etc.).
Packaging is stored in the same section of the warehouse as supplies. For this reason, it is suggested that this category of inventory be combined into one classification.
- Manufacturing and Production
Through the years, Pies by Skye’s owners have established an extensive line of gourmet recipes. As with any traditional recipe, it consists of directions to create, a list of raw materials that will be used, and the amounts of each. In addition, there could be a pre-prep instruction for some of the raw materials, such as rest to room temperature before using.
Once a batch is completed and are determined to be to standard, they are held in a temporary cooling area before being packaged for delivery (or pickup). When ready, the items are packaged using packaging items (also stored as a finished good classified as non-raw material). These packing items are pulled (deducted) automatically as each item is entered into FG inventory with the FG item code remaining the same.
All production is made to order based on an internal document called a sales order. Each recipe is used in various batches, over time.
- Finished Goods
Once a batch is done, the end time is recorded so that pies are not left out too long before delivery. This is when they are entered into the system as a finished good, so that the sales and invoicing processes can be carried out.
All of the same information is entered into the finished goods inventory management system, which includes: Item code, item description, batch identification, end time recorded, which includes date (a date/time stamp).
The pies are then boxed based on size and type of pie and are ready for delivery or pickup. The system deducts the appropriate packaging items from inventory at the point of the finished goods entry. This simply means that the two are integrated. The finished goods code doesn’t change based on packaging as all items must be packaged as part of the process.
If a batch becomes ruined, there is a process outside of the scope of this project, to account for the lack of finished goods despite the deduction of raw materials and the accounting losses that will be written.
At the point that the finished good is entered, the invoice is generated based off of the original sales order document.
- Sales
All pie sales are made-to-order. There is no store front that offers pies for walk-in customers, so there is no standing maintenance of “finished goods inventory”. They are input and output in very short order. However, there is a list of finished goods (pies) that include type, description, seasonal availability and price.
When an order for pies is taken, the employee enters an internal document called a sales order. This document also initiates the entire cycle for inventory management and to schedule the production of batches of pies based on the need by date. Customers do not pay sales orders. At the end of the production cycle, when the finished goods are entered into inventory, this same sales order information is used for invoicing the customer for payment, as discussed in “Receiving Payment: A/R Invoicing”
Customer information must be collected for each sale, which is stored permanently and appears on both the sales order header and the invoice header. Note that the invoice is generated later to bill the customer for payment.
Following are some of the items that will be stored on the tables discussed (some you must be able to figure out on your own by now (like foreign keys!) – and from the detailed information given):
Customer:
Customer name, street address 1 and 2, city, state, zip, phone and e-mail.
Sales order:
Date, special terms, delivery or pickup instructions, special instructions, subtotal, tax, total due, status (scheduled, in process or completed) and employee who took the order. Then, for the items ordered, the quantity ordered and other typical line item information.
Each sales order has a status, which is “new” upon being entered, then “scheduled” when put into a time slot for batch processing, then “in process” when undergoing Q/A and cooling, and then finally “complete” when packaged and ready for shipment.
Invoicing:
There are two types of invoices – those we receive and those we issue. We receive invoices from vendors for purchase orders we have placed with them. We issue invoices to receive payment from customers for sales orders we have placed for them.
Making Payment: A/P Invoicing
Once we have placed orders with our vendors in the procurement process, we have to pay for the goods we receive. Each vendor will issue an invoice to us, to pay, that relates to the purchase order that we placed with them for items we needed. The vendors issue invoices when the product is shipped.
When we receive a vendor invoice, our AP department enters the information into our system in a standardized format that we must keep for accounting and auditing. We carry our PO number as an FK on the invoice. When the goods are received, the “goods receipt” is entered into the system as well. (out of scope) This is where the “three way match” occurs to ensure no discrepancies between what was ordered, what was received, and what we will pay for. (out of scope)
Receiving Payment: A/R Invoicing
At the end of the production cycle, when the finished goods are entered into inventory, the invoicing process is initiated using the information collected on the original sales order. This is a “bill” for the customer to pay and will eventually become an accounts receivable transaction.
Your Task:
Your task is complete the first two phases of the DBLC – a database study and design, as is presented in Chapter 9. This is nothing more than the analysis skills that you have already learned in CIS 299: Systems Analysis I. EVERYTHING that you need to complete a 100% accurate and comprehensive ERD is given to you throughout this business case. You need to carefully read through it and dissect it, entity by entity and relationship by relationship.
Based on your analysis, you are to complete a comprehensive ERD for the business. You have been given a head start for the ERD where all of the accounting processes are completed. The focus of your design will be expanding/connecting procurement (purchasing), production/manufacturing, inventory management, sales and billing/invoicing. You need to show the ERD for the system by reading through the business rules, applying your understanding of both business and database design, logical thinking and common sense.
All of the business rules have been given throughout the case study. My suggestion is to start going through and making your own concise list of rules with the entities and attributes before you start any ERD.
A Visio file has been started for you. It is the same file that I used to complete my own ERD with only the accounting process left.
There may be more than one way to complete this.
