by scott pickard

Cash is king, as the old saying goes. It is the one resource a 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 method to reliably understand and predict the company’s current and future ability to generate the cash it needs to pay all of its bills.

Forecasting cash flow is much easier than it used to be, thanks to the convenient number-crunching power of PCs and spreadsheet software which allows one to build an Integrated Spreadsheet which links a projected balance sheet and income statement for the business. The Integrated Spreadsheet is a tool that will give any CEO the positive direct control they must have over the financial rudder of the business.

A wise time investment for CEO’s
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 this tool, a CEO can more carefully predict where the company is 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 from either miscoding or a faulty accounting interpretation of a particular transaction.
  • With an Integrated Spreadsheet the company always has a three-year plan that is 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 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.


Assuming the user knows their way around a spreadsheet and double- entry accrual accounting, the structure of an Integrated Spreadsheet can be set up in a few hours. It will take a day or so to input the previous twelve months of operating data; a day to input informed estimates of revenues, expenses, and capital expenditures for the next twelve months and to fine-tune those estimates; and a day to train all users who will have access to the Integrated Spreadsheet.

If the CEO cannot spare the time, this task can be delegated to inside accounting staff or subcontracted to an outside accountant or consultant. 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 that 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 important for the user to have a general understanding of how the spreadsheet is laid out and functions. The integrated spreadsheet has three major components:

  • Balance Sheet
  • Income Statement
  • Cash flow adjustment

These three components are linked or integrated so that they balance or tie out in accrual accounting terms. These integrated accounts are highlighted in various colors as shown on the diagram below:


The major points of integration 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 brown, since these two entries must be identical in double-entry accrual accounting. There are in fact numerous links between the income statement and the balance sheet as a result of the double-entry methodology. The beauty of the spreadsheet is it affords the user the flexibility to add/subtract/modify at will and build increasing sophistication into the integrated spreadsheet enabling a more realistic modeling of the financial dynamics of the business.

I’ve posted a power point presentation on prezi that leads you through the basic construction of the integrated spreadsheet. The secret sauce in this process is the synching of a cash flow adjustment (plug) at the bottom of the spreadsheet below the income statement as shown in the diagram below:


By wiring together these key accounts to calculate the actual cash flow for each month, this will cause what’s known as a circular calculation in the spreadsheet which is normally a no-no, but in this case it is a good thing! The user simply needs to go into “settings” and set the automatic calculation to 100 iterations and the spreadsheet will automatically recalculate and balance the statements after each new value entry to a cell.

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 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

  • Reduce payables
  • Reduce long-term debt
  • Make capital expenditures
  • 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
  • Raising equity capital through the sale of common or preferred shares


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!

- sp -

Cash Management

Building the Integrated Cash Flow Forecasting Spreadsheet

Imagine H2O

Imagine H2O is a nonprofit organization with a mission to inspire and empower people to solve water problems.

Hydrosphere Water We pay for water when we use it, and we pay for water when we get rid of it. GLOBAL: How are humans changing the global water cycle, the associated biochemical cycles, and the ecosystem function of the global water system?  What are the socio-economic and environmental feedbacks arising from these changes? Will a lack of water threaten energy production?  How can everyone have sufficient clean water without conflict?  INDUSTRY: Is the water quality of the plant internally and externally in compliance with relevant environmental laws?  What percentage of the company's revenues does it spend to maintain water quality to mandated levels? Is it reasonable or getting out of control? in general: | about | catastrophes: West Virginia | conservation: Americans have no idea how much water they’re using Yoshida | images | point-of-use (POU) purification:, hydronovation | serionix, SODIS solar disinfection | modeling: aquaveo, WaterML 2.0 | organizations: |  rain  barrels | recycling: stillsuit | research: Global Water System Project | scarcity: When the well runs dry, we shall know the value of water. Franklin, The End of Abundance Zetland | smart sprinkler systems: | sponsored projects: bluewater.rbc, charitywater | underground lakes: Kenya | ventre capital: Imagine H2O > Venture Capital | water cycle: video, diagram | water from air: islandsky big ideas | waterless textile dyes: airdye | water rights: California Water Wars  top educational literacy: α (1 / diarrhea) α (clean drinking water) α (solar rays + water) water quality: arid environments: sand dams | rooftop tanks: Inside City’s Water Tanks, Layers of Neglect Rivera | filter mechansisms: drinksoma, puremadi, WaterBean |bottleless water coolers: quenchonline | denitrification: eosenvironmental | desalination: onsite, voltea | on-site potable water purification:, | pool-in-a-river: pluspool |  USEPA Sole Source: Mahomet Aquifer | wastewater filtration: mmfwater books: The Big Thirst Fishman | The Great Lakes Water Wars | Water Wars: Privatization, Pollution, and Profit Shiva Venture Capital | Sustainability

Imagine H2O is a nonprofit organization with a mission to inspire & empower people to solve water problems. Our vision is to turn water challenges into opportunities.

