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 |
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
- Video: Introduction to the Capstone Project
- Reading: Course Overview
- Reading: Special Thanks!
- Reading: About the Course Team
- Reading: FAQ (Frequently Asked Questions)
- Reading: Feedback Survey Information
- Reading: Lesson Overview
- Reading: Elicitation Refresher
- Reading: Letter from Your Project Manager
- Reading: What are Project Managers, Anyway?
- Video: What Watershed Owners Care About
- Reading: Background about the Short-term Rental Industry
- Reading: Lesson Overview
- Video: Elicitation Interview with Your Project Manager
- Video: Elicitation Interview with Watershed's Marketing Director
- Video: Elicitation Interview with Watershed's Financial Director
- Reading: Outlining an SPAP
- Reading: Requirements and Assumptions
- 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
- Video: Meet Your Data
- Reading: How to Meet and Retrieve Your Data
- LTI Item: How to Meet and Retrieve Your Data (Jupyter notebook)
- Reading: Visualize Your Data to Make Sure You Know What They Are
- 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
- Reading: Week 3 Learning Goals
- Reading: Single Workbook Containing Template Spreadsheets 1-3
- Reading: Single Workbook Containing Guide Spreadsheets
- Video: Creating a Predictive Model for Short-term Rental Rates
- Reading: Best Practices for Setting up an Excel Spreadsheet
- Reading: Using the First Best-Fit Line Template Spreadsheet
- Reading: First Best Fit Line Template (Spreadsheet 1)
- Video: Normalizing Rents to Improve Occupancy Forecasting
- Reading: Using the Normalized Data and Model Template Spreadsheet
- Reading: Normalized Data and Model Template (Spreadsheet 2)
- Video: Using the Dollars to Percentile Conversion Guide Spreadsheet
- Reading: Dollars to Percentile Conversion Guide Spreadsheet
- Reading: Applying Normalization to the Comparable Properties
- Reading: Lesson Overview
- Video: Optimizing Rents to Maximize Revenues
- Video: Using the Solver Revenue Maximization Guide Spreadsheet
- Reading: Solver Revenue Maximization Guide Spreadsheet
- Reading: Optimizing Watershed Rents
- Reading: Solver Rent Optimization Template (Spreadsheet 3)
- Reading: Alternative to Solver Template (Spreadsheet 4)
- 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
- Reading: Week 4 Learning Goals
- Reading: Single Workbook Containing Template Spreadsheets 5-6
- Video: Estimating Watershed Cash Flow and Profits
- Reading: Using the Alternative to Solver Template Spreadsheet
- Video: Distinguishing Cash Flow from Profits and Losses
- Reading: Using the Forecasting Cash Flow and Profits Template Spreadsheet
- Reading: Forecasting Cash Flow and Profits Template (Spreadsheet 5)
- Video: Using the Annual Cash Flows and Profits Spreadsheet
- Reading: Annual Cash Flows and Profits Guide Spreadsheet
- Reading: Using the Sorting by Profitability Template Spreadsheet
- Reading: Sorting by Profitability Template (Spreadsheet 6)
- Reading: The Value of Considering Cash Flow Risk and Total Cash Required
- Reading: The Value of Financial Sensitivity Analysis in General
- 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
- Video: Using Tableau to Perform Sensitivity Analysis
- Video: Dashboard for Analyst Use
- Video: Dashboard Modification for a Financial Audience
- Reading: How to Get Started Making Your Dashboard
- Reading: Additional charts for your sensitivity analysis
- Video: Bar in Bar Graphs in Tableau
- Video: Histograms in Tableau
- Video: Tables in Tableau
- 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
- Video: Preparing Your Dashboard for Decision-makers
- Reading: Lesson Overview
- Video: Jittered Maps in Tableau
- Reading: How to Use Jittering to Depict Multiple Data Points in the Same Geographic Location
- Reading: Turning Your Sensitivity Analysis into a Recommendation
- Reading: Finalizing Your Dashboard
- Reading: Tableau Tricks to Try on Your Own (Including R Integration!)
- 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
- Video: Persuading Decision-makers to Follow Your Recommendations
- Reading: New Information from Your Project Manager!
- Reading: White Paper Background Information
- Reading: About the Final Project
- Reading: PART I: Tableau Dashboard Instructions
- Reading: PART 2: White Paper Instructions
- Reading: PART 3: Presentation Instructions
- Video: Congratulations on Joining the Exciting Field of Data Analytics!
- Reading: Feedback Survey
Graded: A Very Important Question!
Graded: Final Project Assignment Submission
FAQs
How It Works
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.
Creators
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.
Pricing
| Audit | Purchase Course | |
|---|---|---|
| Access to course materials | Available | Available |
| Access to graded materials | Not available | Available |
| Receive a final grade | Not available | Available |
| Earn a shareable Course Certificate | Not available | Available |
Ratings and Reviews
It really help me summarise the critical point I learned through out the course.
You will be proud of yourself when you complete this Capstone !
A great learning experience
Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.
© 2017 Coursera Inc. All rights reserved.
Awesome course, the business case, realy cool