Basic excel assingment
ISYS104 2018 S3 - Assignment 1
DEPARTMENT OF COMPUTING
ISYS104 2019 S1 – ASSIGNMENT ONE
(out of 50 - worth 5%)
Due: pm Friday 29 March 2019
Case Background
G’day ticketing company has been selling concert tickets featuring different artists for the past 18 years across Australia. You have been hired to tidy up the excel workbook that has been provided to you. In Concert Sales worksheet, cells B5 to F10 provide data about the number of tickets sold by each artist in concerts across Australia in 2018. Other worksheets provide sales data from 2000-2017.
Task Descriptions
Task 1: Concert Sales Worksheet (15 marks)
1. (1 mark) Add you student ID in cell A1 and your full name in cell B1.
2. (1 mark) Merge cells from A2 to H3 and add a heading “Concert Ticket Sales”. Any price (rows 13-18) must be in currency format with two decimal places.
3. (1 mark) In cells G5 to G10, insert the appropriate function to add the total number of tickets.
4. (2 marks) Using the values in cells B5 to F10, calculate values in cells C13 to G18 (number of tickets sold * cost per ticket)
5. (1 mark) In cells H13 to H18, insert the appropriate function to add the total sales.
6. (2 marks) In cells B23 to F28, calculate the values using the values given in cells B21 to F21 (number of tickets sold / venue capacity)
7. (1 mark) In cells G23 to G28, insert the appropriate function to calculate the average of capacity in percentage.
8. (2 marks) Using conditional formatting, highlight the top 5 ticket sales in cells B5 to F10 with green fill and dark green text.
9. (4 marks) Use your creativity to apply at least four appropriate simple formatting in cells A1 to I28 to improve the readability of this worksheet without changing the structure/placement of data. Examples may include, but not limited to: borders, column width, colours, etc. Do not apply other conditional formatting other than the one requested in no. 8.
Note: Formula must be written using the correct cell referencing that can be copied across to other columns and/or rows.
Task 2: Sort-Me Worksheet (3 marks)
10. (2 marks) Sort data by the place of concert in descending order.
11. (1 mark) Apply borders in and around the spreadsheet area (all borders).
1
ISYS104 2018 S3 - Assignment 1
Task 3: Multi-Sort Worksheet (3 marks)
12. Sort data in this order (multi-level sort)
a. 1st level: Year from the most recent ones (descending order).
b. 2nd level: Artist in alphabetical order (ascending order).
c. 3rd level: Place in reversed alphabetical order (descending order).
Task 4: Coldplay Worksheet (2 marks)
13. Use filter to display sales for Coldplay only.
Task 5: Subtotal Worksheet (4 marks)
14. Use the Subtotal function/button in Data tab to organise data to include the subtotal (SUM) of sales by each year. Hint: an additional step is required to do this properly.
Task 6: Calculate Worksheet (9 marks)
15. (3 marks) In cell H5, use an appropriate formula to count the number of concerts by Adele.
16. (3 marks) In cell H6, use an appropriate formula to count the number of concerts since 2015.
17. (3 marks) In cell H7, use an appropriate formula to add the total sales made in Perth for all the years.
Note: Formula must be independent of any other actions.
Task 7: Chart Worksheet (6 marks)
18. (3 marks) Generate a stacked line chart with markers for all artists at Sydney and Melbourne (both together in one chart). Add appropriate details to make the chart more understandable.
19. (3 marks) Generate a 3D pie chart for all concerts in Melbourne. Add appropriate details to make the chart more understandable.
Note: Both charts should be placed within this worksheet.
Task 8: Pivot Table Worksheet (8 marks)
20. (6 marks) Use the data in ‘Pivot Table’ worksheet to create a Pivot Table that shows for each year, a column where their sales for each place is calculated as an Average. Use Artist as the filter. Create the pivot table in a new worksheet and rename the sheet as ‘Pivot Answer’.
21. (2 marks) Filter data in ‘Pivot Answer’ sheet to only display Adele, Coldplay and Ed Sheeran for the years 2010-2015.
Note: You may need to use a PC version to complete these tasks.
Submission
Please submit your Excel file on iLearn (there will be a submission link made available in the top section) by the deadline. You need to name the Excel file to start with your student ID (e.g., 12345678.xlsx)
2