<<

. 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 .)



>>