Need help with a formula.

The information I have is:

Year            Unit Sales
1                 22,000
2                 30,000
3                 14,000
4                  5,000
Thereafter          0


Working capital will amount to 20% of sales in the following year.
Plant and equipment will require an investment of 200,000. THis
investment will depreciated using MACRS and a 3 year life. AFter 4
years, the equipment will have an economic and book value of zero. The
firm's tax rate is 35%. The dicount rate is 20% How do I calculate the
value project?

---

Request for Question Clarification by omnivorous-ga on 26 Jul 2005 06:52 PDT

Baseball2 --

You're missing one piece of information to solve this: the sales price
per unit, which will allow you to calculate revenues and cash flow.

Also, be sure to recapture your working capital in year 4.

Best regards,

Omnivorous-GA

---

Request for Question Clarification by omnivorous-ga on 26 Jul 2005 08:03 PDT

Baseball2 --

I started to set up this problem (even without price/unit) and
realized that there's probably also a variable cost/unit that's
missing.

Best regards,

Omnivorous-GA

---

Clarification of Question by baseball2-ga on 26 Jul 2005 08:14 PDT

I am so sorry. The following table that I gave you respresents sales
forcast for a specific company. The unit price is 40.00 and the unit
cost of the items is 25.00. Hope this helps!

Baseball2 ?

The tricky part of this whole problem is actually in the working
capital (WC) consumption. As a result, I?ve set up a spreadsheet with
two sections ? one on PROFITS and one on CASH FLOW.  This is similar
to the cash budgeting process that a company would follow ? setting up
income and expense budgets, then calculating cash consumption ? so in
that sense it?s very realistic.

Now, I?ll take you through the spreadsheet linked below, line-by-line:
http://www.mooneyevents.com/cashflows.xls


PROFITS
========

Line3: we use the ?Year 0? convention for upfront costs.  Note that we
have to put our first year?s working capital requirements in here when
we get to cash flow.
Line4: units
Line5: units * $40 to get Revenues

Line7: gross profit can be figured with the contribution ($15) * units
Line8: depreciation, using the half-year Modified ACRS tables for 3-year assets

The modified ACRS depreciation for a 3-year property based on the
half-year convention are:
Year 1: 33.33%
Year 2: 44.45%
Year 3: 14.81%
Year 4: 7.41%

Taxguide.com
?Depreciation Tables?
http://taxguide.completetax.com/tools/deptables_m.asp

Line9: profit before tax, which we?re calculating in order to be able
to figure taxes
Line11: taxes (at 35%)

It?s not necessary to figure the actual accounting profit here, as the
value of a project is in its cash flow.  We could figure the net
profits and add back depreciation but it?s really a useless step.



CASH FLOW
===========

Line15: our initial capital investment

Line17: depreciation doesn?t use cash, so the ?Gross Profits? line
shows cash coming in ? except for the taxes that we?ll pay each year
Line18: one of the two inevitables of modern life: taxes (as the joke
goes, the other is ?death?)
Line19: here?s where we have to account for the working capital (WC)
being used to buy materials for next year?s production
Line20: each year WC is slightly different.  It all gets consumed in
year 0 as you invest in raw materials or work-in-process ? but after
year 0 you are asking yourself whether it?s going up?  Or down?  If
it?s going up, we?re spending $$ on working capital ? if it?s going
down cash is freed.

Line22: Gross Profits + taxes + change in WC  (I?ve used red for
negatives everywhere so it?s obvious what charges are for cash going
out).

Line24: NPV factor, which as you know, is calculated by:
Year 1: 1/(1.2)
Year 2: 1/(1.2)^2
Year 3: 1/(1.2)^3
Year 4: 1/(1.2)^4

Line26: discounted cash flow ? including the total for the project. 
At $254,441 in positive discounted cash flow, this project would be a
go despite the high discount rate of 20%.



Google search strategy:
MACRS + depreciation + percentage

Search IRS site for:
Modified ACRS + depreciation


Best regards,

Omnivorous-GA

As always a job well done. Thanks so much!