<< Ïðåäûäóùàÿ ñòð. 33(èç 62 ñòð.)ÎÃËÀÂËÅÍÈÅ Ñëåäóþùàÿ >>
the â€˜â€˜Cashâ€™â€™ block here. The use of the absolute reference (\$)
in some of the formulas will ensure that the references are
correct.

Accounts Receivable, Inventory, and
Other Current Assets

A B C D E F
88 Accounts receivable 60.0 75.0 90.0
89 % of revenues =IF(B\$8,B88/B\$8,0) >>> >>>
90 Days of revenues =IF(B\$8, >>> >>> 30.0 30.0
B88/B\$8*365,0)
91 =A88 =B88 >>> >>> =IF(E88,E88, >>>
IF(E89,E89*E\$8,
E90/365*E\$8))
92
93 Inventory 120.0 135.0 150.0
94 % of revenues =IF(B\$8,B93/B\$8,0) >>> >>>
95 Days of COGS =IF(B\$12, >>> >>> 98.0 95.0
B93/B\$12*365,0)
96 =A93 =B93 >>> >>> =IF(E93,E93, >>>
IF(E94,E94*E\$8,
E95/365*E\$12))
97
98 Other current assets 10.0 10.0 12.0
99 % of revenues =IF(B\$8,B98/B\$8,0) >>> >>> 1.0% 1.0%
100 =A98 =B98 >>> >>> =IF(E98,E98, >>>
E99*E\$8)
101 Current assets =B72+B78+B84 >>> >>> >>> >>>
+B91+B96+B100
102

TLFeBOOK
Putting Everything Together 177

Accounts receivable
B89 Â¼ IF(B\$8,B88/B\$8,0). Copy across to column D.
B90 Â¼ IF(B\$8,B88/B\$8*365,0). Copy across to
column D.
This is a formula to derive the â€˜â€˜Days of revenuesâ€™â€™
of receivables turnover, or a measure of how
many days elapse on average before the com-
pany collects cash on its receivables. We will use
365 as the number of days in the year, even for
leap years. This formula can be made more
elaborate by making it take into account the
extra day in leap years. Some analysts also use
360 days as the year basis.
B91 Â¼ B88. Copy across to column D.
E91 Â¼ IF(E88,E88,IF(E89,E89*E\$8,E90/365*E\$8)).
Copy across to column G.
This is an example of a nested IF statement and
will calculate the accounts receivable based on
any of the three inputs.
Inventory
In general terms, this block is similar to the â€˜â€˜Accounts
receivable,â€™â€™ but if you create it by copying the block above,
you should take care to make the appropriate changes in the
formulas, especially the reference to COGS, rather than revenues,
for rows 95 and 96.
B94 Â¼ IF(B\$8,B93/B\$8,0). Copy across to column D.
B95 Â¼ IF(B\$12,B93/B\$12*365,0). Copy across to
column D.
This is a formula to derive the â€˜â€˜Days of COGSâ€™â€™ of
inventory turnover, or a measure of how many
days on average that a piece of
inventory remains in the warehouse before it is
used in the production line. We will use 365
as the number of days in the year, even for
leap years. This formula can be made more ela-
borate by making it take into account the
extra day in leap years. Some analysts also
use 360 days as the year basis.

TLFeBOOK
Chapter 9
178

B96 Â¼ B93. Copy across to column D.
E96 Â¼ IF(E93,E93,IF(E94,E94*E\$8,E95/365*E\$12)).
Copy across to column G.
This is an example of a nested IF statement, and it
will calculate the inventory based on any of the
three inputs.
Other current assets
B99 Â¼ IF(B\$8,B98/B\$8,0). Copy across to column D.
B100 Â¼ B98. Copy across to column D.
E100 Â¼ IF(E98,E98,E99*E\$8). Copy across to
column G.
Current assets
E101 Â¼ B72Ã¾B78Ã¾B84Ã¾B91Ã¾B96Ã¾B100. Copy
across to column G.

Net PPE, Intangibles, Long-Term Assets

A B C D E F
103 Net PPE 870.0 950.0 1,000.0
104 Capex 130.0 155.0 130.0
105 % of revenues =IF(B\$8,B104/B\$8,0) >>> >>> 15.0% 18.0%
106 Capex =B104 >>> >>> =IF(E104,E104, >>>
E105*E\$8)
107 Depreciation =B29 >>> >>> >>> >>>
108 =A103 =B103 >>> >>> =IF(E103,E103,
D108+E106-E107)
109
110 Intangibles 58.0 54.0 50.0
111 Amortization >>>
=E32
112 =A110 =B110 >>> >>> =IF( >>>
ISNUMBER(E110),
E110,D112-E111)
113
114 Long-term assets 92.0 116.0 150.0
115 % of revenues =IF(B\$8,B114/B\$8,0) >>> >>> 14.0% 14.0%
116 % growth na =IF(B117, >>>
C114/
B117-1,0)
117 =A114 =B114 >>> >>> =IF(E114,E114, >>>
IF(E115,E115*E\$8,
D117*(1+E116)))
118 Total assets =B101+B108 >>> >>> >>> >>>
+B112+B117
119

