Question

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

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?

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

#### Earn Coins

Coins can be redeemed for fabulous gifts.