Under the accruals basis of accounting revenue is recorded when earned and expenses are recorded when incurred. There are however situations where information is required on a cash receipts and payments basis. In these circumstances this accrual to cash conversion excel worksheet can be used to convert between the two systems.
Further details of the accrual to cash method change can be found in our accrual to cash conversion tutorial.
Using the Accrual to Cash Conversion Excel Worksheet
This accrual to cash conversion excel worksheet can be used to convert revenue, expenses, purchases, and cost of goods sold found in accrual based financial statements to cash receipts and payments information.
Instructions
The Excel accrual to cash conversion excel worksheet, available for download below, is split into four independent sections to deal with revenue, expenses, purchases, and cost of goods sold calculations, and is used as follows.
Section 1 – Revenue
This section of the calculator is used to convert revenue earned calculated on an accruals basis to cash receipts by adjusting for movements on accounts receivable, unearned revenue, and accounts receivables written off.
Step 1
Enter the beginning accounts receivable. This adds revenue relating to the previous period.
Step 2
Enter the ending accounts receivable balance. This deducts revenue which has not yet been received.
Step 3
Enter the ending unearned revenue balance. This adds revenue received in the current period but relating to a future period and therefore not yet earned.
Step 4
Enter the beginning unearned revenue balance. This deducts revenue received in the previous period but relating to the current period.
Step 5
Enter the accounts receivable written off. This adjusts for a reduction in the ending accounts receivable balance caused by non-cash write offs.
The accrual to cash conversion excel worksheet works out the cash receipts based on the revenue earned for the period.
Section 2 – Expenses
This section of the accrual to cash conversion excel worksheet is used to convert expenses incurred to cash payments by adjusting for movements on accrued expenses payable, and prepayments.
Step 1
Enter the beginning accrued expenses payable. This adds expenses relating to the previous period.
Step 2
Enter the ending accrued expenses payable balance. The deducts expenses which have not yet been paid.
Step 4
Enter the ending prepayments balance. This adds expenses which were paid in the current period but relate to a future period and have not yet been incurred.
Step 3
Enter the beginning prepayments balance. This deducts expenses which were paid in the previous period but relate to the current period.
The accrual to cash conversion excel worksheet works out the cash payments based on the expenses incurred for the period.
Section 3 – Purchases of Inventory
This section of the calculator is used to convert inventory purchases to cash payments by adjusting for movements on accounts payable.
Step 1
Enter the beginning accounts payable. This adds purchases relating to the previous period.
Step 2
Enter the ending accounts payable balance. This deducts purchases which are currently unpaid.
The accrual to cash conversion excel worksheet calculates the cash payments based on the inventory purchases for the period.
Section 4 – Cost of Goods Sold
This section of the calculator is used to convert cost of goods sold for the period to cash payments for inventory purchases by adjusting for movements on accounts payable and inventory.
Step 1
Enter the beginning accounts payable. This adds purchases relating to the previous period.
Step 2
Enter the ending accounts payable balance. This deducts purchases which have not been paid for at the end of the accounting period.
Step 3
Enter the ending inventory balance. This adds inventory not sold during the period.
Step 4
Enter the beginning inventory balance. The deducts inventory sold during the current accounting period.
The accrual to cash conversion excel worksheet works out the cash payments for inventory purchases based on the cost of goods sold for the period.
Accrual to Cash Conversion Excel Worksheet Download
The cash to accrual conversion worksheet is available for download in Excel format by following the link below.
Accrual to Cash Conversion Calculator v 1.0 Download Link
The accrual to cash conversion excel worksheet is one of many financial calculators used in bookkeeping and accounting, discover another at the links below.
- Payback Period Calculator
- Reorder Point Calculator
- Declining Balance Depreciation Schedule Calculator
Notes and major health warnings
Users use this accrual to cash conversion excel worksheet calculator at their own risk. We make no warranty or representation as to its accuracy and we are covered by the terms of our legal disclaimer, which you are deemed to have read. This is an example of an accrual to cash conversion template that you might use. It is a purely illustrative cash to accrual conversion tool. This is not intended to reflect general standards or targets for any particular company or sector. If you do spot a mistake in the cash to accrual conversion excel worksheet, please let us know and we will try to fix it.
Last modified July 16th, 2019 by Michael Brown
About the Author
Chartered accountant Michael Brown is the founder and CEO of Double Entry Bookkeeping. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a degree from Loughborough University.
You May Also Like
Posted By: Michael Brown Accrued, Deferred, Financial Calculators
July 16, 2019
Bookkeeping Basics