ACCT5011 Accounting Systems In The Digital Age: Semester 1 Practical Assignment Answer

ACCT5011 Accounting Systems in the Digital Age

Semester 1, 2020 Practical Assignment

This individual practical assignment is worth 30% of the final mark. It requires students to apply what they have learned in the class and use Microsoft ACCESS and EXCEL to solve business problems.

Plagiarism policy: Please familiarise yourself with the University’s Academic Integrity Program. Any student involved in collusion and the copying of assignments will be reported to the University, and may face serious penalties. For more information, please visit this website: https://oasis.curtin.edu.au/ConductAtCurtin/AcademicIntegrity.

Submission Process:

Students are required to submit their assignment both online and in hard copy by Friday, 22 May 2020 at 12 p.m.

Online submission ‐ Blackboard

Students are required to submit the memorandum (in Microsoft WORD format) via Turnitin.

Offline (Hard Copy) submission

Students are also required to put the following items securely in a plastic envelope and submit them to the assignment submission mailbox in the School of Accounting (Building 407, level 4):

a. Electronic files: A USB stick to store the ACCESS, EXCEL and WORD files. Please label your USB stick. All the file names should be named in the format: Lastname_FirstName_StudentNumber_CaringShoeProject.

Only files with appropriate names will be marked

b. Hard Copy (each item on a separate A4 paper)

• A completed assignment coversheet with student details and signature.
• A hard copy of the marking guide (with Student ID and name).
• WORD file: The memorandum to the CEO of the case study. The name and ID of the student should be shown in the header of the document.
• ACCESS: All screen captures should clearly show the path and file name.
  • Screen captures of the structure of all the tables (each on a separate A4 paper) in the data file.
  • A screen capture of the entity relationship diagram showing all attributes in each table.
  • A screen capture of each query design page clearly showing the selection criteria.
  • A printout of each report.

1. EXCEL: All screen captures should clearly show the path and file name.
   • A printout of the overview worksheet content.
   • A screen capture of the formula that combines a ticket holder’s first name and last name.
   • A screen capture of the formula that extracts the year of sale date. A screen capture of the formulas in the four‐column price selection table (Note: show all formulas on the same screen).

• A screen capture of the formula that shows the VLookup function.
• Screen captures of the three pivot tables (each on a separate A4 paper).

Each page should show clearly the pivot table results, field selections, and pivot chart (if applicable).

It is vital that students test to make sure their files can be opened by the computers in the computer labs on campus. Under the July 2018 Curtin University’s ASSESSMENT AND STUDENT PROGRESS: POLICY AND PROCEDURES handbook (Assessment Communication Section 2.6), “submitted files that are unable to be read cannot be marked. These should be treated as a non‐submission” (p.13). Here is a link to the policy document:

https://policies.curtin.edu.au/local/docs/policy/Assessment_and_Student_Progression_Manual.pdf

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 children’s 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.
• Occasionally the price could be exceeded if a store’s sale representative received an approval from the contact at the organisation that gave out the ticket. By ‘gentleman’s 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 part‐time as the Eternity Foundation’s 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 in‐depth analysis of the Caring Shoe Project’s 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:
   1. Rename Tables:
      1. “Limits” table becomes “Ticket Limits” table
      2. “Organizations” table becomes “Organisations” table
2. Sales Number: should be integer
3. Date: use the dd/mm/yyyy format
4. 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.
5. 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.
6. 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.
   1. Report 1: Dr. Lee wants general information about each organisation that hands out shoe tickets. Produce a report that shows each organisation’s name, organisation type, price limit for each organisation’s 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.
   2. 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:
      1. Organisation Name
      2. Total sale amount generated by each organisation
      3. Average sale price per transaction
      4. Total number of sales
      5. Name of contact person

Hint: give meaningful labels to the resulting columns.

1. Report 4: The project’s 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.

1. Report 5: Each organisation’s 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.
2. 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.
3. Query 7: Create a table to combine all data in 2015. Hint: include organisation and ticket details but ensure there are no duplicate fields.
4. Query 8: Create another table to combine all data in 2016. Hint: include organisation and ticket details but ensure there are no duplicate fields.

Part 2: EXCEL (Cell referencing, Formulas, Formatting, Pivot Table, Pivot Chart)

• Export the results of ACCESS Queries 7 and 8 (tables you created in the previous step) to an EXCEL file. Name the Excel file as: LastName_FirstName_StudentNumber_CaringShoeProject.xlsx.

Note: You should put the two tables into one worksheet and name the worksheet appropriately.

• Create a worksheet to give an overview of the Excel file such as what information is in each worksheet, who created the file etc.

Using the combined data set, you should do the following:

1. Create a new column to the immediate right of the First Name column to show the full name of each voucher recipient.
2. Create a column to the immediate right of the Sale Date to extract the year of the Sale Date.
3. The following table classifies sale prices into different categories. Add a column to the data set that classifies the sale price for each transaction. You must use the VLOOKUP function to extract the classification. The formula on row 2 should be flexible to allow you to copy it down to all rows. In case of error, the resulting column should show “Error, contact [Your full name]” instead of “#N/A”.

After you have classified the sale price for each transaction, you should add two columns to the price classification table as shown below. In the Total Sale/Price Level column, you should use the ‘sumif’ function to calculate the total sales made for each price level. In the next column, use a formula to calculate the percentage of sales amount in each price level.