[COMING SOON] Online Payment Transaction Fee Reconciliation

Brief Overview

Reconciling Online Payments requires extra calculations to extract the true value that has been deposited into your bank account.  Clinic to Cloud will show the full value of the invoice however the amount that comes from Stripe has had the Transaction Fee deducted.  This article will walk you through the steps to find these values.  

 

The Short Version

  • Run the Daily Banking Report to obtain the full Online Payment value.
  • Save & Close the Daily Banking Report. 
  • Reopen the Daily Banking Report to use the Download Excel function.  
  • Open the Excel spreadsheet, identify the online payments and deduct the Transaction Fee (1.8% +0.30c per invoice) from the Amount Received.  This is the Stripe Payout.
  • Calculate the total Stripe Payout and total Amount Received.   

Note:  This article is designed for people who have limited experience in using Excel.  By following the steps, you will be able to see the value that Clinic to Cloud marked as received (Amount Received) and the amount that was transferred to your bank account (Stripe Payout).   For this example, the report will look like this at the end (the yellow section is what we will be working on): 

8._completed_report.png

 

Instructions

When you receive your first payment from Stripe, you will receive an SMS that appears like this:

payment_SMS.png

This message will be sent directly from Stripe, with an American phone number. This will only occur for your first payment. 

 

1.  Identify the full Online Payments value by running the Daily Banking Report.   

Note:  For a detailed article on how to do the Daily Banking Report with Online Payments, see: Banking Online Payments from the Patient Portal.  

a.  Go to Reports > Daily Banking Report > set your parameters > click Show.

1._daily_banking_parameters.png

b.  Tick the invoices you want to reconcile, or click Reconcile All > click Summary.

1b._tick_invoices__summary.png

c.  When the Daily Banking Summary appears, click Save & Close.

1c._save_and_close.png

2.  Export the Daily Banking Report to Excel.  

a.  Re-open the Daily Banking Report by clicking on the Eye icon.  

b.  Click Download Excel button.  

c.  Open the Excel file.  

Gif_-_OP_Reconciliation._Step_2.gif

 

3.  Prepare your Excel spreadsheet. 

Note: Your report size may vary from the examples depending on the Daily Banking parameters that were set.

This step shows you how to tidy up the report and add extra columns that we need later.  When you have completed Step 3, it should look like this: 

3._completed_excel.png

 

a.  Remove any non-EFTPOS invoices from the spreadsheet. 

Tip: If you make a mistake, press Ctrl + Z to undo.  

  • Click the row number to highlight it > right click on your mouse > select Delete.  

Gif_-_OP_Reconciliation._Step_3a.gif

 

b.  Delete the total value at the bottom of the columns 'Amount Billed' and 'Amount Received'.  

  • Select the cell that has the total > press Delete on your keyboard.  Repeat for both values.

Gif_-_OP_Reconciliation._Step_3b.gif

 

c.  Add a column called 'Patient Portal' next to the 'Amount Received' column.  

  • Select the cell next to the Amount Received heading > type 'Patient Portal' > press Enter.

Gif_-_OP_Reconciliation._Step_3c.gif

 

d.  Add a column called 'Transaction Fee' next to the 'Patient Portal' column.

  • Select the cell next to the Patient Portal heading > type 'Transaction Fee'.

Gif_-_OP_Reconciliation._Step_3d.gif

 

e.  Add a column called 'Stripe Payout' next to the 'Transaction Fee' column.

  • Select the cell next to the Transaction Fee heading > type 'Stripe Payout'.

Gif_-_OP_Reconciliation._Step_3e.gif

 

4.  Identify which invoices were made through the Patient Portal. 

This step shows you how to indicate which invoices were done through the Patient Portal.  When you have completed Step 4, it should look like this: 

4._completed_excel.png

 

a.  Return to the saved Daily Banking Report you created.  

  • Go to Reports > Daily Banking > click the Eye icon of your report.  

4a._click_eye.png

b.  Review each EFTPOS invoice to see if it was made through the Patient Portal and note in Excel.

  • Click the Invoice Number hyperlink to open in a new tab > review the Transaction History.
  • If the User = 'via Patient Portal', then type Y (for Yes) in the Excel spreadsheet under the 'Patient Portal' column
  • Repeat for all invoices in your Daily Banking Report where the Payment Type is EFTPOS.

Gif_-_OP_Reconciliation._Step_4b.gif

 

5.  Remove the invoices from your Excel spreadsheet that are not Patient Portal payments.

  • Click the row number to highlight it > right click on your mouse > select Delete.  

Gif_-_OP_Reconciliation._Step_5.gif

 

6.  Add formulas to the Excel spreadsheet to calculate the Stripe Payout value.

This step shows you how to type the formulas to work out what the Transaction Fee and Stripe Payouts are.  When you have completed Step 6, it should look like this: 

6._completed_excel.png

a.  Calculate the Transaction Fee.  The Transaction Fee from Stripe is 1.8%+0.30c per transaction.

  • Select the first cell under the column heading Transaction Fee.
  • Enter the formula =(0.018*[Amount Received])+0.3 > press Enter.  

Gif_-_OP_Reconciliation._Step_6a.gif

 

b.  Fill the formula down the column to quick calculate all the Transaction Fees.  

  • Select the first cell in the Transaction Fee column.  
  • Place the cursor in the lower right corner of the cell. 
  • Hold left click and drag the mouse down to the bottom of the invoice list.  
  • Let go of the left click button and the formula will fill down the list. 

Gif_-_OP_Reconciliation._Step_6b.gif

 

c.  (Optional) Change the cell format from General to Currency.  You can do this if it is preferable to see the figures in dollar format.  

  • Select the first cell in the Transaction Fee column. 
  • Hold left click and draft the mouse down to the bottom of the list to highlight the numbers.  
  • Use the ribbon bar to change the format to Currency.

Gif_-_OP_Reconciliation._Step_6c.gif

 

d.  Calculate the Stripe Payout.  The Stripe Payout is the value of the Amount Received minus the Transaction Fee.  

  • Select the first cell under the column heading Stripe Payout.
  • Enter the formula =[Amount Received]-[Transaction Fee] > press Enter.  

Gif_-_OP_Reconciliation._Step_6d.gif

 

e.  Fill the formula down the column to quick calculate all the Stripe Payouts.  

  • Select the first cell in the Stripe Payout column.  
  • Place the cursor in the lower right corner of the cell. 
  • Hold left click and drag the mouse down to the bottom of the invoice list.  
  • Let go of the left click button and the formula will fill down the list. 

Gif_-_OP_Reconciliation._Step_6e.gif

 

7.  Calculate the Total Stripe Payout for this Daily Banking report.  

Note:  This will be the amount of money that is deposited into your bank account from Stripe for this Daily Banking report.

  • Select the first cell in the Stripe Payout column. 
  • Hold left click and drag down to highlight all of the figures in the column. 
  • Click the Sum button.  
  • The total will appear at the bottom of the Stripe Payout column.  

Gif_-_OP_Reconciliation._Step_7.gif

 

8.  Calculate the Total Amount Received for this Daily Banking report.  

Note:  This will be the full value of the invoices that you have receipted from the Patient Portal. 

  • Select the first cell in the Amount Received column. 
  • Hold left click and drag down to highlight all of the figures in the column. 
  • Click the Sum button.  
  • The total will appear at the bottom of the Amount Received column.  

Gif_-_OP_Reconciliation._Step_8.gif

 

8._completed_report.png

 

Further Reading

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request