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.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
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
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
