ERRATA: Note that thie publication is General Technical Report NE-178
not a Research Note as indicated in the Library Card.

Hansen, Bruce G.; Palmer, A. Jeff, 1993. JEFFI: a simplified, Lotus-based
cash flow analysis program. Res. Note NE-178. Radnor, PA: U.S.
Department of Agriculture, Forest Service, Northeastern Forest
Experiment Station. 11 p.

Describes a Lotus-based cash flow analysis program designed to provide a
simple assessment of investments in manufacturing facilities. JEFFI
generates discounted and nondiscounted measures of investment
performance for an overall project. It also generates information on equity
contributions and gives summary statistics on debt repayment.

Keywords: Financial analysis, financial feasibility, investment analysis



depreciation is added back to the after-tax profit to arrive at
after-tax earnings (B37). Again, working capital adjustments
are either subtracted from or added to the after-tax earnings
to arrive at the after-tax net cash flow (B39). (Note that the
cash flow in year 10 again is much larger than cash flows in
preceding years due to the assumed sale of the business
and returns of working capital, land cost, and undepreciated
building and equipment values.) Section B40 provides an
accumulation of net cash flows during the 10-year period.
This row is used to derive the payback period to equity.

The information found in B39 plus the initial investment
supplied by investors (A4 * A7) is used to compute the
discounted net present vaiue and internal rate of return
measures in C11 and C12, respectively. (Note that the
discount rate (C10) used in deriving the net present value is
equal to the user supplied equity rate in A8. The return
measures are for the return to equity contributions only.)

In addition to the outputs in Section B and the performance
measures in A6 through A13, the program also generates
five ratios (that is, percentages) based on sales. They are
computed from project input and summary data contained
in Section B. These five ratios (C1 to C5) total 100 percent,
plus or minus rounding, and account for the total disposition
of sales,

Conclusion

The secret behind JEFFI is simplicity. JEFF! asks for
minimum input about the investment requirements, sales
and cost expectations, and financial arrangements. Yet
from this it gives both discounted and nondiscounted
measures of investment performance on both the overall
project and investor contributions. JEFFI's simplicity aiso is
derived by recognizing the impracticality of attempting to
forecast cash flows beyond 10 years.

Another factor that makes JEFFI exceedingly easy to use is
that it is based on Lotus, a widely distributed and widely
used spreadsheet package. Thus, users of JEFFI
immediately are familiar with many of the rudiments of data
entry and program formulation, such as cell copy and
functions.

For a diskette of JEFFI write or call: Bruce G. Hansen,
Economist, U.S. Department of Agriculture, Forest Service,
Forestry Sciences Laboratory, Route 2, Box 562-B,
Princeton, West Virginia 24740 (Telephone: 304-425-8107).


JEFFI Worksheet (continued)

JEFFI
Reference

C1

c2

c3

Variable costs expressed as a percent of gross revenues. The percent is derived by dividing the
total in B14 by the total in B13, and represents an average for the 10-year period.

Fixed costs expressed as a percent of gross revenues. This percent is derived by dividing the
total in B16 by the total in B13. It represents an average for the 10-year period.

Depreciation expressed as a percent of gross revenues. The percent is derived by dividing the
total in B12 by the total in B13 and represents an average for the 10-year period.

Taxes expressed as a percent of gross revenues. The percent is derived by dividing the total in
B20 by the total in B13, and represents an average for the 10-year period

After-tax profits expressed as a percent of gross revenues. The percent is derived by dividing the
total in B21 by the total in B13, and represents an average for the 10-year period.

Note: The sum of C1 through C5 should equal 100 percent give or take a rounding error.

cé

CT

ca

C10

cn

C12

C13

The discount rate used in deriving the net present value in C7. The rate is based on the
weighted average cost of capital (WACC) concept and reflects the after-tax cost of debt and the
return desired by investors, The program automatically figures the WACC by using the user
specified inputs to AS, AG, A7, and AB. The formula is as follows:

