logo Use SA10RAM to get 10%* Discount.
Order Now logo

Ask This Question To Be Solved By Our ExpertsGet A+ Grade Solution Guaranteed

expert
ISAAC SSEKISAMBUPolitical science
(/5)

720 Answers

Hire Me
expert
Marshall HildrethLaw
(5/5)

547 Answers

Hire Me
expert
Marilyn HowardMarketing
(5/5)

723 Answers

Hire Me
expert
Denis GibbssScience
(5/5)

921 Answers

Hire Me
Others
(5/5)

Provide a solution (or sequence of queries) to determine the market share(

INSTRUCTIONS TO CANDIDATES
ANSWER ALL QUESTIONS

Descriptive Analytics

Data set Description

 

Refer to tabs “Market_Aug2021” and “Market_Aug2018” in the attached excel file “Convergence Dataset.xlsx”

Convergence has various data sets of different Investment funds (Identified by unique reference “FundID” and has a Dollar amount in “Assets”) in the market that are managed by different investment banking companies (Also referred as “Adviser” in the data set, has a unique identity “CRD”). The Advisers of these investment funds use different service providers and pay them a service fee to manage the fund operations. In this instance, the data set provides one such service provider, the Auditing company (Referred as “Auditor”) which is appointed to perform the financial audit for each of the fund. Like any business, the auditing companies prime objective is to audit as many funds as possible, be a competitive service provider thereby increase their market share and revenue from the audit business.

 

Questions

1. Provide a solution (or sequence of queries) to determine the market share(%) of each Auditing company as of Aug2018 and Aug2021. Market Share is determined in two different ways and output both metrics in two different columns in the result. Result should include columns Auditor, Marketshare_Funds, MarketShare_Assets.

a. MarketShare_Funds (%) by Number of Funds the Auditor Audits

b. MarketShare_Assets (%) by the Aggregate $ amount of Assets that they audit

2. Using the above result, Determine the Top 3 biggest gainers and Top 3 biggest losers in change in market share in Funds from Aug2018 to Aug2021.

 

3. You will notice in the data set, that there are funds that are newly disclosed in “Aug2021” and that did not exist in “Aug2018” and funds that are no longer disclosed from “Aug2018” in “Aug2021”. The result for each of the following questions should include CRD, Adviser, FundID, FundName, Assets, Auditor

a. Determine New Funds that are disclosed in “Aug2021” but did not exist in “Aug2018”

b. Determine Dropped Funds that are disclosed in “Aug2018” but are dropped in “Aug2021”

c. Determine Change in Assets (Difference in asset value from 2018 to 2021) for funds that are disclosed both in “Aug2018” and in “Aug2021”. This is also referred as “Asset Inflation/Deflation”. The result should include “ChangeinAssets” column instead of “Assets” column.

d. Determine funds that changed an Auditor between Aug2018 to Aug2021. Indicate who is the Auditor that took the most business from its competitors and Indicate the auditor that lost the most business to its competitors.

4. Adviser is a “client” of the Auditing company disclosed in the data set. An Adviser may advise one or more funds.

a. Determine the number of unique clients each Auditor has in Aug2021. The result set should include columns “Auditor” and “NumberofUniqueClients”.

b. Extend the above 4(a) question to only display the Auditors that have atleast 5 unique clients.

5. The larger the asset size of a fund the Auditor provides a service, the more revenue the Auditing company makes as they get paid on percentage of fund assets Audited. Create a calculated column “AssetSizeBand” in Aug2021 that has coded values “0-$1Bn”, “>$1Bn-2Bn”, “>$2Bn-3Bn” and “>3Bn” based on the “Assets” column. The result should include columns FundID, FundName, Assets, AssetSizeBand

6. Filter “Market_Aug2021” tab to provide only Funds that have Assets greater than $3Bn. The result can include all columns of the tab. Also, the result should be sorted with funds from largest to smallest assets.

 

(5/5)
Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme

Get Free Quote!

271 Experts Online