Description

Choose on of the below options.  You may work in a group of up to 5 people. Each person will need to individually submit their project on blackboard.

You work for a company that does educational consulting.Your boss knows that you are a genius when it comes to Excel and decides to impose upon you a little – he assigns you a project to create an Excel spreadsheet that will be a teacher’s companion.It has to have an input area for the grades, rank them, split them into different quartiles, create relevant charts, give all the summary statistics that are relevant, color code grades that need to be looked into, have a conditional format highlighting when a student does better or worse than previous exams, show relevant lookup values for grades according to number and letter, have relevant pivot tables, etc.The goal is that it is a stand-alone tool for teachers to use to assess everything possible about a student.

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: CREE, TSLA, PCLN, AMZN, NFLX, KO, PG, CIT, AIG, or something else (with a market cap of over 10B).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 work for a government agency, and although you have been there for fifteen years, your boss only recently came over to you and asked you to actually do some work.At first you weren't sure what he was talking about; after consulting with some friends, you remembered that supposedly that is what you are being paid for.Thankfully, you have been very keen to fiddle around with Excel in-between checking email, Facebook, and Halo.Your boss said that the government has a spent a lot of money on a huge housing project which is now empty. Your job, should you choose to accept it, is to create an ad campaign to entice people to move in.You must use government statistics to decide how, where, what when and why to target certain neighborhoods, etc.You must detail and present the reasoning for your plan with clear and precise procedural steps of how you arrived at your conclusion.

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/or 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

Extra credit: 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. Make sure to take a screen shot of your model and paste it into a sheet in Excel, sometimes it doesn't save properly.

freeze panes

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

Instruction Files