Google Sheet

Using Data To Spot Financial Investment Opportunities With Hotels

Project Title: Data-Driven Decision Making for Real Estate Investment (Vacation Rental Analysis using Google Sheets) 

Objective

This project aims to use real-world data modeling techniques to analyze the viability of a real estate investment specifically a vacation rental property by applying key business intelligence metrics and visualizations within Google Sheets. NB: Data source - Kaggle

Story Behind the Dashboard

1. The Investment Context

In traditional asset investing, people often rely on assumptions like “location, location, location.” But smart investors use data-driven decision making. This dashboard was built to simulate such an analytical approach, focusing on a vacation rental data to spot financial investment opportunity through evaluating its financial performance throughout the year.

2. How Revenue Works: Income Per Night

The project uses the concept of short-term vacation rentals (like Airbnb), where money comes in through nightly bookings. Each month’s income is calculated as:

Rental Income = Occupied Nights × Average Price Per Night

This allows monthly variations due to seasonality and pricing to be captured dynamically, as seen in the Net Income Per Month line chart.

3. Understanding Costs

Expenses are categorized into:

• Fixed Costs (e.g., insurance, maintenance)

• Variable Costs (e.g., utilities, supplies, cleaning services)

The dashboard clearly separates and visualizes these to demonstrate how increasing occupancy can raise variable costs, but also dilute some cost per unit; a key learning from the scenario story.

4. Key Financial Metrics: OER & Cap Rate

Operating Expense Ratio (OER) = Total Cost / Gross Income
Helps evaluate how efficiently the property is being managed. A lower OER means better cost-efficiency.

Capitalization Rate (Cap Rate) = Net Operating Income / Purchase Price
Shows the return on investment. Higher is better.

In this project:

• Non-weighted OER: 0.90

• Weighted OER: 0.38

• Cap Rate: 2.23%

These are compared and visualized to emphasize that weighted averages offer a more accurate picture due to seasonal variations.

5. Evaluating Investment Risk

The dashboard classifies this investment (Grand_Hyatt Hotel) as Very Low Risk and the Best Choice among options. This is derived from stable income streams and controlled expenses.

Visual Highlights

Line Chart: Monthly Net Income fluctuations.

Pie Chart: Occupied Nights Rate (seasonality impact).
Scatter Plot: Shows correlation between Occupied Nights and Total Cost, revealing operational insights.

Insights & Lessons Learned

Data helps cut through assumptions; relying on numbers ensures better business decisions.

Weighted metrics outperform simple averages in seasonal business models.
Cap Rate and OER are powerful tools for property comparison.
Visual storytelling (charts and dashboards) makes financial insights more actionable.