WACC = [[A6 * [1 - A5]] * A7} + [AB * (1 - ATI]

The net present value is equal to the sum of the discounted after-tax net cash flows (row B25)
tess the initial investment amount in A4. Cash flows are discounted to the present using the
WACC rate computed in C6. A net present value of zero means the investment will earn the rate
specified. A net present value greater than zero means the investment will not only meet earning
requirements but will return a surplus. A negative net present value means the investment will
not perform up to expectations, and probably should not be funded

The internal rate of return is similar to the net present value in that the after-tax net cash flows in
row 625 are used in conjunction with the initial investment in its derivation, However, rather than
use a predetermined discount rate, the internai rate of return uses an iterative process to come
up with a discount rate that equates exactly the discounted sum of the cash flows in B25 with the
initial investment amount. Investments with an internal rate of return greater than or equal to the
WACC (C6) are desirable.

The payback period is a nondiscounted measure. It calculates the length of time it takes for the
after-tax net cash flows to accummulate to an amount equal to the initial investment. It is a
popular measure, but fails to recognize the timing of cash flows beyond the payback period
itself, The payback period is computed from data in row B26.

This is the discount rate used to evaluate the investment performance in terms of the returns to
equity, and is equal to the return desired by investors found in A8.

The net present vatue is similar to that in C7, with the exception that the initial investment
amount is equal only to that portion supplied by the owners. This is determined automatically by
taking the total investment amount in A4 and multiplying it by 1 minus the debt position
percentage figure in A7. Also the discounted after-tax net cash flow data are taken from B39.

The internal rate-of-return calculation is similar to that in AB, again the difference being the
amount of initial investment and a different set of after-tax new cash flow figures. As in C11, the
initial investinent amount represents only that provided by investors.

The same as in C9 with the exception that the data are taken from 840 and the initial investment
amount is limited to that supplied by investors only.


eagtses — ir6v'0gis)

910 26r'rs QUO'ZEH TS LPOG ES IG: POVES LEG RTLS 2H ERZLS LSP LOB IS ELS ALES

3:0 Z6y FS PLBZEO'LS SBFETS —GES'GEES © OLD FORS — SOFGLES HS OERE SEF ONES 29e'ves8 — (W6v'o918)
200 616 08 0 08 98 0s 0s os 000'9518
94) (26S SEE GIGS «FRE HESS OB LZGS SE LASS SPF IGS 061 F955 ELw 86S 00'S
DOLE IS DOD GUNTSS. ODDS. OND SS O02 — oN E12 oor'eses — ona'Bais
9eE-00SS  PaSORTS NE LOPS BEL'ZSHS arr 9678 108 evwaies — (one'reist
212 9S SEL'6OPS 995 BEES gp 3828 S26'8N3 0S
BSNS — EGE EGRS EF OURS abe 6£98 Loe aes loon HEIs! .
HOSS «WN ESS ONO HSS 000 F98 000 6428 23 WOES — 000 ANKE an ovat yoeaked Cb
ZIP SES LOG BS SUES eaS HIS 2S OL VLE GST HLS 0B ALES 80 6 2 10 Bled RGR “at
O96 POO IS 996 POO''S OSC OOS O96 HOO IS 26 50018 GOETHE'S Q9E'PON'IS 0Bs"sOEs age 129s anyes wasaud 1@N th
a800°02 tuna} ayes wunoasig “QL
206 LUIS CG GPS HERS aS OLS gig'srs nen
O9t £85 O01S, BS POLS “ALND O1 NENLIE
cies Fa 12S PLb Obes ruauted sipevied
80 8: #8 280 1688
$6N LS rer 2958 3E! 8598
as as os
19 $i 2 ro
oes ree Sores bores ge ous yoedhtd 6
PSs £198 Pee pir Agys ve jas 40 ates jeulaqul °g
QE iS — G0: LEES 29¥ 26S anyon wasoid ON
668 9 O68 %00 Si (29em) ayes yuNoIsig “g
a ee "ToaOud OF NUMLTY

FOO IS GHGS 4
2 Ges

B52 18 $ O9E e628

HES 9 m5 8

$02 28 cow FL 2S anze'at saesjyous LV"
100468 wee res aig Ch 59185/51S09 XBL“
uk S sdaq “f

