Submitted by : Christian Robles - March 18, 2025

•  Objective:

  • The goal is to identify the smallest possible whole number value for Total Bookings that will help in creating a better visual for the pie chart shown above.

• Clarification:

  • "Total Bookings" refers to the estimated minimum number of actual bookings.

  • Exact figures are unavailable, and the true values might be two, three, or more times higher than the calculated estimate.

• Calculation:

  • Through mathematical derivation, the minimum possible value for Total Bookings is determined to be 1 million.

  • The percentage distribution of bookings across various categories has been recalculated.

  • Values labeled as "(Corrected)" are validated based on specific assumptions.

• Analysis:

  • Rankings and cumulative totals for bookings have been calculated.

  • The contribution of each category to the overall total bookings is expressed as a percentage.

• Visualization:

  • A Pareto Chart was used to demonstrate the 80/20 principle (where 80% of effects come from 20% of causes).

  • Categories for the number of bedrooms are now labeled as "Bedrooms" (e.g., "2-BR" for two bedrooms).

• Additional Insights:

  • The top value was highlighted for distinction in a bar chart.

  • Specific color cutoffs were established for the Pareto Chart to enhance visual clarity.

• Challenges:

  • An infographic chart was considered but proved difficult due to very small values in some bedroom categories, making graphical elements nearly invisible when scaled.

• Feedback Request:

  • Suggestions are welcome for further improving the dashboard beyond simple adjustments like background or color changes.

This breakdown ensures the key points are clear and easy to follow. Let me know if you'd like any additional refinements!

Link to Google Sheet Answer :
https://docs.google.com/spreadsheets/d/1ybYd94c6yDKDZDW9uUb7Ni9DGlwpgELlmbesKz11jrM/edit?usp=sharing

I've created a single formula inside cell  C2 of 'Highlighted  Listings' tab. Below is the formula :

  }

It uses the force array {} bracket symbol to produce an output in the form of {D,E}, so that I don't need to create a similar formula in E2. This also makes it easier to manage the result.

Then, using the ARRAYFORMULA function to handle the spill to both columns. 

Before it calculates the XLOOKUP formula, it will first the corresponding cells for the current row being evaluated if one of them is blank. ISBLANK is a True or False check and will result to 1 or zero. adding both ISBLANK checks will return 2 if both are true,  return 1 if only one is true, and return 0 if both are false. thus when both are false it will now run the corresponding if condition.

XLOOKUP is not case-sensitive and so this will handle already one of the challenge of this task and does not require UPPER. Since it is in ARRAYFORMULA, we created a search_key as an array that will be handled by Arrayformula as a per row evaluation picking only the current cell for the said raw. The added feature here is the error-catching condition inside XLOOKUP returning "No Match Found" if there's no result returned by the XLOOKUP condition.

As for the bonus point, I have used Trim function to handle extra leading or lagging spaces for the search_key.

More Bonus...

Solution 2 :
Formula for C2:
=Arrayformula(IF(ISBLANK($A$2:$A)+ISBLANK($B$2:$B) , "", XLOOKUP(TRIM(UPPER($A$2:$A)),Data!$A$2:$A,Data!B$2:B,,0)))
Formula for D2:
=Arrayformula(IF(ISBLANK($A$2:$A)+ISBLANK($B$2:$B) , "", XLOOKUP(TRIM(UPPER($A$2:$A)),Data!$A$2:$A,Data!C$2:C,,0)))

Solution 3 :
Formula for C2:

=Arrayformula(if($A$2:$A = "","",if($B$2:$B ="", "", XLOOKUP(UPPER(TRIM($A$2:$A)),Data!$A$2:$A,Data!B$2:B,"Match not found",0))))

Formula for D2:

=Arrayformula(if($A$2:$A = "","",if($B$2:$B ="", "", XLOOKUP(UPPER(TRIM($A$2:$A)),Data!$A$2:$A,Data!C$2:C,"Match not found",0))))

Honorable Mention :

For Column  C2, drag C2 to end of column:

=IF(A2="","",IF(B2="","",IFERROR(QUERY(Data!$A$2:$C,"select B, C where A ='" & UPPER(TRIM(A2)) & "' LIMIT 1",0),"")))

Understanding Indicators and KPIs in Short-Term Rental Management

In the realm of business, indicators serve as essential tools for measuring performance and guiding strategic decisions. They provide insights into various aspects of a company or industry, enabling stakeholders to assess progress towards goals and identify areas for improvement. This document explores the significance of indicators, particularly Key Performance Indicators (KPIs), in the context of a short-term rental management company like Hometime. We will also discuss the specific KPIs that Hometime is currently monitoring or should consider to enhance its operational efficiency and market competitiveness.

