Case Scenario for Assignment (adapted from Monk et al., 2016) Eternity Foundation is a charity organisation that was set up in 1999 to help the poor in Australia. The organisation ran a Caring Shoe Project in 2015 and 2016. The project aimed to help individuals who struggled financially to get good shoes. The project was run in Sydney, Melbourne, and Brisbane during those two years. The project provided shoe tickets to churches, public schools and homeless centres. Church pastors, school principals, and homeless centre managers passed on shoe tickets to help people in need to purchase shoes. Ticket holders then went to a participating store and used the ticket to get a pair of new shoes. At the end of each month, each participating shoe store sent the used tickets and an invoice to Eternity Foundation. Based on the documents, the Caring Shoe Project accountant can tell who received shoes and recorded the cash values of the shoes. The accountant then sent a cheque to each store based on the invoice. Typically, parents were given tickets for themselves and for each of their children. The monthly bill showed the name of the parent and each child who received a pair of shoes. Hence, the accountant could tell from the invoice how many shoes went to adults and children respectively. Often not all tickets were redeemed. For example, a church pastor might have been provided with 30 tickets but there was no guarantee that the pastor would hand out all 30 tickets. Also, there was no guarantee that ticket holders would actually redeem them. The program has certain restrictions. Tickets given out at schools and churches could be redeemed for a pair of shoes that cost no more than $30. These tickets generally would be redeemed for childrens shoes. Tickets given out at homeless centres could be redeemed for shoes that cost no more than $40. These tickets were generally redeemed by adults who needed good shoes to attend job interviews. The price limits were shown on the tickets. ACCT5011 Accounting Systems in the Digital Age Semester 1, 2020 Practical Assignment 6 Occasionally the price could be exceeded if a stores sale representative received an approval from the contact at the organisation that gave out the ticket. By gentlemans agreement, this would not happen often and the extra cost would not be significant. Each organisation had a contact person who worked closely with the Caring Shoe Project. Note that the contact persons at Church of God and Elm St Baptist swapped places in 2016. Dr. Doris Lee is a retired accountant who was invited to work parttime as the Eternity Foundations CEO. Dr. Lee plans to restart the Caring Shoe Project and extend the project to other Australian cities such as Perth and Adelaide. As a recent Accounting graduate from Curtin University, you joined Eternity Foundation because of your passion for charity work. Knowing that you have knowledge in data analysis and are proficient in using Microsoft ACCESS and EXCEL, Dr. Lee would like you to conduct an indepth analysis of the Caring Shoe Projects performance before she makes a decision regarding the future of the project. To start your work, you are given an ACCESS file (CaringShoeProject.accdb) with 6 tables. Note: Data file for this assignment is available on the Blackboard. The specific requirements from Dr. Lee are described below. You should give appropriate names to everything you created. Part 1: ACCESS (Database structure, Query, Report) 1. First, Dr. Lee did a check of the data and design of tables in the database and she would like you to do the following: a. Rename Tables: i. Limits table becomes Ticket Limits table ii. Organizations table becomes Organisations table b. Sales Number: should be integer c. Date: use the dd/mm/yyyy format d. City Name: create an update query to correct the misspelling of one of the cities name. You should give an appropriate name to the query. 2. To help Dr. Lee understand the relationship among different tables in the database, you need to create an entity relationship diagram (ERD) that links all six tables. Each organisation type includes multiple organisations. Each organisation can issue multiple tickets. ACCT5011 Accounting Systems in the Digital Age Semester 1, 2020 Practical Assignment 6 3. Using the revised Access file, you create queries and corresponding reports to provide useful information for Dr. Lee. You should give a meaningful name to each query and report. The query results and reports should be formatted professionally. a. Report 1: Dr. Lee wants general information about each organisation that hands out shoe tickets. Produce a report that shows each organisations name, organisation type, price limit for each organisations tickets, and the name of the contact in 2015 and 2016. The report needs to show the fields in the prescribed order. Sort the results by the name of the organisation in ascending order. b. Reports 2 and 3: Dr. Lee wants to know whether the swap of contacts in two churches led to any significant difference. Create a report that compares shoe sales generated from the tickets given out by Church of God and Elm St Baptist in 2015 and 2016. Report 2 focuses on 2015 data whereas Report 3 focus on 2016 data. Both reports should show the following: i. Organisation Name ii. Total sale amount generated by each organisation iii. Average sale price per transaction iv. Total number of sales v. Name of contact person Hint: give meaningful labels to the resulting columns. c. Report 4: The projects donors expect the money to go to people who need shoes. There is no reason to give tickets to organisations if the tickets are not used. Dr. Lee believes that at least 60% percent of tickets given out by an organisation should eventually result in shoe sales. Hence, create a report that shows for each participating organisation, the number of tickets given out in 2016, the related number of eventual sales, and the percentage of sales to tickets given out. Hint: create one query that counts the number of sales and another query that counts the number of tickets given out, then combine the results in a third query. d. Report 5: Each organisations contact can authorise sales at a price that exceeds the limit. Dr. Lee wants to know how many sales exceed the limit by more than one dollar in 2016. Create a report that show the following: name of contact, organisation type, limit set for each organisation, sales price and number of cases exceeding the limit. Sort the results by the last column in descending order. e. Report 6: Dr. Lee would like to know how many shoe tickets are used in each of the three Australian cities. Using the 2015 data, create a report that shows the total number of tickets used and the total sales amount in each city. f. Query 7: Create a table to combine all data in 2015. Hint: include organisation and ticket details but ensure there are no duplicate fields. g. Query 8: Create another table to combine all data in 2016. Hint: include organisation and ticket details but ensure there are no dup