Question

Download the Applying Excel form and enter formulas in all cells that contain question marks.

Excel : ch_08_Noreen_4e_applying_excel_student_form.xlsx

Data
Example E
Cost of equipment needed	$60,000
Working capital needed	$100,000
Overhaul of equipment in four years	$5,000
Salvage value of the equipment in five years	$10,000
Annual revenues and costs:
Sales revenues	$200,000
Cost of goods sold	$125,000
Out-of-pocket operating costs	$35,000
Discount rate	14%
Enter a formula into each of the cells marked with a ? below
Exhibit 8-6
		Years
	Now	1	2	3	4	5
Purchase of equipment	?
Investment in working capital	?
Sales		?	?	?	?	?
Cost of goods sold		?	?	?	?	?
Out-of-pocket operating costs		?	?	?	?	?
Overhaul of equipment					?
Salvage value of the equipment						?
Working capital released						?
Total cash flows (a)	?	?	?	?	?	?
Discount factor (14%) (b)	?	?	?	?	?	?
Present value of cash flows (a) x (b)	?	?	?	?	?	?
Net present value	?
*Use the formulas from Appendix 8B:
Present value of $1 = 1/(1+r)^n
Present value of an annuity of $1 = (1/r)*(1-(1/(1+r)^n))
where n is the number of years and r is the discount rate

^ the excel part

You will use this worksheet to answer the questions

1) The company is considering a project involving the purchase of new equipment. Change the data area of your worksheet to match the following:
Data

Example E

Cost of equipment needed - $450,000

Working Capital needed- $ 35,000

Overhaul of equipment in four years- $40,000

Salvage Value of the equipment in- $40,000

Annual revenues and costs:

Sales Revenues- $440,000

Cost of goods sold- $235,000

Out-of-pocket operating costs- $65,000

A) What is the net present value of the project?

B) The internal rate of return is between what two whole discount rates (e.g., between 10% and 11%, between 11% and 12%, between 12% and 13%, between 13% and 14%, etc.)?

C) Reset the discount rate to 16%. Suppose the salvage value is uncertain. How large would the salvage value have to be to result in a positive present value?

Answer 1

B)For Internal Rate, we will calculate NPV at two different rates

NPV (10%) = $64,959 (Table)

NPV (20%) = -$55,464 (Table)

IRR = 10% + [64,959/(64,959+55,464)]*10 = 15.4 %

Hence IRR is between 15% and 16%

C) If Salvage Value is unknown, NPV @16% = -$32,027 (Table)

Value of salvage to result in positive Net flow = NPV/ PVF(16%,5th year) = $32,027/0.476 = $67,284

All amounts rounded to nearest dollar. All factors rouned to 3 decimal.
DCF = Dsicounted Cash Flows