Hi, today we study how to create a pro forma income statement of a project using a very interesting example. In most valuation analysis, constructing a pro forma income statement is a key groundwork. The reason is because the valuation procedure, whether it's for a company or for a project, almost always consists of the following three steps. First, we make a pro forma income statement of the project to estimate the operating income of the project. The operating income is also called the earning before interest and taxes, or simply EBIT. The second step is to convert from operating income to operating cash flow. We know that it is not the accounting-based income measure, but cash flows that matters in valuation. The third step is to calculate total cash flows. Just as in a firm valuation total cash flow includes operating cash flow, capital expenditure, and the change in the net working capital. For a project valuation, we additionally consider salvage value of assets. Technically, a project is not a company. Still, we attempt to make a project income statement because we are following the stand-alone principle. Here is an example of a project income statement. It's not different from an income statement for a company at all. We start with revenue and we'll consider two different types of costs, operating cost and depreciation. Then we get the operating income or EBIT. Finally, we subtract the taxes to get the net income. Okay, then how do we determine the annual depreciation cost of a project? If we assume that, we simply use the straight-line method, the annual depreciation, D, will be the initial value of assets, minus the salvage value of assets, divided by the length of the project, measured in years. Let's take an example. The initial value of the equipment is $1000.00. It is assumed that the salvage value of the equipment is zero. If the project's life is five years, what is the depreciation cost each year? It is $200. You can easily check that if the asset is depreciated by 200 every year for the next five years. Then, the net book value of the asset at the end will be zero. Here is our mission in this module. We'd like to make an investment decision by analyzing this project described right here. Today we focus on making a pro forma income statement using the given information. Your company has just developed a bubble gum that enable chewers to fly momentarily. And you have already spent $5,000 while developing the product. You need to invest $6,500 today to build the production facility, this must be the required initial investment. And the project has the life of six years. The facility is depreciated using the straight-line method, and the book sales value will be $500 at the end of the project. However, we further assume that, the actual resale value of the facility is expected to be $1,000 at the end of year six. Also, to produce this special bubble gums, you have to give up another project, where you just produce and sell normal bubble gums. Which has the present project value of $1,000. Sounds like an opportunity cost to me. Next, we are also given information on operations. In the first year of operation, year 1, the sales volume will be 500. The annual sales volume is expected to grow by 5% a year. The unit price will be $15.00. Each year the operating cost will be 60% of revenue in that year. Also the annual required net working capital is expected to be 15% of revenue in the same year. Finally, we are told that the tax rate is 25% and the appropriate discount rate is 20%. Now let's make a valuation model in an Excel spreadsheet. It is always a good habit to start with making an assumption table, and then to let formulas in cells in the income statement and cashflow table reference those numbers in the assumption table. Whenever possible, a formula should be used rather than entering numbers directly in income statement or cash flow table. Doing so, you will not only minimize errors, but also make your model flexible. Note that I have included the value of the alternative project, which had to be given up because of this project, as the opportunity cost. Also note that development cost of $5,000 is not considered in this analysis because that is a sunk cost. Okay, our goal in this lecture is to complete the pro forma income statement of this project. Year zero is when the initial investment is made. The operation will begin starting from year one. What is the expected revenue in year one? To compute revenue we will multiply sales volume by unit price. $7,500 revenue in year one. We expect that revenue will grow by 5% each year. To compute revenue in year two, we'll need to multiply the previous year's revenue in cell C11 by 1 plus the growth rate, which can be found in the assumption table in cell B4. Note that I used the absolute reference for cell B4. Since we froze cell B4, we can simply copy the formula in cell D11 to the right. In other cells in row 11, the formulas will consistently multiply the previous year's revenue by 1 plus the growth rate which is in cell B4. It is assumed that operating costs each year will be 60% of the revenue. So, we multiply revenue in the same year, cell C11, by the ratio of cost to revenue in cell B5. Again, we use the absolute reference for cell B5. We'll again copy the formula in this cell, to the right to complete row 12. Since we assume that the straight line method will be used, the annual depreciation cost will be determine by the initial investment, the book's sales value, and the length of the project, which is six years. As we divide 6500 minus 500 by 6 we get the annual depreciation cost of $1000. As you copy the formula to the right, you will get the same depreciation cost for other years too. In fact, this is what we should find under the straight line method. Next, the operating income or EBIT is calculated as revenue minus operating cost minus depreciation. Once you get the operating income in year one, you can simply copy to the right to complete the operating income calculation. Then we pay taxes and it's determined by operating income times the tax rate which can be found in the assumption table. Again, note that cell B6 is fixed in this formula. We'll pay taxes of $500 in year one. Taxes in other years can be calculated easily, too. Now we can finally get the net income of the project. Because it's just the difference between the operating income, C14, and taxes, C15. So we've got the net income in year one, and by copying the formula to the right, we get net income estimates of all years from one to six. And that completes our pro forma income statement of this project. The next lecture we'll study the next step, the cash flow analysis.
