<<

. 60
( 62 .)



>>

Common stock, 153 [(Current assets À cash) À
forecasting, 235 (current liabilities À short-term debt)]
formulas for, 186 /sales (ratio), 216“217
Common-size statements, 209, 210, Current items (on balance sheet), 147
223“226 Current liabilities, formula for, 181, 182
balance sheet, 223, 224, 226 Current ratio, 152, 213
income statement, 223, 224, 225 Customizing toolbars, 24“28
Computer hardware, 10“11
Conditional formatting, 308“310
D
Control Toolbox toolbar, 287“288
Controls, 287“296 Data:
adjusting size of, 287, 288 entering, 18
check boxes, 289“290 functions for looking up, 91“96
combo boxes, 293“294 validation of, 302“304
command buttons, 290“291 Date and time functions, 64
Design Mode icon for editing, 288 DATE function, 86“89
frames, 296 Dates and dating, 84“91
images, 295 finding number of days between dates,
labels, 295 89
list boxes, 292“293 finding number of months between
option buttons, 291“292 dates, 89“91
placing, 287 formula for, 166
properties associated with, 288“289 monthly, 84“88




TLFeBOOK
Index
332




Dates and dating (Continued): E
non-annual intervals for, 88 Earnings before interest, taxes,
using DAYS360, 89“91 depreciation, and amortization (EBITDA),
yearly, 88 7, 147“148, 212
DAY function, 85“88 Earnings before interest and taxes (EBIT),
DAYS360 function, 89“91 6“7, 146“148, 169, 170, 212
Debits, 115“118, 200 Earnings per share (EPS), 7
Debt, 8, 152 EBIT (see Earnings before interest and
accreting, 222 taxes)
forecasting, 235 EBIT margin, 218, 219
formulas for, 183“185 EBITDA (see Earnings before interest, taxes,
net, 212 depreciation, and amortization)
and NTF, 121“122 (EBITDA - capital expenditures)/cash
payment-in-kind, 222 interest expense (ratio), 222
post-sweep, 250 EBITDA/cash interest expense
short-term, 152 (ratio), 222
Deferred taxes, 231 EBITDA margin, 218, 219
Deleting modules, 319 EBT, formulas for, 171, 172
Depreciation: Edit key, 48“49
accumulated, 151 Edit settings, 39“41
forecasting, 229, 230 Efficiency, ratios for, 214“218
formulas for, 169, 170 EPS (earnings per share), 7
Depreciation triangle, 229, 230 Equity:
Design Mode icon, 288 in balance-sheet-only cash sweep,
Designing projection models, 13“21 246
avoiding errors when, 19“20 in cash flow variation cash sweep,
consistency in, 17“18 262“263
and data entry, 18 static totals for, 240, 246
KISS principle for, 14“15 Equity accounts, forecasting, 235
and maintaining logical arrangement Equity investments, projection models for
of parts, 16“17 analyzing, 5
and making backups, 20“21 Error messages, 96“98
and modular thinking, 18 Errors, avoiding, 19“20
and purpose of model, 15“16 Error-trapping formulas, 255“256, 269
and user expectations, 15 Excel, 23“45
using Excel for, 18“19 Alignment settings in, 30“32
and visibility of calculations, 17 calculation settings in, 40“42
Discount rate, 104 cell borders in, 33
Discounting, 104 cell patterns in, 34
Dividend payout ratio, 211 changing local settings in, 35“36
Dividends: color settings in, 37“40
forecasting, 231“232 column-width setting in, 36
formulas for, 172, 173 creating financial statement copies in,
Dividends payable, forecasting, 224
235 customizing toolbars in, 24“28
Dot operator, 321 edit settings in, 39“41
Double-entry bookkeeping, error messages in, 96“98
113“115 F keys in (see F keys)
Drivers, 298 fonts in, 31“33
Dynamic numbers, 130“132 formatting in, 15




TLFeBOOK
Index 333




