Building the Cash Flow Statement
Cash is king! When running a business like this — particularly a new business — you should never ever run out of cash. You may have a business that’s highly profitable, but if you can’t pay your staff next month, then you don’t have a busi- ness. For this reason, cash flow is the most important out of all the financial statements.
A cash flow statement helps to plan your business’s cash flow, which identifies and eliminates shortfalls or surpluses in cash projections. If you find your cash flow is projecting a shortfall, you need to alter the business’s financial plans in order to provide more cash. You can arrange an overdraft from the bank, adjust inventory, or take other measures that will free up some more cash. If your cash flow forecast reveals surplus or excess cash, it might mean you’re borrowing too much or that you have idle money that could be invested. The objective is to keep the business with a cash reserve that is large enough for unknown eventualities but small enough that it doesn’t waste cash sitting in a low-interest account that could otherwise be put to better use. Keeping tabs on your cash flow with a well- built and regularly updated cash flow model is imperative for avoiding cash flow problems.
Profit and cash flow don’t necessarily go hand in hand. Cash flow is actual cash coming in and out of your bank account; profit is the underlying profitability of the business. You can have a very profitable business, but if you don’t have the cash to pay wages at the end of the month, the business won’t last very long.
Often the discrepancies between cash flow and profit are due to timing differ- ences. If you were to take a large number of orders for a new product for which the customer had not paid a deposit, you would need to purchase raw materials, hire staff, or outlay other expenditure in order to fulfill that order. If you’ve sent the customer the invoice, but the terms of credit to the customer were, say, 60 days, then that means you won’t receive any payment for the goods until 60 days after the goods have been received by the customer. In the meantime, you’ve had to pay staff and possibly pay for raw materials, so cash flow is going to be a problem until that invoice is paid. Because you’ve already sent the customer the invoice, the funds will show on that month’s income statement, but the cash won’t turn up for another two months. Your income statement will look healthy, but you’ll run into problems with cash flow unless you’re able to arrange funding from elsewhere until the invoice is paid. A good financial model will help identify and mitigate these potential problems.
To model the cash flow, start with the opening cash balance, add income, and deduct outflows from payments. This will give you an idea of whether you have a surplus or a deficit for that period. If financing is needed, you’ll add that as an amount coming into the bank account, and then calculate any repayments or interest payable going forward. The closing balance of one period becomes the opening balance of the next period, like a corkscrew, as shown in Figure 10-9.
FIGURE 10-9:
Corkscrew cash flow modeling.
When calculating a cash flow forecast for the cafe model, you need to consider these differences between cash flow and profit. You’ve built an income statement already, so you can use this as a base, making a few key adjustments in order to calculate cash flow. To begin forecasting the cash flow for the new business, you need to outline the initial flows of cash when opening the business in the Pre- Open column.
Capital expenditures (CapEx) represent funds that are spent to acquire, upgrade, or replace physical assets such as property, plant, and equipment (PP&E). Capital expenditures are often used to invest in equipment for new projects or maintain
old ones. The coffee machine and furniture and fixtures that the cafe needs to open are PP&E, so the purchases of these assets are classified as capital expenditures.
Getting your starting position right is important, because it will affect the entire statement. Unlike the income statement, any changes in prior months will flow on and affect the balances in later periods because of the “corkscrew” nature of cash flow.
The starting point for the cash flow statement is the pre-open amounts in column
B. The starting cash balance (the amount you have in the bank before opening the doors) is $5,000, and this is made up of the following:
» $5,000 in purchased inventory (consumables such as cups, coff milk, and so on that you need to purchase prior to opening the café). Note this is a purchase and will, therefore, be expressed on the cash fl statement as a
negative value.
» $45,000 in CapEx for the coff machine ($10,000) and furniture and fi ($35,000). This is also cash out, so it’s shown as a negative value on the cash fl statement.
» $30,000 for the money you received from bank loan.
» $25,000 for the capital raised.
You need to calculate the pre-open by entering the amounts as just described. Although you may be tempted to type these numbers in, it’s very important that you follow financial modeling best practice by only entering data once. All these numbers have already been entered into your model, so you must access them, or “pull them through,” by linking from other parts of the model — in this case, the balance sheet. If you simply type the numbers in, this model will cease to be a fully integrated financial model, because any changes made won’t be reflected in the calculations.
Follow these steps to pull the numbers through:
-
On the IS Cash Flow worksheet, in cell B30, enter the formula =–’Balance
sheet’!K10.
This formula links cell B30 to the –$5,000 for inventory from the uses of funds on the Balance Sheet worksheet. No need to anchor the cell referencing this time because you aren’t copying this formula across.Accounts receivable and accounts payable are not material in this model, so you won’t include the calculations, but you’ll leave the lines there (rows 31 and
32) to show where they go if you want to come back later and add them in.
-
In cell B35, enter the formula =-‘Balance sheet’!K11-‘Balance sheet’!K12.
This formula links cell B35 to the $45,000 in CapEx to where you entered them on the uses of funds on the Balance Sheet worksheet. You need to add them up but because you need a negative value, you’re prefacing the formula with a minus sign after the equal sign, and deducting it rather than adding it. The formula gives the total of –$45,000.
-
In cell B38, enter the formula =’Balance sheet’!K3.
This formula links cell B38 to the $30,000 amount you expect from the bank loan to where you entered it on the sources of funds section of the Balance Sheet worksheet.
-
In cell B39, enter the formula =’Balance sheet’!K4.
This formula links to the $25,000 amount you’ve raised from where you entered it on the sources of funds section of the Balance Sheet.
-
Calculate the closing cash in cell B42 with the formula =SUM(B26:B41).
The calculated value is $5,000, which is what we expect it to be. This represents the amount of working cash your business needs to operate.
-
You need to calculate the closing cash for each month on this statement, so copy this formula all the way across the row to calculate this for the entire year.
Even though the values are now zero, they’ll update as you fi in the cash fl
statement.
Closing cash will represent the opening cash for the next month, so you need to link your opening cash amount to the balance from the prior month.
- In cell C26, enter the formula =B42.
-
Copy this formula all the way across the row to calculate this for the entire year.
The calculated value for each month is $5,000, which is not yet correct, but these cells will update as you fi in the cash fl statement.
Your net income from the income statement also represents cash infl but not all income statement items are cash expenses. Revenues, operating expenses, interest, and taxes are all cash expenses, but D&A expenses are accounting expenses that don’t represent cash outfl So, not only should you add your net income as a cash infl but you should also add back your D&A expense that you subtracted from net income earlier.
-
Link the net income in cell C28 to the net income calculated farther up the page with the formula =C20.
The calculated value is –$272.
-
Link the D&A (noncash) amount in cell C29 to the D&A expense calculated farther up the page with the formula =C13.
The calculated value is $569.
-
Copy the formulas in rows 28 and 29 across the rows to calculate for the entire year.
Because you’ve already entered the opening and closing balances into the cash fl statement, these will automatically update.
You have just completed the cash fl statement! Check your totals against Figure 10-10.
FIGURE 10-10:
The completed
cash flow
statement.
