Making a Hotel Booking and Availability workflow/sheet



I work in the event operating business and I'm trying to look for a way to implement one of my workflows into Smartsheet.

For events, I manage the hotel stays for our crew during the buildup-event-breakdown periods. We work with 11 different hotels where we have a different allotment. So on any given date, we have a number of rooms available to us. What would be ideal for this workflow, is to have a sheet(s) where we can input the crew/guests with check-in and check-out dates, and with that dates, let the sheet(s) automatically calculate if there is still availability for every night the guest is staying, in the particular hotel that we selected.

In Excel, we have already a similar but more analog and time-consuming solution where we have columns with every date, rows with every guest and we put a "1" in each cell for the nights the guest is staying. At the end of the Table, we have the booked reservation (allotment) for each day and we just sum-up every "1" for that date to determine the availability. Unfortunately, this solutions requires us to have a different sheet for each hotel. Ultimately, we would like to have a dashboard that can show us the number of rooms allocated to a guest, the number of rooms per hotel still available and for budget purposes, an overview for each department in our company (production, operations, marketing,..) that shows us the number of rooms that department is responsible for (this can be an extra column or data in the sheet).

This is asking a lot from Smartsheet, but I wanted to see if maybe someone else has already made a similar solution for their business. Basically I'm asking of there is an Hotel-management system that we can build using Smartsheet.

Thank you!