Building Accounting Information System using MS Access

Database Assignment

(Fall 2024)

Building Accounting Information System using MS Access

(100 marks)

1. all accounts’ beginning balances are zero

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 companywebsite. 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 businessactivities. However, storing and analyzing the data in MS Excel has numerous limitations. SPEwould like to use MS ACCESS to record, store analyze and report its business activities within therevenue cycle. As the company’s Accounting Manager, you are asked to design and develop thisdatabase for SPE. The following paragraph describes the revenue cycle activities and businessrules of SPE.A customer of SPE places an online order via the website. The order indicates the products thathe customer wants to purchase. SPE sends a sales invoice to the customer together with thedelivery of the products. When the customer settles the sales invoices, he/she can mail the chequesto SPE or transfer the money to SPE’s bank account through electronic fund transfer. The businessrules are as follow: (i) one order can be fulfilled by several sales but one sale can only be associatedwith one order, (ii) each sales order or sale can include one or more inventory items and for eachinventory item, it can appear in many sales orders or sales, (iii) one sales representative / customercan participate in one or more orders or sales events and each order or sale event is participated bynly one sales representative / customer, (iv) each money receipt from a customer settles one salesinvoice and for each sales invoice, partial settlement by customer isnot allowed, (v) each cashier

/ customer can participate in many money receipts, and for each receipt, it can only be participated

by one cashier / customer, (vi) each receipt will be deposited into one single bank account and the

bank account can take many money receipts.

Required:

Assumall transactions happen in January and February 2024

(20 marks)Using Microsoft Access, create the appropriate entities (or tables) based on the data elements given

in the appendix. Choose the primary key for each entity. Fill in those entities with appropriate data.Decide on the relationship type between entities based on the business rules described above andthe business rules normally adopted by commercial organizations. Link up those entities by joining

the primary and foreign key together. You should follow 代 写Building Accounting Information System using MS Access  the pattern as stipulated by the REAdiagram and draw the diagram in the relationship view of MS Access. Note that some of thePart I: Design SPE’s database using REA diagram and draw it using Microsoft Access2relationship types between entities may be unspecified and you need to think critically and makeyour own judgement based on the materials that you learn in class.

30Part II: Build the database using ACCESS marks)Create a database file using yoursession weekday and student IDs as filename. Use the following

guidelines in coming up with the data of the tables.

Required number of entries (records)

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

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 accountPoint to note when you create your data:If a sales invoice includes many inventory items and you want to show the quantity sold foreach 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 thefields in the sales table, you can only sell one inventory # in each sales invoice given there isonly one cell for input.

Leave the Receipt # as blank in the Sales table if customer has not made any payment to settleThe data created should be consistent with the type of relationship. Thatis, if the relationshipis many-to-many (one-to-many), you must come up with table(s) and data that illustrate themany-to-many (one-to-many) relationship.

Hints:an invoice. You can retrieve these sales by using the criteria: is null, within the receipt # field.3(50 marks)

Part III: Query your database

1. Generathe January 2024 sales report; in your query output, you should display the following

nformation: 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 displaythe following information: inventory item#, inventory item description, sum of quantityold, unit price, unit cost, total revenue, total cost of goods sold and gross margin amountand gross margin percentage.

(10 marks)(Hints for iii: If you use two queries, the first query takes out those sales in January. Thesecond query utilizes the first query to calculate total revenue, total cost ofgoods sold andgross 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 monthscombined? Rank the quantity sold in descending order. Your output should display inventoryitem#, inventory item description and sum of quantity sold. (No date field is necessary as youare including all sales data in both January and February)(7 marks)

4. Assume a sales commission rate of 5%, generate a report that shows the commission income bysalesman for January. The commission income is based on the total sales made by a salesmanmultiplied by the commission rate. Your report should show employee #, employee name andcommission. (Hint: use one of the tables and query 1(i)).(8 marks)
5. Which customers have not settled their sales invoices? Generate a report that shows thefollowing fields: sales invoice #, sales invoice date, customer #, customer name, employee #,employee name, and receipt #.10 marks)4

Appendix

The following data elements and types are given to you. You may leave some of the dataelements nil entries as they are not relevant in generating the queries in part III. Example of thosedata elements are Address, Telephone, Credit Limit, Date Hired, Date of Birth, etc. Note that

some of the data elements may appear in more than one table.Bank Account BalanceCurrency or Number

Note:

1. Credit Limit means the dollar limit that SPE gives to a customer. This field is only forinformation 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 inthe bank account balance as the query does not use this field.

-End-