博客

Project Assignment Description

Project Assignment Description BackgroundRed runs a successful chain of cheese (and wine) shops in Victoria’s Yarra Valley that specialize in Victorian···

Background

Red runs a successful chain of cheese (and wine) shops in Victoria’s Yarra Valley that specialize in Victorian artisan cheeses. He also sells his cheeses over the internet. He is considering expanding his business, but he thinks his current accounting and financial systems are restricting his ability to expand. He has come to you for assistance.

Instructions  Project Assignment Description

Your supervisor met with Red and discussed the BPMN and UML models and explained how they could lead to development of a database for Red Cheeses. Red supplies current records as Excel spreadsheets so that you can develop a relational database and generate some initial financial analyses from it. Your supervisor also discussed how conversion of the recording processes to a relational database might overcome several of the control shortcomings in existing processes. Red is interested and asks for more details.

1. Transfer the Excel worksheets into Access. The worksheets are generally close to the final file structures. You should find and correct data redundancies, such as supplier name in the “purchases admin” worksheet along with the supplier#. Also, you should note redundancies in the “sales” table and split that table into “sales” event information and “sales items” information for use in the appropriate linking table.
2. Link the tables in Access, enforcing relational integrity. Luckily your supervisor was able to combine the data modelling work that all the staff did in Stage 1 of the project and has a UML Class diagram to assist with the associations. Payroll and TimeCards have been removed to simplify the database.
3. Prepare the four queries listed below. Each group member should be responsible for one of the queries and then discuss their solution with the rest of the group.
4. Students should identify Red Cheeses’ AIS requirements and then assess whether internet-based accounting systems, such as XERO, QuickBooks Online, Netsuite, INTACCT, and others, can meet those requirements at reasonable cost.

Deliverables  Project Assignment Description

1. Each group will submit one Access file by the due date with four appropriately named and working SQL queries. Each member of your group should complete a different one of the four queries so that all four queries are covered by your group (Groups with three members to complete the first three queries). The student ID of the responsible group member should be added to each query name.
2. In addition, each group will submit a one-page word document that discusses how an internet-based accounting system could assist Red Cheeses. Consider cost, controls and risk management.

Queries (1 per group member)

1. Total first quarter purchases between 01/01/2018 and 31/3/2018 by store (ascending) of Administrative expenses (rent, electricity, phones, and credit card bills, for example) plus Inventory purchases. Hint: Purchases Admin table records when expenses are incurred, so there are no prepaid amounts. Four steps at least. Hint: Outer join required.
2. Cash receipts between 01/01/2018 and 31/3/2018 (first quarter). Hint: The CC charges are calculated as part of the cash receipts.
3. Trade accounts payable liability between 01/01/2018 and 31/3/2018 (first quarter). First summarize purchases of inventory in the first quarter, then summarize cash disbursements for inventory in the first quarter, and finally subtract cash disbursements from purchases (5 steps).
4. Administrative accounts payable between 01/01/2018 and 31/3/2018 (first quarter). First summarize admin purchases in the first quarter, then summarize cash disbursements for admin purchases in the first quarter, and finally subtract cash disbursements from purchases. Hint: look at query 1 (5 steps).

Appendices  Project Assignment Description

Appendix A

The UML Model

Appendix B

Description of data in Excel

1. Admin suppliers. This table lists the administrative suppliers, such as the utility companies, phone companies, etc. The primary key is supplier#.
2. Shows the balance in each cash account at the beginning of the quarter. Students need to find the ending cash balances by adding cash receipts and subtracting cash disbursements to suppliers, cash disbursement to miscellaneous suppliers, and net pay to employees. Note that Red moves money between accounts and that information is not provided, so the students only need to work with the totals. The primary key is account#.
3. Cash receipts. This table lists the cash receipts from customers. For retail stores, the customer information is not recorded. The primary key is cash receipt#.
4. CD admin purchases. This table lists the cash disbursements for the general and administrative expense items listed in the purchases admin table. Red Cheeses pays these bills at the end of each month. The primary key is check#.
5. CD invent purchases. This table lists the cash disbursements to Red’s cheese and wine suppliers. Again, payments are made on or near the end of the month. The primary key check#.
6. This table lists the wholesale customers (small retailers around the Yarra Valley that buy from Red Cheeses) and internet customers that place online orders. The primary key is customer#.
7. This table lists the employees, including the stores they are assigned to, their job category, and planned work schedule. The primary key is employee#.

