How to calculate monthly sales between two dates!

[rojashan]

Hi,

I need help,

I attached a spreadsheet of my date. I have guests that has a check-in and check-out date. I would need to create a report that would show the revenue by month.

Also if the guests accommodationhas been cancelled or there has been amendment. I want it to show in the report what has been changed since the last time I run the report.


ACCOM.xlsxCAPTURE 1.PNG
Hoping someone can help me,

[6StringJazzer]

When you do book revenue? On each date of a stay, upon check-in, or upon check-out? That is, if someone checks in on Jan 28 and checks out on Feb 5, is the revenue split, goes to Jan, or goes to Feb?

Your sample data shows only Confirmed stays. What do cancellations and amendments look like in your data?

[rojashan]

Hi,

I'm sorry I should have made it clear.

I work for a hotel. We have several guests, column D, who have various check-in (column E) and check-out dates (column D).

Column G calculates the # of nights the guest stays in total. Column H is the average rate per night that I offered to the guest.
Column I is the Total Revenue for the entire stay.
Column J is the status of the reservation: "Confirmed" when guests wants to book; "Amendment" when guest changes the date (for ex. the guest is already checked-in and wants to change his check-out date 5 days early when this happens I changed the check-out date to his new date and change column date to Amendment); "Cancelled" when guests cancels the reservation prior to check-in date.
Column K, states whether the guest is already in-house, arriving or checked-out based on the check-in and checked out dates.

I'm thinking of adding another column that will show the date that I actually created the reservation that will maybe help me create different reports below:

My manager every week asks me to generate a report:

1st Table: will show the in-house guests.

2nd Table: will show the guest's that are arriving

3rd Table: will show reservations that have been changed or cancelled

4th Table: will show the Revenue by Month (for all in-house and arriving guest) January, February, March, etc.. so the sum of all revenue for the month of January, February, March etc. For example, Guest Q stayed 19 nights for January and 27 nights for the month of February. Guest R stayed 17 nights for the month of January, 28 nights for the month of February and 25 nights for the month of March. Both guests are booked at the rate of $182.00.

Total Nights

Average Rate

Total Revenue

January

36

$182

$6,552.00

February

55

$

182

$10,010

March

25

$182

$4,550.00

[6StringJazzer]

Good explanation, thanks. So you book revenue on the night of each stay rather than on check-in or check-out.

Revenue by Month (for all in-house and arriving guest)

I think you want revenue for all IN-HOUSE, ARRIVING, and CHECKED-OUT guests.

Any records where the confirmation status (column J--you should put a column heading there) is Cancelled are to be ignored.

[rojashan]

Hi, yes you are correct on all counts.

Revenue is on the night of each stay.

It should be including checked-out guests.

Yes, cancelled reservations do not count in calculating revenue.

[rojashan]

I'm hoping you can help me with this one.

[6StringJazzer]

I was not able to solve this strictly by using formulas, although I think it may be possible using some variety of array formulas.

I think a VBA solution may be more appropriate, which would take some significant time to produce, and I'm afraid I don't have that kind of time right now.

Frankly, this request is not just a question about some Excel feature you are trying to use--you are asking someone to build a set of reports from scratch for free for a commercial business. You may want to recommend to your management that this is not as simple as they think and they may have to actually pay someone to do this. Another option is to use the Commercial Services forum here, where you can offer payment for someone to build your reports.