Excel Hotel Brand Analysis - Automation & Enhancement
Budget: $400 – $500 USD
Excel Hotel Brand Share Analysis - Automation & Enhancement Project
Project Overview
We need an experienced Excel developer to enhance and automate our EXISTING hotel brand analysis workbook. The workbook foundation is already built with distance calculations and core logic in place. Currently, the system relies on manual copy-paste processes. We need to transform it into a dynamic, formula-driven tool that efficiently handles our dataset of 65,000+ hotel records (16 fields).
What the Tool Does:
• Analyzes hotel competitive positioning within a 10-mile radius
• The model utilizes the existing distance-calculation code and methodology already built into the workbook
• Tracks top hotel parent brands and their market share
• Generates printable brand analysis reports with visual components
Key Objectives
1. Replace ALL manual copy-paste with automated formulas (VLOOKUP, INDEX/MATCH, or XLOOKUP)
2. Make brand selections dynamic (currently fixed to 8 brands, need flexibility for 18 brands)
3. Create user-driven dropdown menus and dynamic reports
4. Add historical trend analysis with auto-updating year ranges
Core Requirements
Input Automation:
• User enters 5-digit STR# code → all hotel details auto-populate
• Implement error handling for invalid entries
Distance Filtering:
• Auto-filter hotels within 10-mile radius (no manual filtering)
• Formula or Power Query solution, the model utilizes the existing distance-calculation code and methodology already built into the workbook
Dynamic Brand Selection:
• User selects which brands to track from master list
• Reports automatically update based on selections
• Support 9-brand and 6-brand report variations
Historical Data Display:
• Show 15-year rolling history (current year + 14 prior)
• Year ranges update automatically from current date year
• Both room count by brand AND property-level views
Reports That Need Enhancement:
• Market Matrix (competitive analysis by brand)
• Brand Opportunities (6-brand comparison)
• Supply trends (15-year room count history)
NEW Tab - Charting Functionality:
• Create new tab with two dynamic charts that update based on user's selected radius distance
• Chart 1: Brand Room Count Chart (total rooms by brand)
• Chart 2: Room Count by Class Chart (room counts segmented by hotel class)
• Both charts must be formatted to print together on one page
• Charts must dynamically respond to radius selection (2.0 miles or 5.0 miles)
• Professional formatting suitable for client presentations
• Property-level supply data (5-year rolling view)
Technical Skills Needed
• Advanced Excel formulas (VLOOKUP, INDEX/MATCH, XLOOKUP, dynamic arrays)
• Data validation and dropdown menus
• Conditional formatting and chart formation
• Power Query (optional but beneficial)
• Understanding of dynamic named ranges
Deliverables
• Fully automated Excel workbook that is easy to use with no manual copy-paste
• Documentation of formulas and update processes
• User-friendly interface with clear instructions
Project Budget & Timeline
Please provide:
• Your estimated timeline
• Fixed price quote
• Examples of similar Excel automation projects
• Brief description of your approach to this automation
Expected Budget Range: $400-$500 USD
This is an enhancement/automation project - the workbook foundation with distance calculations already exists. We're looking for formula optimization and automation, not complex development from scratch.
Note: Detailed technical specifications available upon request for shortlisted candidates.
All bidders must demonstrate understanding of the requirements before starting work.
We look forward to working with a skilled Excel developer who can enhance our hotel brand analysis capabilities and become a long-term partner for future analytics projects. We have 3-4 other Excel models that need revisions.
Project Overview
We need an experienced Excel developer to enhance and automate our EXISTING hotel brand analysis workbook. The workbook foundation is already built with distance calculations and core logic in place. Currently, the system relies on manual copy-paste processes. We need to transform it into a dynamic, formula-driven tool that efficiently handles our dataset of 65,000+ hotel records (16 fields).
What the Tool Does:
• Analyzes hotel competitive positioning within a 10-mile radius
• The model utilizes the existing distance-calculation code and methodology already built into the workbook
• Tracks top hotel parent brands and their market share
• Generates printable brand analysis reports with visual components
Key Objectives
1. Replace ALL manual copy-paste with automated formulas (VLOOKUP, INDEX/MATCH, or XLOOKUP)
2. Make brand selections dynamic (currently fixed to 8 brands, need flexibility for 18 brands)
3. Create user-driven dropdown menus and dynamic reports
4. Add historical trend analysis with auto-updating year ranges
Core Requirements
Input Automation:
• User enters 5-digit STR# code → all hotel details auto-populate
• Implement error handling for invalid entries
Distance Filtering:
• Auto-filter hotels within 10-mile radius (no manual filtering)
• Formula or Power Query solution, the model utilizes the existing distance-calculation code and methodology already built into the workbook
Dynamic Brand Selection:
• User selects which brands to track from master list
• Reports automatically update based on selections
• Support 9-brand and 6-brand report variations
Historical Data Display:
• Show 15-year rolling history (current year + 14 prior)
• Year ranges update automatically from current date year
• Both room count by brand AND property-level views
Reports That Need Enhancement:
• Market Matrix (competitive analysis by brand)
• Brand Opportunities (6-brand comparison)
• Supply trends (15-year room count history)
NEW Tab - Charting Functionality:
• Create new tab with two dynamic charts that update based on user's selected radius distance
• Chart 1: Brand Room Count Chart (total rooms by brand)
• Chart 2: Room Count by Class Chart (room counts segmented by hotel class)
• Both charts must be formatted to print together on one page
• Charts must dynamically respond to radius selection (2.0 miles or 5.0 miles)
• Professional formatting suitable for client presentations
• Property-level supply data (5-year rolling view)
Technical Skills Needed
• Advanced Excel formulas (VLOOKUP, INDEX/MATCH, XLOOKUP, dynamic arrays)
• Data validation and dropdown menus
• Conditional formatting and chart formation
• Power Query (optional but beneficial)
• Understanding of dynamic named ranges
Deliverables
• Fully automated Excel workbook that is easy to use with no manual copy-paste
• Documentation of formulas and update processes
• User-friendly interface with clear instructions
Project Budget & Timeline
Please provide:
• Your estimated timeline
• Fixed price quote
• Examples of similar Excel automation projects
• Brief description of your approach to this automation
Expected Budget Range: $400-$500 USD
This is an enhancement/automation project - the workbook foundation with distance calculations already exists. We're looking for formula optimization and automation, not complex development from scratch.
Note: Detailed technical specifications available upon request for shortlisted candidates.
All bidders must demonstrate understanding of the requirements before starting work.
We look forward to working with a skilled Excel developer who can enhance our hotel brand analysis capabilities and become a long-term partner for future analytics projects. We have 3-4 other Excel models that need revisions.