% os s Q
WOO FES GOR PES OD HS cog 813 6296 . 9 pany 2
DONORS 0's WO OFS GOS WEL GS $/S1S09 IRA}
Hea oes map ORS aE ES 00 088 uve uot) S38NSYAW JONVWHOSHIS “D

000 26S 8 8S 00 L680 AS
in

WEIS ONE BES Oz RIS are REIS 0000 02 ayes Aunb3 -g
002 1698 002 1698 002 1698 93000 6S vonisod q60 “2
noe Ass oR cess HONORE wor'agss —s00 SEs easor eat, ane a ares Bumoui0g “9
SISO2 SBN 9000'S anei xe) annvay3 'S
os 8 8s 0s 0s as 0s as as gunners £

0s 0s as os 0s 8 as 08 98 as z z . |
eS DOWRY ZS DUR'PO! 2S OOR'HG! ZS OB OL ZE OOB'RAL'ZS HILLS OOP ES OOR FILES OOB PSS OOF ze S ionpoig | — 000°998°IS ywaUASAAUE WAOL “P
SiS  Q00'9SIS yeyides BuMLOM
000°009'1$ ‘dinba pue Gpig -%
SWiok OF 6 g é 9 S ° € z bo<-uW3A sanjea pussR3A — 900'O01S puey “b
~ AMNINRS TWIONWNId ONY SLONI M014 HSYO TYANNY 9 SLMdNI Wi Vv

(TTIW GHVGNVLS) SISATYNV SINONOOA NV
‘SHALLVNUSLIV 9 WELSAS - Idd3r


Appendix A: JEFFI Worksheet Example

The data used in this example were from an earlier publication titled “System 6 Alternatives: An Economic Analysis”
authored by Hansen and Reynolds. This publication dealt with an examination of several alternative mill constructs tnat

were proposed for use in manufacturing dimension and glued panels from low-grade, small-diameter hardwoods. The
specific configuration used in this example was referred to as the “‘standard mill.”


JEFFI Worksheet (continued)

JEFFI
Reference Worksheet Description

B21 After-tax profits are derived by subtracting taxes (B20) from taxable income (B19).

822 Depreciation. The same amount as in 818. But this time it is added back to the cash flow stream.

623 After-tax eamings. This is equal to after-tax profit plus depreciation.

B24 Working capital adjustment. This amount is subtracted from after-tax earnings. The amount may,
itself, be negative if there is a decline in sales from one year to the next. in that case, the amount
is added to after-tax earnings.

B25 After-tax net cash flow. This cash flow is equal to after-tax earnings plus any adjustments in
working capital. This row of data is used in deriving the discounted net present value and
internal rate-of-return performance measures found in C7 and C8, respectively.

B26 This row provides an accumulation of the after-tax net cash flow amounts in 825. The row is
used to determine the payback period found in C9.

B27 The periodic, uniform payment required for 10-year amortization of the debt portion of the total
investment. The Lotus spreadsheet function computes payment automatically.

B28 The interest on debt financing contained within the periodic payment. The interest is tax
deductible and subtracted from revenues in B31 before taxes are calculated.

B29 The portion of the periodic payment going to pay back the principal on the loan. Over the period,
the interest amount declines while the principal portion increases. This sum of the payments to
Principal is equal to the total investment cost muttiplied by the debt position.

B30 A repeat of the before-tax revenue amount found in B17. Reflects the remainder after variable
and fixed costs have been subtracted from gross sales. Provides a uniform starting point for the
equity-focused cash flow analysis.

B31 The interest payment. The payment is subtracted from the before-tax revenue.

B32 Depreciation. This is the same amount entered in B12 and used in 818 and B22. As in the earlier
cash flow summary, it is used to shield a portion of operating revenues from taxation.

B33 Taxable income. This income is equal to before-tax revenue less interest and depreciation.

B34 Taxes. Computed by multiplying the taxable income by the tax rate.

Note: B27 to B40 are used in assessing information on the retums to equity. B27 to B29 provide explicit treatment of debt
repayment.

