NPV & IRR Function by samsond

VIEWS: 53 PAGES: 7

									                                                       1


Instructions: Blue Mesa's Agricultural Chemicals Division has just developed a new insecticide for treating
corn and soybeans. The insecticide should be much less harmful to the crops than the current chemicals on
the market and equally effective in preve

Judy Abarca, your training manager, has requested that you create a
spreadsheet showing projected cash flows for the first five years of a new
project that Blue Mesa is considering. She wants to make sure that you
know how to apply the principles from your second week of training.

For this assignment, you will use the template provided in Materials below
to create the spreadsheet that Judy Abarca has requested. Your
assignment will be graded based on the accuracy of your calculations.

Read the Blue Mesa Sales and Cost Projections provided in Materials
below and use that information as the basis for your cash flow
projections.



Sales are expected to grow 15 percent in the first year and 10 percent a year for the next four years.
Gross margin is expected to stay constant as a percentage of sales, as are SG&A expenses.
PP&E is expected to increase by $350,000 per year, with depreciation expense remaining at $375,000 per
year.
Working capital per year is estimated at 35 cents per dollar of sales.
The tax rate will remain constant at 34 percent. Interest expense remains constant at $25,000 per year.
In 2002 working capital was $2,275,000.

Solution :
                          2003             2004                2005          2006          2007
Sales                $    7,500,000   $    8,625,000       $ 8,250,000   $ 8,250,000   $ 8,250,000
Cost of Sales        $    5,025,000   $    5,778,750       $ 5,527,500   $ 5,527,500   $ 5,527,500
Gross Margin         $    2,475,000   $    2,846,250       $ 2,722,500   $ 2,722,500   $ 2,722,500
SG&A Expense         $      394,737   $      453,948       $ 434,211     $ 434,211     $ 434,211
Depreciation         $      375,000   $      375,000       $ 375,000     $ 375,000     $ 375,000
Interest Expense     $       25,000   $       25,000       $    25,000   $    25,000   $    25,000
Taxable Income       $    1,680,263   $    1,992,302       $ 1,888,289   $ 1,888,289   $ 1,888,289
Taxes                $      571,289   $      677,383       $ 642,018     $ 642,018     $ 642,018
Net Income           $    1,108,974   $    1,314,920       $ 1,246,271   $ 1,246,271   $ 1,246,271




Copyright ©2009 Cardean Learning Group LLC. All rights reserved.
nsecticide for treating
e current chemicals on




next four years.
enses.
ining at $375,000 per


t $25,000 per year.




                 2008
             $ 8,250,000
             $ 5,527,500
             $ 2,722,500
             $ 434,211
             $ 375,000
             $    25,000
             $ 1,888,289
             $ 642,018
             $ 1,246,271




            Copyright ©2009 Cardean Learning Group LLC. All rights reserved.
                                                 2 - Part One
**Note: Be sure to complete Part 2 of this Assignment using the template on sheet 2 in this workbook.



Judy Abarca, she
has requested that you assist her by comparing two projects that Blue
Mesa is considering. Specifically, she would like you to determine which
project would be more profitable by calculating present values (PV) and
analyzing the results.


Your Assignment
For this assignment, you will compute the PV of two projects under
consideration by Blue Mesa. For each project, the company is
considering two options. Ultimately, you will determine which project
would be more profitable. Your analysis will be graded based on the
accuracy of your calculations and recommendations.

When making your calculations, use annual compounding.




Part One Instructions:
The Chinese government is conducting an auction for a joint project involving oil exploration. Because of a
desire for U.S. dollars, they require the winning bidder to make an up-front, one-time payment for the rights to
join the p


Solution :

                                           Option 1: simple interest 15% annually
             Year 1   Year 2      Year 3     Year 4      Year 5      Year 6   Year 7      Year 8      Year 9       Year 10


Copyright ©2009 Cardean Learning Group LLC. All rights reserved.
$47 mil




FV =       $        54 $       61 $    68 $       75 $       82 $    89 $         96 $   103 $    110 $    118

                                    Option 2: 10% annually compounded quarterly
           Year 1     Year 2     Year 3   Year 4     Year 5    Year 6   Year 7      Year 8   Year 9   Year 10
$47 mil




