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

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

expert
Susanne ParkerNursing
(5/5)

817 Answers

Hire Me
expert
Jessica FullerComputer science
(5/5)

905 Answers

Hire Me
expert
Milton BlahaaFinance
(5/5)

857 Answers

Hire Me
expert
Alexis JiangEducation
(5/5)

683 Answers

Hire Me
Others
(5/5)

We have various data sets of different Investment funds (Identified by unique reference “FundID” and has a Dollar amount in “Assets”)

INSTRUCTIONS TO CANDIDATES
ANSWER ALL QUESTIONS

Descriptive Analytics 

Data set Description 

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

We have 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 funds operationally. 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.

 

 

I Questions 

 

1. Write a SQL query (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, write a SQL query (or sequence of queries) to 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. Write a SQL query to determine New Funds that are disclosed in “Aug2021” but did not exist in “Aug2018” 

 

b. Write a SQL query to determine Dropped Funds that are disclosed in “Aug2018” but are dropped in “Aug2021” 

 

c. Write a SQL query to 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. Write a SQL query to 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. Write an SQL query to 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) SQL query to only display the Auditors that have alteast 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. Write a SQL query to 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. Write a SQL query to filter “Market_Aug2021” table to output only Funds that have Assets greater than $3Bn. The result can include all columns of the table. Also the result should be sorted with funds from largest to smallest assets. 

 

II Questions 

 

Refer to the same data set above. Write a stored procedure in SQL and executing the stored procedure should output the following for every Auditor. For ease of programming, you can keep the output of some queries in temporary tables and use those temporary tables for subsequent steps in the stored procedure. “KPMG” is used just for illustration purpose and the number of “Funds” are “Assets” shown are just dummy numbers as an example. The output should include actual numbers of every Auditor present in the data set.

 

(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!

382 Experts Online