Increasing Real Estate Management Profits: Harnessing Data Analytics

About this course: In this final course you will complete a Capstone Project using data analysis to recommend a method for improving profits for your company, Watershed Property Management, Inc. Watershed is responsible for managing thousands of residential rental properties throughout the United States. Your job is to persuade Watershed’s management team to pursue a new strategy for managing its properties that will increase their profits. To do this, you will: (1) Elicit information about important variables relevant to your analysis; (2) Draw upon your new MySQL database skills to extract relevant data from a real estate database; (3) Implement data analysis in Excel to identify the best opportunities for Watershed to increase revenue and maximize profits, while managing any new risks; (4) Create a Tableau dashboard to show Watershed executives the results of a sensitivity analysis; and (5) Articulate a significant and innovative business process change for Watershed based on your data analysis, that you will recommend to company executives. Airbnb, our Capstone’s official Sponsor, provided input on the project design. The top 10 Capstone completers each year will have the opportunity to present their work directly to senior data scientists at Airbnb live for feedback and discussion. "Note: Only learners who have passed the four previous courses in the specialization are eligible to take the Capstone."

Created by:  Duke University

• Taught by:  Daniel Egger, Executive in Residence and Director, Center for Quantitative Modeling

  Pratt School of Engineering, Duke University

• Taught by:  Jana Schaich Borg, Assistant Research Professor

  Social Science Research Institute

Basic Info Course 5 of 5 in the Excel to MySQL: Analytic Techniques for Business Specialization Commitment 8 weeks of study, 8-10 hours/week Language English How To Pass Pass all graded assignments to complete the course. User Ratings 4.7 stars Average User Rating 4.7See what learners said Coursework

Each course is like an interactive textbook, featuring pre-recorded videos, quizzes and projects.

Help from your peers

Connect with thousands of other learners and debate ideas, discuss course material, and get help mastering concepts.

Certificates

Earn official recognition for your work, and share your success with friends, colleagues, and employers.

Duke University Duke University has about 13,000 undergraduate and graduate students and a world-class faculty helping to expand the frontiers of knowledge. The university has a strong commitment to applying knowledge in service to society, both near its North Carolina campus and around the world.

Syllabus


WEEK 1


Introduction
The goal for this week is to learn about the Capstone Project you are tasked with, acquire background about the business problem, and begin to outline the steps of your analysis.


5 videos, 14 readings expand

1. Video: Introduction to the Capstone Project
2. Reading: Course Overview
3. Reading: Special Thanks!
4. Reading: About the Course Team
5. Reading: FAQ (Frequently Asked Questions)
6. Reading: Feedback Survey Information
7. Reading: Lesson Overview
8. Reading: Elicitation Refresher
9. Reading: Letter from Your Project Manager
10. Reading: What are Project Managers, Anyway?
11. Video: What Watershed Owners Care About
12. Reading: Background about the Short-term Rental Industry
13. Reading: Lesson Overview
14. Video: Elicitation Interview with Your Project Manager
15. Video: Elicitation Interview with Watershed's Marketing Director
16. Video: Elicitation Interview with Watershed's Financial Director
17. Reading: Outlining an SPAP
18. Reading: Requirements and Assumptions
19. Reading: Feedback Survey

Graded: Your Three Elicitation Interviews
Graded: Elicitation

WEEK 2


Data Extraction and Visualization
The goal of this week is for you to extract the relevant data from the MySQL database you are given access to, and to look at it briefly in Tableau to get sense of what data you have.


1 video, 3 readings expand

1. Video: Meet Your Data
2. Reading: How to Meet and Retrieve Your Data
3. LTI Item: How to Meet and Retrieve Your Data (Jupyter notebook)
4. Reading: Visualize Your Data to Make Sure You Know What They Are
5. Reading: Feedback Survey

Graded: Verify You Have Extracted the Correct Data
Graded: Make Sure You Understand What Your Data Mean

WEEK 3


Modeling
The goal of this week is for you to create a financial model using Excel to analyze the data you extracted from the database, and to start to predict short-term rents for some of Watershed's existing properties.


5 videos, 16 readings expand

