Lodge Room Booking & Status Excel

(Please submit the proposal only if you have existing Ratings & Reviews) We have lodge excel sheet where when the guest check-in & check-out of the rooms will b entered. Based on that the room status will be automatically opened and closed. Based on daily payments from guest of same room and rent/day the amount needs to be calculated and pending amount should be displayed. If 24 hrs or 11 am check outs is selected, then it should display time is about to over or time over need to collect xx money from guest. Day summary should be automatically filled on Room occupancy details. If there is a check-in entered two times without checkout for a room then that has to captured as error in notes. Similar Error checking has to be implemented. We have the excel with partial working of it and needs to be completed fully. Need to provide solution for the fresh day after day close the previous required data(from previous day excel) should be automatically captured(room occupancy, guest & payment details) on the new excel for the new day . Keep it simple and should not affect the size or performance of a file. Need continuity. Need Proper explanation of what and how is implemented .