The assignment consists of 2 parts that need to be completed in ACCESS. You will be given 2 sets of data to analyse and then prepare a report discussing the results of each. ONE report should be prepared to include BOTH, part I and part II of the assignment.
Written Report: The report must be done in a professional format. You should use Times New Roman, size 12 fonts and 1.5 line spacing. Preferably use double sided printing. The report should be written using a professional style: do not forget to add page numbers, proper structure and formatting with titles and subtitles, and include a spiral binding. Report MUST include all group members’ names and ID numbers.
DROP-BOX: Your files (2 separate ACCESS files that include all saved queries & tables) will be submitted via the class drop-box on FirstClass (your drop box message MUST include all your group members names and ID numbers).
1. You will be given an excel file with sales information and you will need to create the database tables in ACCESS. (For guidance with the format refer to problem 5 Tables on page 276 or to the comprehensive problem solution on page 283)
a. Consider what tables are needed (i.e. how does splitting the information make sense)
b. What are the primary keys and what are the relationships you should create between the tables
c. Focus on removing redundant information where possible while still maintaining completeness of the data
d. *HINT* You can create the tables in excel by copying the relevant information for each needed table into a separate worksheet and then import each worksheet into ACCESS one by one using the “external data” menu in ACCESS. (I suggest you DO NOT alter the original Excel worksheet as it will be useful for you to refer back to it in case you have issues with the import or analysis)
e. *HINT* (DO NOT DO THIS ON THE ORIGNAL EXCEL WORKSHEET) - To prepare your excel worksheets for ACCESS (separate tables) You may find it useful to use the following functions/menu items in Excel to help you (“remove duplicates” under the “DATA” ribbon, create “subtotals” in the “DATA” menu, copy only relevant subtotals by using the “Go To visible cells only” in “EDIT” menu & then “SPECIAL” button)
2. You will be given 7 specific questions to answer using ACCESS queries (see exhibit I)
a. *HINT* You can double check if your query results in the correct answer by attempting the same question using the original file in Excel (by for example sorting or using formulas in Excel) – but this is solely to help you and does not replace the query that must be created in ACCESS.
3. You must save your queries (see instructions at top of page 267) and name them appropriately so I can easily see which query relates to which question.
4. You will be submitting your database file that contains all of the saved queries via the dropbox on FirstClass (your drop box message MUST include all your group members names and ID numbers).
a. Include the excel file with worksheets that you created (only if applicable, if you chose not to alter the Excel file and simply inputted the information into ACCESS then do not submit the Excel file to me)
b. Include the database (and all relevant tables you entered)
c. Include all your saved queries
d. In the body of your written report you should include printscreens of each query result (i.e. what shows up when you run the query) for the questions you were assigned.
e. And only add a discussion in the written report for the question where you are directed to do so (see exhibit I for details).
1. This part of the assignment is an analysis of a different database.
2. Use the “Insurance Claims” database file provided in ACCESS format. You do not need to modify the database data or structure.
3. Refer to the description of the company and facts about the situation (see Exhibit II).
4. No questions given this time, you need to come up with the relevant questions or issues.
a. Perform 5 different queries for this analysis.
5. Assume the client has called you as a consultant to investigate because they have concerns over fraud/errors in the data.
a. The file provided contains anomalies (errors and/or possible fraud indicators).
6. In your written report discussing your analysis you should:
a. Include printscreens of each query result (i.e. what shows up when you run the query)
b. State the query you came up with, i.e. what was your question (example: search for duplicate claim # for each year in the data)
c. State the purpose of your query, what hypothesis were you trying to solve or what objective did you have (example: identifying duplicate claim # can possibly give an indication that…)
d. Discuss the results of your query based on your assumptions AND the relevant facts of the situation as presented in Exhibit II (example: findings were XXX and may demonstrate that there are fictitious claims entered in the system... This could be the result of XXX… )
e. Discuss any relevant recommendations that you would make to either correct the issue you found or to prevent this in the future.
7. OVERALL CONCLUSION: Finally, at the end of your written report, provide an overall conclusion on your analysis for part II.
a. This should incorporate your conclusions as a result of your queries as well as your analysis of the situation provided in Exhibit II.
i. Refer to your knowledge of past course chapters to include relevant comments/recommendations as part of your overall conclusion (Ch6: Computer Fraud, Ch2 & 3: Controls, Ch 7 & 8: System development, Ch 9: Relational databases)
ii. Consider: Where did they go wrong with their controls or system development as evidenced by your findings and/or the situation described in Exhibit II?
iii. What suggestions do you have as to how to eliminate these weaknesses in controls or avoid these issues in the future?
iv. Consider: If there is additional information you may need from the client. What additional questions do you have for the client in order to perform a better analysis?
8. Provide the database & your saved queries via the drop box on FirstClass (in the same message as with Step I)
a. Queries should again be appropriately named (example: you can number the query as Query 1 and then discuss Query 1 in your report – or use some other naming scheme but ensure that I can relate which query you are discussing in each section of your written report)
Questions to be answered for PART I of the assignment:
1. How many invoice numbers are in the file?
2. What were the total $ sales by year?
3. Which month had the largest amount of $ sales (specify month and year)?
4. Which month had the largest number of individual invoices (not number of lines)?
5. Which customer has the highest sales amount?
6. The CustID should begin with the same few characters as the Customer Name – are there any instances where the 1st letter of the CustID does not match the customer name?
a. *HINT* research how to use the “Left” function in MS ACCESS. You can use that function to see if the 1st letter (or more) in one field is not equal to the 1st letter (or more) in another field.
7. (Include a brief discussion in written report – query not necessary) Are there any problems with the ProductID field? What did you notice? What recommendations would you make to management?
(ONLY For Part II)
Insurance Claims Database
You are a consultant and a VP at the insurance company, InsureCo, contacted you to assist with some concerns over the database being used and its data quality.
The Company insures car dealerships for the warranty sold as part of vehicle sales. When customers purchase extended warranties, the dealership is responsible to cover costs related to any warranty claims (i.e. repairs required) and then submit claims to the insurance company for reimbursement (coverage).
The VP noted a significant amount of insurance claims over the past two years and is concerned about possible fraudulent warranty claims. InsureCo is wary of the control environment at the dealerships that are sending in the claims. Another concern is the database and the quality of the data that has been entered into the database currently being used.
VP tells you the following:
“The person that created the database prided themselves on being “self-taught” in Microsoft ACCESS and didn’t care much for controls. Unfortunately, they no longer work for us and no one else seems to be able to answer my questions.
To be fair, the database was really needed. Before it was created we processed claims as they came in and no one really bothered to analyse the data or understand the claims trends. In order to better manage risks and structure our services we knew we’d have to consider all of the claims being received, but no one really knew how to go about doing that with the current information system. We were all overwhelmed by the enormity of the task.
That’s when one person stepped up and decided to take matters into their own hands. They single-handedly created the entire database and populated it with all of the data from the existing information system that is used to log and process claims. We’re really grateful for that, but it would have been nice if they had consulted the accounting staff as there are some fields that are not clear to me and no one can decipher what they represent. So, we aren’t really sure that the analysis we are doing are even valid and whether we should even continue with this database.”
Your task is to analyse the database for anomalies and possible fraud indicators and prepare a written report for VP detailing the results of your findings and recommendations.