Many hoteliers charge deposits. So they may need to generate a list of advanced payments charged at any point in time to verify their reporting. Keep in mind that advanced payments would cause the difference between Revenue and Payments in the Daily Revenue report (which can result in big Grand Total and Ledger numbers).
This article explains how to generate a list of advanced payments posted on a particular day or during a particular period of time using the Payment Ledger Report.
Our Payment Ledger Report would be the most helpful to generate the list of advanced payments at any point in time (for both future and past reservations):
1. If you need to check the amount of deposits held on a particular day, you need to generate a list of payments posted on that day for the guests arriving the following day or later.
2. If you need to check it for a period of time, you'll also need to do the same but then extract the report and remove the payment transactions which were not advanced in the spreadsheet.
Access the Payment Ledger report and apply the following filters:
- Payment Type: select all
- Select the desired Payment Date (e.g. the 1st of November)
- No Associated Transactions should be selected under the drop-down menu
- Select Check-in Date rage: it should be from the second day from the first payment date till any day far in the future (e.g. it should be from the 2nd of November until some date 2 years in the future)
- Type: select reservations only
- Click on Apply
- Export the report as an excel file (if needed).
You will end up with a list of payment transactions posted on the selected day (the 1st of November) for the reservations that are checking-in in the future. The report is ready and you can look up the final number.
If you are interested in a payment date range (e.g. the whole month of November), you need to generate a Payment Ledger report in a similar way, export the file and continue working on the spreadsheet. Access the Payment Ledger report and apply the following filters:
- Payment Type: select all
- Select the desired Payment Date range (e.g. the whole month of November)
- No Associated Transactions should be selected under the drop-down menu
- Select Check-in Date rage: it should be from the second day from the first payment date till any day far in the future (e.g. it should be from the 2nd of November till somewhere in 2 years)
- Type: select reservations only
- Click on Apply
- Export the report as an excel file.
Please note, you end up with the list of both advanced and not advanced payment transactions. Some of them were posted during the guest's stay. Unfortunately, you cannot remove them in the system, so in this case, you will need to export the generated Payment Ledger report and delete the payments which were not advanced. The instructions below describe the best way to do it in a Google Spreadsheet.
In the extracted report, you need to delete payment transactions posted on the day of the check-in or later. You can use the "IF formula" to mark those transactions. Then you need to delete them and to recalculate the total. The following instructions explain how to do it in detail.
Open the Excel file in the Google Spreadsheet:
- For your convenience, freeze the 1st row
How: click on View > Freeze > 1 row - Change the format for the following columns: B (Date/Time), I (check-in date) and J (check-out date). Let's switch from 'Plain Text' to 'Date'
How: select the 3 columns> click on Format > Number > Date - Add an additional column at the end of the sheet (column P)
- On the new column, go ahead and apply an 'if formula' in order to mark the non-advanced transactions
How: add the following formula: =ARRAYFORMULA(IF(I:I<=B:B,"N/A","ADVANCED")) to P1
Basically, the formula says the following: if the check-in date is less or equal to the payment date, it will be called "n/a", if not, it will be called "advanced".
ARRAYFORMULA = use this array formula in order to have the 'if formula' applied to the whole column
I:I<=B:B means: check-in date is less or equal to payment date/time
"N/A","ADVANCED" = excel will generate the results based on the 'if formula' applied and they will be displayed as 'N/A' (not applicable) and 'Advanced'. These are just the terms and you can use any other words.
Please note that Both "N/A and "Advanced" should be added into parentheses, otherwise excel will not accept it.
5. Apply a filter to the column that you've just created
How: select the column that you've just created > click on the 'create filter' icon
6. By using the filter that you've just created, go ahead and remove the 'Advanced' transactions from the filter in order to have a list of only 'non-advanced' transactions (N/A)
How: Click on the filter icon > remove 'Advanced' from the drop-down > Click OK to apply
7. Select all the N/A rows and delete them
8. Remove the filter
How: Click on the filter icon > turn off filter
Now, you have only 'Advanced' payment transactions on the list.
9. Recalculate the total
How: apply the sum formula on the last row.
Click on the current total > Click on the Functions icon on the top > select SUM > select the 1st and the last column:row) Example: =SUM(O2:O279).
See the example below, and please note that the row numbers depend on the number of transactions/rows in your sheet.
Example:
Now you have the total that you're looking for!
Comments
Please sign in to leave a comment.