1. Reading: Week 3 Learning Goals
2. Reading: Single Workbook Containing Template Spreadsheets 1-3
3. Reading: Single Workbook Containing Guide Spreadsheets
4. Video: Creating a Predictive Model for Short-term Rental Rates
5. Reading: Best Practices for Setting up an Excel Spreadsheet
6. Reading: Using the First Best-Fit Line Template Spreadsheet
7. Reading: First Best Fit Line Template (Spreadsheet 1)
8. Video: Normalizing Rents to Improve Occupancy Forecasting
9. Reading: Using the Normalized Data and Model Template Spreadsheet
10. Reading: Normalized Data and Model Template (Spreadsheet 2)
11. Video: Using the Dollars to Percentile Conversion Guide Spreadsheet
12. Reading: Dollars to Percentile Conversion Guide Spreadsheet
13. Reading: Applying Normalization to the Comparable Properties
14. Reading: Lesson Overview
15. Video: Optimizing Rents to Maximize Revenues
16. Video: Using the Solver Revenue Maximization Guide Spreadsheet
17. Reading: Solver Revenue Maximization Guide Spreadsheet
18. Reading: Optimizing Watershed Rents
19. Reading: Solver Rent Optimization Template (Spreadsheet 3)
20. Reading: Alternative to Solver Template (Spreadsheet 4)
21. Reading: Feedback Survey

Graded: First Best-Fit Line
Graded: Normalization
Graded: Applying Normalization to the Comparable Properties
Graded: Optimization Basics
Graded: Optimizing Watershed Rents

WEEK 4


Cash Flow and Profits
The goal for this week is for you to use your projections about the Watershed properties to estimate cash flows and profits Watershed would experience if it converted properties to short-term rentals.


3 videos, 11 readings expand

1. Reading: Week 4 Learning Goals
2. Reading: Single Workbook Containing Template Spreadsheets 5-6
3. Video: Estimating Watershed Cash Flow and Profits
4. Reading: Using the Alternative to Solver Template Spreadsheet
5. Video: Distinguishing Cash Flow from Profits and Losses
6. Reading: Using the Forecasting Cash Flow and Profits Template Spreadsheet
7. Reading: Forecasting Cash Flow and Profits Template (Spreadsheet 5)
8. Video: Using the Annual Cash Flows and Profits Spreadsheet
9. Reading: Annual Cash Flows and Profits Guide Spreadsheet
10. Reading: Using the Sorting by Profitability Template Spreadsheet
11. Reading: Sorting by Profitability Template (Spreadsheet 6)
12. Reading: The Value of Considering Cash Flow Risk and Total Cash Required
13. Reading: The Value of Financial Sensitivity Analysis in General
14. Reading: Feedback Survey

Graded: Alternative to Solver
Graded: Profitability
Graded: Cash Flow Risk and Total Cash Required
Graded: Sensitivity Analysis: Measuring Cutoffs at 40% Transaction Fee

WEEK 5


Data Dashboard
The goal of this week and next week is to build an analytical dashboard in Tableau using the data models and assumptions you have discovered in prior weeks.


6 videos, 3 readings expand

1. Video: Using Tableau to Perform Sensitivity Analysis
2. Video: Dashboard for Analyst Use
3. Video: Dashboard Modification for a Financial Audience
4. Reading: How to Get Started Making Your Dashboard
5. Reading: Additional charts for your sensitivity analysis
6. Video: Bar in Bar Graphs in Tableau
7. Video: Histograms in Tableau
8. Video: Tables in Tableau
9. Reading: Feedback Survey

WEEK 6


Dashboard for Decision-makers
This week complete your dashboard and add design elements so the dashboard is ready for stakeholders (Watershed executives, for example) to use it to test your model's assumptions.


2 videos, 6 readings expand

1. Video: Preparing Your Dashboard for Decision-makers
2. Reading: Lesson Overview
3. Video: Jittered Maps in Tableau
4. Reading: How to Use Jittering to Depict Multiple Data Points in the Same Geographic Location
5. Reading: Turning Your Sensitivity Analysis into a Recommendation
6. Reading: Finalizing Your Dashboard
7. Reading: Tableau Tricks to Try on Your Own (Including R Integration!)
8. Reading: Feedback Survey

Graded: Sensitivity Analysis

WEEK 7


Final Project
This week, design and give a presentation for Watershed executives with your business recommendations, and complete a white paper template. Evaluate 3 peer's dashboards, white papers and presentations.


2 videos, 7 readings expand

1. Video: Persuading Decision-makers to Follow Your Recommendations
2. Reading: New Information from Your Project Manager!
3. Reading: White Paper Background Information
4. Reading: About the Final Project
5. Reading: PART I: Tableau Dashboard Instructions
6. Reading: PART 2: White Paper Instructions
7. Reading: PART 3: Presentation Instructions
8. Video: Congratulations on Joining the Exciting Field of Data Analytics!
9. Reading: Feedback Survey

Graded: A Very Important Question!
Graded: Final Project Assignment Submission