ñòð. 33 |

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 |