formatting numbers in, 304“308 Financial statements, 110“112, 224
functions in (see Functions) (See also specific headings,
General setting in, 30 e.g.: Balance sheet)
gridlines in, 29 Financing, cash flows from, 201
hiding cells in, 34, 35 Fixed asset turnover ratio, 217
iteration in, 43“45 Fixed assets, 151
locking cells in, 34, 35 Fixed charge coverage, 222
moving between worksheets and ˜˜Flip flops,™™ 128“132
VBE in, 278“279, 316“317 FLOOR function, 102
Number attribute in, 30, 31 Fonts, 18, 31“33
objects in, 319 Forced calculations, 57
ranges in, 58“62 Forecasting, 227“235
screen appearance in, 28“29 of balance sheet accounts, 232“235
structuring models in, 14“15 of income statement accounts, 228“232
style settings in, 29“30 key principles in, 227
TRUE and FALSE keywords in, 67“69 Formatting, 15
using full power of, 18“19 conditional, 308“310
Visual Basic Editor in, 315“317 of numbers, 304“308
writing macros in, 271 (See also Macros) Formatting toolbar, 24
Excess cash, 121 ˜˜Formula Is™™ formatting, 309
Extraordinary items, forecasting, 231 Formulas:
avoiding complexity in, 14
code for writing, 283
F errors, formula, 19“20
F keys, 47“58 hiding rows with, 311“313
CtrlþAltþF9 forced calculation, 57 pasting names into, 52
F1: help, 48 recalculating portions of, 50“52
F2: edit, 48“49 in titles, 166“167
F2þF4: cycle through absolute tracing back to sources of, 50
references, 49“50 Frames, 296
F2þF5: tracing back to formula Function macros, 315, 325“326
Functions, 63“108
sources, 50
AND, 76“77
F2þF9: recalculating portions of
ABS, 101
formula, 50“52
for adding, 77“81
F3: paste name into formula, 52
arguments in, 64“65
F4: repeat last action, 52“53
AVERAGE, 83“84
F5: go to, 53“55
basic, 64
F6: move to next pane, 56
CEILING, 101
F7: spelling command, 56
CHOOSE, 74, 76
F8: anchor start of range to extend
COUNT, 81“82
selection, 56
COUNTA, 82
F9: recalculate, 56“57
COUNTIF, 82“83
F10: make menu bar active, 57“58
for counting, 81“84
F11: create chart, 58
for dates, 84“91
F12: Save As command, 58
and error messages, 96“98
FALSE keyword, 67“69
financial, 104“108
Features, adding, 297“298
FLOOR, 102
Financial functions, 64, 104“108
HLOOKUP, 95“96
Financial model (see Working financial
IF statement, 66“71, 76
model)




TLFeBOOK
Index
334




Functions (Continued): Gross margin, 218
INDEX, 93“95 Gross profit, formulas for, 167“168
INT, 101 Group command, hiding rows with,
IRR, 104“105, 108 310“311
ISBLANK, 102 GroupName property, 292
ISERROR, 98“100
ISNUMBER, 102
H
ISTEXT, 102
LEFT, 102 Help key, 48
LEN, 103 Hiding rows, 310“313
for looking up data, 91“96 with formulas, 311“313
LOWER, 103 with Group command, 310“311
MATCH, 91“95 Hierarchy, object, 319“321
MAX, 71“73 HLOOKUP function, 95“96
MID, 103
MIN, 71“73
I
MOD, 101
names of, 67“68 Icons:
NPV, 104“108 adding, 26“27
OFFSET, 74“76 deleting, 27
OR, 77 suggested additional, 27“28
PROPER, 103 IF statement, 66“71, 76
RIGHT, 103 <>() portion of, 69
ROUND, 100“101 alternatives to, 71
ROUNDDOWN, 102 AND function with, 76“77
ROUNDUP, 102 nested, 69“70
SUM, 77“78 and TRUE and FALSE keywords,
SUMIF, 78“81 67“69
SUMPRODUCT, 81 and whether or not cell is 0, 69“71
TEXT, 103 Images, 295
UPPER, 103 Income, net, 7
using MATCH and INDEX together, Income statement, 111, 145“149
94“95 attaching, in cash sweep model,
VALUE, 103 250“256
VLOOKUP, 95, 96 and balance sheet, 118, 136“143
for working with numbers in a basic form of, 146
direction, 101“102 in cash flow variation cash sweep,
for working with parts of numbers, 263“264
100“101 common-size, 223“225
for working with text, 102“103 EBIT on, 146“147
XIRR, 105, 108 EBITDA on, 147“148
XNPV, 105, 107“108 effects of balance sheet plugs on,
Future valuing, 104 140“143
financial model, input for,
160“161
G linking retained earnings and,
General setting, 30 254“255
Go To key, 53“55 output sheet for, 194“195
Goodwill, 5, 150 Income statement accounts, forecasting,
Gridlines, 29 228“232




TLFeBOOK
Index 335




Indents, 323 LEFT function, 102
INDEX function, 93“95 LEN function, 103
Industry, projection models for, 3 Leverage, ratios for, 220“221
Information functions, 64 Leveraged buyouts (LBOs), 5
Inserting modules, 318“319 Liabilities:
Insurance companies, projection models in accounting equation,
for, 4 112“113
INT function, 101 on balance sheet, 151“153
Intangibles, 151, 178“180 in balance-sheet-only cash sweep,
Interest: 244“246
from NTF, 142“143 in cash flow variation cash sweep,
from SF, 140“142 262“263
using AVERAGE to calculate, financial model, input for,
252“254 164
Interest expense: long-term, 152
forecasting, 230“231 static total for, 240, 244“246
formula for, 171, 172 Life insurance companies, 4
Interest income: LinkedCell:
forecasting, 229, 230 for check boxes, 290
formulas for, 171“172 for combo boxes, 293
Internal rate of return (IRR), 5 for list boxes, 293
Inventory, 150 for option buttons, 292
forecasting, 233 for spin buttons/scroll bars,
formulas for, 176“177 295
Inventory days, 215 for text boxes, 290
Inventory/cost of goods sold * 365 for toggle buttons, 294
(ratio), 215, 219 Liquidity, ratios for, 213“214
Investment(s): List boxes, 292“293
in affiliates, 150 ListFillRange:
cash flows from, 201 for combo boxes, 293
IRR function, 104, 108 for list boxes, 293
IRR (internal rate of return), 5 ListRows, 293
ISBLANK function, 102 Local settings, changing, 35“36

<<

. 60
( 62 .)



>>