Cash management plays an important role for treasurers whose responsibilities include safeguarding the financial stability and liquidity of their organizations. Effective cash management not only ensures that an organization can meet its current obligations, but it also promotes strategic planning and investment for future growth. Given these important functions, treasurers often use a variety of tools to track and manage cash flows accurately.
One such tool is the cash position worksheet. This document provides a detailed record of balances and transactions across an organization's bank accounts on a daily basis. It’s designed to give the cash manager a comprehensive overview of the organization's liquidity at any point, enabling them to make informed financial decisions.
Whether you are new to treasury management or looking to refine your financial oversight skills, download our cash position worksheet to help you significantly contribute to your organization’s success and enhance transparency and accountability.
What is a Cash Position Worksheet?
Cash Position Worksheet Template
A cash position worksheet is an essential financial document that treasurers use to monitor and manage the flow of cash within an organization. It provides a systematic approach to recording all cash transactions during a specific period, typically daily, weekly, or monthly, and projects what the closing cash position will be at the end of each of these periods.
Typically, a cash manager will prepare a cash position in the morning and revise the position in the afternoon if necessary. It’s this daily management of liquidity that ensures today’s obligations are covered and if not, funding is acquired to do so.
The cash position worksheet is important for maintaining a clear and accurate overview of the organization’s liquidity, which in turn supports effective decision-making and financial planning.
A cash position worksheet helps to ensure that treasurers have real-time access to comprehensive data regarding the organization's cash flows. It acts as a financial snapshot, showing the current state of cash resources, which helps in assessing the organization's ability to cover short-term liabilities and fund ongoing operations.
By regularly updating and reviewing this worksheet, treasurers can prevent financial issues, strategize cash utilization, and optimize liquidity management.
Key Components of the Cash Position Worksheet:
- Bank Account Details / Prior Day Report
- Opening Available Balance
- Cash Inflows
- Cash Outflows
- Closing Balance
Benefits of Using a Cash Position Worksheet
A cash position worksheet is not just about recording numbers; it's about leveraging those numbers to enhance overall financial management and strategic planning.
Here are the key benefits of using a cash position worksheet:
Improved Visibility into Cash Flows
One of the primary advantages of using a cash position worksheet is the enhanced visibility it provides into an organization's cash flows. By documenting every cash inflow and outflow, cash managers can get a granular view of where funds are coming from and where they are being spent.
This visibility is important for identifying trends, recognizing fraud, pinpointing inefficiencies, and making informed decisions about cash management.
With a clear understanding of cash movements, cash managers can better manage operating cash, anticipate cash shortages, and take advantage of opportunities for investing excess cash.
Enhanced Ability to Forecast and Plan for Future Cash Needs
Accurate and detailed records of cash transactions are valuable for forecasting and planning. A cash position worksheet helps cash managers analyze historical data, recognize patterns, and predict future cash requirements with greater precision.
This foresight enables proactive management of cash resources, ensuring that the organization has sufficient liquidity to cover upcoming expenses or capital projects and can invest capital efficiently into the future.
Effective forecasting also supports strategic decision-making, such as timing major expenditures or negotiating terms with vendors to optimize cash flow.
Key Elements of a Cash Position Worksheet
The elements listed below offer a solid foundation for understanding and managing your organization’s finances. However, it's important to recognize that this list should be viewed as a starting point. Additional data might be required from other sources, which can be managed by different teams or housed in various systems.
Effective cash management often employs the 80/20 rule—focus should be given to detailing the 20% of items that account for 80% of cash flows, while summarizing the remaining 80% of items that represent the lesser 20% of cash flows.
This approach ensures that attention is concentrated on the most impactful areas, thereby enhancing the accuracy and utility of the cash position worksheet.
Bank Account Details / Prior Day Report
This is the starting point of the daily cash position as it’s important for cash managers to pull down reporting that shows all the activity that happened in the prior day. It ensures that the daily cash position starts from a known point and can then allow for assumptions to be layered into the position.
It also allows cash managers to keep track of each account's performance and ensure that all funds are accounted for properly across the organization's various banking relationships. It supports effective cash management by providing a clear, detailed starting point to conduct a daily cash position.
Opening Available Balance
The opening available balance is the amount of cash available at the beginning of the day for a specific bank account. This will be found in the prior day report for the bank accounts being used as part of the daily cash position.
To determine the starting cash balance, cash managers should review the most recent prior day statement to ensure accuracy and then pull out the opening available balance. This figure sets the foundation for tracking all cash movements during the period and is important for accurate cash flow analysis.
Cash Inflows
Cash inflows represent all the money that is credited to an organization’s bank account or accounts during the day that a cash manager is updating their daily cash position.
Common types of cash inflows include:
- Tax revenues
- Federal grants
- Tuition
- Transfers from investments or coupon payments
- Bond proceeds
Recording cash inflows involves detailing each source of cash and categorizing them appropriately. This categorization helps cash managers conduct variance and trend analysis, reporting, identify fraud or suspicious transactions, forecast and fine tune their long-term cash flow. It’s important to record inflows as they occur to maintain real-time accuracy of the cash position.
Cash Outflows
Cash outflows are all the payments made by the organization during the period, including:
- Capital projects
- Payroll and benefits
- Debt service payments
- Bond proceeds spend
- Vendor payments / accounts payable
- Transfers to investments
Managing cash outflows effectively involves strategies such as forecasting future outflows, optimizing payments matrix, and scheduling payments to optimize cash flow.
Other strategies include ensuring funding arrives in time to properly fund expenditures so that organization’s do not liquidate investments early and identifying suspicious or fraudulent transactions. Through categorizing and conducting a daily cash position, organizations can ensure payments are made on time and in an efficient manner.
Closing Balance
The closing balance is calculated by starting with an organization’s aggregated opening available balance across all bank accounts included, adding posted and forecasted cash inflows and subtracting the posted and forecasted cash outflows. This figure represents the amount of liquidity projected as the bank accounts closing cash position for the day.
It’s important for the closing balance to be accurate prior to moving to the next day of the cash position. While a full bank account reconciliation will occur as part of month-end activities by accountants, cash managers should conduct a light version of this reconciliation to ensure that all expected transactions posted to the bank accounts are used as part of the daily positioning work. This traditionally occurs as part of reviewing the prior-day report from the bank to identify any discrepancies due to errors or fraud, to ensure the next daily cash position begins with an accurate starting position.
How to Calculate Cash Position
The following steps will show you how to complete each section of the cash position worksheet, along with examples of common transactions and how they should be recorded.
Step 1: Gather and Groom Necessary Data
Retrieving bank account information is a manual process involving logging into one or more bank portals daily to export balance and transaction data. In order to start doing a daily cash position, cash managers should start by pulling down the prior day report (BAI file) from an organization’s banking partners to confirm yesterday’s transactions.
After obtaining the bank data, a cash manager likely needs to confirm with Debt Managers, Investment Managers, and the Payroll and Accounts Payable Manager to compile a complete picture of the expected cash inflows or outflows for the day, week, or month. All of this information will then need to be cleaned up to put into Excel.
Example: Gather all opening available balances, debt service payments, payroll payments, investment maturities, significant AP or vendor payments, and begin categorizing into their different groups within the cash position worksheet.
Step 2: Record the Opening Available Balance
Start by entering the opening available balance at the top of the worksheet for any and all accounts being used to inform the daily cash position. This is the amount of cash available at the beginning of the given day and can either be taken from the closing balance noted in the prior day report or the opening available balance of the current day report.
Example: If the closing balance from the prior day was $1,500,000, this becomes your opening balance for today’s cash position worksheet.
Step 3: Record Cash Inflows
Next, list all cash inflows during the day under the cash inflows section. Each entry should include the date, a brief description of the source of the cash (which could come from the BAI file or an organization’s ERP), and the amount received or expected. Using the prior or current day report from the bank, include as much remittance detail as possible to add context to the purpose of each payment (originator, invoice number, transaction reference number, etc).
- Tax revenues: Categorize revenues similar to how these will be reconciled within an ERP so cash can be applied as soon as possible.
- Investment returns: Include dividends or interest received during the period.
- Debt service payments: Note series, fund, purpose, or project associated with any of these transactions.
Example:
- March 4: Invoice #123 received - $2,000.
- March 4: Investment redemption from Local Government Investment Pool - $1,000,000.
- March 4: Bond proceeds drawdown for Library Project - $275,000.
Step 4: Record Cash Outflows
In the cash outflows section, document all payments made during the day. Include the date, a description of the payment, and the amount paid or expected to be paid. Organize the payments into categories like expenses, debt repayments, and purchases. Using the prior or current day report from the bank, use as much remittance detail as possible to add context to the purpose of each payment (beneficiary, invoice number, transaction reference number, etc).
- Expenses: Record operational costs such as payroll, vendor payments, utility bills, and rent.
- Debt service payments: Note series, fund, purpose, or project associated with any of these transactions.
- Capital project purchases: Document payments for inventory or capital assets.
Example:
- March 4: ADP wire debit for payroll - $50,000.
- March 4: 2019A Debt service payment - $118,471.
- March 4: 2018 Bond proceeds spend down on Library Project - $275,000.
Step 5: Calculate the Closing Balance
To find the closing balance, take the opening balance, add the total cash inflows, and subtract the total cash outflows for posted and forecasted transactions. This final number represents the projected closing cash position at the end of the day, week, or month in scope.
Formula:
Projected Closing Cash Balance=Opening Available Balance+Total Inflows−Total Outflows.
Example for March 4th: Opening Available Balance - $1,000,000.
Total Inflows
- March 4: Invoice #123 received paid - $2,000.
- March 4: Investment redemption from Local Government Investment Pool - $1,000,000.
- March 4: Bond proceeds drawdown for Library Project - $275,000.
Total Outflows
- March 4: ADP wire debit for payroll - $50,000.
- March 4: 2019A Debt service payment - $118,471.
- March 4: 2018 Bond proceeds spend down on Library Project - $275,000.
$1,000,000 + ($2,000+$1,000,000+$275,000) - ($50,000+$118,471+$275,000) = $1,833,529
Step 6: Review the Closing Balance Before Moving to Next Day
Finally, after the day has closed and the prior day report for the cash position that was just completed is available, review the report to check the closing cash position to ensure accuracy. Check for any discrepancies, suspicious or fraudulent transactions, or anything else that looks out of the ordinary. For items in questions, begin research and start the process over to calculate the next cash position.
Implementing Technology in Cash Management
DebtBook, the leading software in GASB 87, GASB 96, and debt management, now offers a Cash Management application that’s purpose-built for government and nonprofit treasury teams. By providing comprehensive tools to power the efficient management of cash flow and liquidity, DebtBook’s Cash Management application can help you reach informed cash decisions faster.
Our solution additionally includes our Bank Fee Analysis feature, which allows treasury teams to better understand the fees they are being charged, identify any discrepancies, and ensure balances are being held efficiently to offset fees without excess balances earning zero interest.
In fact, during preliminary testing, DebtBook’s Bank Fee Analysis feature helped users identify millions of dollars of idle cash that, when invested, could be earning $200K to $2M on average in interest revenue on an annual basis.
(The on average $200K-$2M per annum calculation is based upon average excess balances identified in operating accounts that could be invested through a daily liquid money market fund returning 5% APY.)
Downloadable Cash Position Worksheet Template: Your Tool for Effective Cash Management
To help you get started on your path to improved financial oversight, we’re offering a free downloadable cash position worksheet. This template is designed to be intuitive and user-friendly, allowing you to begin implementing the concepts discussed in this guide immediately. Whether you’re tracking daily operations or preparing for strategic financial reviews, this template serves as an important starting point.
The template includes predefined sections for the opening available balance, cash inflows, cash outflows, and the closing balance, ensuring that you can easily record and analyze your organization's cash movements. It also allows for users to add categories, specific to their organization, by which transactions can be grouped and tracked for historical and trend analysis. By utilizing this template, you'll be able to maintain a consistent and accurate record of your financial activities, providing a solid foundation for all your cash management needs.
Disclaimer: DebtBook does not provide professional services or advice. DebtBook has prepared these materials for general informational and educational purposes, which means we have not tailored the information to your specific circumstances. Please consult your professional advisors before taking action based on any information in these materials. Any use of this information is solely at your own risk.
