Excel Fundamentals for Data Analysis | Online Course Support

You have been asked by a school to help automate their accounts processing. Every few days they receive a text file from the bank detailing payments received (BPAY sheet). This information must be matched to the system data that has been downloaded into Excel (SYS DATA sheet) and a report produced that will be uploaded to the school system.

1. Question 1 You have been asked by a school to help automate their accounts processing. Every few days they receive a text file from the bank detailing payments received…

Excel Fundamentals for Data Analysis | Online Course Support

Discounts are calculated as a percentage of the balance owed, rates vary depending on how large the balance is. The lookup table in S2:T5 details what percentage is applied, so for example balances from 300 to 11999.99 get a 5% discount.

17. Question 17 Discounts are calculated as a percentage of the balance owed, rates vary depending on how large the balance is. The lookup table in S2:T5 details what percentage…

Excel Fundamentals for Data Analysis | Online Course Support

We didn’t quite get the results we wanted in the last question and on closer inspection we discover that there is a space at the end of the BPAY reference. Modify the calculation in column A to remove the extra spaces. The Customer References should now be corrected. What is the updated value for Check Digit 2 in T14?

4. Question 4 We didn’t quite get the results we wanted in the last question and on closer inspection we discover that there is a space at the end of…

Excel Fundamentals for Data Analysis | Online Course Support

The Customer Reference number is how we identify customers in our system. The BPAY reference appears to consist of the 9 digit School identifier, followed by the 5 digit Customer Reference.

3. Question 3 The Customer Reference number is how we identify customers in our system. The BPAY reference appears to consist of the 9 digit School identifier, followed by the…

Excel Fundamentals for Data Analysis | Online Course Support

Where there is more than one student enrolled, customers are given a 5% sibling discount. This is applied to the fees up front but as part of the report you are required to calculate what this amount was.

19. Question 19 Where there is more than one student enrolled, customers are given a 5% sibling discount. This is applied to the fees up front but as part of…

Excel Fundamentals for Data Analysis | Online Course Support

In column L calculate how many working days (excluding Saturdays and Sundays, but not holidays) those who paid early, paid by. If they did not pay early the calculation should just return 0. In the total row add a sum for this column. What is the total shown in the total row?

Excel Fundamentals for Data Analysis | Online Course Support

The due date for invoices is 21 working days after invoices are issued. This has already been calculated in column J. In column K we would like to identify those people who paid early. Create a calculation in column K that returns a Y if they paid before the due date and otherwise leaves the cell blank. What is the value of Check Digit 6 in T18?

14. Question 14 The due date for invoices is 21 working days after invoices are issued. This has already been calculated in column J. In column K we would like…

Excel Fundamentals for Data Analysis | Online Course Support

You realise you could make your life a little easier and your solution more robust by working in a table. Convert the data in A1:Q69 to a table. Turn on the Total row and add a SUM total in the Balance column. Filter the table to only show payments made in October. What is the Total Balance?

11. Question 11 You realise you could make your life a little easier and your solution more robust by working in a table. Convert the data in A1:Q69 to a…

Excel Fundamentals for Data Analysis | Online Course Support

In column E use a calculation to convert the paid date in the BPAY sheet to a valid Excel date. Formats will differ for different regions, but once converted you should be able to switch between number format and short date format. (Hint: you will need to separate and rejoin the separate parts of the date using an appropriate date function. What is the value of Check Digit 4 in T16?

7. Question 7 In column E use a calculation to convert the paid date in the BPAY sheet to a valid Excel date. Formats will differ for different regions, but…

Excel Fundamentals for Data Analysis | Online Course Support

In the BPAY file dates come through in the format YYYYMMDD, which makes them difficult to perform calculations with. In column D create a calculation to extract the two digit month from the corresponding paiddate in the BPAY sheet. (Do not convert to a number). What is the value of Check Digit 3 in T15?

6. Question 6 In the BPAY file dates come through in the format YYYYMMDD, which makes them difficult to perform calculations with. In column D create a calculation to extract…