Excel Assignment
docx
keyboard_arrow_up
School
Centennial College *
*We aren’t endorsed by this school
Course
MISC
Subject
Communications
Date
Feb 20, 2024
Type
docx
Pages
4
Uploaded by ChefComputerMeerkat24
Excel Assignment
COMM 2110 Communication for Music Business Professionals II – Fall 2023
Instructor:
Val Lapp | Grading:
Out of 20, worth 10% of final grade. Due Date: Posted on DC Connect and discussed in class
Instructions
Apply basic Excel skills to a music-industry spreadsheet.
Complete the online training and formative assessments in DC Connect: Content > Excel before beginning the assignment.
Download the Excel workbook attached to the submission folder.
Follow the attached instructions
, using the downloaded Excel workbook.
Submission Upload completed Excel workbook to the “Excel Assignment” submission folder by the due date.
Resources
DC Connect: Content > Excel
Time Management
Time needed will vary depending on your Excel skill level:
Training Resources – Approximately 1 – 2 hours
Formative Assessments – Approximately 15 - 45 mins per assessment (there are 4)
Assignment –
Approximately 1 – 2 hours Grading
Rows & Columns (3 marks)
Sort & Filter (3 marks)
Format Formula Function (8 marks)
Chart (6 marks)
20 marks total; 10% of your final grade. Full marks will be given for accurate work using the correct formatting, functions, and formulas.
Late Policy:
Late assignments receive a 10% deduction for each day late, and will not be graded if submitted more than 72 hours after due date. Maximum 30% late deduction.
Excel Assignment – Instructions
NOTE: Durham College tuition pays for free access to Office 365, which includes Excel. If you have not already registered for Office 365, register here
. You can then access Excel any time you want.
1.
Open the provided Excel worksheet.
Go to the assignment submission folder (Activities > Assignments > Excel Assignment) and download the Excel workbook from there.
You will see a workbook that has five tabs (worksheets) in it. The first tab “ORIGINAL DATA – Don’t change” contains the raw data you will need to get started. As the tab name suggests, you can copy this info, but otherwise leave it in the first tab exactly as it is. The raw data shows some of the royalty earnings for SoundPie Records from May to July of 2023. Many thanks to Yasin for supplying this data for the assignment.
The other four tabs have been labelled “Rows & Columns”, “Sort & Filter”, “Format, Formula, Function” and “Chart”, but the worksheets in them are blank.
2.
Rows & Columns. (The following should be completed within the Rows & Columns worksheet)
Click the first tab “ORIGINAL DATA – Don’t change” and select everything in it.
Click the “Rows & Columns” tab and copy/paste the information from “ORIGINAL DATA – Don’t change” into the “Rows & Columns” worksheet.
Make heading row bold
, and change it to a 12 pt font
Adjust column widths
so that you can see all the information in the columns.
Rearrange the columns
so that they read, from left to right: Reporting Period… Track Artists…Track Title…Catalogue… ISRC…Digital Service Provider…Territory… Content Type…Count… Royalty
Change row height for the entire table to 16.1
3.
Sort & Filter. (The following should be completed within the Sort & Filter worksheet)
Click the “Rows & Columns” tab and select the entire worksheet that you just completed.
Click the “Sort & Filter” tab and copy/paste the information from “Rows & Columns” into the new worksheet.
Select entire worksheet, and then click Filter to remove all the complete rows that include “YouTube Adjustments”
. YouTube Adjustments is one of the items listed under Digital Service Provider.
Do NOT remove any other YouTube items, just the rows with YouTube Adjustments.
Select entire worksheet, and then click Data>Sort to sort by
o
Reporting Period, then by
o
Track Artists, then by
o
Track Title, then by
o
Digital Service Provider
4.
Format, Formula, Function (The following should be completed within the Format, Formula, Function worksheet)
Click the “Sort & Filter” tab and select the entire worksheet that you just completed.
Click the “Format, Formula, Function” tab and copy/paste the information from “Sort & Filter” into the new worksheet.
Select the Royalty column. Format this column as a Currency
column, with four decimal places. You can find “currency” under Number.
Insert a blank row after the heading row, and fill it with a colour.
See example below.
Select entire worksheet, and then
click Data > Sort to sort by Digital Service Provider
In the Digital Service Provider column, you should now see all of the providers grouped together. After each group, insert two blank rows
to create space between the different providers. Keep all Amazon’s and YouTube’s together, even though they might have different descriptions.
a.
In the first blank row after each group, merge the cells in all the columns from A-H
b.
Type TOTAL _____ into the merged cell, using the name of the Digital Service Provider
(see example)
. Make the row bold
.
c.
Select the second blank row after each group and fill it with a colour. See example.
Insert a column to the right of the Royalty column, and call it Royalty Per Unit Sold
. Format this column as a Currency column, with four decimal places.
Create a formula in the Royalty Per Unit Sold column that divides
the Royalty column (J) by the Count column (Column “I”). The formula should look something like this: =J3/I3. Insert it in the first row of data (in the sample, this is Row 3), and then “copy it down” for all of the rows that have data in them.
In each “TOTAL ___” row
, use the SUM function
in the Count column and the Royalty column
to add each Service Provider’s totals.
Format the cells in the Count column and the Royalty column just above the bolded totals with a double line at the bottom of the cell.
(The next steps are NOT shown in the image above)
After the final Service Provider total, insert a couple of blank rows.
In the next row down (after the blanks), merge from columns A-H, and type GRAND TOTALS
Change the row’s font to 16 point type, and change the row height to 25.8.
Using the SUM=(cell + cell + cell) formula in the Count and Royalty columns, add up all the different Service Providers sub-totals (e.g. the Amazon total + the Apple total + the Deezer total etc.) for a grand total.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
5.
Chart. (The following should be completed within the Chart worksheet)
Click the “Chart” tab.
This time, you will not be copying your previous work into the new worksheet.
Create two columns in the blank worksheet:
o
Call the left column Service Provider. In the three rows below the header, type Facebook/Instagram, Spotify and YouTube.
o
Call the right column SoundPie Royalties May – June 2023. Format this column as Currency, with a $ sign and two decimal places. Look for the royalty totals you found for these three service providers in the
“Format, Formula, Function” worksheet. In the row beside each service provider, copy/paste these royalty totals.
Select all the data you have entered, including the headers. Create a pie chart
with this data:
o
Choose whichever colours you want
o
Call the chart “SoundPie Royalties May – June 2023”
o
Use callout labels for each of the parts of the pie chart
o
The labels should show the service provider, the actual dollar value, and the percentage.
o
Don’t use a legend (delete it if it appears)
Here’s a partial look at how your chart should look:
The finished worksheet will still show your columns with the data, as well as the pie chart.