Cash statement import advanced

The article Import cash statement discusses the basics of a cash import. This is quite useful for administrations where revenue can be recorded entirely from petty cash. However, sometimes there is a more complex interplay between recording revenue and payments, where large parts are sold via debit or credit card and other parts are still sold on account. Especially when large numbers are involved, setting up a highly automatable exchange between Yuki and the system recording the revenue and payments is more complex and requires special attention.

Example

Suppose you want to lonk a hotel system to Yuki. All sales are registered in the hotel system, whereby the services provided are sometimes paid for in cash or via debit card (or credit card) and sometimes are drawn up on an invoice that can be paid afterwards (via bank). In Yuki you particularly want to be able to follow up on invoices on account so that you can check who has not yet paid. The remaining revenue does not have to be recorded at customer level but can be aggregated into one entry per day

In such a setup, we run into the following questions in practice:

• How do we collect revenue per day?
• How can we follow up on invoices still to be paid in Yuki?
• How do we handle debit or credit card payments and the matching with the aggregated receipt at the bank?
• How do we deal with invoices that are partly paid in cash and partly still to be paid via bank?

Record revenue per day

Instead of booking the revenue from the cash on a transaction basis, it is often better to book the revenue using invoices and book the payment separately. Especially with large numbers of transactions per month, it is better from a cost and overview point of view, to aggregate per day. We recommend submitting sales to Yuki using the Sales web service. With the 'Sales web service', we can better determine how the revenue is broken down into sales groups, how the VAT is split and how we then settle the payments.

When aggregating several transactions into one sales invoice, it is advisable to do so while taking into account how transactions were paid: split fully paid transactions from transactions that still have to be paid in full or in part at a later date (via bank or later by debit card, credit card or cash).

After all, you want to be able to track the latter group by name, while the former group may remain anonymous in the administration. 

We therefore recommend:

• For each day, create a sales invoice for all transactions that were immediately paid in full (cash, debit or credit card).
  • If necessary, break the invoice into multiple lines to split these sales in the general ledger to different revenue accounts. To do this, use items to specify what was delivered and how it should be accounted for.
  • Always make sure that a line has only one VAT type (high, low or 0) so that the VAT bases match the VAT amounts
  • Always book the invoice to a 'dummy' customer e.g. 'Counter customer'
  • Give the invoice a clear and unique invoice number so we can later match payments (from the petty cash) to that invoice. Example of an invoice number/characteristic: '2016-10001'.
  • Use payment method 'Electronic transfer' for that invoice
• Create a sales invoice by name for every transaction that is still to be paid in full or in part. So even for a transaction that was partially paid, but the rest remains outstanding on account an invoice should be created by name for the entire transaction.

Book cash

To complete the revenue recording, the cash needs to be booked.  The best way to do this is to import the cash statement into Yuki. When creating the cash transaction lines, proceed as follows:

Per invoice

For each invoice, create a transaction how much has already been paid on that invoice. So for the fully paid summary invoice, that is the entire invoice amount. For a partially paid invoice, that is the amount paid. For an invoice that has not yet been paid in its entirety, you do not need to create a transaction. 

Example:

You recorded 2,500 euros worth of fully paid invoices one day.  Also, you had a customer who bought for 100 euros but paid 50 euros in cash. The rest he is going to transfer through the bank. You also had another customer who bought for 60 euros but that was entirely on account.

The first line of the spreadsheet must have the same column titles as prescribed in the table below:

For that day, first create the following transactions:

Subsequent payments

If on one day a customer also comes by to pay an invoice from a previous day, then there is no new invoice, but there is a payment transaction by name:

Then create transactions per day per payment method for the total of all “non-cash” payments, such as debit cards, credit cards, gift cards, etc. Give each payment method a standard transaction code. Since these are like corrections to the cash balance, the amounts for these transactions are negative. 

Example:

Of the previously mentioned invoices on one day, 1250 euros was paid in cash, 800 euros via credit card, 400 euros via debit card and 100 euros with a gift voucher. You then create the following transactions:

Payments from cash

If money is also withdrawn from cash to pay receipts or invoices, create transactions by name for that too:

Other transactions

You also need to create transactions that affected the day's cash balance. Consider:

• Money deposited in cash
• Money deposited to the bank
• Petty cash differences detected.

For each transaction type, a bank processing rule for cash transactions must be created in the administration to set where each line should be posted.