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

Chapter 13: Applying Excel: Excel Worksheet (Part 1 of 2)
Download the Applying Excel form and enter formulas in
all cells that contain question marks.
For example, in cell C22 enter the formula "= B10".
Note: The present value factors could be computed using the
built-in Excel function PV, but we recommend using the formulas in
Appendix 13B.
Verify that your worksheet matches the example in the text.
Check your worksheet by changing the discount rate to 10%. The
net...

Oakmont Company has an opportunity to manufacture and sell a new
product for a four-year period. The company’s discount rate is 16%.
After careful study, Oakmont estimated the following costs and
revenues for the new product: Cost of equipment needed $ 250,000
Working capital needed $ 82,000 Overhaul of the equipment in year
two $ 8,000 Salvage value of the equipment in four years $ 11,000
Annual revenues and costs: Sales revenues $ 380,000 Variable
expenses $ 185,000 Fixed out-of-pocket...

Oakmont Company has an opportunity to manufacture and sell a new
product for a four-year period. The company’s discount rate is 18%.
After careful study, Oakmont estimated the following costs and
revenues for the new product:
Cost of equipment needed
$
260,000
Working capital needed
$
87,000
Overhaul of the equipment in year two
$
10,500
Salvage value of the equipment in four years
$
13,500
Annual revenues and costs:
Sales revenues
$
430,000
Variable expenses
$
210,000
Fixed out-of-pocket...

Oakmont Company has an opportunity to manufacture and sell a new
product for a four-year period. The company’s discount rate is 17%.
After careful study, Oakmont estimated the following costs and
revenues for the new product:
Cost of equipment needed
$
165,000
Working capital needed
$
67,000
Overhaul of the equipment in year two
$
10,000
Salvage value of the equipment in four years
$
13,000
Annual revenues and costs:
Sales revenues
$
320,000
Variable expenses
$
155,000
Fixed out-of-pocket...

Oakmont Company has an opportunity to manufacture and sell a new
product for a four-year period. The company’s discount rate is 17%.
After careful study, Oakmont estimated the following costs and
revenues for the new product:
Cost of equipment needed
$
165,000
Working capital needed
$
67,000
Overhaul of the equipment in year two
$
10,000
Salvage value of the equipment in four years
$
13,000
Annual revenues and costs:
Sales revenues
$
320,000
Variable expenses
$
155,000
Fixed out-of-pocket...

Problem 13-18 Net Present Value Analysis [LO13-2]
Oakmont Company has an opportunity to manufacture and sell a new
product for a four-year period. The company’s discount rate is 16%.
After careful study, Oakmont estimated the following costs and
revenues for the new product:
Cost of equipment needed
$
170,000
Working capital needed
$
68,000
Overhaul of the equipment in year two
$
12,000
Salvage value of the equipment in four years
$
16,000
Annual revenues and costs:
Sales revenues
$...

Problem 13-18 Net Present Value Analysis [LO13-2]
Oakmont Company has an opportunity to manufacture and sell a new
product for a four-year period. The company’s discount rate is 17%.
After careful study, Oakmont estimated the following costs and
revenues for the new product:
Cost of equipment needed
$
165,000
Working capital needed
$
67,000
Overhaul of the equipment in year two
$
10,000
Salvage value of the equipment in four years
$
13,000
Annual revenues and costs:
Sales revenues
$...

Problem 13-18 Net Present Value Analysis [LO13-2]
Oakmont Company has an opportunity to manufacture and sell a new
product for a four-year period. The company’s discount rate is 17%.
After careful study, Oakmont estimated the following costs and
revenues for the new product:
Cost of equipment needed
$
165,000
Working capital needed
$
67,000
Overhaul of the equipment in year two
$
10,000
Salvage value of the equipment in four years
$
13,000
Annual revenues and costs:
Sales revenues
$...

Lander Company has an opportunity to pursue a capital budgeting
project with a five-year time horizon. After careful study, Lander
estimated the following costs and revenues for the project: Cost of
equipment needed $ 250,000 Working capital needed $ 62,000 Overhaul
of the equipment in two years $ 19,000 Annual revenues and costs:
Sales revenues $ 370,000 Variable expenses $ 190,000 Fixed
out-of-pocket operating costs $ 84,000 The piece of equipment
mentioned above has a useful life of five years...

ITS AN EXCEL PROBLEM, PLEASE ATTACH SCREENSHOT OF EXCEL OR
EXPLAIN THE EXCEL FORMULAS YOU USED
Suppose a company is about to start
the following project, where all the dollar figures are in
thousands of dollars. In year 0, the project requires a fixed cost
of $12,000. The fixed costs is depreciated on the straight-line
basis over five years, and there is a salvage value of $1,500 in
year 5. The sales generated in years 1-5 are estimated to be...

ADVERTISEMENT

Get Answers For Free

Most questions answered within 1 hours.

ADVERTISEMENT

asked 11 minutes ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 1 hour ago

asked 2 hours ago

asked 3 hours ago

asked 3 hours ago

asked 3 hours ago

asked 4 hours ago