2

>individual Report Example

year’s project

. Senario Analysis

Criteria

Base Worst Best

0%
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Probabilities

($)

Microsoft Office User: Microsoft Office User:
a similar size of investment from a competitor or industry (with the citation on research and assumptions)

10,000

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 5 5 5

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 2,000 2,000 2,000

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

3,000

/ unit (initial year)

Microsoft Office User: Microsoft Office User:
reasonable expected sales price per unit, expected number of goods sold (with the citation on research and assumptions)

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 15

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 1,000

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 40%

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Analysis

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 4,000

3,500
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

included)

5%
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 5% 5% 5% Selling price

Inflation

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 3% 3% 3%

Microsoft Office User: Microsoft Office User:
Industry average cost of capital (with the citation on research and assumptions)

12%

12%

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 17% 17% 17%

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

15% 15% 15%

rate

35%
Microsoft Office User: Microsoft Office User:
assumptions and research with the citation 35% 35% 35% Discount rate (WACC)

0 1 2 3 4 5

Investment

NPV

0 1 2 3 4 5

Microsoft Office User: Microsoft Office User:
assumptions and research with the citation

Deviation

Initial Investment NPV

Tax

– 0 Deviation

Depr.

0 1 2 3 4 5

NOWC

0 1 2 3 4 5 – 0

Free Cash Flows

Case. Blooper Industry

5

1

0

1. Inputs

Base

Worst

Best

6. Project Valuation & Don't use plagiarized sources. Get Your Custom Essay on Finance (excel functions) Just from $13/Page Order Essay

Investment

Final

Probabilities

50% Microsoft Office User: Microsoft Office User: assumptions and research with the citation

20% Microsoft Office User: Microsoft Office User: assumptions and research with the citation

3

Initial Investment

10,000

Microsoft Office User: Microsoft Office User: assumptions and research with the citation

10,500 Microsoft Office User: Microsoft Office User: assumptions and research with the citation

9,500 Microsoft Office User: Microsoft Office User: assumptions and research with the citation

Disc. Payback Period

useful life of the invested assets (years)

5 Microsoft Office User: Microsoft Office User: assumptions and research with the citation

Net Present Value

Salvage value of the fixed asset ($)

2,000

IRR

Sales of the fixed assets, t=5 ($)

3,000

2,500 Microsoft Office User: Microsoft Office User: assumptions and research with the citation

3,500 Microsoft Office User: Microsoft Office User: assumptions and research with the citation

PI

Selling price

15

1

4

16 Microsoft Office User: Microsoft Office User: assumptions and research with the citation

Number of units sold a year

1,000

900 Microsoft Office User: Microsoft Office User: assumptions and research with the citation

1,100 Microsoft Office User: Microsoft Office User: assumptions and research with the citation

Variable Cost (% of sales)

40%

4

5%

35%

7. BEP

NPV

Initial fixed Cost ($)

4,000

4,500 Microsoft Office User: Microsoft Office User: assumptions and research with the citation

Number of Units sold a year

