Call Us 1.703.780.2044 or
16
Jul
2008
Doing Projections in SurvivalWare – Part 4: Working Capital
Fluctuations in Working Capital can be the source of a major disconnect between profits and cash flow. This article covers how to analyze what has happened in the past, and how to use that knowledge to forecast Working Capital in the future.
This is part 4 of a 5 part series on how to create a cash flow projection using SurvivalWare and the generic financial model that comes with it.
- Getting familiar with the Forecast Tool
- Sales and Expenses
- Debt Service and Investment
- Working Capital
- Other Cash Flow
Working Capital
In this section of the model, you’ll find out just how sensitive your cash is to your ability to collect receivables and control inventory turnover.
You can forecast Accounts Receivable (A/R) based on Days Sales Outstanding, or override the forecast in any month with a dollar amount.
The same is true for Inventory and Accounts Payable.
The first step is to see how you have performed in the past.
History
A/R (Accounts Receivable) Days
You might see the term DSO (Days Sales Outstanding), or even Collection Period used interchangeably with A/R Days.
DSO is easy to calculate based on the A/R balance and recent sales history. A true collection period would be very difficult because it would require an analysis of all invoiced sales and how long it took for the customers to pay. What makes that calculation even more difficult is having to decide how to treat open invoices. Does excluding them bias the calculation? If you include them, what value do you assign for how long it took to collect them? Also, do you weight them by invoice amount?
DSO is an approximation of collection period, and works well in a projection model. Here is how the monthly DSO is calculated in SurvivalWare:
1. For each month, take the ending A/R Balance
2. Divide by average daily Sales
You compute the average daily sales based on the last 3 months of sales to smooth things out. To do this, take the last 3 months of sales, multiply by 4 to get an annualized number, then divide by 365 to get a daily number.
Example – numbers needed to calculate A/R Days for June:
|
|
|
April |
May |
June |
|
(1) A/R Balance |
Balance Sheet |
|
|
$ 45,660 |
|
(2) Sales each Month |
Income Statement |
36,602 |
29,183 |
$ 45,603 |
|
(3) Sales last 3 months |
Sum of Apr, May, June |
|
|
$ 111,388
|
|
(4) Annualized Sales |
(3) times 4 |
|
|
$ 445,552
|
|
(5) Average Daily Sales |
(4) divided by 365 |
|
|
$ 1,221
|
|
(6) A/R Days |
(1) divided by (5) |
|
|
37 |
This is how it is presented in SurvivalWare’s DataViewer after you do a drilldown on “Days of Sales in A/R”:
Drilldown on A/R Days
Doing a barchart showing the trend helps you figure out what a reasonable assumption for the future might be. It looks like 35 to 40 days will be about right assuming no major changes in collection policies or the mix of customers.
Barchart of A/R Days
If you have inventory in your business, you analyze days of inventory in much the same way as Days of Sales in A/R. For a given month, you take the ending Inventory Balance. Then divide by the daily average “Cost of Sales.”
“Days of Expenses in A/P” is calculated in a similar fashion. For a given month, you take the ending balance of Accounts Payable. Then divide by daily average “Expenses.” These expenses exclude ones that never enter the payables system, such as Payroll, Depreciation, and Amortization. “Days of Expenses in A/P” is an approximation for how long you take to pay your vendors. If you see the number rising, it could be a sign that cash flow is deteriorating, and you are depending on your vendors to carry you.
Projections
The reason for analyzing the three key measures of working capital in the past is to come up with reasonable estimates for these measures in the future. The balances for A/R, Inventory, and A/P are calculated as a function of your projections for Sales, Cost of Sales, and Expenses.
Then as you change your projections for sales and expenses, the working capital balances are updated automatically. The model needs to know how much you will have tied up in Receivables and Inventory, and how much you defer in Payables before it can figure out how much cash you will end up with for any given month.
.
SurvivalWare gives you the option of entering these balances directly by typing numbers into “override” cells, or by entering the number of “days” and letting the model calculate the balances.
When you enter days, this is the formula for calculating projected Accounts Receivable:
A/R Balance = Avg Daily Sales * A/R Days
The example below shows how you can enter $50,000 as the Accounts Receivable balance in July, 2008 (“A/R Override”), and then assume 40 days of Accounts Receivable thereafter. The model calculates the resulting balances to use in the balance sheet.
A/R assumption in the SurvivalWare grid
Related Posts
Tags: Cash Flow Analysis, Cash Flow Projections, Cash flow Software, Working Capital
on Wednesday, July 16th, 2008 at 3:34 pm and is filed under Cash Flow Analysis and Projections, SurvivalWare "How to". You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
[...] Working Capital [...]