124|POWERBI – The EPS Industry Explorer: A PowerBI Adventure

Challenges
, ,
1

BYU Student Author: @IWillyerd
Reviewers: @Marco, @Andrew
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

  • Power BI

Need a program? Click here.

Overview
You’re a first-year analyst and a small boutique hedge fund and you’ve been tasked with creating a dashboard to do some high-level due diligence on some equity investments. You took the list of companies that the fund is looking to take a position in and generated three datasets with different types of information. One has financial statement information, another financial ratio information, and the last one has basic identifying information. Your task is to load up these files in PowerBI, connect them using the data provided and create a small dashboard that especially highlights liquidity as that is one of the main traits your fund looks at in its investments.

Instructions

  1. Start by opening PowerBI Desktop or PowerBI Online, and then create a new report.
  2. Use the model function within PowerBI to link the three sheets from the dataset in a relational manner.
  3. Remove the “Waste Management” Industry from the data using the filter option.
  4. Create the following visualizations:
    • A tree map that shows the relationship between Industry and Earnings Per Share
    • A multi-row card visualization that shows the top 3 Earnings Per Share securities.
    • A table that shows the following values (note the name variations may be different in the datasets)
      • Company Name
      • Ticker
      • Revenue
      • Gross Profits
      • Free Cash Flow
      • Current Ratio
      • Operating Margin
    • Two Cards
      • The average Quick Ratio
      • The other card shows the sum of the market cap value
  5. Organize and format the visualizations to make them visually cohesive and clear and adjust the names of the values as needed.
  6. Format the numbers from the datasets in a clear and concise manner. (Check the data tab for this)
  7. Answer the following questions using the report to check your data.
    • What are the top three companies by earnings per share in the semiconductor industry?
    • What is the market cap value of the Discount stores industry?
    • What is the current ratio of Pfizer (ticker PFE) in the Drug Manufacturers-General Industry?

Data Files

Solution

Solution Image

Challange124_Solution_Image
Challange124_Solution_Image2141×1205 322 KB

Challenge124_Solution.pbix
Solution Video: Challenge 124|POWERBI – The EPS Industry Explorer: A PowerBI Adventure

2

This was a very good challenge and I will admit I got stumped a couple of times but I was able to figure it out in the end!!
PowerBI-TheEPSIndustryExplorer.pbix (104.2 KB)

3

Using the report I have provided #7 questions and answers below.

What are the top three companies by earnings per share in the semiconductor industry?
(Broadcom Inc., Micron Technology, Inc., Qualcomm Incorporated.)

What is the market cap value of the Discount stores industry?
(438 billion)

What is the current ratio of Pfizer (ticker PFE) in the Drug Manufacturers-General Industry?
(1.39)
PBIChallenge124.pbix (98.6 KB)