Hi boobee,
a. The initial investment would be equal to the plant and equipment
expenditure of $50,000 plus the working capital for the first-year.
The working capital for the first-year is defined as 20% of the
revenues of year 2, so it is equal to $6,000. Therefore, the total
initial investment is $56,000. The working capital for the remaining
years is $4000, $2000, and zero dollars, respectively.
b. Straight-line depreciation over four years means that the plant
and equipment will be depreciated in equal amounts over four years. A
salvage value of zero means that the plant and equipment will be
completely depreciated after four years. Therefore, the annual
depreciation is equal to $50,000 divided by four or $12,500 per year.
The tax payment per period is equal to the (revenue minus the expenses
minus the depreciation) multiplied by 40%. Working capital is not an
expense, so it is not used in this calculation. In the third and
fourth years, the depreciation plus the expenses exceeds the revenue,
so there is no tax payment due. The taxes for year one are $4600, for
year two are $2200, and for years three and four are zero dollars.
The cash flow in each year is determined by taking the revenue minus
the expenses minus the working capital used minus the tax payment.
Depreciation is a noncash expense, so it does not affect cash flow.
The cash flow for year one is $13,400, for year two is $11,800, for
year three is $10,000, and for year four is $18,000. Note that all of
the working capital outlays for years one through three are recaptured
in year four because the business is ended.
c. The net present value is calculated by subtracting the initial
investment amount from the sum of the cash flow for each period
divided by (1 + i)^n where i is the cost of capital and n is the
period. The net present value for the project is -$8,250.73.
Generally, one wishes to avoid projects with negative net present
values.
d. The internal rate of return is the value of i for which the net
present value equation equals zero. An iterative approach is required
to solve this one. My Hewlett-Packard calculator calculates an
internal rate of return of approximately 2.42%. If the cost of
capital were less than 2.42%, then the project would have a positive
net present value.
Here is a table of computed values:
Year 0 1 2 3 4
Plant -$50,000
Revenue $40,000 $30,000 $20,000 $10,000
Expenses -$16,000 -$12,000 -$8,000 -$4000
Working Capital -$6,000 -$4000 -$2000 $12,000
Depreciation -$12,500 -$12,500 -$12,500 -$12,500
Tax Payment -$4600 -$2200 $0 $0
Cash Flow -$50,000 $13,400 $11,800 $10,000 $18,000
Present Value -$8,250.73
Internal Rate of Return 2.4186%
Sincerely,
Wonko |
Request for Answer Clarification by
boobee-ga
on
03 Jul 2003 13:37 PDT
Hi Wonko-ga
Thanks for all your help -- on this problem the NPV is ($60,000)
instead of ($80,000)-- sorry for the typo. Does this make a
difference in the present value of cash flows and the irr? I would
think so -- so sorry.
Fareedah
|
Clarification of Answer by
wonko-ga
on
03 Jul 2003 14:59 PDT
I'm sorry, but I'm not understanding what you are telling me. I can't
find any values of 80,000 to replace with 60,000 in this problem.
Also, we are calculating the Net Present Value, so it wouldn't be a
given. Perhaps you are referring to a different question and attached
this information to the wrong one? Alternatively, perhaps the initial
investment value is too high since it is making the net present value
negative for the given rate of return?
Please let me know.
Thanks!
Wonko
|
Request for Answer Clarification by
boobee-ga
on
03 Jul 2003 16:03 PDT
Hi Wonko-ga,
I used your calculations in the excel worksheet and the NPV is
2,835.74 and your answer is (8,250.73). Did I miss some calculation?
Please advise. Thank you.
a. What is the initial investment in the product? Remember working
capital.
Initial outlay $50,000
Working capital (6,000)
Initial investment $44,000
b. If the plant and equipment are depreciated over 4 years to a
salvage value of zero using straight-line
depreciation, and the firm's tax rate is 40 percent, what are the
project cash flows in each year?
Working Cash
Year Sales Expenses Capital Depreciation Flow
0 ($44,000)
1 $40,000 ($16,000) ($6,000) ($12,500) $28,600
2 $30,000 ($12,000) ($4,000) ($12,500) $18,200
3 $20,000 ($8,000) ($2,000) ($12,500) $9,800
4 $10,000 ($4,000) $12,000 ($12,500) ($10,600)
c. If the opportunity cost of capital is 10 percent, what is the
project NPV?
NPV ($2,835.74)
d. What is the project IRR?
IRR 2.42%
|
Clarification of Answer by
wonko-ga
on
03 Jul 2003 22:04 PDT
OK. Let me comment on your calculations.
a. Both the initial outlay and the working capital outlay for the
first-year need to have the same sign since they both represent an
outflow of cash even though we get the working capital back eventually
when the business ends). The standard convention is to use a - to
represent outflows. Therefore, the initial outlay and the working
capital are added together to arrive in initial investment of -$56,000
(cash put into the business).
b.-d. Something is definitely wrong with the formula you have in
place for calculating the cash flow. Consider year 4, for example.
Your calculation has yielded a cash flow of -$10,600. However, we
have revenue > expenses, a net return of working capital, and no
income taxes, so there is no way that the net cash flow can be
negative (depreciation is not considered since it is not a cash
expense).
The cash flow is calculated by taking Revenue - Expenses - Working
Capital (add working capital for year 4 instead of subtracting it
because it is returned at the conclusion of the business) - Taxes.
Taxes are calculated by multiplying 0.4 times the quantity (Revenue -
Expenses - Depreciation). Taxes are zero if the quantity (Revenue -
Expenses - Depreciation) is less than or equal to zero.
You should get my Net Present Value once you have the cash flow
calculation revised properly.
If you have any further need for clarification, please feel free to
ask.
I have enjoyed working on your Finance questions. Please post more if
you have any.
Sincerely,
Wonko
|
Clarification of Answer by
wonko-ga
on
03 Jul 2003 22:06 PDT
By the way, the numbers enclosed by parentheses in Excel are negative
numbers. That is the standard way they are formatted in accounting.
If this confuses you, you can change the formatting so that a - is
used.
Wonko
|
Request for Answer Clarification by
boobee-ga
on
04 Jul 2003 06:40 PDT
Hi Wonko,
Thanks for the clarifying the earlier posts. For this problem, I used
the numbers that you sent me in the first answer response. The
document I am putting the numbers into is an excel workbook and has
certain numbers already in place that cannot be changed. For example:
a. Initial outlay is $50,000 (already in place and cannot be changed).
Working Capital is a blank (find and insert the number)
Initial Investment calculates the total of initial outlay (of $50,000)
and whatever number is plugged into the working capital slot.
b. Already has the numbers for Years & Sales in place and cannot be
changed. Expenses, Working Capital, and
Depreciation have blanks that are to be filled in. Cash Flow is
($50,000) at year zero with no figures needed for
Sales, Expenses, WC or Depreciation. Cash Flow for Years 1-4
calculates itself based on the numbers put into Expenses, Working
Capital and Depreciation.
c. The NPV is calculated and put into place based upon the numbers put
into b.
When I plugged your numbers into b, I got Cash Flow numbers and NPV as
shown in the last clarification request. Please advise how I should
proceed.
Thanks for your help.
|
Clarification of Answer by
wonko-ga
on
05 Jul 2003 13:31 PDT
OK. Now that I better understand what you are trying to accomplish,
let me present the numbers slightly differently with more detail about
how to incorporate them into the spreadsheet. Also, I now better
understand what question a. is asking.
Question a.: we calculate the working capital for Year 0 by
multiplying the Year 1 revenue by 0.2 to obtain a working capital
requirement of $8,000. Enter this number under working capital as a
positive number. It should flow through the spreadsheet to being a
-$58,000 in the cash flow calculation in Question b for year 0
(initial outlay plus working capital with no revenue or expenses).
Question b.: the revenues are given. The expenses are equal to 40% of
the revenues (-$16,000, -$12,000, -$8,000, and -$4000). The working
capital is equal to 20% of the subsequent year's revenue except for
year 4, when it is the sum of the previous years' working capital.
So, the figures are -$6,000, -$4000, -$2000, and $20,000. The
depreciation is -$12,500 per year. Using these figures, and
calculating a tax payment of -$4600 in Year 1 and -$2200 in Year 2,
yields cash flows of -$58,000, $13,400, $11,800, $10,000, and $26,000
for Years 0-4, respectively.
Question c.: using the above cash flows, I calculate a net present
value of -$10,794.62.
Question d.: using the above cash flows, I calculate an internal rate
of return of 1.95%.
Hopefully when you plug these numbers into your spreadsheet, you will
get the same result. Make sure that you have the signs correct. If
you don't get the same result, please provide me with the formula of
the spreadsheet is using for the cash flow calculation so that I can
figure out what the difference is. The spreadsheet may be using a
different sign for one or more of the entries, for example, like it
turned out to be using in Question a.
Sincerely,
Wonko
|
Request for Answer Clarification by
boobee-ga
on
05 Jul 2003 14:59 PDT
Hi Wonko,
Here is what I get when I plugged in those numbers:
0 ($58,000)
1 $40,000 ($16,000) ($6,000) ($12,500) $42,600
2 $30,000 ($12,000) ($4,000) ($12,500) $18,200
3 $20,000 ($8,000) ($2,000) ($12,500) $9,800
4 $10,000 ($4,000) $20,000 ($12,500) ($18,600)
c. If the opportunity cost of capital is 10 percent, what is the project NPV?
NPV ($9,572.57)
d. What is the project IRR?
IRR 2.42%
|
Request for Answer Clarification by
boobee-ga
on
07 Jul 2003 05:33 PDT
Hi Wonko,
Thanks for all your help on this problem. I was just wondering about
my last request for clarification. Do you have any further comments?
|
Request for Answer Clarification by
boobee-ga
on
07 Jul 2003 05:59 PDT
Hi again Wonko,
One more thing....
For working capital in b. you state that "in year 4, the figure should
be the sum of the previous years' working capital. So that would be
$8,000 initial working cap + -6,000 + -4,000 + -2,000. I get -4,000
because $8,000 is a positive figure. If I put $8,000 as a negative
number then the total would be a $-20,000. Is this where the
confusion is? The cash flows calculate differently and also the NPV
than what you have figured. Also, There is no mention of tax payments
mentioned anywhere.
Hope to hear from you
|
Clarification of Answer by
wonko-ga
on
07 Jul 2003 09:30 PDT
All of the working capital cash flows for Year 0-3 are the same sign
(negative) for the purposes of the cash flow calculation. The only
reason I had you enter it as a positive number for question a. is
because that was the only way to get the proper sign in the formula
for question b.
Unless you can provide me with the formula the Excel spreadsheet is
using, I cannot explain the difference in your calculations from mine
or how the tax issue is being handled within the Excel spreadsheet. I
have an MS management from Sloan School at MIT, I have carefully
reviewed my finance textbooks, and I am confident in the calculations
that I have provided you and the relevant formulas. I am sorry I
cannot explain the difference in the results obtained by your
spreadsheet, but without access to the formula the spreadsheet is
using, there is nothing more that I can do. If you are able to
provide me with the formula the spreadsheet is using to calculate the
cash flows, I would be happy to examine it and explain what is
happening.
The only thing I can think of without being provided with the formula
in the Excel spreadsheet is to try entering all values except for the
last working capital flow for year 4 as positive numbers just in case
the spreadsheet has the negative signs already included in the formula
it is using to calculate cash flow. If that is the case, then the
working capital flow for year 4 would be entered as a negative number.
Sincerely,
Wonko
|
,
0 Comments
)