ERP Launchpad
Assignment
for Data
Analytics
Internship
ERP Launchpad is a B2B
Software Development & Data
Analytics Company.
Which has a Major
Focus on the
Manufacturing
Industry
We use Google Sheets as
Our Database &
Google Data Studio to Create
Dynamic Report Dashboards
So we want you to be
Comfortable with Advanced
Google Sheets Formulas like :
1.) IF Conditions
2.) Query Function
3.) Vlookup
4.) ARRAYFORMULA etc.
Be Comfortable with Pivot
Tables
Formulas Training
Now Let's Begin with the Assignment
Data Set
Make a Copy of the Above Data Set Google Sheet
Instructions :
You will make a Copy of the Sheet & Perform Tasks in your Own
Google Drive Account in the Sheet & the Data Studio Report.
You have to Create a PDF Explanation Document with Screenshots
to Explain all your Solution including Report Dashboard.
Objectives
Data Manipulation Tasks
Task 1 : First Create a New Sub Sheet & Fetch all the Data from the Sheet
called "Data Set" where Data from the PE Ratio is not Blank & PE Ratio <
100 using Query Function and sort the data by Descending of Market Cap
Amount.
Task 2 : Now Create 2 Columns of Industry & NSE/BSE Code & Fetch Data
from the Second Sheet with Vlookup, Make sure the Vlookup Formula is
used with "ARRAYFORMULA" meaning formula will be written only once but
will be applicable throughout the column,
Also make sure the Formula of Vlookup will be applied only until when
data is there in the column, handle this with IF CONDITIONS in that same
formula.
Task 3 : Now Create 2 More Columns at the End with the Heading "Bear
Mode 1" & "Bear Mode 2"
Bear Mode 1 Condition : Use IF Condition to print "Yes" if % Change from
52 Week High > 30 else "No"
Bear Mode Condition 2 : Use IF Condition to print "Yes" if % Change from
52 Week Low < 30 else "No"
Stock Status : Now Create a Column named "Stock Status" & then apply
multiple IF Conditions to check if PE Ratio <65, Bear Mode 1 & 2 = "No" then
Print "Good Stock" in the column else "".
Task 4 : Now create a new sheet & fetch all Data where Stock Status =
'Good Stock' which will be your Final Organized Data Source so Create
Proper Formatting to this Sheet like the Data Source Sheet & also add
Appropriate Conditional Formatting Colour Scales.
Task 5 : Create a Pivot Table Report as Follows :
Industry wise Total Count of Companies
Industry wise Average Market Cap
Industry wise sum of Market Cap
( The Report should have proper formatting with proper heading, slicers & Color
formatting with color scale or Conditional Formatting so that it looks like a
professional report & not just a simple pivot table)
Submit your Solution
ERP Launchpad
Pune, Maharashtra, India.
© ERP Launchpad 2021 All Rights Reserved.
Powered by ERP Launchpad
