more-arw search

Q&A Forum

Cashflow Forecasting

John Veloski's Profile

I've just joined a SaaS company which has a new CEO and no real cash forecasting in place. Right now, cash balances are OK, but in order to ensure that status continues, I need to come up with a better way of doing things. What I have just started in an Excel spreadsheet in a Income Statement format with a Consolidated worksheet, an Actual worksheet and a Projected worksheet. The Actual and Projected worksheets are linked to the Consolidated one. The way this works is that the normal, recurring monthly outlays are entered into the Actual. Items such as an upcoming trade show or marketing campaign are estimated and entered into the Projected. As the Projected becomes real, the expenditures are moved from Projected into Actual. As you can imagine, this becomes a fair amount of work and I know there has to be a better way of doing it. No, they don't have a budget in place at the moment, but there will be one next year. Can anyone share how they do projected cash flows or does anyone know of a better way of doing this? Thanks,

Answers

Topic Expert
Henry Schumann
Title: Manager FP&A
Company: Allscripts
(Manager FP&A, Allscripts) |

John,
I'm sure none of what I'm about to say is ground breaking, but a cash flow model needs three sections. 1. Cash receipts 2. Recurring payments (i.e. Payroll, rent, utilities) 3. Non-recurring payments (i.e. Capital purchases, event payments)

Your spreadsheet then should have a roll forward schedule. Beginning balance plus #1 less #2 less #3 equals ending cash balance. You can set up this schedule daily, weekly, monthly, quarterly depending on how tight you need the forecast to be.

Best of luck,

Henry

tracey Knight
Title: Director, Solution Consulting
Company: Reval
(Director, Solution Consulting, Reval) |

It sounds like you’ve got the right building blocks for the forecast itself, but it is the manual process that is difficult. Treasury and Risk Management software (TRM) can help to automate the process by automating the retrieval of the actual data for comparison against the forecast. You can build rules to drive the various bank transactions (actual data) into your categories for comparison against the forecast. A SaaS-based solution can make it easy for you to get this kind of automation.

Bill Jancosko
Title: Partner, CFO
Company: Timepiece Capital Inc.
(Partner, CFO, Timepiece Capital Inc.) |

All good advice above. This may be a bit simplistic but, we have had success using a Spreadsheet format of...
Beg Cash Balance
Receipts
Disbursements
End Cash Balance
We first create actual prior month columns in our spreadsheet, so we can see the trend from actual cash to forecast cash. The monthly receipts and disbursements totals are from GL detail report of cash (debits = receipts, credits = disbursements). The Cash GL detail report gives us the detail to fill-in the major line items. We use a plug line for capture all the small items. Forecast AR collections based on historical and seasonal ration of cash collected to sales. Hope this is helpful.

Topic Expert
Alan Hart
Title: Consultant
Company: Pacific Shine Group
(Consultant, Pacific Shine Group) |

Ideally, forecasting cash flow for a company is being able to forecast an accurate and complete Balance Sheet from which you can compile a Statement of Cash Flows (with the help of a forecasted Income Statement). This is similar to compiling an actual Statement of Cash Flows where you use an actual Income Statement and an actual Balance Sheet (both obtained from your actual accounting system) and clearly identify the three main sections: Cash Flows from Operations, Cash Flows from Investing Activities and Cash Flows from Financing Activities.

What is very hard for most people is to forecast a Balance Sheet from which they can put together a Statement of Cash Flows for each accounting period identified in their budget.

From my experience in using spreadsheets to forecast a Balance Sheet, I’ve realized, as many others have, how difficult and actually daunting it is to attempt to do this, unless you use crude assumptions and approximations which generally should not be relied on.

As many people are starting to realize, budgeting and forecasting tasks should not be performed using spreadsheets, for many reasons. There are software applications specifically designed to do that, some of which are actually designed for the small or medium size organization and may be surprisingly affordable for even smaller companies.

One solution I like to use to accomplish the exact task you are referring to in your question is an application titled Budget Maestro from Centage Corporation.

This software will automatically generate all common financial statements for the duration of your budget, including a Statement of Cash flow identifying where cash is coming from and where it is going, in all three cash flows categories and in all forecasted periods. It even allows you to choose between the direct method and indirect method. I find it a much better way to accomplish what you are describing, with considerably less work and much more accuracy and eliminating all the risks that spreadsheets present.

There are other solutions that will allow you to automate your budgeting process but you may find them cost prohibitive and complex, as they were primarily designed for larger enterprises with significantly more IT and finance resources.

My best advice would be to research all available solutions once you realize that spreadsheets, while having their place in business, are not the right tool for an integrated, all-encompassing budgeting and forecasting application.

Gerry Anderson
Title: President and Founder
Company: Logicon Solutions
LinkedIn Profile
(President and Founder, Logicon Solutions) |

HI John,
Thank you for posting this question. There are actually a number of software packages we are aware of (under $5 K) that can take your spreadsheet and automate what you have set up. It will compliment your work and easily tie into any accounting package. The system will actually allow you do to scenario planning and determine the effects of strategy changes on your financials in minutes not hours, days or weeks.

Kind Regards,
Gerry A.

Ben Murray
Title: Vice President and CFO
Company: Cartegraph
(Vice President and CFO, Cartegraph) |

John,

I just created a SaaS Financial Model in Excel that can help you with this. With a little modification, you can pull in Actuals. You can find it at www.TheSaaSCFO.com. Free download.

Regards,
Ben

Steve Sheridan
Title: Associate
Company: Dean Lewis Associates
(Associate, Dean Lewis Associates) |

I don't know if your business is cyclical, but if it is I found that looking at historical check runs helped me create a cash flow forecast. I looked at the past few years of check runs and graphed them. If I saw trends, such as a spike in expenses in certain periods, I could forecast a higher cash outlay for those periods, and vice versa. I'm sure software could do this automatically, but it wasn't that difficult to compile this in a spreadsheet.

20522 views

Get Free Membership

By signing up, you will receive emails from Proformative regarding Proformative programs, events, community news and activity. You can withdraw your consent at any time. Contact Us.

Business Exchange

Browse the Business Exchange to find information, resources and peer reviews to help you select the right solution for your business.

Learn more

Contribute to Community

If you’re interested in learning more about contributing to your Proformative community, we have many ways for you to get involved. Please email content@proformative.com to learn more about becoming a speaker or contributing to the blogs/Q&A Forum.