You work for a finance firm and are asked to firstly prepare a report detailing whether or not your company should invest in one of the following companies of your choice: TSLA,.You must prepare a report that details all the financial information (using Excel functions) and presents it in a clear and concise manner. Secondly, you were asked to come up with a system to invest in this stock. You must create something based on past history, test it, and show that your system is a relevant and important approach.
You must use every tool at your disposal. You must use clear, concise and appropriate formulas. You must present your information in a detailed and relevant manner. You will be graded on the “level” of formulas you used, on your ability to manipulate data, on your ability to present data for the intended audience, on how inventive you employ the tools at your disposal, on how thorough you are in your presentation, etc.
The first page should be a cover page with hyperlinks to the other pages and a picture in the background.
The second page should be a dashboard.
All subsequent pages should display every tool we have used throughout the semester, including a screen shot of your data model measures and calculated columns.
Extra credit: Power BI dashboard. You should have two (2) different pages (dashboards) from Power BI.
Extra credit: Your Power BI dashboard should include at least 2 custom visuals as per: https://www.youtube.com/watch?v=8S4YblyU22k&list=PLAQtmrhlOUzHhx_6Kbrz-bPkyGIAgb2lg
include relative absolute and mixed cell reference
use all the summary statistics (sum, average, min, max, count, etc.)
use at least two different instances of date and time functions
use at least three different formats such as number, date, custom etc.
use at least three different instances of nested if functions
use vlookup and index/match functions
create and use range names and use them in formulas
have at least 7 charts with axis names, chart title, etc. describing the data
use data bars
use at least three instances of conditional formatting, one of which needs to be custom formatted
design at least 2 tables
design at least 4 pivot tables
use the data model and create measures using CALCULATE, ALL, FILTER, and DISTINCTROWS (at least). You should be using the measures in your pivot table.
text boxes to explain
name your sheets and give them colors
sort the table at two levels or more
use the subtotal function
have grouped data
have at least four relevant pivot tables with at least two calculated fields (measures) and a calculated item
have a slicer available for the pivot table
create a one variable data table
create a two variable data table
use solver (with saved and summarized scenarios)
use advanced filters
use the database functions
use the sumif(s), countif(s), averageif(s) functions
use rank, percentrank, quartile, frequency, stdev, and correl functions
create a histrogram
at least four different formulas using arrays
at least two cases of data validation
use dynamic charts connected to lists
use some VBA (even just something simple)
use at least 2 different types of buttons (non-VBA)
use a doughnut chart and other effective visualizations from Udemy section: Data and Visualizations Overview
use the Advanced Review section from Udemy for ideas