Calculating and Interpreting WIP Lock Up Days

 

At the moment the best way to calculate WIP lockup with the information you have is to run a WIP Comparison for 12 months, take your most recent Closing WIP balance, divide this by the sum of last 12 months invoiced values and multiply that number by 365. If you have monthly fixed price agreements you'll want to segment these out first using the Job Category as these WIP days will be negative.

 

Here is a 12 month example using 12 month WIP results:

( $312,285.48 / $1,577,379.08 ) * 365 = 72 days

 

The way we would interpret this would be to say 'on average we have 72 days of earnings sitting in our Work In Progress'.

 

It's a moving average so it can be calculated using shorter periods if we modify the calculation to take an average of the shorter periods. For example a 4 month  WIP calculation would look like this:

 

($171,262.26+$128,955.61+$115,683.42+$82,734.20)/4 = $124,908.87 (Average 4 month revenue)

 

($312,285.48 / ($124,908.87*12) * 365 = 76 days

 

The increase in WIP days when looking at just the 4 month period would be seen in your WIP Comparison report as your closing WIP balance increasing over the last 4 months in relation to your earnings for the same period.

 

In order to arrive at Lockup Days we need to add your Debtor Days to this which is best done by running a Balance Sheet from your Xero and taking your Accounts Receivable balance from the end of last period and dividing it by the sum of the 12 monthly revenue figure used above, then multiplying by 365. An example using a $200k AR balance is below:

 

( $200,000 / $1,577,379.08 ) * 365 = 46 days

 

By adding the two days numbers together you should arrive at the total time from timesheet to money in your bank. By looking at the two numbers separately you can see whether the problem lies in delivery or collections. The lower the numbers the higher the velocity of money in your practice.