Hi jetlee15!!
Lucky me!! I found the spreadsheets on the Internet at the Allegheny
College site, specifically at the "Webpub at Allegheny College", the
part of the site that hosts the home pages for students, faculty,
employees, groups, and some departments at Allegheny.
To download the files just do a right click on the spreadsheet's link
and select the appropriate command such as "Save Target As" or "Save
Link As" or something similar depending on your browser. Then specify
your download information answering to the popup boxes displayed. When
the file is downloadad in the location selected by you , you will be
able to open it with MS Excel.
The link for the first problem's spreadsheet (the one named 7.21 in
the question) is:
http://merlin.alleg.edu/employee/d/dgoldste/econ427/CH08-03a.xls
Select the "Problem 8-21" sheet.
Regarding to this spreadsheet I must tell you that it has some
mistakes that are easy to correct:
The problem states an initial investment in plant and equipment of
$45,000 (but note that it use $50,000 for depreciation -
12,500=50,000/4 -, and for the Cash Flow of the Year zero in the
solution).
You must do the following:
·Cell E44 (capital spending): change $45000 to $50000 to get an
Initial Investment of $58000.
The problem states an opportunity cost of capital is 10 percent, but
in the Solution it claims for a 12% rate!!
The cell E61 (NPV) has the following value (Formula):
=-E46+VNA(0.12;I54:I57)
change it to:
=-E46+VNA(0.10;I54:I57)
Note that the difference is the 0.10 instead of the 0.12 rate, with
this you will get the NPV for a 10% opportunity cost of capital as you
want for this problem.
One more thing: you will note some differences between the Cash Flows
from the spreadsheet and the answer given at question ID 254308 (in
consequesce you will find a different NPV). This is because the
formula used for the Cash Flow for each year Yi is the following:
If for each year Yi (i = 1 to 4) we call:
Ri = revenues of Yi ,
Ei = expenses of Yi,
and
Depreciation = D = (Invest in plant and equipment) / 4 =
= $12,500
then for each year Taxes will be:
Ti = T * (Ri - Ei - D) with T = 0.4
Working Capital Change for year Yi:
ChWCi = Current WC - Previous Year WC
(Note that in this problem ChWCi = -$2,000 -negative- for all years)
Then the cash flow formula is:
CFi = Ri - Ei - Ti - ChWCi =
= (1-T)*(Ri-Ei-D) - ChWCi (this is the formula used in the spreadsheet).
Note that (Ri - Ei) is the Net Operating Profit for the year i; then
we can say that in general:
CF = Net Operating Profit - Taxes - Net Change in Working Capital
That's all for this spreadsheet.
------------------------------------------------------------
The link for the second problem's spreadsheet (the one named 6.20 in
the question) is:
http://merlin.alleg.edu/employee/d/dgoldste/econ427/CH07-03a.xls
Select the "Problem 7-19" sheet.
Only two things on this spreadsheet:
The text on cell H38 (value = -20000+25000/(1+D38)^3) is the formula
for the NPV for project B, I think that the intention is to show it.
The values on the cells F57 and F58 are the "guesstimatimations" of
the IRR using the NPV value (remember that IRR is the rate that makes
NPV equal to zero).
-----------------------------------------------------------
The link for the third problem's spreadsheet (the one named 11.10 in
the question) is:
http://webpub.alleg.edu/employee/d/dgoldste/econ427/CH12-03a.xls
Select the "Problem 12-10" sheet.
Note that the values in the Market Value column is in millions of dollars.
You also will note a difference with the result on the question ID
254308, the Cost of debt used in the solution of the problem is the
Yield of Maturity of the bonds (without dividing it by the 110%). You
can see this problem solved at the following PDF document (see the
example F at the page 10):
"Lecture 9: Cost of Capital" at University of British Columbia's site:
http://finance.sauder.ubc.ca/~ortizmolina/comm397/Lecture%209%20(cost%20of%20capital%20I).pdf
------------------------------------------------------------
The following pages will be useful in helping you to use financial
functions in Excel:
"Financial Functions in Microsoft Excel":
http://www.utexas.edu/its/training/handouts/excelff/
"Selected Excel Financial Functions":
http://www.sytsma.com/cism700/excelfinfcns.html
"Microsoft Excel Tutorial - Lesson 13: Financial Functions":
http://www.functionx.com/excel/lesson13.htm
------------------------------------------------------------
I hope this helps you. If something in the answer is unclear or have
problems downloading the spreadsheets please post a request of a
clarification before rate this answer.
Best regards.
livioflores-ga |