8. And  Project Assignment Description

9. This table shows information for the various types of cheese and wine that Red Cheeses sells. The retail price is the selling price per kg of cheese or bottle of wine. Red Cheeses values inventory at average cost. The beginning cost is the average cost from the previous quarter and it should be used to value the beginning inventory. All other inventory transactions are valued at average cost except purchases are at actual cost. For the internet sales, Red Cheeses sells packages that combine one or two bottles of wine and half or full pounds of cheeses. There is no separate physical inventory for the packages, since they are assembled after the order. The primary key is invent#.
10. Job categories. This table lists the various jobs in Red Cheeses, the count of employees in each category, and the starting pay for employees in that category. The primary key is job category.
11. Physical inventory counts. This table lists the physical counts of inventory at each store at the beginning and end of the quarter. Since there is a many-to-many relationship between stores and inventory, this is the linking table connecting them. The primary key is the combination of store# and invent#.
12. This table lists the items pulled from inventory and given away during wine and cheese tastings. Red Cheeses takes these amounts out of inventory (COGS) and counts then as general and administrative expenses.
13. Purchases admin. This table lists the administrative purchases, such as utilities, rent, taxes, etc. The primary key is purchase#.
14. Purchases inventory. This table lists the purchases of wine and cheese. Although it is possible for Red Cheeses to purchase several inventory items with a single purchase#, in practice they do not. The item amount is the total paid for that quantity (not the cost per item). The check# foreign key is omitted. The primary key is purchase order#.

15. And  Project Assignment Description

16. This table lists the retail store, wholesale, and internet sales. Each type collects somewhat different information. Red Cheeses applies one order# to all the retail stores sales each day. Since this table lists the quantity of each inventory item, it contains substantial redundant data. It should be split into a sales table (with order#, order date, sale date, customer#, order employee#, pack employee#, van#, sale type, cash receipt#, and shipping per order) and a sales items table (with order#, inventory#, quantity, sale price, and amount). Take care importing the split tables into Access, since the first rows of data for order date and shipping per order are blank and Access will assume that they are text. The primary key of the split sales table is order#. The primary key of the split sales items table is order# plus invent#.
17. This table lists the 11 stores, ten of which are rented. The warehouse is owned by Red Cheeses. The table includes either monthly rent or original cost and depreciation information. The primary key is store#.
18. This table lists the quantities transferred from the warehouse to the retail stores. The transfers are done by Red Cheeses vans. The quantity transferred is the amount to be subtracted from the warehouse inventory and added to the retail store inventory. This table would be linked twice to both employees and stores. The primary key is transfer control#.
19. This table lists the vans, their original cost, and information necessary to calculate quarterly depreciation expense and accumulated depreciation for the quarter. All the vans are assigned to the warehouse. Red Cheeses takes a full year’s depreciation in the year acquired.
20. Wine and cheese suppliers. This table lists the suppliers. The primary key is supplier#.
21. Work schedules. This table lists the four different work schedules for employees. The primary key is schedule.

Note that students could combine the “purchases” tables, the “suppliers” tables, and the “cash disbursements” tables. That simplifies the table structure, although it complicates their subsequent queries. In general, it is recommended that students keep the tables separate at least for part of the project.

更多其他：代写作业 数学代写 物理代写 生物学代写 程序编程代写 加拿大essay代写

合作平台：天才代写 幽灵代写  写手招聘  paper代写