ISYS104 Department Of Computing

Tasks

Work on each instruction in the correct worksheet.

T1: Add your name and ID in cells A1 and B1 respectively.

T2: Rename the worksheet name Sheet1 to TV Advertisement.

T3: In the cells G8 to G15, use a formula to calculate the difference of cost between 2018 and 2019 (positive result if the cost in 2019 is higher). Read notes above.
T4: In H8 to H15, use a formula to calculate the percentage of difference between 2018 and 2019. Hint: Use the result of column G and the cost of 2018. Read notes above.

T5: In I8 to I15, use a formula to calculate the projected cost in 2020 based on the cost in 2019 and the percentage of difference with the predicted growth. Hint: Use the result of column H added with the rate of yearly growth (C17), then use it with the cost in 2019. Read notes above. Example: If the difference is 1.5% and the growth rate is 1.12%, then 2020 cost should be an increase of 2.62% from 2019 cost.

T6: In B20 to B22, use a predefined function to calculate the average cost of advertisement in 2019 according to the age restriction classifications (PG, M, MA15+). Read notes above.

T7: In F20 to I21, use a predefined function to calculate the minimum and maximum advertisement cost in 2019 for each network (ABC, CBS, Fox, NBC). Read notes above.

T8: Use your creativity to apply general formatting to improve the readability of data within this worksheet. No changes should be made to the original nature of the worksheet

T9: Add a bar chart for the average cost based on age restriction classifications. Include the necessary chart labels.

T10:  Add a line chart with markers to compare the minimum and maximum cost in 2019

Include the necessary chart labels

T11: Apply conditional formatting to highlight the top 15 ratings in green.

T12: Apply conditional formatting to highlight the classification for mature audiences (M) in red.

T13: Sort the data based on the title in descending order.

T14: Sort the data based on the Network (ascending), then by Classification (descending), then by Ratings (descending).

T15: Use filter to show only TV series from Netflix with ratings above 8.5 (exclusive).

T16: Use the Subtotal button in Data tab to organise data to include the average of ratings for each network

Note: depending on your MS Excel version, an additional step may be required before applying subtotal.