ñòð. 62 |

financial model, input for, 165

formulas in, 166

formula for, 186, 187

on income statement output

Shortcut keys (macros), 273â€“276,

sheet, 194

324

Toggle buttons, 294â€“295

Short-term debt, 152

Toggle setting, 298, 299

Short-term investments, forecasting,

Toolbars:

233

adding icons to, 26â€“27

Short-term notes, formulas for, 181

TLFeBOOK

Index 339

Control Toolbox, 287â€“288 writing Excel macros in, 276â€“282

customizing, 24â€“28 Visual Basic Editor (VBE), 277â€“279, 315â€“317

deleting icons from, 27 main areas in, 277, 278

Visual Basic, 276â€“277 moving between worksheets and,

Total assets: 278â€“279, 316â€“317

difference between total liabilities Ã¾ View Code icon for, 291

equity and, 246 Visual Basic for Applications (VBA),

formula for, 178, 180 19, 326

Total debt/EBITDA (ratio), 221 Visual Basic for Applications (VBA)

Total debt/shareholdersâ€™ equity macros, 271, 315â€“327

(ratio), 220 deleting modules, 319

Total debt/total invested capital editing code for, 282â€“285

(ratio), 221 following hierarchy in, 320â€“321

Total liabilities, formula for, 183, 185 function macros, 325â€“326

Total liabilities Ã¾ equity, difference inserting modules, 318â€“319

and objects, 319

between total assets and, 246

reading code for, 280â€“282

Total liabilities and shareholdersâ€™ equity,

running, 323â€“324

formula for, 186, 187

and Visual Basic Editor, 315â€“317

Trademarks, 151

working with methods, 320

Treasury stock, 152

working with properties, 320

Trigonometry functions, 64

writing codes for, 322â€“323

TRUE keyword, 67â€“69

VLOOKUP function, 95, 96

True Type fonts, 31

Typefaces, 31

W

U Weighted average shares outstanding, 232

With statement, 284â€“285

UPPER function, 103

Working capital, 7â€“8, 213

User errors, 20

Working financial model, 155â€“192

User expectations, 16

accounts payable formulas in, 181â€“182

Uses of cash, 200

accounts receivable formulas in,

176â€“177

V adding features to, 297â€“298

amortization of intangibles formulas in,

Validation, data, 302â€“304

VALUE function, 103 169, 170

Values: appearance of, 159â€“165

in check boxes, 289 balance sheet inputs for, 162â€“165

code for entering, 282â€“283 balancing rows in, 165

different formats for, 305â€“306 balancing the balance sheet in, 187â€“190

for option buttons, 292 cash formulas in, 174â€“176

for spin buttons/scroll bars, 295 COGS and gross profit formulas in,

for toggle buttons, 294 167â€“168

VBA (see Visual Basic for Applications) common stock formulas in, 186

VBA macros (see Visual Basic for connecting balancing lines in, 190

Applications macros) and connecting rows to complete

VBE (see Visual Basic Editor) model, 191â€“192

View Code icon, 291 current assets formulas in, 176, 178

Visual Basic: current assets input for, 162

turning on toolbar for, 276â€“277 current liabilities formula in, 181, 182

TLFeBOOK

Index

340

Working financial model (Continued): provision for taxes formulas in, 172,

date formula in, 166 173

debt formulas in, 183â€“185 revenues formulas in, 166â€“167

depreciation formulas in, 169, 170 SGA formulas in, 168â€“169

dividends formulas in, 172, 173 shareholdersâ€™ equity formula in,

EBIT formulas in, 169, 170 186, 187

EBT formulas in, 171, 172 shareholdersâ€™ equity input for, 165

formulas in titles for, 166 short-term notes formulas in, 181

income statement input for, 160â€“161 ST investments formulas in, 174, 176

intangibles formulas in, 178â€“180 structure of, 155â€“158

interest expense formula in, 171, 172 surplus funds formulas in,

interest income formulas in, 171â€“172 174â€“175

inventory formulas in, 176â€“177 total assets formula in, 178, 180

liabilities input for, 164 total liabilities and shareholdersâ€™ equity

long-term assets formulas in, 178, 180 formula in, 186, 187

long-term assets input for, 163 total liabilities formula in, 183, 185

long-term liabilities formulas in, 183, 185 Worksheets, moving between VBE and,

net income formulas in, 172, 173 278â€“279, 316â€“317

net PPE formulas in, 178â€“179

net to retained earnings formula

X

in, 172, 173

non-operating expenses formula XIRR function, 105, 108

in, 169, 171 XNPV function, 105, 107â€“108

NTF formulas in, 183â€“184

operating expenses formulas in,

Y

168, 169

other current assets formulas in, â€˜â€˜Yâ€™â€™ (yes), 311â€“313

176, 178 YEAR function, 85â€“88

other current liabilities formulas in,

181, 182

Z

other net worth account formulas in,

186, 187 Zero, 70â€“71, 205â€“206

TLFeBOOK

ABOUT THE AUTHOR

John S. Tjia (pronounced â€˜â€˜Chee-ahâ€™â€™) has 15 years of experience

in developing financial models, including 7 years as head of the

Models Group J.P. Morgan (now known as JPMorgan Chase) in

New York. He was responsible for designing, developing, and

providing the training for the firmâ€™s standard analysis and exec-

ution financial models for the Mergers & Acquisitions and

Investment Banking divisions worldwide. Prior to that, he was

an investment banker in the firmâ€™s Hong Kong office and had

assignments in credit analysis and financial advisory in New

York. Now a founding partner of the TMG & Associates, LLC

modeling consulting company (www.tmga.com), he lives in

Pleasantville, New York, with his wife and two children. He

can be contacted at johntjia@buildingfinancialmodels.com.

TLFeBOOK

This page intentionally left blank.

TLFeBOOK

ñòð. 62 |