MIS 4304 Spring 2022

Project 1

From Webcampus download the file intitled Project 1

When open create a new worksheet named Dashboard

Objective: To create a Dashboard providing a decisionmaker with a visual summary of significant statistics for the purpose of understanding a business.  The dashboard will be created using Excel formulas, techniques and objects

Due Date: Wednesday, April 20th, End of day

Provided:  

An Excel spreadsheet having 2000 unique real estate transactions. Each transaction consists of 22 columns(fields) describing its attributes/characteristics.  See Fields table. 

Deliverables:  

A dashboard created by utilizing Excel functions and features discussed in class and presented in the textbook. The dashboard will be used by a manager to assist with drawing conclusions and making decisions about the real estate business he/she operates. It must include Excel formulas and objects (spin buttons, sliders, dropdown lists, etc.) and be formatted in a visually appealing, understandable and professional manner.


Requirements:
Dashboard
Must include but not be limited to the following:
o At least one Spin button/slider and one dropdown list
o Some combinations of SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS functions
o Some combinations of SUM, AVERAGE, MEDIAN, MODE, MIN, MAX. RANK, LARGE, SMALL, STDEV functions
o Some combinations of AND, OR, NOT, TRUE, FALSE functions
o Some combinations of CHOOSE, VLOOKUP, INDEX/MATCH functions
o Application of Financial functions such as PMT, PV, FV, IPMT, CUMIPMT, CUMPRINC
o At least one application of Custom formatting
o Use of Range names
o Application of Conditional Formatting
o At least two types of Charts (Not including Pie, Line, Column, Bar)
▪ I suggest Bubble chart, Tree chart, Radar chart

Can include the following:
o Nested functions
o Wildcards
o RANDBETWEEN, RAND()
o TODAY()
o Charts such as Waterfall, doughnut, 100% Column
o Commission analysis
Cannot include:
o Pivot tables
o Functions not discussed in class or the textbook
o Pie charts, Simple Column or Bar charts
o Line charts


Fields table: A spreadsheet which includes data with the following attributes


Analysis can include but is not limited to:
• Analysis of Sales by location, realtor, etc.  
Grading:
• Adherence to the Requirements listed above
• Use of sufficient number and variety of Excel tools and features
• Mastery of Excel concepts presented in class and the textbook
• Creativity in terms of employing tools of data analysis
• A well formatted, visually appealing dashboard which presents statistics which you deem to be important to a manager of a real estate business.
• Uniformity of formatting throughout the analysis  

Suggestions:
• Create range names
• Most important information on dashboard is found in upper left corner
• Convert Transaction worksheet into a table

Examples of Types of Analysis:
By Sales
• Realtor
• Location
• Property type
• Bedrooms
By Realtor
• Sales
• Location
• Property type
• Bedrooms
• Number transactions
By Year
• Sales
• Location
• Property type
• Bedrooms
• Number transactions
By Days
• From contract to sale
• Realtor
• Number transactions
By Condition
• Realtor
• Location
• Property type
• Price
Statistical
• Averages
• Medians
• Modes
• Standard deviations
• Largest, Smallest, Rankings
Spin button, Dropdown, Slider
• Year
• Realtor
• Location