You are an analyst at the music publishing firm, KNM Inc. KNM Inc. controls copyrights and collects royalties by distributing music. Royalties are earned when songs are played on radio broadcasts or online streams. KNM Inc. distributes music to the following content providers: - Amazon - Apple Music - Google Play Radio - Facebook - Radio - Tidal - TikTok - YouTube Music - Soundcloud - Sirius XM - Spotify KNM Inc. receives royalties from music providers above then forwards payment to the artist. KNM Inc. retains a percentage of this royalty based on a negotiated rate, known as the Effective Rate, between KNM Inc. and artists they have under contract. Currently, KNM Inc. has 13 artists under contract. Each artist has a different negotiated Effective Rate. The Effective Rate that is negotiated is based on expected volume of streams, popularity, and skill of the artist's agent and determines how royalties will be allocated between KNM Inc. and the artist. KNM Inc. will retain (100 minus the Effective Rate) as revenue with the remainder being sent to the artist. For example, the Rolling Bones has an Effective Rate of 66.667 (found in the Index worksheet). In January 2023, the Rolling Stones song ID 17358 earned $0.09 from Google Play Radio (this is found in Row 2 of the Data worksheet). Therefore, KNM Inc. will pay the Rolling Bones $0.06 and retain the remaining $0.03 as revenue.

Requirement #1: Unique Songs
In the Q1 Worksheet, complete the following tasks:
a) List all unique songs
b) Using formulas, perform the following calculations:
i) Calculate the number of unique songs
iI) Determine which song and artist earned the most amount of Gross Revenue (Amount Received) and calculate the amount.
iii) Determine which song and artist earned the most amount of revenue for KNM Inc. and calculate the amount.
iv) Determine which song and artist earned the most amount for KNM Inc. in July 2023. How much was earned?
Hints: All of the above can be accomplished by using SUMIF, SUMIFS, VLOOKUP, MAX, COUNT, and multiplication. Be sure to reconcile your work.
Requirement #2: Monthly Revenue by Artist
In the Q2 Worksheet, using formulasprepare two schedules. One schedule is to show the Gross Revenue (Amount Received) earned by each artist each month. The other schedule is to show KNM Inc.'s portion of revenue by artist. Be sure to reconcile your work. Who is KNM Inc. most important and least important artist? How do you know?
Requirement #3: Monthly Revenue by Source
In the Q3 Worksheet, Insert a Pivot Table that shows the KNM Inc. revenue by month and by source. Ensure to insert a Slicer that allows the user to filter by artist.
Bonus Opportunity
Completing this is optional, and you will have the opportunity to receive the 30 marks between requirements 1, 2, and 3. However, if you would like the opportunity to earn bonus marks, use the Bonus worksheet and answer the question: Which genre would you say KNM Inc. specializes in? How do you know?

Transcribed Image Text:

Artist Code and Song Code are unique identifiers. Amount Received is the dollar amount received from the revenue source over the specified period. Effective Rate is the percentage of the Amount Received that is allocated between the artist and the KNM Inc. based on a contractual rate. KNM Inc. retains (100-Effective Rate) percent of the Amount Received as an Date Period From is the starting month and Date Period To is the ending month for the Amount Received. All time periods are 1 month. Source provides details of the payer. Note: The Arist Code has additional features. Each artist is assigned a 5-digit code. The first digit represents the genre of the artist, the next two digits are the year the artist was signed to KNM Inc., and the final two digits refer to the order in which the artist was signed. For example, consider 15101 (The The leading 1 indicates that this is a Pop band. The next two digits, 51, indicate that they were signed in 1951. The final two digits, 01, indicate that they were the first band signed that year. Artist Code and Song Code are unique identifiers. Amount Received is the dollar amount received from the revenue source over the specified period. Effective Rate is the percentage of the Amount Received that is allocated between the artist and the KNM Inc. based on a contractual rate. KNM Inc. retains (100-Effective Rate) percent of the Amount Received as an Date Period From is the starting month and Date Period To is the ending month for the Amount Received. All time periods are 1 month. Source provides details of the payer. Note: The Arist Code has additional features. Each artist is assigned a 5-digit code. The first digit represents the genre of the artist, the next two digits are the year the artist was signed to KNM Inc., and the final two digits refer to the order in which the artist was signed. For example, consider 15101 (The The leading 1 indicates that this is a Pop band. The next two digits, 51, indicate that they were signed in 1951. The final two digits, 01, indicate that they were the first band signed that year.

Answer rating: 100% (QA)

Requirement 1 Unique Songs a List of all unique songs In the Q1 worksheet create a column to list all unique songs You can achieve this by using the U... View the full answer