B35 After-tax profit. This profit is equal to the taxable income less taxes.

B36 Depreciation. Depreciation is to be added back to the cash flow stream.

B37 After-tax eamings. This is equal to the after-tax profit amount plus depreciation.

838 Working capital adjustments. These are the same as those in B24.

B39 After-tax net cash flow. This is equal to after-tax earnings plus or minus any adjustments in

working capital. This row is used in computing discounted cash tlow values for C11 and C12.

B40 Accumulated net cash flow. This provides information on the accumulation of cash flows
occurring throughout the life of the investment. This row is used in the calculation of the payback
period in C13,


@

United States
Department of
Agriculture

Forest Service

Northeastern Forest
Experiment Station

General Technical
Report NE-178

JEFFI: A Simplified, Lotus-based
Cash Flow Analysis Program

Bruce G. Hansen
A. Jeff Palmer


Section 8: (Continued)

JEFFI JEFFI Lotus Lotus
reference header address contents

B27 Periodic payment 145 to R45 @PMT($D$15"$D$10,
$D$14,$D$17)

B28 Interest 146 to R46 ($0$15*$D$10"$D$14)

B29 Principal 147 to R47 (145-146)

B30 Before-tax revenue 149 to R49 (133)

B31 interest 150 to R50 (146)

832 Depreciation 151 to R51 (126)

B33 Taxable income 152 to R52 (149-150-151)

B34 Taxes 153 to R53. @IF(I52< =0,0,(52"$D$13))

B35 After-tax profit 154 to R54 @IF(IS2 < =0,152,(((149-150)
-151)*(1-$D$13)))

B36 Depreciation 155 to R55 (126)

B37 After-tax earnings 156 to R56 (154 +155)

B38 Working capital adjustment 157 to R57 (140)

B39 After-tax cash net flow 158 to Q58 (156-(157 +147)

B40 Accumulated net cash flow 159 to R59 (H59 + 158)

@ Formulas for year 1. Specifications are similar for years 2 through 10 unless otherwise noted. Lotus letter reference changes
according to year, that is, a year 1 content of H42 +141 would be 142 +J41 in year 2, J42 + K41 in year 3, and so on.

» R40 (year 10) is the numeric constant 0.”

© R41 (year 10) formula specification is: (R39-R40) + D7 + (D8 + T34) + T40 + D9.

4 R58 (year 10) formula specification is: (R56-(R57 + R47) +7 + (D8-T51) + 157 +D9).

Section C: Performance Measures

JEFFI JEFFI Lotus Lotus
reference header address contents

C1 Variable costs/sales D22 (T29/T28)

c2 Fixed costs/sales D23 (131/T28)

C3 Depreciation/sales D24 (726/728)

C4 Tax costs/sales D25 (T36/T28)

cs Atter-tax profit/sales D26 (137/728)

cé Discount rate (WACC) D35, (D14*(1-D13)*D15) +
(D16*(1-D15))

C7 Net present value D36 @NPV(D35,141..R41)+H41

cs internal rate of return D37 @IRR((D35*5),H41..R41)*

co Payback period D38 (sL$48)>

C10 Discount rate (equity) D51 @VALUE(D16)

ci Net present value D52 @NPV(D51 158,.R58) + H58

C12 Internal rate of return DS3 @IRR((D51 *5),H58..R58)°

C13 Payback period D54 (SL$6o)4

10

& The IRR function requires a seed value to begin iteration. The weighted average cost of capital (D35) is used automatically.
However, where the IRR was much greater than the seed, iteration would not begin. This problem was overcome by
multiplying ihe seed (D35) by the constant 5. Should the program be unable to compute an IRR, we recommend changing the
constant in D37.

» This is the address of the hidden equation used to Compute the payback period for the overall project. Values from this
equation are placed in corresponding columns in row 72. Macros \A to \D (Appendix D) are used fo locate the first positive
value in row 72 and transfer it to the payback address D38.

© The IRR function requires a seed value to begin iteration. The user specified return-to-equity value (AB and D51) is used
automatically. However, we found where the [RR was found to be much greater than the seed, iteration would not begin. This
problem was overcome by multiplying the seed (D51) by the constant 5. Should the program be unable to compute an IRR, we
recommend changing the constant in D53.

4 This is the address of the hidden equation used to compute the payback period for equity. Values from this equation are
placed in corresponding columns in row 73. Macros \E to \H (Appendix D) are used to locate the first positive value in row 73
and transfer it to the payback address D54.


Appendix C: Lotus Formulas

Section A: Initial inputs

JEFFI JEFFI Lotus Lotus
reference header address contents
Ai Land 07 Numeric input
A2 Building/equipment D8 Numeric input
A3 Working capital p9 Numeric input
A4 Total investment p10 @ SUM (D7_.D9)
AS Effective tax rate o13 Numeric input
AS Borrowing rate Dis Numeric input
A? Debt position p15 Numeric input
AB Equity rate pie Numeric input

Section B: Annual Cash Flow Inputs and Financial Summary

JEFFI JEFFI Lotus Lotus

reference header address(es) contents®

Bt Product 110 to R10 Numeric input

B2 Product 2 IittoRtit Numeric input

83 Product 3 12 to R12 Numeric input

B4 Labor 115 to R15 Numeric input

BS Materials 116 to R16 Numeric input

B6 Sales expense 117 to R17 Numeric input

B7 Other variable cost(s) 118 to R18 Numeric input

BS Mgmt/administration 121 to R24 Numeric input

B9 Insurance 122 to R22 Numeric input

B10 Maintenance 123 to R23 Numeric input

Bt Other fixed costs 124 to R24 Numeric input

B12 Depreciation 126 to R26 Numeric input

B13 Gross sales 128 to R28 @SUM(10. 112)

B14 Total variable cost 129 to R29 @SUM(I15..118)

B15 Profit contribution 130 to R30 (128-129)

BI6 Total fixed cost 131 to R31 @SUM(I21. 124)

Bi7 Before-tax revenue 133 to R33 (130-131)

B18 Depreciation 134 to H34 (126)

B19 Taxable income 136 to R35 (133-134)

B20 Taxes 136 to R36 @IF(I35< =0,0.
(135°$D$13))

Bat After-tax profit 137 to R37 @AF(I35 < = 0,135((133-
134)*(1-$D$13)))

B22 Depreciation 138 to R38 (126)

B23 After-tax earnings 139 to R39 (137 +138)

B24 Working capital adjustment 140 to Q40 ($D$9/$1$28)* (J28-128)°

B25 After-tax net cash flow 141 to O41 (139-140)°

B26 Accumulated net cash flow 142 to R42 (H42 +141)


Appendix B: Item-by-item Description of the JEFFI Lotus-based Worksheet

JEFFI
Reference

Al
A2
A3

Aa
AS
AG
Av
AS
B1
B2
B3

RE

87

BO
B10
B11

B12

B13
B14
B15

Bi6
B17
Bis

B19
B20

Worksheet Description
Initial investment in land.
Initial investment in buildings and equipment. These items are depreciable.

Investment in working capital. Working capital is used to account for monies tied up in raw
material inventories, work-in-process, finished goods inventories, and receivables.

Sum of A1-A3. Represents the total initial investment.

The combined state and federal tax burden on income. Expressed in percent.

Borrowing rate. The cost of debt financing. Expressed as a percent.

Debt position. The percent of the total investment to be financed with borrowed capital.

The return desired by investors. Expressed as a percent.

Expected gross revenue from the sale of product 1.

Expected gross revenue from the sale of product 2. If only one product is sold, shown as zero.

Expected gross revenue from the sale of product 3. If only one product is sold, shown as zero. If
more than three products are sold, total shown here.

Total labor costs including wages and fringes.
Total material costs.
Total selling expenses including salaries, commissions, advertising.

All other variable or direct costs. Variable costs are those costs that change with the level of
production.

Management and administration costs.
Insurance costs.
Maintenance costs.

All other fixed or indirect costs. Fixed costs are those costs that do not change with the level of
production.

Depreciation. Represents the sum of building and equipment depreciation applicable in a
particular year. The sum can reflect straight-line or any of several other methods employed by
the user.

Gross sales. Sum of user-input gross revenues in B1 to B3.
Total variable cost. Sum of user inputs in B4 to B7.

Profit contribution. Accounting term used to recognize amount of revenue remaining from the
sale of products after all direct costs have been deducted.

Total fixed cost. Sum of user inputs in B8 to B11.
Gross sales revenue less all variable and fixed costs.

Depreciation. The amount entered in B12. Brackets indicate that it is to be subtracted from
before-tax revenue.

Taxable income. This is the result after subtracting depreciation from before-tax revenue.
Taxes. This is derived by multiplying taxable income (B19) by the tax rate given in AS.


in accounting, working capital refers to a firm's short-term
assets. Gross working capital is defined as the firm's total
current assets: net working capital is defined as the
difference between current assets end current liabilities.
Gross working capital can include such things as cash,
short-term securities in addition to accounts receivable,
and inventories.

Our use of the term working capital is narrower and reflects
the need to cover the costs of raw material inventories,
work-in-process, finished goods inventories, and
receivables. The initial amount to be set aside is entered in
A3. However, usually as sales increase so do inventories
necessary to support the higher fevel of operation (many
otherwise successful and growing businesses have seen
their operations turn sour because of failure to anticipate
the additional capital required to sustain the overall higher
fevel of operation).

The worksheet program automatically increases or
decreases working capital based on changes in the
estimated level of sales from one year to the next. Working
capital is taken from (sales increase) or added to (sales
decline) the year-end net after-tax cash flow in the year
preceding the change in sales. The amount by which
working capital is increased or decreased is based on the
ratio between the initial working capital allowance A3 and
the gross sales in the first year (B13). Subsequent changes
in the level of sales are multiplied by this ratio to arrive at
the adjustment in working capital. in year 10, the sum of all
working capita! adjustments is added to the year-end after-
tax net cash flow.

The discount rate used to derive the net present value for
the net cash flows to the overall project (C6) is based on the
weighted average cost of capital (WACC) concept. This
concept recognizes that investment funds usually include
debt and equity. The program uses the user specified
borrowing rate (an average of the rates charged by lenders),
equity rate (the average return desired by investors), and
debt position (that percent of the total to be borrowed)
inputs in A5 to A8 to derive the WACC according to the
following formula:

WACC = ((borrowing rate * (1 - tax rate)) * debt position)
+ (equity rate * {1 - debt position)

Worksheet Explanation: Inputs

Section A contains user specified inputs on the initial
investment costs and financial arrangements. The initial
investment costs are divided among land, buildings and
equipment, and working capital because land and working
capital allowances are not depreciabie and the program
automatically adjusts working capital with changes in sales
according to the base-year relationship between working
capital and sales.

Section B presents a user-specified input section and two
sets of summary statistics. The user-specified input section

2

allows for three separate products; variable (or direct) costs
including !abor, materials, sales expenses (salaries,
commissions, advertising, and so on), and any other
variable cost items. Fixed (or indirect) cost items typically
include management and administration costs, insurance,
rent, maintenance, and utilities. The user also must enter
the yearly sum of depreciation on buildings and equipment.
This may be based on any schedule option available such
as the standard straight-line method to the more
accelerated methods (for a compilation of current
depreciation methods see the U.S. Master Tax Guide
published by the Commerce Clearing House, New York).

After each change of values, press <Alt>a. This assures
that the macro responsible for initializing the payback
period values is activated.

Worksheet Explanation: Outputs

The first set of summary data, B13 to B26, includes totals
for sales (B13), variable cost (B14), and fixed cost (B16)
inputs provided in B1 to B11. Gross sales less total variable
cost gives the profit contribution (B15). The profit
contribution less total fixed costs results in before-tax
revenues (B17). Taxable income (B19) is equal to the
before-tax revenue less the amount of depreciation (B18).
After-tax profit (B21) is equal to taxable income less taxes
(820). Depreciation is added back to the cash flow stream to
arrive at after-tax earnings (B23). The after-tax net cash flow
(B25) is equal to after-tax earnings plus or minus any
changes in working capital (B24) that result from either an
increase or decrease in sales. This row (B25), along with
the initial total investment A4, is used to derive the
discounted cash flow net present value and internal rate-of-
return measures, C7 and C8, respectively. Note that the
year 10 revenue is larger significantly than those in
preceding years. This reflects return of working capital, land
cost, and undepreciated building and equipment costs
discussed earlier. The discount rate used in the net present
value calculations (C6) is the weighted average cost of
capital (also discussed earlier). Row B26 is a year-by-year
accumulation of annual after-tax net cash flows occurring
throughout the project's 10-year life. The program computes
the payback period from the data in this row.

The second set of cash flow summary data begins in B27
and runs through B40. Sections B27 to B29 give explicit
treatment to repayment of principal and interest on
borrowed capital based on a 10-year amortization schedule.
The periodic payment is computed automatically from user
supplied inputs to A4, A6, and A7.

Before-tax revenue (B30) is the same value as in B17.
However, in deriving taxable income, both the interest on
the borrowed capita! (B31) and depreciation (B32) are
‘subtracted from before-tax revenues. Interestingly, in the
example this results in a negative taxable income (B33).
When this happens, the program automatically sets taxes
(B34) at zero. Also, when taxes are zero, after-tax profit
(B35) equals taxable income. As in the earlier summary,


Introduction

More and more community development agencies and
wood products utilization specialists are being called on

to evatuate the potential feasibility of wood products.
manufacturing facilities of one kind or another. Critical

to such evaluation is a realistic assessment of the
economics involved. While there are canned packages
available to investigate economic feasibility, many are quite
complicated, confusing, and exceedingly esoteric. Others
have been boistered to include so many extraneous
considerations that they all but hide the investment
Parameters themsetves. Basically the information needed
is, ‘Will the investment pay for itselt’? More specifically,
“Will creditors get paid’? And, ‘What can investors expect
to make"?

The cash flow analysis program to be described has been
designed to provide utilization and community development
specialists with Just Enough Fine Financial Information
(JEFFI)'. JEFFI provides a straightforward, sophisticated,
yet simple assessment of investments in manufacturing
facilities. One of the chief advantages of JEFFI is that it has
been formulated using Lotus spreadsheet. Lotus is one of
the more widely used and widely available spreadsheet
packages.

JEFFI provides discounted (net present value and internal
tate of return) and nondiscounted (payback period)
measures of investment performance. It also includes a
Separate set of summary statistics that give explicit
treatment to debt repayment and returns to equity. So, if
you need a fast but thorough assessment of investment
Potential, just call for JEFFI.

The Worksheet Configuration

The worksheet is divided into three sections (see Appendix
A). Rows in each section are numbered for convenience in
referencing. Each row is referenced in discussion by its
section—A, B, or C—and the row number. Appendix B
contains an item-by-item description of all worksheet
entries. Appendix C provides the Lotus formulas for each
worksheet entry. Appendix D contains two sets of Lotus
macros responsible for selection and transfer of payback
period values.

Section A contains user supplied inputs on the initial
investment requirements in land, buildings, equipment, and
working capital; the income tax rate; the borrowing rate on

1 The computer program described in this publication is
available on request with the understanding that the U.S.
Department ot hgature cannot assure its accuracy,
completeness, re lability, of Suitability for any other purpose
than that reported. The recipient may not assert any
proprietary rights thereto nor represent it to anyone as other
than a Government-produced computer program.

debt financing; the debt position (percent of total investment
amount borrowed); and the return desired by investors.
Section B contains a mix of user supplied input data on
sales, direct and indirect costs, and depreciation, along with
annual cash flow summaries for the project and for
investors. Section C contains overall investment
performance criteria for returns to the project as a whole
and for retums to investors.

With the exception of two internal rate-of-return formulas
(C8 and C12), all cells containing formulas have been
protected. Thus, should anyone attempt to change a
formula, the worksheet will give an error message.
Formulas can be changed after first disabling the global
Protection command (/Avgpd) or by unprotecting a specific
range (/ru).

Special Features

JEFFI assumes a fixed 10-year investment period. This
simplifying assumption was justified on the following: First,
cash flows more than 10 years into the future virtually are
impossible to predict (even forecasting up to this point may
be quite impractical). Second, the nature of the business
itself is likely to be quite different 10 years in the future due
to changes in production technology and markets (that is,
the odds that you will be doing something different, or the
same things differently, are high). Third, even if the
business were to remain relatively intact and sales and cost
forecasts relatively accurate, the discounted present value
of cash flows more than 10 years into the future are of less
value than those earned in the first 10 years. Finally, as an
alternative to predicting cash flows more than 10 years into
the future, we treat the investment as though it were
liquidated at the end of 10 years (this being the worst
possible outcome). Under this assumption, any
undepreciated value of the assets, all working capital set
asides, and initial land expenditures are added to the after-
tax net cash flow at the end of year 10. This, to varying
degrees, compensates for cash flows not considered after
10 years.

Depreciation may be calculated in any manner desired
since the program calls for annual totals to be entered
individually. Although accountants treat depreciation as an
expense, depreciation is not a true annual cost; the cost
represented by depreciation already has been taken into
account in terms of the initial investment in plant and
equipment. Rather, depreciation, or capital recovery, is
recognized in discounted cash flow analyses for its role in
shielding a portion of the before-tax revenue from taxation.
Thus, depreciation is deducted from before-tax revenues to
derive taxable income. Depreciation is added back to the
after-tax profit to arrive at after-tax earnings. Net after-tax
cash flows are equal to after-tax profit, plus or minus any
adjustments to working capital. Net after-tax cash flows are
used to derive the discounted net present value and internal
rate of return measures.


Abstract

JEFFI is a Lotus-based cash flow analysis program designed to provide a
straightforward, sophisticated, but simple assessment of investments in
manufacturing facilities. JEFF! generates discounted (net present value and internal
rate of return) and nondiscounted (payback period) measures of investment
performance for the overall project. In addition, it generates similar information on
equity contributions and a separate set of summary statistics that give explicit
recognition to debt repayment.

The Authors

BRUCE G. HANSEN is an economist with the U.S. Department of Agriculture,
Forest Service, Northeastern Forest Experiment Station's Forestry Sciences
Laboratory in Princeton, West Virginia. He received a B.S. in economics from
Concord College at Athens, West Virginia, and an M.B.A. and Ph.D. in forest
products marketing from Virginia Polytechnic institute and State University at
Blacksburg. He began work with the Northeastern Station in 1968.

A. JEFF PALMER is a biological aid with the Northeastern Station at Princeton.
West Virginia. He currently is pursuing a degree in business administration with a
specialization in management and computer science at Bluefield State College,
Bluefield, West Virginia.

The use of trade, firm, or corporation names in this publication is for the information
and convenience of the reader. Such use does not constitute an official
endorsement or approval by the U.S. Department of Agriculture or the Forest
Service of any product or service to the exclusion of others that may be suitable.

Manuscript received for publication 14 April 1993

Northeastern Forest Experiment Station
5 Radnor Corporate Center

100 Matsonford Road, Suite 200
Radnor, Pennsyivania 19087-8775

October 1983


Appendix D: Transfer Macros

The following set of macros is used to select and transfer the payback period values for the overail investment and to equity. The
macros are located in Lotus cells AB6 through AB26.

Lotus Macros

\A
\B

\c
\D
\E
\F

\G
\H

{GOTO} 172 ~ {BRANCH \Bj

{IF @CELLPOINTER(CONTENTS") < >"“0.0"| (BRANCH \C!
{BRANCH \D}

IC ~ D388 ~~ |HOME} ~ [BRANCH \E;

{RIGHT 1} (BRANCH \B}

{GOTO} 173 ~ {BRANCH \F}

{E @CELLPOINTER(“CONTENTS”) < >“0.0") [BRANCH \Gi

(BRANCH \H}
IC ~ DS4 ~~ {HOME} ~ (QUIT)
{RIGHT 1) (BRANCH \F}

Ww