Search This Blog

Thursday, November 5, 2015

Preparing A Cash Flow Projection - Bringing it All Together

Today's will attempt to bring together some of the concepts that I have discussed and show you how to prepare a set of cash flow projections in Excel that you can easily modify as your circumstances change and present quickly to potential lenders.

Setting up the Spreadsheet and Formulas

Assumptions and Hypotheses Tabs

Because so many of the calculations in the projections are dependent on the assumptions made, I would create a separate tab for the qualitiative assumptions that do not directly affect the calculations, and one for the quantitative assumptions that will drive many of the calculations. One efficient way to avoid becoming too bogged down in detail is to start with assumptions about the value of the following:

1. Your average profit margin on sales.
2. The number of orders you can process in a month.
3. The average dollar value per order.
4. The time taken to process orders.
5. The amount of square footage required per employee.
6. The productivity figures for each employee position.
7. The rental amount per square foot.
8. The CPP, EI and EHT Rates.
9. The interest rate.
10. The average office supplies expense per employee.
11. The average telephone expense per employee.
12. Health benefit cost per employee.
13. Average vehicle expense per employee.
14. Average utility expense per square foot.
15. Insurance premium rates.
16. The tax rate.
17. Property tax rates.
18. Employee salary amounts for each position being filled.

Then as you refine your projections, you can always do research to verify the accuracy of the above amounts. But if you set up your main spreadsheet to calculate based on values pulled from this tab, then you can make changes to your entire projection very quickly when any of the above changes.

Main Spreadsheet

I would leave the first 8 or 9 vertical columns blank so that you have enough room to enter in the descriptions of each line item in the projection. You should then have the spreadsheet divided into the following sections:

1. Cash inflows.
2. Cash outflows that are expenses.
3. Excess cash flow before tax.
4. Non-cash expenses to convert excess cash flow to profit before tax.
5. Income tax expense.
6. Excess of cash outflow after tax.
7. Debt payments.
8. Excess cash available before capital expenditures.
9. Capital expenditures and inventory purchases.
10. Excess cash available for distribution.
11. Distributions to investors .
12. Financing obtained from creditors and investors.
13. Net cash flow for the period.
14. Opening cash balances.
15. Ending cash balances.


The first step is to complete the expense outflows for things that are known right off the bat:

1. Monthly rent if you are currently paying it and don't expect it to change for a while.
2. Monthly utility bills if you are currently paying them and don't anticipate a change.
3. Monthly telephone bills and internet bills.
4. Your annual insurance policy.
5. E-bay fees and memberships that are fixed in amount.
6. Equipment purchases that you know you will need to make when the amounts are known.
7. Interest expenses on money that has been borrowed.

There are others, but the above should give you an idea of what these types of outflows are.

Then you should complete the cash inflows section, starting with known amounts like grant monies and programming easy to determine amounts like interest earned on excess funds. Then program the formulas to calculate your monthly sales or service revenue. Use the copy and paste feature to copy your cell formulas across the spreadsheet, which will save you A LOT of work later on. It is a good idea to complete one column from start to finish to make sure that your formulas make sense before you copy them across.

Once you have the sales section completed, it is time to begin calculating your staff requirements using your statistics for the amount of time required to complete the aggregate job functions and your estimates of employee productivity. Once you have the number of employees you can set up the calculations to determine salaries expense, health benefits, CPP, EI and EHT.

Once you have a firm handle on the number of employees, the next step is to complete those variable expenses and fixed expenses that are dependent on the number of employees:

1. Check space requirements to ensure that premises are adequate and if not adjust accordingly.
2. Complete calculations of cell phone expense, vehicle expense, office supplies expense and other expenses.

At this point, you should have nearly all your outflows identified. There are a few discretionary items such as advertising and travel that you should attempt to estimate at this point and include. Also, you can identify and add those non-cash tax deductions that convert excess cash flow into profit before tax and can calculate income taxes.

Once you have deducted income taxes from excess cash flow, it is time to consult your expansion plan to determine any equipment purchases required in future or purchases of inventory. Those are then added to the spreadsheet to determine if there is any cash available to pay owners. I would leave your compensation till last, understanding that if you take salary, it will affect your tax calculation.

Once all of this has been completed, you should be able to see what the excess cash or shortfall is in each period that you are projecting. This is very useful because you will be able to see at a glance when the business will require financing and you can then make decisions as to which kind of financing to seek. For example:

1. If your shortfall is temporary and due solely to a large equipment purchase, then you should consider term financing.

2. If your shortfall is not due to any capital expenditure and it is very temporary, being less than 1 year you may want to apply for a line of credit, particularly if you have a series of intermittent cash shortages followed by intermittent surpluses, as this will give your business maximum flexibility.

3. If your shortfall extends over several periods, you may want to consider either term financing or raising equity capital, which can be injected to eliminate all the shortages.

That concludes my discussion of cash flow projections. I hope that you found my series of posts useful.








2 comments:

  1. Were you involved in preparing / analyzing cash flow projections at the mid sized firm that you were a partner in?

    It appears that your stamp business in incorporated. Many start up businesses begin as proprietorships. Why did you go the corporate route right away?

    What's the next blog topic?

    ReplyDelete
    Replies
    1. You know, the funny thing is that in 21 years as a public accountant I was never asked to prepare cash flow projections. Surprising really, given how critical they are to properly managing a business.

      I am indeed incorporated. I did this right away because my business was profitable from the start. I started 5 years ago on a part time basis and was making money from the get go, but reinvesting it all. Therefore I didn't want to be taxed at the high personal rate on my profits.

      Haven't decided on the next topic. I may just write about my progress with sales and my listings. I'm open to suggestions though.

      Delete