Growth rate (

Inflation

3%

Variable Cost (%)

Discount rate (WACC)

12%

13% Microsoft Office User: Microsoft Office User: assumptions and research with the citation

Accounts Receivable, as fraction of sales

17%

Inventory as fraction of following years’ expenses

15%

8. Restricted 4 years DPP Analysis

Tax

Growth Rate

2. Capital Expenditure

Fixed Cost

Sales of fixed asset

Tax on gain of sale

9. Sensitivity Analysis

Cash flows in Capital Expenditure

Selling Price

3. Operating cash flows

Deviation

– 0

Revenue

VC

FC

Depr.

EBIT

Deviation Microsoft Office User: Microsoft Office User: assumptions and research with the citation

Cash flows in Opr.

4.

NOWC

Cash flows of Changes in NOWC

5.

Free Cash Flows

PV of FCFs

Cumulative PV of FCFs

FIN325 Corporate Finance

.

Individual Project Assignment (30%).

Investment Project & Project Analysis.

One of main highlights of the course for the midterm is valuing an investment project and analyzing it. Students will be able to perform the procedure of creating a spreadsheet for a hypothetical project, evaluate it with four investment criteria, and demonstrate breakeven analysis, sensitivity analysis, and scenario analysis.

This assignment is very important in that students will face this type of interview questions in hiring procedure and that students planning their own business must provide investors with the investment project spreadsheet to propose a funding. Please carefully read the instruction to get a high grade and to prepare your future.

A. Instruction

1. Students will create a hypothetical but realistic project just like United Pigpen (Excel Exercise) case and submit Investment proposal in Word file and Spreadsheet in Excel file.

2. Word file MUST include: (1) Project description (2) List of Input variable assumptions (3) Investment proposal (Please use the Word format below.)

3. Excel file MUST include: (1) Input variable assumptions in each cell using Note (2) FCFs spreadsheet model (3) Investment criteria (4) Breakeven analysis (5) Sensitivity analysis (6) Scenario analysis.

a. You may use Spreadsheet from Excel Exercise example, which is a basic format, but students should develop their own spreadsheet model beyond the example in order to get a higher grade.

b. Any other project spreadsheet from other websites or Google is acceptable but MUST include all the required work mentioned above.

B. Grading Criteria

1. Must submit Both Word file and Excel file.

2. Sharing Excel model, project description, or list of the assumptions with others will result in Zero grade both giver and receiver. (Turnitin over 50%). This is an alternative of Midterm Exam.

3. Reduction criteria. (Refer to the rubrics for the specific point reduction.)

a. Just using the same Excel Example model without development won’t get full credit.

b. Missing or Sharing the requirements on the Word file.

c. Missing or Sharing the requirements on the Excel file.

d. Failure of connecting Input variables all the way through spreadsheet. All the values in spreadsheet MUST be connected to input variables. Don’t put a hard number anywhere in spreadsheet except Input variables. (That’s the most serious failure of spreadsheet in the real business! Investors or your boss will try to change the variables to see how the investment criteria change.)

C. Due Date

1. April 5th, 11:59PM, on Sunday

2. Late Policy by the school rule.

a. 10% additional reduction for any late assignment.

b. Late assignments will be accepted up to two days past the due date.

c. Following this 2-day grace period, there will be no marks given. 

*Remove the instruction part above to avoid plagiarism.

—————————————————————————————————————————–

FIN325 Corporate Finance.

Individual Report Assignment (30%).

Investment Project & Project Analysis.

Name:

1. Project Description. (Strongly recommended to keep revising along with creating spreadsheet in Excel. Must be realistic based on research.)

e.g. United Pigpen is considering a proposal to manufacture high-protein hog feed. The project would make use of an existing warehouse, which is currently rented out to a neighboring firm. The next year’s rental charge on the warehouse is $100,000, and thereafter, the rent is expected to grow in line with inflation at 4% a year. In addition to using the warehouse, the proposal envisages an investment in plant and equipment of $1.2 million….

2. List of the input variable assumptions. (Why should be this amount? Must be realistic based on research. All input assumptions MUST be described here in Word and in a cell with Note on Excel Spreadsheet)

e.g. United Pigpen case

Input variables

a. Investment $1.2 million: a similar size of investment from a competitor or industry (assumptions and research with the citation)

b. Revenue $4.2 million: reasonable expected sales price per unit, expected number of goods sold (assumptions and research with the citation)

c. Cost of Capital 12%: Industry average cost of capital (assumptions and research with the citation)

.
.
.

*Please research and complete just one or two variables every day as if you’re planning your own project. You will be enjoying this assignment. (You have plenty of time to complete until the due date, but clock is ticking. Work on just about an hour every day and start right now to avoid procrastinating and cramming.)

3. Investment Proposal. (Why investors or your company should invest)

e.g. Blooper Industry case

Conclusion with key points.

Results of DDP (how the project shortens the payback)

NPV (how much value expected)

IRR (how much profitable than cost of capital)

PI (how much profitable than initial investment)

Break Even Analysis (how many units to sell)

Sensitivity Analysis (which variable is most sensitive)

Scenario Analysis (considering risks)

And other comments to propose the investment.

2

>

Project

interaction

,

, PI, PP, DPP

2

C

IRR

0

00

%

3

%

50

-350

3.83

-350 23.83

0 1 2 3 WACC NPV IRR Profitability Index Payback Period Discounted payback period

Free Cash Flows

25

7%

%

Cumulative Payback -375 -350

PV of CF -375

Cumulative Disc. Payback -375

57.94

%

, and given the data listed below, when should you purchase the computer?

10% 0 1 2 3 4 5

70 70 70 70 70

5

enefit at purchase

25

37

20

7

3

” method.

Cost of Capital

6% 0 1 2 3

costs

-4 -4 -4

)

