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.
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
B128 Â¼ IF(B$12,B126/B$12*365,0). Copy across the
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
B134 Â¼ B122Ã¾B129Ã¾B133. Copy across to column G.
Putting Everything Together 183
Necessary to Finance, Debts 1 to 3, and
A B C D E F
136 Necessary to finance
137 Interest rate 10.000% 10.000%
138 Interest expense =E137* >>>
140 Debt 1 200.0 225.0 150.0
141 =A140 =B140 >>> >>> >>> >>>
142 Interest rate 10.000% 10.000%
143 Interest expense =E142*AVERAGE >>>
145 Debt 2 200.0 200.0 225.0
146 =A145 =B145 >>> >>> >>> >>>
147 Interest rate 10.000% 10.000%
148 Interest expense =E147*AVERAGE >>>
150 Debt 3 110.0 110.0 110.0
151 =A150 =B150 >>> >>> >>> >>>
152 Interest rate 8.000% 8.000%
153 Interest expense =E152*AVERAGE >>>
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, >>>
158 =A155 =B155 >>> >>> =IF(E155,E155, >>>
159 Total liabilities =B134+B136+B141 >>> >>> >>> >>>
Necessary to finance (NTF)
This is the plug for the liabilities side, and it is assumed to be
B136â€“D136 These cells should always be blank, since
there should be no plugs for the historical years.
See the comment under â€˜â€˜Surplus funds,â€™â€™
regarding using formulas for the historical
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
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.
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-
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
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.
Copy the Debt 1 block to create this second block. Remember to
change the title.
Copy the Debt 1 block again to create this third block. Remember
to change the title.
Like long-term assets, this may or may not be related to
operations. For this reason and to be flexible, we will have two
B156 Â¼IF(B$8,B158/B$8,0). Copy across to
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.
B159 Â¼ B134Ã¾B136Ã¾B141Ã¾B146Ã¾B151Ã¾B158
Common Stock, Retained Earnings, and Other
A B C D E F
162 Common stock 460.0 560.0 580.0 650.0 650.0
163 =A162 =B162 >>> >>> >>> >>>
165 Retained earnings 200 261.0 337.0
166 Net to retained earnings =E65 >>>
167 =A165 =B165 >>> >>> IF(E165,E165, >>>
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, >>>
172 Total SH equity =B163+B167+B171 >>> >>> >>> >>>
173 Total liabs & SH equity =B159+B172 >>> >>> >>> >>>
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
Putting Everything Together 187
Other equity account
B170 Â¼ IF(B$8,B169/B$8,0). Copy across to
B171 Â¼ B169. Copy across to column D.
E171 Â¼ IF(E169,E169,E170*E$8). Copy across
to column G.
B172 Â¼ B163Ã¾B167Ã¾B171. Copy across to
Total liabilities and shareholdersâ€™ equity
B173 Â¼ B159Ã¾B172. Copy across to column G.
BALANCING THE BALANCE SHEET