How to guide – doing Detailed Cash Planning in Excel

Here is a step by step approach to preparing a detailed cash plan in Excel.  I’m sure there are several ways to do it.  I picked the first one that seemed to work. 

This is an expansion of the example mentioned in the article “Surviving a Cash Crisis – Fundamentals” (a must read if you are experiencing cash flow problems right now). 

There are Five steps to creating a detailed cash plan using this spreadsheet. (Download it for free at www.survivalware.com/download/Weekly_Cash_Flow_Forecasting.xls

Step 1 is to figure out how much cash on hand you have now.  Hopefully your book-keeping is caught up and this is a simple matter of going into QuickBooks (or other accounting software) and checking to see what is in your bank account. 

Step 2 is to figure out who you have to pay when, going out over the next few weeks or months.  In preparation, you may want to print out the check register for the last 90 days to get a feel for what you have been paying, and what time of the month.  If your payables are up to date, print out a detailed listing.  Otherwise, keep handy the stack of bills, sorted by date due. 

Enter the end date for week one in the yellow cell (E7) and the other dates are adjusted to be at one week intervals. 

  

Step 3 is to estimate receipts from current customers.  Enter a list of open Invoices, including the date and amount of each open invoice. 

The date of payment for each invoice will be calculated by adding the Collection Period in the yellow cell (C26) to the date of each invoice. 

  

Step 4 is to estimate cash inflows from sales that happen in the future.  Enter monthly or weekly sales estimates and make an assumption about collection period. 

  

Step 5 is to enter any special events – such as going to the limit on your credit cards, or cashing in a 401k.  

Then look at your forecasted ending cash balance at the bottom of the spreadsheet.  You don’t want to see any red numbers down there.

Advertisements

2 Responses

  1. Thank you for good information~~*

    Please comeback to visit my blog too : http://about-accountingsoftware.blogspot.com/

    I’m sorry , If you think this is spam. but may i thank you again.

    Bye

  2. […] How to guide – doing Detailed Cash Planning in Excel […]

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

%d bloggers like this: