Forecasting cash flow
a CEO master skill
Payables, receivables, debt service, capital expenditures, sales/repurchases of stock ... an integrated spreadsheet handles this complex financial interaction with electronic precision, helping the busy CEO to more carefully predict where the company is heading.
by Scott S. Pickard
CASH IS KING,” as the old saying goes. It's the one resource a construction company (or any company) cannot survive without for any length of time until the doors are closed, voluntarily or involuntarily. Which is why the CEO must find or develop a reliable method to understand and predict the company's current and future ability to generate the cash it needs to pay all of its bills. Well, help is on the way!
Forecasting cash flow is much easier than it used to be, thanks to the convenient number-crunching power of PCs and spreadsheet software like Microsoft Excel and Lotus 1-2-3 that allow one to build an “integrated spreadsheet” linking a projected balance sheet and income statement for the business. The integrated spreadsheet is the tool that will give any CEO the positive direct control he or she must have over the financial rudder of the business.
A Wise Time Investment. A company's cash flow at any point in time is a juxtaposition of payables, receivables, debt service, capital expenditures, sales/repurchases of stock and other factors. An integrated spreadsheet handles this complex financial interaction with electronic precision. Using an integrated spreadsheet, a CEO can more carefully predict where the company is heading or could be heading. That's powerful planning and peace of mind for the executive/owner that shoulders the burden of consistently meeting payroll and staying current with suppliers on all bills.
The integrated spreadsheet gives the CEO a rational way to appropriately pace capital expenditures, quarterly (or even monthly) bonus payments and sweeps of excess cash into less liquid but higher-returning financial instruments at the earliest possible time.
The integrated spreadsheet allows the CEO to look at the effect on cash from big-picture business initiatives such as acquiring a new business, selling off a division, developing and staffing a new department or launching a new product line.
Using an integrated spreadsheet helps the CEO disclose mistakes that are sometimes made in monthly financial statements either from miscoding or faulty accounting interpretation of a particular transaction.
With an integrated spreadsheet, the company always has a three-year plan built on actual operating numbers, but fine-tuned to reflect management's best judgment of future revenues and expenses.
The integrated spreadsheet can easily generate an unlimited number of graphs to analyze past performance and predict future performance, which is often the most effective way to understand and communicate financial data to employees, directors, shareholders and the bank.
An integrated spreadsheet will demonstrate to the bank, board and investors that the financial management and budgeting of the company is under control, which promotes confidence in the officers and the business by its internal and external constituents.
Do It Yourself, or Start With a Template. While there are off-the-shelf programs that do integrate a balance sheet and income statement and allow one to build pro forma statements, they don't offer the unlimited customization flexibility of a spreadsheet. Assuming the developer knows their way around a spreadsheet and double-entry accrual accounting, an integrated spreadsheet can be set up in a few hours. It will take about a day and a half to input the previous 12 months of operating data, make an informed estimate of revenues and expenses for the next 12 months, and to train all users who will have access to the integrated spreadsheet. Start to finish, a complete initial implementation of an integrated spreadsheet is a two-day job for one person. If the CEO cannot spare the time, this task can be delegated to inside accounting staff or subcontracted to an outside accountant or consultant. [Note: To anyone that requests it, the author will send a starting Excel template at no charge.]
Exhibit 1. The spreadsheet has three major components: balance sheet, income statement, and cash flow adjustment. These components are “linked” or “integrated” so that they “balance” and “tie out.” The advantage of such a spreadsheet over an off-the-shelf program is that it gives the user the infinite flexibility to add/subtract/modify at will, allowing a more realistic modeling of the financial dynamics of the business within the double-entry accrual financial framework.
If this project is delegated, it is still important that the CEO be trained in the use of the integrated spreadsheet so that he or she can perform “what if” analyses and generally watch over the constantly revising forecast of the financials of the business, a function that should be “owned” by the CEO. Financial forecasting involves hundreds of experience-based estimates of highest-probable-outcomes of revenues, expenses, capital expenditures, debt service, equity inflows and outflows, extraordinary gains and losses, and other income and expenses, by someone who has the overview of the business. The CEO has this overview, as it is part of the responsibility of the position.
Basic Structure of the Integrated Spreadsheet. It is outside the scope of this article to present a detailed “how to” for building the integrated spreadsheet. However, it is important for the user to have a general understanding of how the spreadsheet is laid out and functions. Exhibit 1 shows the basic layout of the integrated spreadsheet.
The spreadsheet has three major components: balance sheet, income statement, and cash flow adjustment. These three components are “linked” or “integrated” so that they “balance” and “tie out,” to use accounting terms. The integrated accounts are highlighted in various colors in Exhibit 1.
Moving left to right through the spreadsheet and starting with the left-hand side of the spreadsheet are the same major and sub-accounts that are used on the business's balance sheet and income statement. Immediately to the right of the balance sheet accounts listed in Column A are the ending balances (Column B) from the previous fiscal year, which are the beginning balances for the current fiscal year. Then in Columns C–N are the changes in each account for each month of the year. It's important to note that this is the change for the month, not the ending balance for the month; which is what you see on a traditional balance sheet. Column O sums the total of the monthly changes for the entire fiscal year, and that total change is added to the current year's beginning balance (Column B) to give the ending balance for the year in Column P.
Moving top to bottom through the spreadsheet, sub-accounts are summed just as they are on typical financial statements. The various links are identified by the matching colors. For example, the link between “depreciation” on the balance sheet and “depreciation expense” on the income statement is shown in blue, since these two entries must be identical in double-entry accrual accounting.
Without getting into a primer on financial accounting, there are in fact numerous “links” between the income statement and the balance sheet as a result of the double-entry methodology. The advantage of a spreadsheet over an off-the-shelf program is that it gives the user the infinite flexibility to add/subtract/modify at will and build increasing sophistication into the integrated spreadsheet, allowing a more realistic modeling of the financial dynamics of the business within the double-entry accrual financial framework.
The model shown in Exhibit 1 can be replicated and extended as many years forward as one likes, either month-to-month, quarter-to-quarter, or year-to-year. All that is required is to link the ending balances of Year X to the beginning balances of Year X+1, and so forth.
When building a forecast for the current fiscal year, the previous 12 months (Year –1) offer the first best model of what numbers to use. Likewise, Year +1 will logically flow from the pattern of numbers established in the current Year 0. Years +2, +3, and +4 can be forecast using reasonable estimates of how items will increase/decrease over the years, but due to the degree of uncertainty in these projected numbers, projecting Years 2–4 at the monthly level is generally not necessary or appropriate given the degree of accuracy or appropriate given the degree of accuracy in forecasting this far into the future.
Using the Tool. Once the integrated spreadsheet is set up, using it effectively involves inputting the actuals from each monthly financial statement as they occur and reforecasting the numbers going forward from the most recent actuals. This process repeats itself every month—inputting the most recent actuals and reforecasting ahead—and as each month goes by and the user gains experience in using the spreadsheet and making experience-based judgments of how the numbers will track, the integrated spreadsheet in effect becomes an “expert system” that does a better job over time of forecasting the financial fortunes (or misfortunes) of the company. The key point is that all of this boils down to one most important account: cash flow.
Once the process of updating actuals and forecasting ahead is complete, the CEO looks at the impact on cash and then develops a financing plan that optimizes the uses of working capital in the next six months and beyond. If the projected cash flow shows surpluses being generated, the CEO can decide how that excess cash could be used today and in the coming months to reduce payables, reduce long-term debt, make capital expenditures, and make other long-term investments.
If the projected cash flow is negative, the CEO must plan for how the minimum working capital requirements for the business will be generated to carry the company through tight cash periods, by a combination of drawing down cash surpluses, deferring certain operating and capital expenditures, extending the payables cycle for a brief period within acceptable bounds, making a draw on an operating line of credit, securing additional long-term financing, and raising equity capital through the sale of common and/or preferred shares.
A BOOK COULD easily be written to cover all the nuances of financial forecasting and how that can be applied within the integrated spreadsheet, but that's really not necessary. If the CEO can build the integrated spreadsheet for the business and start using it each month, if not each day, learning-by-doing is the most efficient user's manual. The CEO will quickly discover the many dimensions of value that are derived from the integrated spreadsheet, aside from the very tangible value of forecasting cash flow. The integrated spreadsheet causes the user to think about every aspect of the business, across all accounts, across time, across strategies, by “looking back to look forward.” And at the end of the exercise, instead of saying, “I hope we'll have the money in the bank when we need it,” the CEO can say, “We expect to have the cash we need, and here's how.”
That's powerful business confidence! ■
Scott S. Pickard is the president and CEO of ERES Consultants, Inc., an international engineering consulting firm based in Champaign, Ill.
PM Network • November 1997