<<

. 34
( 62 .)



>>

the interest expense for the company to be
shown as one number for all debt accounts, so
there is no need to show it separately for the
different types of debt.
E124 ¼ E123*AVERAGE(D122:E122). Copy across
to column G.
Accounts payable
This block is similar to the inventory section, including the use of
COGS rather than revenues, for the secondary inputs.
B127 ¼ IF(B$8,B126/B$8,0). Copy across to
column D.




TLFeBOOK
Chapter 9
182




B128 ¼ IF(B$12,B126/B$12*365,0). Copy across the
column D.
This is a formula to derive the ˜˜Days of COGS™™
of accounts payable turnover, or a measure of
how many days on average the company holds
its payables before actually paying them off. The
longer it is, the more of a ˜˜free loan™™ from its
suppliers the company enjoys. 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.
B129 ¼ B126. Copy across to column D.
E129 ¼ IF(E126,E126,IF(E127,E127*E$8,
E128/365*E$12)). Copy across to column G.
This is an example of a nested IF statement and
will calculate the accounts payable based on a
hierarchy of the three inputs.
Other current liabilities
B132 ¼ IF(B$8,B131/B$8,0). Copy across to column D.
B133 ¼ B131. Copy across to column D.
E133 ¼ IF(E131,E131,E132*E$8). Copy across to
column G.
Current liabilities
B134 ¼ B122þB129þB133. Copy across to column G.




TLFeBOOK
Putting Everything Together 183




Necessary to Finance, Debts 1 to 3, and
Long-Term Liabilities

A B C D E F
136 Necessary to finance
137 Interest rate 10.000% 10.000%
138 Interest expense =E137* >>>
SUM(D136:E136)/2
139
140 Debt 1 200.0 225.0 150.0
141 =A140 =B140 >>> >>> >>> >>>
142 Interest rate 10.000% 10.000%
143 Interest expense =E142*AVERAGE >>>
(D141:E141)
144
145 Debt 2 200.0 200.0 225.0
146 =A145 =B145 >>> >>> >>> >>>
147 Interest rate 10.000% 10.000%
148 Interest expense =E147*AVERAGE >>>
(D146:E146)
149
150 Debt 3 110.0 110.0 110.0
151 =A150 =B150 >>> >>> >>> >>>
152 Interest rate 8.000% 8.000%
153 Interest expense =E152*AVERAGE >>>
(D151:E151)
154
155 Long-term liabilities 40.0 38.0 37.0
156 % of revenues IF(B$8,B158/B$8,0) >>> >>> 4.0% 4.0%
157 % growth na =IF(B158, >>>
C155/
B158-1,0)
158 =A155 =B155 >>> >>> =IF(E155,E155, >>>
IF(E156,E156*E$8,
D158*(1+E157)))
159 Total liabilities =B134+B136+B141 >>> >>> >>> >>>
+B146+B151+B158
160




Necessary to finance (NTF)
This is the plug for the liabilities side, and it is assumed to be
debt.
B136“D136 These cells should always be blank, since
there should be no plugs for the historical years.




TLFeBOOK
Chapter 9
184




See the comment under ˜˜Surplus funds,™™
regarding using formulas for the historical
periods.
E136“G136 For the moment, leave this blank because we
have not created the calculation lines at the
bottom of the balance sheet. We will get back to
this as we complete the model.
E138“G138 ¼ E137*SUM(D136:E136)/2. Copy across to
column G.
We will need to calculate the interest expense from
any NTF. Note the use of SUM/2, rather than
AVERAGE. This is because we expect D136 to
be blank, so a formula such as
¼E137*AVERAGE (D136:E136) would be
considered by Excel to be the same as
¼E137*E136. Excel™s AVERAGE function does
not consider a blank cell as a cell to be counted.
Since we want the interest expense to be on the
basis of the average of the starting and ending
numbers, we need to use the SUM/2 approach.
In columns F and G, we can use AVERAGE.
Likewise, to use AVERAGE in column E, another
way would be to put the 0 digit in cell D136.
Debt 1
The description for Debt 1 will apply as well to Debt 2 and Debt
3, so I will describe only how to build Debt 1. Once this block is
built, simply copy it to create the other two debt blocks.
B141 ¼ B140. Copy across to column G.
B143“D143 This will remain blank as we will not need to
calculate the interest income separately for debt
in the historical years. Typically, historical
interest expense is given as one number
representing the interest expense of all interest-
bearing liabilities.
E143 ¼ E142*AVERAGE(D141:E141). Copy across
to column G.
The interest expense is calculated on the basis
of the average of the beginning and ending




