Calculating Operating Cash Flow in Excel

Calculating free cash flow is useful for investors and lenders to evaluate the success of a company. To create an Excel spreadsheet to calculate operating cash flow, first merge the first row of cells together (between columns A to N). This row will serve to title this document, such as "Cash Flow 2019-2020." Repeat the same step for the second row; this row serves to write the name of a business.

Using Excel For Cash Flow

In cells B4 through M4, put one month of the year within each starting with the beginning of the tax year. In A5, type "Opening Balance." In A7 through A12, type the following: "Money In" (A7), "Owner's Capital" (A8), "Bank Loans" (A9), "Sales" (A10), "Other" (A11), and "Total Money In" (A12). In A12 through M12 put a border at the top. These cells will represent the money coming into the business.

Modify the margins of column A to comfortably fit all of the words in these cells. In A14 type in "Money Out," which will account for all of the business' costs. Type the following in the designated cells: "Loan Repayments" (A16), "Goods for Resale" (A18), "Equipment" ( A19), "Advertising" (A21), "Website" (A22), "Merchant" (A23), "Postage" (A24) "Stationary" (A25), and "Total Money Out" (A27). Add necessary expenses in columns B through M.

For A27 through M27, put a border at the top. Highlight all of the cells between B5 to M27, click format cells, go to Numbers, and then Accounting. Format it so that the cells' values have two decimal places. In B12, put in "=SUM(B8:B11)" and press enter. Drag this formula across for each month. In B27, input "=SUM(B16:B26)" and press enter. Drag this formula across the Excel sheet for each month. In A29, type in "Closing Balance." In cell B29, type in the following formula: "=B5+B12-B27." Drag this formula across for each month (to cell M27).

Lastly, type in all of the values that are specific to your business for each month. The closing balance row represents the operating cash flow and will also indicate a company's budget.