Excel & Power BI Booking Dashboard

I need our current OneDrive-based reservation form transformed into a clear, structured workflow built in Excel with an accompanying Power BI dashboard. The goal is to let any department instantly locate and update a guest’s reservation, while management enjoys real-time visibility of all in-house activities.

Key data points to capture and surface are:
• Guest name and room number
• Reservation time-slots with current status
• Departmental communication notes
• A consolidated view of every activity tied to a room number

Here’s how I picture the finished solution:

1. An Excel file (or connected workbook set) that houses the master data. Advanced formulas, data validation, and light VBA or Power Query automation should keep entries clean, prevent double-booking, and generate unique IDs for each activity.

2. Seamless search and filter capability inside Excel—think intuitive slicers or a simple form—so staff can pull up a guest’s details in seconds.

3. A Power BI dashboard wired directly to that workbook. It should visualize occupancy of time-slots, highlight pending vs. confirmed bookings, and allow drilling down from room number to every activity scheduled for that guest.

4. Simple refresh mechanics: daily automated refresh is fine, but a one-click manual option is essential in case a department needs to push an urgent change live.

Acceptance criteria
• Entering or editing a booking in Excel updates all calculations without error.
• Refreshing Power BI reflects the latest data within one round of refresh.
• Search by either guest name or room number returns the correct record in under three seconds on a typical office PC.
• Clear, step-by-step handover instructions so our team can maintain the file and add new outlets in future.

If you’ve built reservation or scheduling tools with Excel formulas, VBA, Power Query, and Power BI before, I’d love to see a quick outline of your approach and any relevant examples.