Building Accounting Information System using MS Access

Database Assignment (Fall 2024) Building Accounting Information System using MS Access (100 marks)
SPE Limited sells different kinds of smartphones that it purchases from different manufacturers. Its customers purchase their desired products via filling in an online order form in the company website. Peter Watsons started the company 6 years ago and the company revenue has grown four times since its incorporation. Currently, SPE uses MS Excel to record the revenue cycle business activities. However, storing and analyzing the data in MS Excel has numerous limitations. SPE would like to use MS ACCESS to record within the revenue cycle. As the company’s Accounting Manager, you are asked to design and
database for SPE. The following paragraph describes the revenue cycle activities and business rules of SPE.
A customer of SPE places an online order via the website. The order indicates the products that the customer wants to purchase. SPE sends a sales invoice to the customer together with the delivery of the products. When the customer settles the sales invoices, he/she can mail the cheques to SPE or transfer the money to SPE’s bank account through electronic fund transfer. The
, store analyze and report its business activities
           rules are as follow: (i) o with
can
Required:
Assume
1. 2.
but
  inventory item, it can appear in many sales orders
invoice and for each sales invoice, partial settlement by customer is
all accounts’ beginning balances are zero
all transactions happen in January and February 2024
Part I: Design SPE’s database using REA     it using
will
Using Microsoft Access, create the appropriate entities (or tables) based on the data elements given in the appendix. the     key for each entity. Fill in those entities with appropriate data. Decide on the     type between entities based on the business rules described above and the business rules normally adopted by commercial organizations. Link up those entities by joining the primary and foreign key together. You should follow the     as stipulated by the REA diagram and draw the diagram in the relationship view of MS Access. Note that some of the
1
Choose
relationship
ne order can be fulfilled by several sales
or sales, (iii) o
participate in many money receipts, and for each receipt, it can only be participated
one sale can only be associated
ne sales representative / customer
develop this
 participate in one or more order
s or sales
allowed
is     by settles one sales
, (v) each c
participated
business
   one order, (ii) each sales order or sale can include one or more inventory items and for each
  only one sales representative / customer, (iv) e
events and each order or sale event
 ach
money
receipt from a customer
   not
ashier
    / customer
can
    by one cashier / customer, (vi) each receipt
 bank account can take many money receipts.
be deposited into one single bank account and the
      diagram
and draw
Microsoft
 Access
 primary
(20 marks)
 pattern
  
relationship types between entities may be unspecified and you need to think critically and make your own judgement based on the materials that you learn in class.
   Part II: Build the
 database
 using ACCESS
 Create a database file using your session weekday and student IDs as filename. guidelines in coming up with the data of the tables.
Required number of
Create at least 5 customers
Create at least 7 inventory items
Create at least 8 sales orders, with 5 in Jan. and 3 in Feb. 2024 Create at least 10 sales, with 6 in Jan. and 4 in Feb. 2024
(30 marks)
Use the following
  entries
 (records)
        Create at least two employees: one salesman, one cashier. Use your full name(s) as employee name(s). If two students work together, then one student acts as salesman and the other student acts as cashier.
 Create at least 8 money receipts, with 2 in Jan. and 6 in Feb. 2024. There should be at least one sales invoice that has not been settled in your database.
Create at least 1 bank account
Point to note when you create your data:
The data created should be consistent with the type of relationship. That is, if the relationship is many-to-many (one-to-many), you must come up with table(s) and data that illustrate the many-to-many (one-to-many) relationship.
Hints:
If a sales invoice includes many inventory items and you want to show the quantity sold for each item, it is not feasible to include the “Quantity Sold” field in the sales table because each sale is represented by only one row in the sales table. If you include inventory # as one of the fields in the sales table, you can only sell one inventory # in each sales invoice given there is only one cell for input.
Leave the Receipt # as blank in the Sales table if customer has not made any payment to settle an invoice. You can retrieve these sales by using the criteria: is null, within the receipt # field.
2
   
1. Generate
i the January 2024 sales report; in your query output, you should display the following information: sales invoice date, sales invoice number, sales invoice amount, customer #, employee # and receipt #. (10 marks)
ii the total January revenue using the query in (i). (5 marks)
iii the January 2024 gross margin by inventory item; in your query output you should display the following information: inventory item#, inventory item description, sum of quantity sold, unit price, unit cost, total revenue, total cost of goods sold and gross margin amount
and gross margin percentage. (10 marks)
(Hints for iii: If you use two queries, the first query takes out those sales in January. The second query utilizes the first query to calculate total revenue, total cost of goods sold and gross margin. In the second query, do not include any date field.)
2. Which inventory item is most popular in terms of total sales quantity for the two months combined? Rank the quantity sold in descending order. Your output should display inventory item#, inventory item description and sum of quantity sold. (No date field is necessary as you are including all sales data in both January and February) (7 marks)
3. Assume a sales commission rate of 5%, generate a report that shows the commission income by salesman for January. The commission income is based on the total sales made by a salesman multiplied by the commission rate. Your report should show employee #, employee name and commission. (Hint: use one of the tables and query 1(i)). (8 marks)
4. Which customers have not settled their sales invoices? Generate a report that shows the following fields: sales invoice #, sales invoice date, customer #, customer name, employee #, employee name, and receipt #. (10 marks)
(50 marks)
Part III:
 Query
 your database
 3

Appendix
The following data elements and types are given to you. You may leave some of the data elements nil entries as they are not relevant in generating the queries in part III. Example of those
data elements are Address, Telephone, Credit Limit, Date Hired, Date of Birth, etc. some of the data elements may appear in more than one table.
Note that
Field
Customer #
Customer Name
Customer Address Customer Telephone Credit Limit
Employee #
Employee Name Employee Title Commission Rate
Base Salary
Date Hired
Date of Birth
Sales Order #
Sales Order Date
Quantity Ordered
Sales Invoice #
Sales Invoice Date Inventory Item #
Inventory Item Description Unit Price
Unit Cost
Quantity sold
Receipt #
Receipt Date
Receipt amount
Bank Account #
Bank Account Type
Bank Account Balance
Note:
Date Type
Text or Number Text
Text
Text
Currency or Number Text or Number Text
Text
Number
Currency or Number Time/Date Time/Date
Text or Number Time/Date
Number
Text or Number Time/Date
Text
Text
Currency or Number Currency or Number Number
Text
Time/Date
Currency or Number Number
Text
Currency or Number
1. Credit Limit means the dollar limit that SPE gives to a customer.
information purposes. Students can leave it as blank as the query does not use this field.
2. Example of bank account type is saving account or checking account. You could have zero in the bank account balance as the query does not use this field.
-End-
This field is only for