Watershed Rental Properties

Analysis & Recommendations for Converting Long-Term Rentals into Short-Term Rentals

MySQL, Tableau, Excel

Scenario:

Watershed Properties is a long-term rental company that typically offers residential home leases in durations of 1-2 years. The company is interested in growing the business into the short-term rental market (AirBNB, VRBO, etc.) and would like an analysis of the potential profits and cash flow that could be made by using a $500,000 CAPEX budget to convert some of its long-term properties into short-term rentals. This analysis uncovers which localities and property types would offer the greatest potential returns.

Summary:

Accessed and retrieved data from MySQL databases on current long-term rental properties. Created entity relationship diagrams and relational schemas to understand data structure. Performed exploratory data analysis (EDA), nightly rental forecasting, variable cost forecasting, and occupancy rate forecasting. Analyzed CAPEX, cash flow, and profit/loss projections to determine the financial viability of property conversions. Developed a comprehensive white paper and Tableau dashboards to present findings and recommendations, ensuring data-driven decision-making for property investments.

Technical White Paper Potential Properties Dashboard Full Project on GitHub

 

Recommendations Presentation

 

 

Follow the corresponding slides to the corresponding notes.

 

Presentation Introduction

  • Hello and welcome to this presentation on our Analysis & Recommendations for Converting Watershed’s Long-Term Rental Units into Short-Term rentals.
  • After completing a previous analysis on the potential profit outlook for converting long-term to short-term rentals, the Executive team has decided it is willing to commit $500,000 in total CAPEX for the first round of conversions.
  • For the analysis in this presentation, we have developed a strategy to maximize future profits by converting the properties with the highest potential for profits by city and property type.

Which markets are best for the current business model?

  • These are annual revenue figures for all current long-term property rentals. Watershed collects a flat 10% across the board on these properties, so New York City and San Francisco are the most profitable under the long-term business model.

Which markets look best for the Short-Term market?

  • The potential profit figures here are based upon competitive nightly rental rates that were optimized for maximum occupancy. These rates were extrapolated to a yearly revenue based upon local comparative occupancy rates, then all fixed and variable costs were removed, and finally the current long-term annual revenue was subtracted to show the DIFFERENCE in potential profit from conversion.
  • Properties underneath the yellow line here are properties that are potentially unprofitable under a short-term rental business model.
  • Austin, Miami, and Palo Alto are the highest areas for potential profits.

Which cities have the most profitable properties?

  • This chart shows the average number of potential properties in each city. If we average the potential profits by city, we can see from the color mapping to this plot that Austin, Miami, and Palo Alto are still the most profitable.
  • However, as our Y-axis counts the number of profitable properties in these cities, there are 25 properties.
  • We have been given a $500,000 CAPEX budget at a maximum of $25,000 per property. This only allows for 20 property conversions. Therefore, we need a systematic approach to narrowing the selection to 20 properties.

What types of properties are potentially more profitable?

  • In this plot we can see the potential profits for every city grouped by property types. When we sort the property types in this order, we see a linear relation in profit potential.
  • And if we look closer at Austin (click), Miami (click), and Palo Alto (click), we see this trend continue.
  • (If we go back to our average profit rate per city, we can remove all apartments in Austin which lowers the number of properties by 5 (which is exactly how far we were over the limit) and increases the average property profit from $25,000 to $30,000.

How much total profit could each city bring in?

  • These colors of the bars in the plot are now the summation of profits for its respective city.
  • Properties in Miami and Palo Alto, and houses in Austin now sum to 20 conversions.
  • San Diego might look appealing here, but we can achieve nearly half of the $154,000 profit by only converting 2 properties in Palo Alto opposed to 9 properties in San Diego.

Financials for recommended property conversions.

  • These are the aggregated financial metrics for the 20 properties I have recommended.
  • These bars represent the Cash Flow and Net Profits for the year of conversion, and the years thereafter.
  • The risk parameters used for short-term profit calculation are available on the right, and the original default parameters given can be viewed by hovering over this blue box. Feel free to adjust and compare if reviewing this presentation later. Keep in mind adjusting these parameters will add/remove the number of properties that are profitable which will add/remove the number of properties from this dashboard.
  • These 20 properties use all $500,000 of the allotted initial CAPEX for conversions and when depreciated over a period of 5 years, will yield an annual Return on Investment of 719%!

Exit Summary

  • To close I leave you with this summary table which includes the 4 most important financial metrics for this conversion proposal, locations, property types, average city profits, and ROI for each city.
  • This concludes my presentation of which Watershed properties would be the most profitable to convert given the $500,000 CAPEX budget. This recommendation leaves New York and San Francisco untouched in the current long-term business model as the most profitable long-term rental cities. And only converts 2 of the 9 properties in Palo Alto (which is the 3rd most profitable city under the long-term model) all while unlocking the most profitable short-term rental properties in Miami, Palo Alto, and Austin.