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.

 

  1. Getting familiar with the Forecast Tool
  2. Sales and Expenses
  3. Debt Service and Investment
  4. Working Capital
  5. 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

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

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

A/R assumption in the SurvivalWare grid

About these ads

5 Responses

  1. [...] Working Capital [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 70 other followers

%d bloggers like this: