. 59
( 62 .)


to know is that the Arg1 is an argument. You can use any name
of your own choosing instead of Arg1.
Now go to the worksheet part, select a cell anywhere, and
write. Within the parentheses in the worksheet, enter the number
you want to convert or the cell address holding the number to



where the cell B1 holds the number 68.
When you do this, the cell returns the value of 20. Yes, 68
degrees Farenheit has just been converted to 20 degrees Celsius.
You write the function in the worksheet in exactly the same way
it is defined in the function macro. VBA makes the automatic
connection between the value in the parentheses in the worksheet
function and the function macro™s argument Arg1 and makes the
function work.

Chapter 19

You can create a function macro that reads more inputs.
Here is one that multiplies the three inputs:

Function Mult3 (Arg1,Arg2,Arg3)
End Function

When you write the formula

it will return the results of multiplying the contents of the three
cells on the worksheet.

Here is a brief road map of the subjects to cover as you begin
your own exploration of VBA. I leave you with these:

u Declaring them using the Dim statement for the

appropriate data type.
u Declaring them at the local, module, or projectwide

Ways to control the execution of the code

u If-Then, the VBA equivalent of the worksheet IF

u Ways to repeat a block of instructions, e.g., the For-

Next loop, and the Exit For statement to stop the loop
when a condition is met.
u Error trapping

How to make procedures work with each other

User forms: How to create your own dialog boxes for data

input and display.
Custom toolbars and custom menus: How to create your

own toolbar for access to the features that you have in
your model.

Writing a Macro in Visual Basic for Applications 327

I hope I have given you a useful tour of modeling and Excel™s
features in support of making user-friendly models. Knowing
VBA adds to your ability to create even more powerful and
functional models, and there are tremendous resources available
now in bookstores and online for more advanced information
about this programming language. This book covers only the
beginning steps for learning VBA, and I invite you to turn to
these other sources for continuing your learning.

This page intentionally left blank.


> >, 165 Assets:
$ (dollar sign), 49“50 in accounting equation, 112“113
¼ (equal sign), 64 on balance sheet, 149“150
#DIV/0! error, 97 in balance-sheet-only cash sweep,
#NAME? error, 98 244
#REF! error, 98 in cash flow variation cash sweep, 262
¼TEXT function, 103 core, 219
#VALUE! error, 98 fixed, 150
net fixed, 150
other current, 150
A other long-term, 151
ABS function, 101 static totals for, 240, 244
Accounting, 109“110 Audit toolbar, 24“26
Accounting credits, 200 Autofilter, 311, 313
Accounting debits, 200 Available cash flow:
Accounting equation, 112“113 applying, to cash sweep, 265“266
Accounting format, 306 finding, 264“265
Accounts payable, 151“152 AVERAGE function, 83“84, 252“254
forecasting, 233“235
formulas for, 181“182
Accounts payable/cost of goods
sold * 365 (ratio), 215“216 Backups, 20“21
Accounts receivable, 149, 219 Balance sheet, 111, 119“143, 147“153
forecasting, 233 assets on, 149“150
formulas for, 176“177 attaching income statement to, 250“256
Accounts receivable/sales * 365 (ratio), balance sheet method of balancing, 122,
214“215 124“136
Accreting debt, 222 basic form of, 148“149
Accrued expenses, 152 cash flow statement method of
Accumulated depreciation, 150 balancing, 122“124
Adding, functions for, 77“81 common-size, 223, 224, 226
Additional paid-in capital (APIC), current vs. noncurrent items on, 148
152 excess cash on, 121
Alignment settings, 30“32 financial model, inputs for,
Amortization of intangibles, formula for, 162“165
169, 170 and income statement, 118, 136“143
Anchoring key, 56 liabilities on, 151“153
AND function, 76“77 NTF on, 121“122
APIC (additional paid-in capital), 153 output sheet for, 196“197
Apostrophe (for comments), 285 use of ˜˜plug™™ numbers in, 119“121
Arguments (in functions), 64“65 Balance sheet accounts:
Asset intensity ratio, 217 forecasting, 232“235
Asset turnover ratio, 218 reconciliation tables for, 202“203


Copyright © 2004 by John S. Tjia. Click here for terms of use.

Balance sheet method, 122, 124“136 Cash sweep (balance sheet only variation),
improved variation of, 132“136 237“256
static and dynamic numbers in, attaching income statement to, 250“256
130“132 for building balance sheet, 243“250
summary of, 125 connecting NTF to balance sheet, 250
Bank debt, cash sweep for, 238 connecting remaining surplus funds to
Bank debt/EBITDA (ratio), 221 balance sheet, 249
Banks, projection models for, 3“4 and difference between (total liabilities þ
Basis points, 230 equity) and total assets, 246
Benchmarks, ratio, 210 error-trapping formula, adding,
˜˜Black box™™ models, 17 255“256
Bold type, 15, 29 function of, 237“238
Bookkeeping, double-entry, 113“115 interest, calculating, 252“254
Boolean logic, 68“69 layout for, 238“239
Borders, cell, 33 and linking income statement to
retained earnings, 254“255
and NTF plug, 247
C and post-sweep debt numbers, 250
Calculation settings, 40“42 static total for assets side, finding, 244
Calculations: static total for equity, finding, 246
following flow of, 16“17 static total for liabilities, finding,
forced, 57 244“246
visibility of, 17 structuring, 239“243
Capital, working, 213 and surplus funds available for sweep,
Capital expenditures (capex), 8, 199 247“248
Caption property: surplus funds for payment of debt
for check boxes, 289 tranches, applying, 248“249
for command buttons, 290 Cash sweep (cash flow variation), 257“269
for option buttons, 291 assets side, laying out, 262
for toggle buttons, 294 available cash flow, finding, 264“265
Cash, 121, 150, 174“176 available cash flow to sweep, applying,
Cash accounts, forecasting, 232“233 265“266
Cash equivalents, 220 cumulative NTF, calculating,
Cash fixed charge coverage, 223 267“268
Cash flow statement, 111 cumulative plug lines to balance sheet,
flows in, 200“201 connecting, 268
output sheet for, 199“207 cumulative surplus funds, calculating,
purpose of, 199 266“267
reconciliations in, 202“207 debt amounts after sweep and reference
Cash flow statement method to balance sheet, calculating,
(of balancing balance sheet), 268“269
122“124 error-trapping formula, adding, 269
Cash flows, 4“5 laying out income statement and
available, 264“266 calculating net income, 263“264
in cash sweep calculations, 259 layout for, 259“261
from financing, 201 liabilities and equity, laying out,
from investments, 201 262“263
from operations, 201 structuring, 258“259
sources of, 200 CEILING function, 101
uses of, 200 ˜˜Cell Value Is™™ formatting, 308“309

Index 331

Cells: scroll bars, 295
addresses of, 49 spin buttons, 295
borders of, 33 text boxes, 290
with conditional formats, finding, toggle buttons, 294“295
Convergence, 141
hiding, 34, 35
code for, 284
locking, 34, 35
of financial statement sheets, 224
patterns, cell, 34
Core assets, 219
shading of, 37
Cost of goods sold (COGS), formulas for,
Check boxes, 289“290
Chief financial officer (CFO), 2 167“168
CHOOSE function, 74, 76 Cost of goods sold/accounts payable
Circular references, 43, 99“100 (ratio), 216
Code, macro: Cost of goods sold/inventory (ratio),
definition of, 315 215
editing, 279“285 COUNT function, 81“82
reading, 279“282 COUNTA function, 82
writing, 322“323 COUNTIF function, 82“83
Code Editor window, 317 Counting, functions for, 81“84
COGS (see Cost of goods sold) Coverage, ratios for, 221“223
Color: Create Chart key, 58
in number formatting, 307 Credit analysis, 4“5
settings for, 37“40 Credits, 115“118, 200
Column-width setting, 36 Currency format, 306
Combo boxes, 293“294 Current assets:
Command buttons, 290“291 financial model, input for, 162
Comments, 285 formulas for, 176, 178


. 59
( 62 .)