Once complete, the cash flow needs to be simple enough for the business owner or CFO to input the data and drivers each week within one hour. And then be able to test different scenarios. While there are apps and software programs for cash flow modeling, Microsoft Excel remains the best tool.
The information and data you need:
- Monthly Income Statements. This will provide you a benchmark for historical revenue and expenses. We recommend for the previous 12 months.
- Banking receipts and disbursements. Most banks provide bank statements and transactions download to excel.
- Cash inflows (receipts) – this is not revenue – cash inflows are collected Accounts Receivable, cash sales for goods or services provided, but also includes interest income, sale of assets, loan proceeds and other cash inflows.
- Cash outflow projections for the period, including payroll and taxes and benefits, rent, utilities, total of all other operating expenses, vendor payments and other cash outflows including debt payments. Which vendors will be paid timely, i.e. which are critical to continuing operations?
The inputs may include:
- Cash Inflow changes to model:
- Now that we’re a few weeks into the COVID-19 business environment, review the past 2 to 3 weeks cash deposits and sources to estimate the future deposits by week. One example is what percent of AR is collected each week;
- Are there any proceeds from the sale of equipment or other assets planned?;
- New loan proceeds, PPP and other stimulus receipts;
- Equity infusions and loan advances.
- Cash Outflow changes to model:
- Changing payroll obligations including commissions, bonuses, benefits and payroll taxes;
- Rent, utilities, insurance, and group other operating expenses;
- Legal, financial and professional services (these are usually special expenses);
- Vendor payments for inventory purchases;
- Capital Expenditures that are unable to be delayed (CAPEX);
- Loan payments which may be interest only during this time;
- Any other payments not included above.
Include multiple planning scenarios:
- The cash flow model should allow for multiple “what-if” scenarios, so management can dynamically see the cash flow impacts of any modeled changes and scenarios.
This may seem daunting or complicated, but not if you have complete and quality data.
Our experience tells us that once you begin utilizing this type of cash flow analysis, you are likely to maintain after the situation returns to normal. This cash flow worksheet provides key performance indicators (KPI) that are very useful to manage your business.
If you need help building a cash flow model, please call or email us.