Background Info
The tvseries.xlsx workbook contains eight (8) worksheets (Sheet1 to Sheet7 and Original Data worksheets).
Sheet1 worksheet contains excerpts of TV series information with the advertisement cost (values in dollar) for each 30 seconds time slot during the show in 2018 and 2019. You are tasked to complete this worksheet with the necessary formula and charts, as well as make it presentable.
Sheet2 to Sheet7 worksheets serve different purposes, you will need to prepare them for each of those intended purposes as listed.
Original Data worksheet contains the copy of the original data used in worksheets Sheet2 to Sheet7. If you need a fresh copy, you can duplicate this worksheet and rename it accordingly. Do not make any changes to this worksheet.
Notes:
- All formula must be written using the correct cell referencing (relative/absolute/mixed cell reference) that can be copied across to other columns and/or rows of similar nature.
- Display the result of all calculations in 2 decimal places.
- Tasks
Work on each instruction in the correct worksheet.
Sheet1
T1: Add your name and ID in cells A1 and B1 respectively. Add your name and ID yourself.
T2: Rename the worksheet name Sheet1 to TV Advertisement.
T3: In the cells G8 to G15, use a pre-define formula to calculate the total cost in 2018 and 2019. Read notes above.
T4: In H8 to H15, use a formula to calculate difference between 2018 and 2019 (cost in 2019 minus 2018). Read notes above.
T5: In I8 to I15, use a formula to calculate the percentage of difference between 2018 and 2019 (difference over 2018). Read notes above.
T6: In B18 to B20, use a predefined function to calculate the number of movies in 2019 according to the age restriction classifications (PG, M, MA15+). Read notes above.
T7: In F18 to G21, use a predefined function to calculate the average advertisement cost in 2019/2018 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 (rows, columns, placement of data, etc.) Conditional formatting will not be accepted.
T9: Add a pie chart for the number of movies based on age restriction classifications. Include the necessary chart labels.
T10: Add a line chart with markers to compare the average costs in 2018 and 2019 (2 series). Include the necessary chart labels.
Sheet2
T11: Apply conditional formatting to highlight the ratings below the average in yellow.
T12: Apply conditional formatting to highlight the classification for restricted for 18 and over (R18+) in red.
Sheet3
T13: Sort the data based on the ratings in descending order.
Sheet4
T14: Sort the data based on the Network (ascending), then by Classification (descending), then by the Title (ascending).
Sheet5
T15: Use filter to show only TV series from Netflix and Fox with ratings below 8.5 (exclusive).
Sheet6
T16: Use the Subtotal button in Data tab to organise data to include the minimum ratings for each classification.
Note: depending on your MS Excel version, an additional step may be required before applying subtotal.
Sheet7
T17: Use the data to create a new worksheet called Pivot Table containing a pivot table that shows the average ratings for each network with different classifications in the columns. Add start year as the filter.
T18: Filter the data of the pivot table created to show only information for CW, Disney+ and Fox that started in 2011 to 2015.
Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).
After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.
If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.