AIS Project
Organizational Overview:
Smart Tech Computer Co. is a large personal and office computer manufacturing company that distributes to well known tech focused retailers like Best Buy, Amazon, Walmart, and Target. We are based out of Corvallis, Oregon and have over 500 active employees with three main roles. One division in our company is the actual assembly of our computers, another division is our sales and retail relations department to keep our retail stores satisfied with our business, and our shipping/distribution warehouse which handles all deliveries of our computers to our retailers. We average about $30 Million in sales revenue and have a target sales growth of 4% annually.
Every wholesale we approve must meet a minimum quantity of 200 units. Our price per desktop computer is between $500-$700 depending on the make and model. Our price per laptop ranges from $650-$800 also depending on make and model. Our four main retailers order on average 500-2000 units every quarter.
Process Overview:
The specific process of Smart tech we are reviewing is the companies sales process. The goal of the sales process is to receive the customers specific order. Make sure the warehouse has the necessary quantity to complete the order, and report back to the customer and complete the invoice. The resources involved are our website and sales team filling out orders to be reviewed by the warehouse, the warehouses inventory on hand, and the accounting team ordering the new inventory in case our current inventory can’t complete a customers order. Some success factors are ordering computers and laptops that suffice our customers needs to keep a steady flow of our customers order time to the completion of the order which would be when the customer receives the shipment of computers/laptops. Some risks we face is having to put customers on backorder in case we don't have a sufficient quantity for the customers order. We could possibly lose business with certain retailers if this becomes a recurring problem. If this problem occurs then we notify the accounting department to order more computers for our warehouse and notify the customer of the backorder. Another risk we may face is incorrect information from a customer invoice. If this occurs we could potentially send out the wrong order and risk losing business with a customer. If this happens we need the warehouse to double check the invoice with our retail customer to make sure the correct order information was received.
Process Description:
The Sales process begins with one of our retail customers placing an order for computers and laptops. The Retailer would either call one of our sales representatives or place an order on our website which would be sent to a sales representative as well. This would then lead to whatever sales rep receiving the order to gather information from the Retailer placing the order. If the Retailer is a recurring customer then the sales representative would Create a new invoice. If the Retailer is a new customer then the sales rep would begin by adding the Retailers information like company name, city, state, address, and contact information into Smart Tech’s data store so the Sales department could retrieve this information at any time. Once all the new information is added then you would create the invoice for the new customer.
The invoice created for the retailers order will contain the customers ID, the Date of the Invoice, the Invoice ID, and the quantity of the order as well as the Computer ID which is the model of the computer/laptop being ordered. Once this Invoice is created it will get sent to the data store to be saved in Smart Tech’s database. The Warehouse will receive this invoice from the Sales team and begin to check if they have the necessary quantity available to complete the Customers order. They will record the quantity of all their makes and models in the warehouse and send that information to the companies Data store. Once the Warehouse checks their inventory and records the amount, they will either approve or decline the order. If the order is approved then the Sales team is notified and the Retailer will then pay for the invoice. If the order is declined by the warehouse. Then this means the warehouse will contact the accounting department and let them know about the insufficient amount of computers/laptops the warehouse has and tell them how much they need to order. The Accounting department will then order the new inventory for the warehouse. Once the Accounting Department places the order for the warehouse they will notify the Sales Department about the back order. The Sales team will then notify the customer of the back order that they will be placed on, and let the customer know about how long that back order will take. This could lead to the customer to not complete the invoice and discontinue business with Smart Tech, which would result in the end of our sales process.
If the Customer decides to pay for the invoice whether or not there is a back order, the sales team will then finalize the sale by transferring the financial details of the transaction over to the Accounting Department. The Accounting department will record the transaction into accounts receivable and send the billing statement and customer information into the customer accounts database.
Once the Accounting department records the billing statement and finalizes the invoice, they will send it over to the warehouse. The warehouse will receive the invoice which gives them the green light to begin Smart Tech’s shipping and handling process. Once the shipping process is completed the Retailer will receive their computers/laptops which will end the process.
Transactions and Objects:
A transaction table is used to record an event in a process. An object table stores information about a person, product, or service that is associated with the business process.
Object tables:
One object table in our sales process is our inventory data storage. In this object table we have records of how many desktops and laptops we currently have on hand in our warehouse. Smart tech produces two different desktops and three different laptops. Our inventory records will reflect the quantity of each type of computer that we produce so our sales team will always be updated on the amount of inventory that we have on hand. This data store is vital to the success of our company because we don’t want to promise a shipment to a customer if we don’t have the computers on hand to ship
Another object table that we keep in our database is our customer credentials storage. In this table we assign a customer number to each company that we do business with. This helps us with our invoice and shipping processes because we need to link our invoices with each customer. When we create our invoices we will have the customer number at the top of the page that has all of the customer credentials such as their company name, address, and date that the invoice is processed. At the beginning of our sales process we make sure to check whether or not we are dealing with a new or existing customer. If they are new we make sure to get all of their credentials entered into our system so that we can proceed with the rest of our sales process.
Transaction tables:
A transaction table that we use is our invoice generation process. When a customer comes to our sales team and puts in and order for our computers we generate an invoice that reflects the quantity and and type of computers that the customer wants. This transaction is then passed along to our warehousing staff to see if we have the inventory on hand to satisfy their order. Each order that is submitted to our sales team will be assigned and invoice number so that our accounts receivable department can see the money we have coming in, who is buying it, and the amount of each computer that they are buying. Assigning an invoice number to each of our orders ensures that we will have detailed records of each order and will be able to project future cash flows.
When we receive an order from a customer and do not have enough inventory on hand we send a buy order to our accounts payable department. This transaction ensures that we will have enough computers on hand to satisfy our customers order. Once the order is made we notify the customer that their order has been placed on backorder and will out for shipping as soon as possible. Not having enough computers on hand to satisfy our customers orders is never ideal so we have generated a process to make this order as fast as possible.
Figure 1 – This shows the flowchart for the sales process of Smart Tech Computer Co.
Data Diagram
This document depicts the database for the sales process of Smart Tech Computer Co.
Figure 2 – This diagram shows the relationships between primary and foreign keys among all tables and attributes in the database.
Explanation of database design
Our database design includes the following object, transaction, category and intersection tables:
Object tables:
· Customers
· Inventory
Transaction table:
· Invoices
Category table:
· Computer_Type
Intersection table:
· Invoice_Inventory
Table Explanations:
Customers: The primary key for our customer table is our Customer ID Number. Each customer has their own Customer ID so that we can discern between each customer that we do business with. The foreign keys for our customer table are Customer Name, Street, City, State, and Zip Code. This information in our customer table is used for our shipping and accounts receivable departments.
Inventory: The primary key in our inventory table is our Inventory ID. We offer two different types of desktops and three different types of laptops so the Inventory ID reflects which type of computer we are referring to. The foreign keys in our inventory table are description, unit price in dollars, and units on hand. The description key lets us know which model of computer we are selling. For our laptops we have a C250, C300, and Z260 model. For our desktops we have an A1000 and A2000 model. The unit price in dollars reflects the price point that we are selling our computers at. Since we have a different price point for each computer this key is important for our accounting department and allows us to run data analytics to see where we are generating our profit from. Lastly we have a units on hand key. This key is used to communicate between the warehouse and sales team. When the sales team receives an order they are able to see how many units we currently have on hand and will let the customer know if we are able to satisfy their order. If we don’t have enough computers to satisfy their order the accounts payable department is contacted and they will place an order for more computers.
Invoices: The primary key for the invoices table is the invoice ID number. Each invoice has a unique ID that distinguishes it from other invoices. One of the foreign keys for our Invoice table is the customer ID, each customer has an ID number and we use it in the invoices table to know which customer is related to each Invoice. The second foreign key is the date. This information is important because it lets us know when the order was placed and gives us a timetable to adhere to.
Computer_Type: The primary key in the computer type table is the Computer ID which is an ID that represents the computer types that we are selling. In our company, we only sell two types of computers thus ID number 1 represents laptop and Number 2 represents desktop.
Invoice_Inventory: The primary key in our invoice inventor table is the Invoice Inventory ID. This number represents the try and amount of each computer bought on each invoice. It separates each invoice into a breakdown of how many specific types of computers were purchased on each invoice. The foreign keys in this table are Invoice ID, Inventory ID, Quantity, and Computer ID. The Invoice ID represents which invoice the purchase was made on so we can then see what company made the purchase. The Inventory ID denotes which type of computer was purchased, either a laptop or desktop. The quantity lets us know how many of the computers were purchased and the Computer ID represents the exact model of computer that was ordered. This table gives us a detailed breakdown of the purchases of our buyers and can be used in many different evaluation functions.
The Invoice_Inventory intersection table supports a many-to-many relationship between the Invoices table and the Inventory table.The intersection table, itself, is not a list of individual events or objects of interest, rather its purpose is to record and association between items in the other tables. It has its own primary key and has two foreign key attributes Invoice_ID which points to the primary key of Invoices and Invoice_ID which points to the primary key of Invoices.
Data
The sample data is provided to illustrate how the database design stores and uses its data.
Customers
Table 2.1 - Our Customers table gives each customer an ID number, and shows the name and address of each customer. There is a one to many relationship between the customer and Invoices table.
Inventory
Table 2.2 - This table gives each of our products an ID number which is the primary key for this table, but used as a foreign key in the Invoice_Inventory table. This table also provides the name of each product, the price, and how many units the company has in stock.
Invoices
Table 2.3 - The invoices table gives each invoice an ID number, and provides the date of the invoice. This table also includes the customer ID of the customer that made the purchase, which is a foreign key from the customers table (note the one to many relationship).
Computer_Type
Table 2.4 - Our category table tells which of our products is a desktop, and which is a laptop.
Table 2.5 - Our intersection table gives us a table that brings our many to many relationships together. The table tells us which products were purchased on which invoice and the quantity of each. The table also tells us whether the items were a laptop or a desktop. We can later use this information to determine which product line is our best sellers.
Note the multiple many to many relationships in this table. For example many invoices can have many different pieces of inventory, and can have many different computer types.