FV =       $        52 $       57 $    63 $       69 $       76 $    83 $         92 $   101 $    111 $    122




Copyright ©2009 Cardean Learning Group LLC. All rights reserved.
                                              3


Your Assignment
For this assignment, you will use an Excel spreadsheet template to
compute the value of a government bond at two different market interest
rates. The template has more detailed instructions, including all the
figures you will need to complete this assignment. Your analysis will be
graded based on the accuracy of your calculations.

Use the template or a financial calculator to perform your calculations.
(If you decide to use a financial calculator, please input your calculations
into the template provided. Be sure to show your work.)



Instructions: Assume a government bond has a face value of $1,000, a coupon of 6 percent,
semiannual payments of interest, and a five-year maturity. If the market interest rate for such a bond
is 5 percent, how much is the bond worth? How much is it worth



Solution : Face value of Bond =                   $   1,000
           Coupon Rate =                      5%
           Semiannual Coupon Payment =            $     25
           Yield to Maturity =                6%

                             5% Interest
           Year 1      Year 2     Year 3      Year 4          Year 5
  $1,000
      6%


PV =            $895       $796        $702           $614        $531 Application of PV function in excel

           Data given,
           Face value of Bond =               $ 1,000
           Coupon Rate =                      8%
           Semiannual Coupon Payment =        $    40
           Yield to Maturity =                6%

                             8% Interest
           Year 1      Year 2     Year 3      Year 4          Year 5
  $1,000
      6%


Copyright ©2009 Cardean Learning Group LLC. All rights reserved.
PV =           $866       $740        $621        $509      $403 Application of PV function in excel




Copyright ©2009 Cardean Learning Group LLC. All rights reserved.
                                                                                                              4

Once again, Judy Abarca would like to use a possible new project for
Blue Mesa as an opportunity to see if you can apply what you've been
learning during your training period with her. Specifically, she would like
you to calculate the net present value (NPV) and internal rate of return
(IRR) for the new project.

Your Assignment
For this assignment, you will compute the NPV and IRR of a new project
that Blue Mesa is considering. You will work with the Excel spreadsheet
template provided below. This assignment will be graded according to the
accuracy of your calculations.


The Excel spreadsheet template provided in Materials below contains
detailed directions and figures regarding Blue Mesa's new project. Use
the template to create a cash flow diagram of the project's inflows and
outflows, as follows:


• Input the yearly project inflows
• Compute the discount factor for each year
• Compute the NPV of the project
• Using the cash flows in the template, use the IRR formula to calculate
the IRR of the project




Instructions: Blue Mesa Oil needs to launch a new production facility to reach its strategic goals. The new facility will cost $15 million to acquire. The company will use it for 35 years, at which time the company expects to sell the facility for $1.2
mi

Solution :

Cash Flow Diagram:


                                    $ 1,296,170        $ 1,752,521   $ 1,621,271     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271   $ 1,621,271   $ 1,621,271   $ 1,621,271   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########

                                           1                2              3               4              5               6              7               8              9              10              11             12              13             14            15            16            17            18           19          20          21          22          23          24          25          26          27          28          29          30          31          32          33          34          35

                          Year 0   $ 15,000,000




                                       Year 0                1             2               3              4               5              6               7              8               9             10              11             12              13             14            15            16            17          18          19          20          21          22          23          24          25          26          27          28          29          30          31          32          33          34          35
Cash Flow:                         $ (15,000,000)      $ 1,296,170   $ 1,752,521     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271     $ 1,621,271    $ 1,621,271   $ 1,621,271   $ 1,621,271   $ 1,621,271   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########   #########

Discount Factor:
(Assume Discount Rate 12.5%)                   1.000         0.889          0.790           0.702          0.624           0.555          0.493           0.438          0.390           0.346          0.308           0.274          0.243           0.216         0.192         0.171         0.152         0.135       0.120       0.107       0.095       0.084       0.075       0.067       0.059       0.053       0.047       0.042       0.037       0.033       0.029       0.026       0.023       0.021       0.018       0.016

NPV:                                 ($2,363,310) Application of NPV function in excel

IRR:                                            10% Application of IRR function in excel




Copyright ©2009 Cardean Learning Group LLC. All rights reserved.

								
To top