See Also:
Cash Flow After Tax
13 Week Cash Flow Report
How to Develop Daily Cash Report
Cash Cycle
Cash is in Your Business
Download ToolDynamic Cash Flow Projections
Financial statements are the basic building block for understanding how a business is doing. They provide management a way to assess the results and consequences of past decisions. However, because financial statements reside in the past, they are of limited use when used to forecast the future. While there does not exist a fool-proof way to forecast the future, there does exist a reasonable best-guess method to forecast how things may turn out. This is where financial projections come in.
Like historical financial statements, there are 3 basic financial projection reports: Projected
Income Statement, Projected
Cash Flow Statement and Projected
Balance Sheet. A dynamic cash flow projection model is an important tool for managing your business.
Most professionals will produce projected income statements. Some will produce projected cash flow statements. However, few will do projected balance sheets! A complete set of financial projections is very important to keep and maintain.
These financial projections typically cover an entire fiscal period of 12 months and are based on best-guess assumptions. Some more sophisticated companies and practitioners will go so far as to create best case, worst case, and probable financial projections. These in turn are based on best case, worst case and probable assumptions for the company's future business prospects.
Each of the 3 financial projections tells a different story about the company.
Income Statement Projections
A projected Income Statement provides management an idea of how the company's profitability will look 12 months into the future. This projected profitability rests in large part on management's ability to forecast industry and customer demand, costs, as well as many other macro and micro economic factors.
Cash Flow Statement Projections
The projected Cash Flow Statement provides management with an idea of how the firms liquidity will be impacted given the business assumption inputs for the Income Statement projection. The projected Cash Flow Statement seeks to answer questions such as: How much working capital will we have? How much additional capital might the firm need if assumptions for growth change? Will we have enough money to make payroll? Can we make our debt payments?
Balance Sheet Projections
The projected Balance Sheet allows management to know the state of its asset, liability and equity base. As business expands or contracts so too will the firm's
assets, liabilities and
equity. The projected Balance Sheet allows the company to project debt levels and covenants.
Cash Flow Step by Step
In order to successfully create financial projections a variety of information will need to be gathered. It is important to obtain the most recent and up-to-date information as all projections of the future are grounded in the past.
In other words…Garbage In Is Garbage Out! Done correctly, you will only need to do this once. However, if you come to find out that there are pieces of information missing, you may need to adjust accordingly. Someone in accounting, preferably a person who has access to the most current financial statements.
Thankfully, historical and year to date financial statements can be easily obtained if you use an accounting system such as PeachTree, QuickBooks or Great Plains. Since all the future projections will be made in a spreadsheet format, it will be imperative that the historical financial statements also be in a spreadsheet format. Each of the above three programs can export into an Excel format.
Step 1. Historical Financial StatementsMake sure that you get all 3 financial statements from the previous fiscal period and/or year to date into a spreadsheet format such as Excel.
Step 2. Format Financial StatementDouble check that the format is how you want it to be. Make any changes accordingly. The historical financial statement will serve as a template for how the financial projections will look.
Cash Flow Assumptions
While financial projections foretell business transactions of the future, they are firmly based in the business realities of the past. In order to project how the company's performance will look like tomorrow it will be important to review all the assumptions effecting cash flow that went into shaping the events of today and yesterday.
The objective of the assumption page is to have an input page that serves as a reference for the financial projection. Done correctly, you only need to enter numbers once into the assumption page. Make sure each number is entered into its own cell. Then, just reference that particular cell any time a formula is entered. This allows you to easily see the effects of changes to your assumptions instead of wasting time trying to find which formulas it applies to and in which spreadsheet.
Documenting assumptions is a continuous work in process. Things will change all the time. Certain assumptions will be taken away, while others will be added later. Expect to make frequent updates to this page. The assumptions page has inputs from all parts of the business. It may be necessary for the CFO/controller updating or creating the spreadsheet to go to various departments to get their input on various subjects: Revenue, Cost of Goods Sold,
Overhead Expenses, Capital Expenses, Inventory and
Work-in-Progress, Debt Obligations, DSO, DPO and Cash Conversion Cycle.
As described above, it may be necessary to discuss future growth assumptions with a representative from different parts of the business.
Sales: Ask them what their growth projections are. How many units do you forecast to sell? By what % do you forecast growth to occur? (this is the easiest way to ask)
Operations: Ask them what their growth projections are. How will costs for inventoried items and raw material change in the next 12 months? Do you foresee any shortages or excess capacities? Will there be any changes in price or payment required ements? What sort of capital expenditures are there for the next 12 months? What percentage of raw material gets turned into Work-in-Progress?
Finance/Accounting: Ask them what their growth projections are. What are the notes that the company is obligated to pay? How much do we pay each month in total for debt? How is it broken out? What is the relationship between
accounts receivable versus sales? Is there an average percentage of sales that you can use? What is the relationship between
accounts payable cash disbursement? When do we pay? What percentage of raw material gets turned into Work-in-Progress?
Once you have gathered up all this information, put it down on an Excel worksheet titled "Assumptions". Remember to enter in numbers into their own individual cells.
Income Statement Projections
After you have finalized the entries for the assumptions page, the next task at hand will be to tackle the Income Statement projections. Of all the projections you do, this is one that receives the most attention. The Income Statement projection can serve as a budget as well as a tool for management to analyze various business scenarios.
Notice how we started with the Assumptions Worksheet. The information there feeds the Income Statement.
The Projected Income Statement will need to be done prior to the beginning of the next fiscal period. You will need to do a lot of research before you can begin. Specifically, the assumptions page will need to be completed. Please see the "Document Assumptions" section for further details.
Projections are an important tool for management decision making as well as for bank reference. Delegation of this task needs to go to someone who is familiar with both finance and accounting. The person also needs to be comfortable maneuvering in Excel.
Using the basic format as a guide, below is a pictorial view of how to organize the spreadsheet. Formulas for individual cells will need to be created and referenced to the appropriate cells bask in the Assumptions Worksheet. Use the same format as used in the actual financial statement so that you can drop in actual results on a monthly basis.
Cash Flow Statement Projections
The next task at hand will be to tackle the Cash Flow Statement projections. Often overlooked, the Cash Flow Statement is critical for projecting out working capital needs to the Owner(s)/Management. Typically, most CPAs and other financial professionals will use the indirect method or Statement of Changes. Some CFOs prefer to use the direct method (gross cash receipts vs change in accounts receivable) which looks similar to the Income Statement. This format is easier for the entrepreneur to understand due the similar appearance and format between the Cash Flow Statement and the Income Statement. Also, the entrepreneur can relate the Gross Cash Receipts, which is on a monthly basis, to the weekly Cash Receipts which in turn can be related to the Daily Cash report. More sophisticated users, however, such as venture capitalists, may prefer to see the indirect method.
Formulas for individual cells will need to be created and referenced to the appropriate cells back in the Assumptions Worksheet.
It may be easier to look at the Projected Cash Flow Statement as consisting of two halves. The first half deals with the actual cash flow from operations and closely mirrors the line items seen on the Projected Income Statement. The second half of the Projected Income Statement consists of two sections: 1) "Add: Sources of Cash" and 2) "Less: Uses of Cash".
It is this second half portion of the I/S that you will be working with to help you balance out the B/S. This portion is basically a "worksheet" that you use in balancing out the B/S. It should not be a part of any printouts. With the exception of Cash, A/R, Inventory, and A/P, the rest of the B/S line items are all adjusted in the “Less: Uses of Cash” section. Cash, A/R, Inventory, and A/P are adjusted above in the CFS portion. It should also be noted that we assume that SG&A expenses are paid out in the same period as they are incurred. This assumption makes it easier to see the impact of cash flows on working capital.
Balance Sheet Projections
After you have finalized the entries for the Projected Cash Flow Statement , you are now ready to begin working on the projected Balance Sheet. The projected Balance Sheet allows you to quantify and project debt levels and financial ratios. Furthermore, it gives managers a feel for how the company's assets, liabilities, and equity will change over time. These changes are all a result of various assumptions and decisions that management made and go all the way back to the Assumptions Worksheet.
Of all the projected financial statements, the Balance Sheet projections are the trickiest to do. The main reason is because you will need to cycle back and forth between to the Projected Cash Flow Statement and the Projected Balance Sheet. A lot of the balances on the Balance Sheet are influenced by changes in cash flow. Adjustments will also need to be made in order to make sure that both the Total Assets as well as the Total Liabilities and Shareholder's Equity are equal. This task is the main thrust of what you are trying to accomplish in the Balance Sheet projection.
As mentioned before, you will be shifting back and forth between the Projected Cash Flow Statement and the Projected Balance Sheet. Let us first revisit the Projected Cash Flow Statement. It may be easier to look at the Projected Cash Flow Statement as consisting of two halves. The first half deals with the actual cash flows themselves and closely mirrors the line items seen on the Projected Income Statement. The second half of the Projected Income Statement consists of two sections: 1) "Add: Sources of Cash" and 2) "Less: Uses of Cash". It is this second half portion of the Income Statement that you will be working with to help you balance out the Balance Sheet. This portion is basically a "worksheet" that you use in balancing out the Balance Sheet. It should not be a part of any printouts.
When you are done, it will be time to review and balance the Balance Sheet projections to make sure everything fits. The most obvious place to look for errors is the balance sheet (i.e. Total Assets do not agree with Total Liabilities and Equity). One important place to check for Balance Sheet error is to make sure that the
retained earnings "roll forward" correctly. Another place to double check is the assumption sheet and any formulas and/or links you have in the Balance Sheet.
Use client Balance Sheet format to make dropping in actuals easier. If you are having difficulty balancing the projected Balance Sheet, flatline sales & expense growth to make it easier to identify problems with formulas. Roll forward major accounts: Accounts Receivable, Accounts Payable, Inventory & Retained Earnings.
Maintaining Cash Flow Projections¶
As stated before, projections are based on historical information. As each month ends, it will be important to "drop in" the actual numbers for Revenues, Cost, Expenses, Cash Flows, Assets, etc. In so doing, the forward-rolling projections are actually grounded in truth and you can even see the disparities somewhat between what the estimates were versus initial estimates. As your actuals are dropped in, the goal becomes to use these actuals to help drive operations toward a target versus just benchmarking them against original estimates.
After 12 months go by, the projections for the year will have morphed into becoming the Income Statement, the Cash Flow Statement, and the actual Balance Sheet for the trailing twelve months. This then can be used for tax planning purposes or debt compliance purposes.
After you have "dropped in" the actual numbers for the financial statements it will be time to review your work. We will need to balance or "tie down" the financials again.
Every time a month has closed and you have access to "real numbers", you ought to drop in the actuals. Note though, that as soon as you drop in your actuals for Income Statement and Cash Flow Statement, you will need to balance and review that period again to make sure that the Balance Sheet ties.
Each month you should drop in the actual results in the projections. By doing so the year end net income is a moving target. The assumptions that go into the sales and expense also change frequently. Consequently, every time you update the projections with actuals, you should also review assumptions for the remainder of the year. This procedure makes your cash flow projections “dynamic”.