-6

)

Machine X

6% 3 ? 25.69 0

rate nper pmt (EAC) pv fv

6% 2 ? 21.00 0

Cost of Capital

9% 0 1 2 3 4

-15

5.2

Project B

Project A rate nper

pv fv

9% 4 ?

0

Project B rate nper pmt (EAA) pv fv
9% 3 ?

0

per year to operate.

but is much more efficient (only $8,000 per year in operating costs)

Cost of Capital

6% 0 1 2 3 4 5 NPV

($,thousand)

-8 -8 -8 -8

)

($,thousand)

-12

)

t

Not

New rate nper pmt (EAC) pv fv
6% 5 ? 58.70 0
Old rate nper pmt (EAC) pv fv
6% 2 ? 22.00 0
Equivalent Annual Cost 0 1 2 3 4 5

0

-13.93 -13.93 -13.93 -13.93

-12 -12

Project Interactions.
			N	PV					IRR
Q. You have two proposals to choose between. The initial proposal has a cash flow that is different than the revised proposal. Using IRR, which do you prefer									?
Initial		Projects																										0																1																					3		W			A			C				NPV		Profitability Index		Payback Period		Discounted payback period
	Free Cash Flows		-3								5																4					7		23.				8	14.2							9	1.07	0.88	0.94
	Cumulative Payback			-350
				PV of CF		37
	Cumulative Disc. Payback
Revised Projects
			-375			25	475		57.94	12.5									6	1.15	2.68	2.85
-325	150
23.36	21.84	387.74
-351.64	-329.				80
Problem 1. The investment Timing Decision
You may purchase a computer anytime within the next five years. The computer will save your company money only for the year when you purchase
and the cost of computers continues to decline. If your cost of capital is											10
			Cost of Capital
