Spreadsheets Tutorial : Build a cash flow model

Cash flow models are created from the income and balance statements that you built in the previous exercises. Let's take a closer look at their parts. The level of detail varies for cash flow statements, but they generally include operating expenses or the day to day work of the business. Investing sections involve the property and equipment a business owns, while financial includes money paid and borrowed from different sources. In this example you can see the three parts of the cash flow document and how these values might look for the year 2016. Let's look at how each piece was added. First, you can use the income statement to add the information about net income, depreciation and cash dividends. These items are selected directly from the year 2016. These cell references work the same as within a sheet including the row and column number along with the information about which sheet contains the information. The rest of the cells in this cash flow are estimated from the balance statement sheet. Here we are examining change over the last year so we want to subtract 2015 values from the same values in 2016 to see either growth positive numbers or decreases negative numbers. In parentheses you would type equals, then click on the year 2016 cell for each item. Type minus and then Click on the 2015 cell for each item. These formulas are relative so you can copy them across all three years for the sheet in this picture you can view the formulas a bit clearer. We have selected just year 2016 from the income sheets and then created a change score for the balance sheet items. The cell references include quotes around the sheet name followed by an exclamation point and then the column letter and row number. An important part of business is forecasting, meaning making predictions for the upcoming year. We can add a forecasting column to our cash flow using two formulas. First, we want to estimate next year by using the 2018 column and multiplying that by one plus a percent change. This formula means that we expect to keep last year's numbers plus a percent increase or decrease. That percent can be added to a forecast factor column. These percentages can be estimated from the previous year's knowledge or taken directly from a common size statement as we created in the previous videos. The common size percentages were added to the forecast column from the relevant line on the balance sheet as shown here. You can now create cash flow document and try to forecast the company balance for the.