The Rise and Rise of Bitcoin

Will the rise of Bitcoin bring a monetary paradigm shift that will forever change the world?

Production & Utilization of Wealth/Money Currency and Exchange  search What has been the impact of the fluctuating value of the dollar on the company's import and export activities?  What protective measures does the company take against currency fluctuations?  What exploitive measures does the company take with respect to currency fluctuations? in general: calculators: | open source: opencoin | paper: fortresspaper | sending/receiving: stellar | trading: forex,, | virtual currencies: amazon coins, webmoney | Wolfson Economics Prize big idea bitcoin: about: FBI assessment | ATM |  buy/use/accept: coinbase | documentary: The Rise and Rise of Bitcoin Globalization | Financial | Risk | Inflation | Innovation | Intl | Monetization

How bad a shape is Illinois in?

The looming bankruptcy red flags continue to mount for Illinois as individuals and companies exit the state in search of good jobs, lower taxes, and to escape the entrenched cronyism, corruption and dysfunctionality of its politicians and unions.

Financial Management Cash Management What anti-check fraud procedures are in place?  Do we have systems in place to detect money laundering?  Are all bank accounts reconciled each month? What minimum cash balance is appropriate for the company?  Is the company using every available technique to minimize transfer time for collecting cash balances? (a) rapid billing; (b) lock boxes; (c) discount policy; (d) collection efforts  Who is authorized to make payments of company funds and what amounts are they authorized to disburse?  Are authorized persons bonded at the appropriate amounts?  What is the company's plan for optimizing the balance between maintaining the liquidity of excess cash vs. generating maximum short-term and long-term interest income? What proactive measures are taken within the company to prevent embezzlement? cash flow: What are the long-term cash needs for the company for the purchase of property and equipment?  What effect does the variation in key assumptions (sensitivity analysis) have on the financial projections? | cash flow statement: direct vs indirect | check fraud: anti-check fraud procedures | Forecasting Cash Flow sp | money laundering: detect money laundering bankruptcy: Has the company ever filed, or considered filing, for bankruptcy?  Are any of the company’s vendors in bankruptcy or near bankruptcy? | American Bankruptcy Institute: | appraisal of asset values: | banks: bank run |checklist: IRS | IR Internet Bankruptcy Library: | liquidation services: investment policies: What is the minimum acceptable investment return on capital items?  How are lease vs. purchase decisions evaluated?  What is the company's investment policy with respect to excess cash?  How much is kept in short-term marketable securities?  What is the record of gains (losses) from sales of securities in the company's portfolio?  Does the company perform background checks on either the firms or individuals that handle the company's trading in securities? Checklists | Cities | Identity Management | Personal Finances


DAVOS: World Economic Forum

Economic Theory and Systems Economic Theory: index of economic freedom Economic Systems Historical development of economic systems Capitalism Centrally-planned systems Appraising economic systems The Consumer and the Market Scarcity, utility, and value: behavioral economics: | Valuation Consumer behavior: Buying Decisions | inelasticity Markets: markets hate uncertainty | Randomness Price system in capitalist economies Organization of Production and Distribution Organization of the production of goods Marketing and Merchandising Inputs of the productive process Institutions that facilitate business formation, production, output Money Currency & Exchange Banks International: Bank for International Settlements BIS economic statistics business organization stock markets Agricultural economics International trade Government’s role in production & distribution Methods of business organization Advertising & Promotion Distribution of risk Consumer credit For a disciplined credit card user, the interest rate is essentially irrelevant because a credit-smart consumer pays off the most recent transactions with each monthly bill; i.e., you never carry an outstanding balance which is the most powerful way to build a solid credit rating.  If you want a rewards program with your card, then that generally requires an interest rate greater than 0% and the higher the interest rate, the better the rewards since interest income is what covers the cost of the rewards program for card issuers.  But as previously stated, if you are paying off the transactions each month and not carrying an outstanding balance, the high interest rate is irrelevant.  BOTTOM LINE: use a credit card for convenience, security (don’t have to carry alot of cash around), and to create a detailed transaction record,  but not as a loan! Distribution of Income and Wealth the rich get richer and the poor get poorer Matthew Effect Distribution by categories of the population: Forbes 400 | gini coefficient How government affects the distribution Capitalism Taxation Laffer Curve postulates that no tax revenue will be raised at the extreme tax rates of 0% and 100% and that there must be at least one rate where tax revenue would be a non-zero maximum. Poverty Macroeconomics National income and employment theory: misery index Intl. equilibrium and disequilibrium Business cycles Inflation and deflation Economic Growth and Planning Nature and causes of economic growth Planning for economic growth and stability Government Finance Growth books, forums, journals Stealth of Nations by Robert Neuwirth The Mystery of Capital by Hernando De Soto big ideas The Wealth of Nations by Adam Smith Wall Street Journal World Economic Forum Davos Globalization | Buying | Acquisitions | Economics | Human Rights | Issues |