Savings						70
– Cost	-50				-4	-40	-36	-33	-31
=				B		20	30	34	39
PV of Benefit	22.73	24.79	25.54	2	5.2	24.22
Invsetment Timing (year)
Problem 2. The choice between long and short-lived equipment
EX 1. Choosing Lowest Annual Cost.
Given the following costs of operating two machines and a 6% cost of capital, select the lower cost machine using “	Equivalent Annual Cost
	NPV of Cost
	Machine X		-15	(	25.69
Machine Y costs	-10		-6	(	21.00
Lower cost Machine: X or Y
Machine I									rate									nper							pmt (EAC)									pv									fv
($9.61)
Machine J
($11.45)
EX 2. Choosing Highest Annual Cash Flows of the projects.
Select one of the two following projects based on highest Value “Equivalent Annual Annuity” (r = 9%).
NPV of Project
						Project A	4.9	5.9	6.2	2.82
							Project B	-20	8.1	8.7	10.4	2.78
Higher EAA project: A or B
	pmt (EAA)
(2.82)
$0.87
(2.78)
$1.10
Problem 3. When to replace an old machine, calcualating PV of operating cost
Q. You are operating an old machine that will last 2 more years before it gives up the ghost. It costs $1				2,000
You can replace it now with a new machine that costs $2					5,000
and will last for 5 years. Should we replace the machine now or stick with it for a while longer? The opportunity cost of capital is 6%
					New	-25					-8	(	58.70
			Old				-12	(	22.00
Replacement or			No
($13.93)
($12.00)
New ($,thousand)					-13.93
Old ($,thousand)

NPV & PI

Project A Project B

0

-$200

1 80

2 80 100
3 80 100
4 80

11%

Projects

PV

Project A

Project B

or No

Yes

Relationship between NPV and PI
The following are the cash flows of two projects:
Is the project with the highest profitability index also the one with the highest NPV?
						Input variables:
Year
	-$200
		100
Cost of capital		11%
				Solution and Explanation:
Profitability index
1.2410	$248.20
1.2219	$244.37
High PI/high NPV?			Yes
Notes:
The project with the higher PI must also have the higher NPV.

IRR &

Discount rate

, but it will increase the firm’s cash flows by

a year for each of the next 8 years.

Input variables:

$4,000

8

9%

0 1 2 3 4 5 6 7 8

-$20,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000 $4,000

Solution and Explanation:

NPV

9%

Yes

14%

No

IRR

Relationship between IRR & Discount rate
A new computer system will require an initial outlay of	$	20,000									$4,000
	Initial investment		–	$20,000	(input as a negative)
Annual cash flow
Years
Discount rate 1
Discount rate 2		1		4%
		Year:
Cash flow:
a. Calculate the NPV and decide if the system is worth installing if the required rate of return is 9%. What if it is 14%?
	Discount Rate	Installing? Y/N
$2,139.28
-$1,444.54
b. How high can the discount rate be before you would reject the project?
11.81%

NPV & DDM

NPV & DDM

to install, will generate a perpetual growing stream of cash flows. Cash flow at the end of the first year will be

, and cash flows in future years are expected to grow indefinitely at an annual rate of

. The discount rate for this project is 10%

Input variables:

Initial investment

5000 5000 5000 5000 5000 5000

$5,000

5%

Discount rate 10%
NPV $20,000
IRR

Growth Enterprises believes its latest project, which will cost	$80,000		$5,000	5%
-$80,000	Input as a negative							5000
Year 1 cash flow
Cash flow growth rate
a. If , what is the project NPV?
PV of Future Cash Flows	$100,000
b. What is the project IRR?
11.25%
Note!
IRR = r = (CF1/Initial Investment) + g
based on DDM model. PV = D1/(r-g)

NPV & IRR

Input variables:

Project 0 1 2

Project A

21,000

Project B

33,000

Discount rate 10%

Solution and Explanation:

IRR
Project A

Project B

Project A

Project A or B

Project B

NPV vs. IRR
Consider projects A and B:
NPV @ 10%
				(30,000)		2															1,000	6,446
(50,000)		3									3,000	7,273
a. Calculate IRRs for A and B.
25.69%
20.69%
b. Which project does the IRR rule suggest is best?
	Project A or B
IRR selection
c. Which project is really best?
Best selection

PP vs. DPP

Input variables:

Projects 0 1 2 3 4
A

1,000 1,000 3,000

B

– 0 1,000 2,000 3,000

C (5,000) 1,000 1,000 3,000 5,000

3

Discount Rate 10%

Solution and Explanation:

Project

Year: 0 1 2 3 4

A 3

(5,000) 1,000 1,000 3,000 – 0

(5,000)

– 0 – 0

B 2 CFs (1,000) – 0 1,000 2,000 3,000
Cumulative CF (1,000) (1,000) – 0 2,000 5,000
C 3 CFs (5,000) 1,000 1,000 3,000 5,000
Cumulative CF (5,000) (4,000) (3,000) – 0 5,000

Project Payback period Year: 0 1 2 3 4

A

CFs (5,000) 1,000 1,000 3,000 – 0

PV of CF (5,000)

– 0

(5,000)

(1,011)

B

CFs (1,000) – 0 1,000 2,000 3,000

PV of CF (1,000) – 0 826

Cumulative PV (1,000) (1,000)

C

CFs (5,000) 1,000 1,000 3,000 5,000

PV of CF (5,000) 909 826 2,254

Cumulative PV (5,000) (4,091) (3,264) (1,011)

Project A, B, C

Accept projects B

False

Payback Period vs. Discounted Payback Period
Here are the expected cash flows for three projects:
the opportunity cost of capital is 10%
															(5,000)												– 0
							(1,000)
Cutoff years
a. What is the payback period on each of the projects?
	Payback period
					CFs
		Cumulative CF		(4,000)		(3,000)
b. If you use a cutoff period of 3 years, which projects would you accept under payback period criteria?
	Project A, B, C
	Accept projects	A,B,C
c. What is the payback period on each of the projects?
longer than 4 years
	909			826		2,254
		Cumulative PV		(4,091)		(3,264)			(1,011)
2.12
1,503	2,049
(174)	1,329	3,378
3.30
3,415
2,405
d. If you use a cutoff period of 3 years, which projects would you accept under discounted payback period criteria?
e. “Payback gives too much weight to cash flows that occur after the cutoff date” True or	False
True/False

EAC

Lease

vs.

Buy

annually. It can instead buy a truck at a cost of $80,000,

. The truck will be sold at the end of 4 years for $30,000.

Input variables:

4

$30,000

$80,000

$10,000

$30,000

Discount rate 10%

Solution and Explanation:

Buy

0 1 2 3 4 NPV of Cost

Lease (30,000) (30,000) (30,000) (30,000)

Buy

(10,000) (10,000) 20,000

rate nper pmt (EAC) pv fv

Buy 10% 4 ?

0

Equivalent Annual Cost.
A firm can lease a truck for 4 years at a cost of		$30,000
with annual maintenance expenses of	$10,000
What is the equivalent annual cost of buying and maintaining the truck if the discount rate is 10%?
Lease/Truck life in years
Lease annual cost
Cost to buy truck
Truck annual cost
Truck sale ending sale price
PV of costs of BUY	(91,208.3)
Equivalent annual cost of BUY	(28,773.5)
Lease or buy
Lease or Buy
(95,096)
(80,000)			(10,000)	(91,208)
Buy EAC
91,208.25
($28,773.54)

EAC New vs. Old

a year.

Input variables:

2

$5,000

$20,000

10

$2,000

4%

0 1 2 3 4 5 6 7 8 9 10 NPV
New

(2,000) (2,000) (2,000) (2,000) (2,000) (2,000) (2,000) (2,000) (2,000)

)

Old (5,000) (5,000)

New rate nper pmt (EAC) pv fv

4% 10 ? 36,222 0

Yes

0 1 2 3 4 5 6 7 8 9 10 NPV

New (20,000) (2,000) (2,000) (2,000) (2,000) (2,000) (2,000) (2,000) (2,000) (2,000) (2,000)

)

Old (5,000) (5,000)

New rate nper pmt (EAC) pv fv

12% 10 ? 31,300 0
Equivalent annual cost of the new

No

EAC: New vs. Old
A forklift will last for only 2 more years. It costs $5,000 a year to maintain. For $20,000 you can buy a new lift that can last for 10 years and should require maintenance costs of only	$2,000
Old lift life in years
Annual maintenance old lift
Cost new lift
New lift life in years
Annual maintenance new lift
a. Discount rate
b. Discount rate		12%
a. Calculate the equivalent cost of owning and operating the forklife if the discount rate is 4% per year.
	(20,000)																				(2,000)	(	36,222
(9,430)
	Equivalent annual cost of the new	(4,465.82)
Replace?
b. Calculate the equivalent cost of owning and operating the forklife if the discount rate is 12% per year.
(	31,300
(8,450)
(5,539.68)
Relace?

2

>Blooper

Industry

year’s project

. Inputs

. Project Valuation &

Criteria

,000

.26

5

.65

%

,000

(% of sales)

($)

rate

0 1 2 3 4 5

,000

– 0 – 0 – 0 – 0

– 0 – 0 – 0 – 0 – 0

–

(3000-

0)*35%

– 0 – 0 – 0 – 0

0 1 2 3 4 5

+

s

– 0

– Variable Cost – 0

– Fixed Cost – 0 4,000

0*(1+3% inflation)

–

(Straight Line)

– 0

1,600 1,600 1,600 1,600

00-2000)/5

=

– 0

– Tax – 0

+

– 0 1,600 1,600 1,600 1,600 1,600

= Operating Cash Flows – 0

0 1 2 3 4 5

–

– 0

=

4,721 – 0

0 1 2 3 4 5

(11,500)

(11,500)

)

																																																																																																						Case. Blooper Industry										5
								1						6	Investment
Initial Investment ($)	1								0		Disc. Payback Period									4
useful life of the invested assets (years)		Net Present Value	5,09				8
Salvage value of the fixed asset ($)		2,000		IRR	24.0				7
Sales of the fixed assets, t=5 ($)										3		PI	1.44
Selling price / unit (initial year)	15
Number of units sold a year	1,000
	Variable Cost	40%
	Fixed Cost		4,000
Growth rate (Inflation included)		5%
Inflation on Fixed cost	3%
Discount rate (WACC)		12%
Accounts Receivable, as fraction of sales at current year	17%
Inventory as fraction of following years’ expenses	15%	*Assuming Inventory (Direct Material, Direct Labor, Work In Process) sold next year
		Tax			35%
	2. Captial investment
													–		Investment in fixed assets		10																																																								– 0
					+		Sales of fixed assets	3,000
Tax of Gain on sales of fixed assets			350							=	200
	=		Cash Flow in fixed assets	(10,000)	2,650
3.		Operating Cash Flows
	Revenue	15,000	15,750	16,538	17,364	18,233
6,000	6,300	6,615	6,946	7,293
4,																120	4,244	4,371	4,502	=		400
	Depreciation										1,600	=(		100
	Profit before tax (EBIT)	3,400	3,730	4,079	4,448	4,838
1,190	1,306	1,428	1,557	1,693
	Add back Depreciation
3,810	4,025	4,	251	4,491	4,744
=17%*15000+15%*(6300+4120)
	4. Change in working capital
	Working capital	1,500	4,113	4,306	4,509		4,721	Must be zero
	Cash Flows of Changes in net Working capital	(1,500)	(2,613)	(193)	(203)	(212)
	5. Project Cash flows
	Project cash flows			(11,500)	1,197	3,831	4,049	4,279	12,116
	Present Value of projected cash flows	1,069	3,054	2,8	82	2,719	6,875
	Cumulative PV of projected cash flows	(10,431)	(7,377)	(4,495)	(1,7	76	5,099

United Pigpen

a year. In addition to using the warehouse, the proposal envisages an investment in plant and equipment of $1.2 million. This could be depreciated for tax purposes straight-line over 10 years. However, Pigpen expects to terminate the project at the end of 8 years and to resell the plant and equipment in year 8 for $400,000. Finally, the project requires an immediate investment in working capital of $350,000. Thereafter, working capital is forecasted to be

of sales in each of years 1 through 7. Working capital will be run down to zero in year 8 when the project shuts down. Year 1 sales of hog feed are expected to be $4.2 million, and thereafter, sales are forecasted to grow by 5% a year, slightly faster than the inflation rate.

s are expected to be

of sales, and profits are subject to tax at 35%. The cost of capital is 12%. What is the NPV, IRR, DPP, PI of Pigpen’s project?

6. Project Valuation & Investment Criteria

Investment

Disc. Payback Period

Revenue

Net Present Value

5% IRR

90% PI

100

4%

10

35%

400

350

10%

12%

2. Captial investment 0 1 2 3 4 5 6 7 8
– Investment in fixed assets 1,200 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
+ Sales of fixed assets 400
–

of fixed assets

– 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0

= Cash Flow in fixed assets

– 0 – 0 – 0 – 0 – 0 – 0 – 0

0 1 2 3 4 5 6 7 8

+

– 0 4,200

2

– Manufacturing cost – 0

9

–

– 0 100

– Depreciation – 0 120 120 120 120 120 120 120 120
= Profit before tax (EBIT) – 0 200

– Tax – 0

76 82

96

+ Add back Depreciation – 0 120 120 120 120 120 120 120 120
= Operating Cash Flows – 0

4. Change in working capital 0 1 2 3 4 5 6 7 8
– Working capital 350

– 0

= Cash Flows of Changes in net Working capital

563

5. Project Cash flows 0 1 2 3 4 5 6 7 8
Project cash flows

251

273

Present Value of projected cash flows (1,550)

Cumulative PV of projected cash flows (1,550)

86

United Pigpen Case. 8 year project
United Pigpen is considering a proposal to manufacture high-protein hog feed. The project would make use of an existing warehouse, which is currently rented out to a neighboring firm. The next year’s rental charge on the warehouse is $100,000, and thereafter, the rent is expected to grow in line with inflation at	4%	10%	Manufacturing cost	90%
1. Input variables ($,thousand)
	1,200	7.83
	4,200	85.80
Rev growth (%)	13.19%
Manufacture costs (% of sales)	1.06
Rent (opp. Cost)
Rent Growth (%)
Dep’n Life (years)
Tax Rate (%)
Plant Sale, t=8
WC investment, t=0
WC ongoing, t1-7, % of sales
Cost of Capital (%)
Tax of	Gain on Sale	56	=(400-(1200-120*8))*35%
(1,200)	344
3. Operating Cash Flows
Revenues	4,410	4,631	4,	86	5,105	5,360	5,628	5,910
3,780	3,	96	4,167	4,376	4,595	4,824	5,066	5,319
Rent Opportunity cost	104	108	112	117	122	127	132
217	235	254	274	294	316	339
70	89	103	111	119
250	261		273	285	298	311	326	341
420	441	463	486	511	536		563
(350)	(70)	(21)	(22)	(23)	(24)	(26)	(27)
		(1,550)	180	240	262	286	299	1,247
161	191	178	166	155	145	135	504
(1,389)	(1,198)	(1,020)	(853)	(698)	(553)	(418)

Gain on Sale after tax

3

5

35%

40,000

18,000

Gain on Sale 2,000

Cash Flows. Tax from Gain on Sales of Invested Asset
Quick Computing installed its previous generation of computer chip manufacturing equipment 3 years ago. Some of that older equipment will become unnecessary when the company goes into production of its new product. The obsolete equipment, which originally cost $40 million, has been depreciated straight-line over an assumed tax life of 5 years, but it can be sold now for $18 million. The firm’s tax rate is 35%. What is the after-tax cash flow from the sale of the equipment?
Input variables:	($ thousand, %)
Age of equipment
Initial cost		40,000
Useful life
Current market value		18,000
Tax rate
Solution and Explanation:	($ thousand)
Initial Cost (Original Cost)
Accumulated Depreciation (3years)	24,000	=40000/5*3
Book Value	16,000	=40000-40000/5*3
Sales Price
=18000-16000
Tax on Gain	700	=2000*35%
Cash flow. Gain on sale after tax	17,300	=18000-700

DDM

Dividend Model.

per share, and it is widely expected that the dividend will increase by

per year indefinitely.

?

.

Current Price

5.00

%. What must be the expected growth rate of the company’s dividends?

-5/50

Discount
Q1. Gentleman Gym just paid its annual dividend of		$3		5%
a. What price should the stock sell at if the discount rate is							15%
	Current	Price	$31.50	=3*(1+5%)/(15%-5%)
b. What price should the stock sell at if the discount rate is				12%
$	4	=3*(1+5%)/(12%-5%)
Q2. Arts and Crafts, Inc., will pay a dividend of $5 per share in 1 year. It sells at $50 a share, and firms in the same industry provide an expected rate of return of	14
Expected growth rate	4.0	0%	=1	4%

Yields

per year with the following financial data.

15%

$3

-10.00%

Yields. Income Gain and Capital Gain in DDM Model
Horse and Buggy Inc. is in a declining industry. Sales, earnings, and dividends will be all shrinking at a rate of			10%
Input data.
Annual growth rate		–	10.00%
	Required return
		DIV1
a. what is the value of a share today?
Value of a share	$12.00	=3/(15%+10%)
b. What price do you forecast for the stock next year?
		Stock price	10.800	=3*(1-10%)/(15%+10%)
c. What rate of return should you expect if you buy the stock today and sell it in one year?
Expected rate of return	15.00%			=15%
c-1. What is the income gain and the capital gain if you buy the stock today and sell it in one year?
Income gain (Dividend Yield)	25.00%	=3/12
Capital gain (Investment Yield, g)	=-10%

Growth

Growth

15% 15%

15% 10%

at year 1

.00

$1.00

Here are the forcasted data on two stocks of the year 1, both of which have discount rates of 15%:
a. What are the dividend payout ratios for each firm?
b. What are the expected dividend growth rates for each firm?
c. What is the proper stock price for each firm?
Stock A	Stock B
		Discount rate
Return on equity at year 1
	Earnings per share		$2	$1.50
Dividends per share at year 1			$1.00
a. Dividend payout ratio	50.00%	66.67%	=1/1.5
b. Expected dividend growth	7.50%	3.33%	=10%*(1-66.67%)
c.	Stock price today	$13.33	$8.57	= 1/(15%-3.33%)

PS valuation

Discount rate

$66.67 =8/12%

12%

0%

12%

– 0%

Preferred Stock Valuation.
Preferred Products has issued preferred stock with an annual dividend of $8 that will be paid in perpetuity.
		Input variables:
Annual dividend	$8.00
12.00%
a. If the discount rate is 12%, at what price should the preferred sell?
Price0		$66.67		=8/12%
b. At what price should the stock sell 1 year from now?
Price1
c. What is the dividend yield, the capital gains yield, and the expected rate of return of the stock?
Dividend yield	=8/66.67
Capital gains yield	=(66.67-66.67)/66.67
Rate of return		=12%

PE ratio

per share this year. Its

is

. What is the stock price?

$2.40

18

Price

Price Earning (P/E) Ratio
Favorita Candy’s stock is expected to earn	$	2.40				P/E ratio	18
	Data.
EPS	=2.4
P/E Ratio	=18
$43.20	=C5*C6

Growth & Multiples

& P/E,

million by selling shares of stock. Management plans to earn a

%

rate of return on equity, which is more than the 15% rate of return available on comparable-risk investments.

of all earnings will be reinvested in the firm.

200

Book value ($, million) 200 =200

24%

Return on equity (%) 12% =12%

Earnings ($, million) 24

40%

Plowback(Retention) ratio (%) 40% =40%

Dividends ($, million) 14

10%

g (%) 5%

15% =15% Discount rate (%) 15% =15%

Market value ($, million)

P/E ratio

P/E ratio

P/B ratio

P/B ratio

	Return on Equity		P/B ratio
Start-Up Industries is a new firm that has raised $		200		24		40%
a. when ROE 24%	b. when ROE 12%
	Book value ($, million)		=200
	Return on equity (%)	=24%
	Earnings ($, million)	48	=200*24%	=200*12%
	Plowback(Retention) ratio (%)		=40%
	Dividends ($, million)	29	=48*(1-40%)	=24*(1-40%)
	g (%)	=24%*40%	=12%*40%
	Discount rate (%)
	Market value ($, million)	533	=C10/(C12-C11)	141	=F10/(F12-F11)
11.11	=C13/C8	5.88	=F13/F8
2.67	=C13/C6	0.71	=F13/F6

Permanent value

per year, and you expect this growth rate in earnings and

Data.

20%

$2

Discount rate 15%

4%

DIV1

2.40

28.02

=D15*(1+C9)

Stock Price with Permanent Value
Q. Tattletale News Corp. has been growing at a rate of			20%
dividends to continue for another 3 years. If the last dividend paid was $2, the discount rate is 15% and
the steady growth rate after 3 years is 4%, what should the stock price be today?
Growth rate for Years 1-3
Last dividend paid
Growth rate after 3 years
Stock price PV0		DIV2		DIV3
	28.02	2.88	3.46		=D15*(1+C9)
=C10*(1+C9)	=C15*(1+C9)	32.67	PV3
Short-cut of PV0	=(E15*(1+C12))/(C11-C12)
=C15/(1+C11)^1+D15/(1+C11)^2+(E15+E16)/(1+C11)^3
=NPV(C11,C15,D15,E15+E16)

Holding Return

= $1 per share.

Input variables:

20%

4

5%

DIV0 $1.00

10%

?

DIV1

DIV2

DIV3

DIV4

Stock price today

10.00%

Expected Holding Period Rate of Return
Better Mousetraps has come out with an improved product, and the world is beating a path to its door. As a result, the firm projects growth of 20% per year for 4 years. By then, other firms will have copycat technology, competition will drive down profit margins, and the sustainable growth rate will fall to 5%. The most recent annual dividend was	DIV0
Initial growth rate
Initial growth rate years
	Sustainable growth rate
Discount Rate
a. What are the expected values of DIV1, DIV2, DIV3, and	DIV4
$1.2000	=C9*(1+C6)
$1.4400	=C13*(1+$C$6)
$1.7280	=C14*(1+$C$6)
$2.0736	=C15*(1+$C$6)
b. If the discount rate is 10%, what is the expected stock price 4 years from now?
Stock Price in 4 yrs (HV4)	$43.5456	=C16*(1+C8)/(C10-C8)
c. What is the stock price today?
$34.738	=NPV(C10,C13,C14,C15,C16+C19)
d. Find the dividend yield, DIV1 / P0.
Dividend yield year 1	3.45%	=1.2/C22
e. What will next year’s stock price, P1, be?
Expected Stock price at year 1	$37.012	=NPV(C10,C14,C15,C16+C19)
f. What is the expected rate of return to an investor who buys the stock now and sells it in 1 year?
Expected Return at year 1	=(37.012-34.738+1.2)/34.738

PVGO

of all earnings into the firm. Earnings this year will be $3 per share, and investors expect a 12% rate of return on stocks facing the same risks as Web Cites.

Input variables:

Return on Equity 20%

70%

Earnings per share $3
Required return 12%
Sustainable growth rate

Stock price

P/E ratio

Stock price

P/E ratio

PVGO

Present Value of Growth Opportunities (PVGO)
Web Cites Research projects a rate of return of 20% on new projects. Management plans to pay back	70%
Payback ratio
a. What is the sustainable growth rate?
6.00%	=C6*(1-C7)
b. What is the stock price?
$35.00	=3*70%/(12%-6%)
c. What is the P/E ratio?
11.67	=C15/C8
d. What would the price and P/E ratio be if the firm paid out all earnings as dividends?
$25.00	=C8/C9
8.33	=C21/C8
e. What is the present value of growth opportunities (PVGO)?
$10.00	=C15-C21