COMPUTER APPLICATIONS IN FINANCE FIN 3450 ASSIGNMENT 5 USE EXCEL TO SHOW ALL YOUR WORK

COMPUTER APPLICATIONS IN FINANCE
FIN 3450
ASSIGNMENT 5
USE EXCEL TO SHOW ALL YOUR WORK
DUE ON 8/13/2018; Team assignment; 50 Points

Problem 1: Down under Inc. is considering a new three-year
project. The project requires an initial investment of $1.8 million and working capital investment of $200,000 (at time ‘0’) The project is estimated to generate $1.25 million in sales each year. It has costs of $500,000 per year each year.
The tax rate is 35% and the discount rate (WACC) is 12%.
Calculate the NPV of the project. Use straight line depreciation.
The project has no salvage value.
Generate three possible scenarios using the Scenario Manager in Excel using the following data:

Base Case Optimistic Case Pessimistic case
 

Sales $1,250,000 $1,500,000 $1,000,000
Costs $500,000 $400,000 $600,000
Discount rate 12% 10% 14%
Tax rate  35% 30% 40%

Initial
Investment  $1,800,000  $1,500,000 $2,000,000

Continued . . . . 

Problem 2: Given the following data for a capital budgeting project;

Acorn Inc. is considering a new three-year project. The project requires an initial investment of $5 million and an additional working capital investment of $1 million (at time ‘0’). The project is estimated to produce 2 million acorns that can be sold for $3.00/unit each year.  It has variable costs of $0.50/unit each year and fixed cost of
$1 million per year. The tax rate is 35% and the discount rate (WACC) is 10%. Use straight line depreciation. The project has no salvage value. Working capital is recovered at the end of the project.

 

Calculate the NPV of the project. 

Generate three possible scenarios and a summary sheet using the “Scenario Manager” in Excel using the following data:

  Base Case Optimistic Case Pessimistic case
Initial Investment   $5 million $4 million $6 million
Working capital $1 million $0.8 million $1.2 million
# Units sold /Year 2 million 2.5 million  1.5 million
Price per unit   $3  $4 $2
Variable cost/unit $0.5  $0.4 $0.6
Fixed cost/year $1 million $0.75 million $1.25 million
Tax rate  35% 30% 40%
Discount rate (WACC) 10% 8% 12%