TLFeBOOK
Putting Everything Together 179

Net PPE
Net PPE is a little bit more complicated because there are a few
more flows that affect the final numbers. For historical periods,
only the entries on row 103 will matter. The entries in row 104 for
capital expenditures (â€˜â€˜Capexâ€™â€™) are there for informational pur-
poses only, mainly so that we have a measure of Capex as a
percentage of revenues to use as a forecasting yardstick. The
line for â€˜â€˜Depreciationâ€™â€™ in the historical years is also not strictly
needed. The depreciation in the forecast columns is used, how-
ever, for calculating the Net PPE number when there is no hard-
coded input for this account.
B105 Â¼ IF(B\$8,B104/B\$8,0). Copy across to
column D.
B106 Â¼ B104. Copy across to column D.
E106 Â¼ IF(E104,E104,E105*E\$8). Copy across to
column G.
This formula shows the Capex, either as the
hard-coded input or as a percentage of
revenues.
B107 Â¼ B29. Copy across to column G.
B108 Â¼ B103. Copy across to column D.
E108 Â¼ IF(E103,E103,D108Ã¾E106Ã€E107). Copy across to
column G.
This formula shows the Net PPE, either as a
hard-coded input, or as a calculation based on
the prior Net PPE Ã¾ the Capex number
previously calculated Ã€ Depreciation. We could
have referenced the formula directly back to the
income statement, but there is something to be
said for having first brought the depreciation
values down (to row 107), clearly labeling that,
and then using that in this formula.
Intangibles
Whether we have the amortization numbers for the historical
years is not important, as the model will read the hard-coded
inputs for these years.
B111â€“D111 These can be left blank, or you can write
Â¼B32 and copy to column D.

TLFeBOOK
Chapter 9
180

E111 Â¼ E32. Copy across to column G.
B112 Â¼ B110. Copy across to column D.
E112 Â¼ IF(ISNUMBER(E110),E110,D112Ã€E111).
Copy across to column G.
The ISNUMBER function is important here. With
this in use, you can bring the account down to
zero by entering a 0 in the input. Without the
ISNUMBER, entering a 0 will give a FALSE
result for the IF statement, and the result is the
prior yearâ€™s number less the amortization for
the year.
Long-term assets
There are two subentry lines for long-term assets, one for â€˜â€˜% of
revenuesâ€™â€™ and the other for â€˜â€˜% growth.â€™â€™ This gives flexibility for
defining this account: The first input defines the account as a
function of revenues and thus operations, and the second defines
the account by growth, as an investment account.
B115 Â¼ IF(B\$8,B114/B\$8,0). Copy across to
column D.
B116 â€˜â€˜naâ€™â€™
C116 Â¼ IF(B117,C114/B117Ã€1,0). Copy to column D.
B117 Â¼ B114. Copy across to column D.
E117 Â¼ IF(E114,E114,IF(E115,E115*E\$8,
D117*(1Ã¾ E116))). Copy across to column G.
This is a nested IF with three levels for calculating
the long-term assets. The formula reads row 114
first. If that is 0, then it reads the next row, and
so on.
Total assets
B118 Â¼ B101Ã¾B108Ã¾B112Ã¾B117. Copy this across to
column G.

TLFeBOOK
Putting Everything Together 181

ST Notes, Accounts Payable, and Other Current
Liabilities

A B C D E F
120 LIABILITIES
121 Short-term notes 10.0 12.0 14.0 14.0 14.0
122 =A121 =B121 >>> >>> >>> >>>
123 Interest rate 10.000% 10.000%
124 Interest expense =E123*AVERAGE >>>
(D122:E122)
125
126 Accounts payable 60.0 70.0 80.0
127 % of revenues =IF(B\$8,B126/B\$8,0) >>> >>>
=IF(B\$12,B126/
128 Days of COGS >>> >>> 55.0 55.0
B\$12*365.0)
129 =A126 =B126 >>> >>> =IF(E126,E126, >>>
IF(E127,E127*E\$8,
E128/365*E\$12))
130
131 Other current liabilities 10.0 20.0 20.0
132 % of revenues =IF(B\$8,B131/B\$8,0) >>> >>> 2.0% 2.0%
133 =A131 =B131 >>> >>> =IF(E131,E131, >>>
E132*E\$8)
134 Current liabilities =B121+B129+B133 >>> >>> >>> >>>
135

Short-term notes
B122 Â¼ B121. Copy across to column G.
B124â€“D124 This is left bank as these are for the historical
years. In these years, we would expect to have
 << Ïðåäûäóùàÿ ñòð. 33(èç 62 ñòð.)ÎÃËÀÂËÅÍÈÅ Ñëåäóþùàÿ >>