In the previous course, you learned financial statement analysis and how to make estimate of future financial status. In this course, you are going to learn capital budgeting. That is, how to make an investment decision. You would like to select the best project among various projects you can take. Then, you need to know the criteria. In this course, you are going to learn investment decision criteria such as NPV and IRR, which are most popular decision rules. Using financial analysis and discounted cash flow method, you can make pro forma financial statement and estimate project cash flows. Then, you apply investment criteria to determine whether to invest or not. After learning how to apply NPV and IRR method to investment decision, you are going to learn how to evaluate NPV estimate and scenario, what-if analyses and break-even analysis. In addition to NPV and IRR, you are going to learn Payback period method and Profitability method to determine whether to invest or not when there is a political risk or capital rationing.
2.2 Pro Forma Income Statement
Loading...
From the course by Yonsei University
Applying Investment Decision Rules for Startups
22 ratings
Yonsei University
22 ratings
Course 4 of 5 in the Specialization Valuation and Financial Analysis For Startups
From the lesson
Project Analysis
In this module, you will learn how to estimate project cash flows using Excel. Overall, project cash flow analysis is similar to firm-level cash flow analysis. However, there also exist some unique features of a project cash flow analysis, which requires us to study the project cash flow principles.
Meet the Instructors
Saeyeul ParkAssistant Professor
School of Business
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.
Explore our Catalog
Join for free and get personalized recommendations, updates and offers.
Coursera provides universal access to the world’s best education,
partnering with top universities and organizations to offer courses online.
© 2018 Coursera Inc. All rights reserved.