TLFeBOOK
Putting Everything Together 185




outstandings. We can use the AVERAGE func-
tion here because the formula is looking to cells
that will always have a number (even if it is
zero). In this way, the AVERAGE function will
calculate properly. Compare this with the case of
the NTF (and Surplus funds) above, where we
have to use the SUM/2 approach.
Debt 2
Copy the Debt 1 block to create this second block. Remember to
change the title.
Debt 3
Copy the Debt 1 block again to create this third block. Remember
to change the title.
Long-term liabilities
Like long-term assets, this may or may not be related to
operations. For this reason and to be flexible, we will have two
subentry lines.
B156 ¼IF(B$8,B158/B$8,0). Copy across to
column D.
B157 ˜˜na™™
C157 ¼ IF(B158,C155/B158À1,0). Copy to column D.
B158 ¼ B155. Copy across to column D.
E158 ¼ IF(E155,E155,IF(E156,E156*E$8,
D158*(1þE157))). Copy across to column G.
This is a nested IF with three levels for calculating
the long-term liabilities.
Total liabilities
B159 ¼ B134þB136þB141þB146þB151þB158




TLFeBOOK
Chapter 9
186




Common Stock, Retained Earnings, and Other
Equity Account

A B C D E F
161 EQUITY
162 Common stock 460.0 560.0 580.0 650.0 650.0
163 =A162 =B162 >>> >>> >>> >>>
164
165 Retained earnings 200 261.0 337.0
166 Net to retained earnings =E65 >>>
167 =A165 =B165 >>> >>> IF(E165,E165, >>>
D167+E166)
168
169 Other equity acct 10.0 11.0 12.0
170 % of revenues =IF(B$8,B169/B$8,0) >>> >>> 1.1% 1.02%
171 =A169 =B169 >>> >>> =IF(E169,E169, >>>
E170*E$8)
172 Total SH equity =B163+B167+B171 >>> >>> >>> >>>
173 Total liabs & SH equity =B159+B172 >>> >>> >>> >>>
174


Common stock
B163 ¼ B162. Copy across to column G.
B166“D166 Leave this blank because, for the historical
years, retained earnings will be defined by the
numbers from the data source, which will be
entered in the input cells in row 165.
E166 ¼ E65. Copy across to column G.
This is the reference to the ˜˜Net to retained
earnings™™ row in the income statement.
B167 ¼ B165. Copy across to column D.
E167 ¼ IF(E165,E165,D167þE166).
For the forecast periods, we are most probably
going to rely on retained earnings being
calculated as the prior period™s number plus the
current period™s net to retained earnings. So the
formula need only be ¼D167þE166 in cell E167.
Nevertheless, on the off chance that you would
want to use a specific input, we will use the IF
formula shown to give you the ability to specify
retained earnings by specific hard-coded
inputs.




TLFeBOOK
Putting Everything Together 187




Other equity account
B170 ¼ IF(B$8,B169/B$8,0). Copy across to
column D.
B171 ¼ B169. Copy across to column D.
E171 ¼ IF(E169,E169,E170*E$8). Copy across
to column G.
Shareholders™ equity
B172 ¼ B163þB167þB171. Copy across to
column G.
Total liabilities and shareholders™ equity
B173 ¼ B159þB172. Copy across to column G.


BALANCING THE BALANCE SHEET

<<

. 34
( 62 .)



>>