What are Indicators?

Indicators are quantifiable metrics that reflect the performance of a business or industry. They can be financial, operational, or customer-focused, and they help organizations track their progress over time. By analyzing these indicators, companies can make informed decisions, optimize processes, and ultimately drive growth.

Importance of Indicators in a Company or Industry

• Performance Measurement: Indicators allow businesses to evaluate their performance against set objectives. This measurement is crucial for understanding how well a company is doing in relation to its goals.

• Informed Decision-Making: With clear indicators, management can make data-driven decisions rather than relying on intuition. This leads to more effective strategies and resource allocation.

• Benchmarking: Companies can use indicators to compare their performance with industry standards or competitors. This benchmarking helps identify strengths and weaknesses.

• Trend Analysis: By monitoring indicators over time, businesses can identify trends that may affect their operations, allowing them to adapt proactively.

• Accountability: Indicators create a framework for accountability within an organization. Teams can be held responsible for achieving specific targets, fostering a culture of performance.

Key Performance Indicators (KPIs) for Hometime

For a short-term rental management company like Hometime, the following KPIs are essential for monitoring performance and ensuring success:

1. Occupancy Rate: This KPI measures the percentage of available rental units that are occupied over a specific period. A higher occupancy rate indicates effective marketing and pricing strategies.

2. Nights Blocked: Nights blocked is the average number of nights you've blocked (manually or as a default) during the selected time frame. For individual listings, we show you the total number of nights you've blocked.

3. Nights Booked: Nights booked is the average number of nights booked during the selected time frame. For individual listings, we show you the total number of nights booked.

4. Unbooked Nights: Unbooked nights is the number of calendar nights, which includes blocked nights, that are unbooked for the selected time frame. For individual listings, we show you the total number of unbooked nights.

5. Check-ins: Check-ins is the average number of check-ins occurring during the selected time period. For individual listings, we show you the total number of check-ins.

6. Average Daily Rate (ADR): ADR calculates the average rental income per occupied unit per day. This metric helps assess pricing strategies and revenue management.

7. Revenue Per Available Room (RevPAR): RevPAR combines occupancy rate and ADR to provide a comprehensive view of revenue generation. It is a critical indicator of overall financial performance.

8. Guest Satisfaction Score: Measuring guest satisfaction through reviews and ratings is vital for maintaining a positive reputation and encouraging repeat bookings.

9. Booking Lead Time: This KPI tracks the average time between a booking being made and the actual stay. Understanding lead times can help optimize marketing efforts and pricing strategies.

10. Cancellation Rate: Monitoring the percentage of bookings that are canceled can provide insights into customer behavior and potential issues with the booking process.

11. Cost Per Acquisition (CPA): This metric assesses the cost associated with acquiring a new guest. It is essential for evaluating the effectiveness of marketing campaigns.

12. Net Promoter Score (NPS): NPS measures customer loyalty and the likelihood of guests recommending Hometime to others. A high NPS indicates strong brand loyalty.

13. Nightly Rate: We calculate the average nightly rate by dividing total nightly revenue by the number of booked listing nights.

14. Quality (5-Star Rating): It aggregates the number of 5-star ratings you get from guests across 7 categories: Accuracy, Check-in, Cleanliness, Communication, Location, and Value. It also allows you to monitor your percentage of 5-star ratings, track your overall rating, review quality over specific time frames for each category, and compare your rating to similar listings.

15. Length of Stay: This is the average number of nights guests stay per booking.

16. Earnings:

    • Average Nightly Revenue: This is the average amount of money you’ve received from a booking, including fees you charge, before our service fee, taxes, or any other fees are deducted.

    • Total Nightly Revenue: This is the total amount of money you’ve received from a booking, including fees you charge, before our service fee, taxes, or any other fees are deducted.

    • Cancellation Fees: These are the fees you get when guests cancel reservations.

17. Booking Conversion: Overall conversion rate shows the average daily number of unique visitors who viewed your listing in search and then booked your stay. Conversion has 3 stages:

    • First-page Search Impressions: A guest views your listing in search, sometimes on the first page.

    • Search-to-listing Conversion: The guest then clicks through to your full listing page.

    • Listing-to-booking Conversion: The guest books your stay.

18. Booking Lead Time: This is the average time between booking and the check-in date.

19. Returning Guests: This shows you the percentage of your guests who have previously stayed at any of your listings. We calculate this by dividing the total number of returning guests by the total number of all guests for each listing, then we average based on how many listings you have.

20. Views: The number of times your listings have been viewed over a set period. You can compare this to the average for similar listings.

21. Wishlist Additions: This is the average number of times your listings are added to guest wishlists.

