Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. I will show you how to create a real estate cash flow model in Excel in this article.
Introduction to Cash Flow Statement
A cash flow statement is a financial statement that describes the inflows and outflows of cash over a certain period for a specific organization or company. It usually shows how any change in the balance sheet affects the cash and cash equivalents.
All the inflows a company receives while operating are described in that statement and all the corresponding expenses are also considered throughout a specific period.
The statement provides all the data to visualize the overall profit or loss a company is making. Remarkably, the net amount of cash at the end of the concerning time is the beginning balance for the next period. So, from the statement, the company gets the idea of what approach should be made to overcome the loss and what change should be made to increase the cash every time it runs the operation.
Now, I will discuss how to create a real estate cash flow model in Excel. I have divided the entire process into 8 steps. Let’s see these steps one by one.
Step 1: Preparing the Required Format to Create a Real Estate Cash Flow Model
The first step is to prepare a format of the cash flow statement.
This is a cash flow statement format. The amounts will be calculated from the balance sheet and the income statement of the company.
Step 2: Adjusting Depreciation Expense
Now, I will put the amounts one by one. These values will come from financial statements like the balance sheet and the income statement. But here, to keep things simple, I am going to use some random values.
First of all, we will consider the operational activities.
Let’s assume that the net income is $32000 and the depreciation expense is $14,500.
Step 3: Measuring Change in Inventory
Now comes the change in inventory. The increase in inventory is $7,000. Since inventory is an asset, an increase in inventory will outflow the cash. So we have to use a negative sign here.
- How to Calculate Payback Period with Uneven Cash Flows
- Calculate Incremental Cash Flow in Excel (2 Examples)
- How to Create Cash Flow Projection Format in Excel
- Calculate Present Value of Future Cash Flows in Excel
- How to Prepare Daily Cash Flow Statement Format in Excel
Step 4: Adjusting Accounts Receivable
Now, we have to adjust the accounts receivable. Let’s assume an increase of $16,000. Similar to inventory, accounts receivable are an asset. So, an increase in accounts receivable will outflow the cash.
Step 5: Calculating Change in Accounts Payable
Next, I will calculate the change in accounts payable. This is a liability. That means an increase in accounts payable will increase the cash flow.
Let’s further assume that the increase is $9,000.
Step 6: Calculating Net Cash Flow from Operational Activities
- Go to D14 and write down the following formula
- After that, press ENTER to get the output.
- How to Apply Discounted Cash Flow Formula in Excel
- Calculate Payback Period in Excel (With Easy Steps)
- How to Calculate IRR in Excel for Monthly Cash Flow (4 Ways)
- Calculate Cumulative Cash Flow in Excel (with Quick Steps)
- How to Calculate Future Value of Uneven Cash Flows in Excel
Step 7: Measuring Cash Flow from Investment and Financial Activities
The next step is to measure the cash flow from the investing and financial activities. This will include all the activities related to finance and investment. These activities vary depending on the nature of the industry and the organization.
Let’s assume that the cash flow from the investing activities is $15,000 and the cash flow from the financial activities is $13,000
Step 8: Calculating Cash Flow
Finally, I will calculate the net cash flow of the organization for the specific period. This will be the arithmetic sum of the
- Net cash flow from the operating activities.
- Net cash flow from the investing activities.
- Net cash flow from the financial activities.
To calculate it,
- Go to D18 and write down the following formula
- Then, press ENTER to get the output.
Things to Remember
- If an asset increases, cash outflow occurs.
- The information used in cash flow statements comes from the balance sheet and income statements.
Download Practice Workbook
Download this workbook and practice while going through the article.
I have explained how to create a real estate cash flow model in Excel in this article. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.
- How to Create a Cash Flow Waterfall Chart in Excel
- Draw a Cash Flow Diagram in Excel (with Easy Steps)
- Calculate Discounted Cash Flow in Excel (with Easy Steps)
- How to Calculate Discounted Payback Period in Excel
- Calculating Payback Period in Excel with Uneven Cash Flows
- How to Forecast Cash Flow in Excel (with Detailed Steps)
- Create Weekly Cash Flow Statement Format in Excel