22. Average Length of Stay (ALOS): This metric displays the average number of nights booked by a single guest. This reveals the number of consecutive nights that a single occupant booked your property. The average length of stay is critical to monitor because too many short stays can raise your operating costs and make your venture less profitable.

23. RevPAR - Revenue per Available Room: This term refers to the amount of money made per available room. It calculates how much revenue your units generate based on your average daily rate and occupancy rate. This is an excellent metric for gaining an overview of business performance and increasing revenue.

24. Net Operating Income (NOI): The revenue earned by a property after deducting operating expenses is known as net operating income. It's an important metric to examine your vacation rental profitability, which can be improved by lowering your operating costs.

25. Revenue per Property: Hosts and property managers in charge of multiple properties must understand how much each one generates. By calculating the revenue per property, you can determine which properties are the most cost-effective.

26. Total Revenue for All Your Vacation Rentals: Your total revenue is the sum of all reservations made during a given time period. This data can assist you in determining how much profit you might be able to make in the future.

27. Revenue per Channel: If you list your properties on sites like Airbnb, VRBO, and Booking.com, as well as have your own direct booking website, it's critical to track your rental revenue per channel.

28. Inquiry-to-booking Conversion Rate: While receiving inquiries is great, they are useless if they do not convert into bookings. This metric would be extremely useful for staying on track with your conversion rate as well as determining whether you are converting enough inquiries into bookings.

29. Average Response Time to an Inquiry: To maintain your response rate, you need to respond to inquiries within a minimum of 24 hours. Moreover, it is one of the key ranking factors on any platform and this is applicable even when it is your direct booking site as well as any other social media platform. This is also a contributing factor when applying for superhost status on Airbnb.

By focusing on these KPIs, Hometime can gain valuable insights into its operations, enhance guest experiences, and drive profitability in the competitive short-term rental market.

In conclusion, indicators and KPIs are vital components of effective business management. For Hometime, leveraging these metrics will not only improve operational efficiency but also position the company for sustainable growth in the dynamic landscape of short-term rental management.

With over 11 years of experience in data analytics, I’ve worked on numerous projects requiring data integration from diverse sources. Below are examples of projects I’ve completed, highlighting my expertise and the tools I utilized.

1. Foundever (Operational Reporting Dashboard for Ford)
   Tools: Power BI, MS Excel, SharePoint
   This project involved consolidating data from a main SharePoint folder and its sub-folders, where each campaign stored daily or weekly production data. Reporting analysts retrieved raw data from client tools or internal systems, and a team of Power Query experts transformed it into a standardized Excel format (columns: Date, KPI Name, Numerator, Denominator, Factor, etc.). I used Power BI to build a unified dashboard, providing a holistic view of company performance for quick insights and decision-making.

2. Foundever (Amazon Project River Dashboard)
   Tools: Power BI, MS Excel, SharePoint
   For Amazon, a key Foundever client, I developed a global dashboard for sites managed by operations in the Philippines. Using the same SharePoint-Excel data-handling approach, I created a scalable visualization in Power BI to monitor performance across all sites.

3. Appen (Data Analytics Dashboards)
   Tools: Mode Analytics (SQL), Power BI, Excel, CSV
   I built three dashboards using Mode Analytics, a web-based SQL editor that integrates with SQL databases, cloud data warehouses (Snowflake, Redshift), and client systems (with appropriate access). I wrote SQL queries to create views, then used Power BI for visualization. Mode’s fast refresh rate and ease of use streamlined reporting.

4. Concentrix SREV (Quick Data Analysis Projects)
   Tools: Power BI, MS Excel, CSV, Salesforce, HubSpot
   I conducted multiple rapid analyses using client data, primarily from Excel and CSV files, supplemented by tools like Salesforce and HubSpot. These projects delivered actionable insights into underperforming areas, supporting consultants in securing client partnerships.

5. TaskUs (Billable Hours Template and Dashboard)
   Tools: Google Sheets, MS SQL, Power BI
   For a North America-based request, I designed a Google Sheets template to calculate monthly billable hours, integrating data from workforce, HR, and finance tools. The template auto-computed totals based on configurable conditions, while a Power BI dashboard provided period-specific (e.g., monthly) data extracts for stakeholders.

6. TaskUs (Uber Dashboards)
   Tools: Power BI, Excel, CSV, SQL
   I single-handedly created and managed client-facing, internal, executive, and MBR/QBR dashboards for Uber while overseeing my team. Using Excel and CSV extracts, I set up automated refreshes via a gateway in Power BI, ensuring real-time updates.

Beyond these, I’ve worked with additional platforms like Oracle, AWS, and Azure for various projects, adapting to diverse data sources and visualization needs. My experience aligns closely with the multi-source data integration and visualization tasks described.