<<

. 21
( 62 .)



>>

u

range of values. An IRR is the rate that would bring the
NPV on a set if it flows to 0. The values must contain at
least one number that is positive and one that is negative.
The guess rate is a rate you can enter, such as 0.10
(10 percent), to help the function to begin its calculations
correctly. You can omit this guess, in which case Excel will
begin its calculations with the guess rate of 10 percent.
XIRR(values,dates,guess) will return the IRR on a range of
u

values associated with the dates. These two ranges must
span the same columns so that Excel will know which
year™s data are associated with which year. The dates in
XIRR can have irregular intervals of months or years.




TLFeBOOK
CHAPTER 6


Guerilla Accounting for
Modeling




We can do ˜˜modeling™™ for historical numbers without knowing
any accounting. Showing past data is not modeling, really,
because it is only an exercise in setting down perfectly reconciled
and matching numbers on the worksheet. When we want to do
financial projection modeling, however, we will be creating num-
bers based on new and changeable assumptions. We will need to
know how to make these numbers mesh with one another so that
they become useful information. We do this through accounting.


A BEAUTIFUL SYSTEM
Accounting may not seem to be the easiest”or most interest-
ing”subject to learn at first glance. But this system of keeping
track of the numbers in a business, first found in the records of
the trading centers of Venice and Genoa in the thirteenth century,
is really an elegant and beautiful system. Its core essence is a
simple system of logical and clear thinking. For this reason, if we
can have a good grasp of accounting flows, then the models we
create will likewise be logical and clear.
This chapter will attempt to explain just the basic principles
in accounting”the principles we need to understand in order to
create a working financial model, especially a working financial
projection model.
109



Copyright © 2004 by John S. Tjia. Click here for terms of use.
TLFeBOOK
Chapter 6
110




Accounting is a big subject since it needs to cover all aspects
of business operations, whether it is accounting for billions of
dollars of sales or keeping track of small internal flows like expen-
ses for office supplies. However, to do modeling, we need to know
only its main principles. Thus, this chapter is a small ˜˜guerilla
attack™™ on the subject: it will get us the best return on investment
in terms of comprehension for the time and effort required.
The process of creating a model in itself is a terrific way to
get more comfortable with accounting. With a model that imme-
diately tells you how the numbers are flowing through the finan-
cial statements, you will get a sense of accounting as a dynamic
system that keeps track of all the flows. You will never be fazed
again when you need to look at financial statements.


THE FINANCIAL STATEMENTS
There are three types of financial statements:
1. The income statement
2. The balance sheet
3. The cash flow statement
Whenever stakeholders”whether they are a business™s man-
agement, employees, investors, customers, supplies, or what have
you”look at a business, they want to ask questions such as these:
1. How much revenue does the business have, and how
well does it control its expenses so that there is positive
net income?
2. What does the business have as its assets (what it has
under its control), as its liabilities (what it owes), and as
its equity (what it owns outright)?
3. How is the money being used? For buying production
equipment? Paying down debt? For stocking up on
inventory? How much cash does the operation have at
the end of its accounting period?
Answering each of these three”and other”questions is
the main function, respectively, of each of the three financial
statements.




TLFeBOOK
Guerilla Accounting for Modeling 111




The Income Statement
The income statement is an ongoing record of the revenues and
expenses through the accounting period. The typical accounting
period is one year, and this fiscal year usually ends on December
31, to coincide with the calendar year. However, some companies
end the fiscal year in other months to be at the most favorable
position within the business season. Large retailers, like depart-
ment stores, like to have the fiscal year end in January or
February, when the year-end holiday shopping season leaves
them with their inventories low and cash in the bank high.


The Balance Sheet
The balance sheet is the snapshot of the company™s assets, liabil-
ities, and equity at the end of the accounting period. Note that
whereas the income statement is a record covering 12 months,
the balance sheet is a one-point-in-time record.


The Cash Flow Statement
The cash flow statement shows the flows of cash in the company:
how much is coming in and how it is being used. The cash flow
statement looks at the income statement for the year and the
balance sheet at two points in time: the end of the current year
and the end of the prior year. (The end of the prior year can be
thought of as the beginning of the current year.) It is the most
difficult to build, or, to put it another way, the easiest to build
incorrectly.


We Need Just Two of the Three Types
of Statements
To make our model useful, we will be including the three state-
ments in it. Although all three are interrelated, it is useful to
understand that we need only the information from the income
statement and the balance sheet. We always need the income
statement, as it gives the lists of revenues and expenses. We
will also need the balance sheet information, as it lists the




TLFeBOOK
Chapter 6
112




assets, liabilities, and equity. The cash flow statement informa-
tion, however, can be derived from what we know of the income
statement and the balance sheet. In fact, we should understand
that the cash flow statement is nothing more than a reconciliation of
the flows in the income statement and the changes in the balance sheet.
You will see later on that the balancing mechanism for the
projected balance sheet does not require the cash flow statement
either.


THE ACCOUNTING EQUATION
Let™s look at the balance sheet first. This is where the trickiest
question arises when you do modeling. The accounting equation
is this:

Asset ¼ Liabilities þ shareholders0 equity

Or to use the language of guerilla accounting:

What you have ¼ What you owe þ what you own

This is most easily illustrated when we buy something by
paying with some of our own cash and some with a loan, such as
a house. We can say that we ˜˜have™™ a house. But really, what we
˜˜owe™™ the mortgage bank is an amount that represents a big
portion of the house, and so the part we truly ˜˜own™™ is just a
small part of the house. In this case, the balance sheet for owning
a house would look like this:

Assets Liabilities

We have a house: $100,000 We owe on the mortgage: $80,000

Shareholder™s (SH) Equity

The portion of the house we truly
own because we put in our own cash: $20,000

Total assets: Total liabilities and SH equity:
$100,000 $100,000




TLFeBOOK
Guerilla Accounting for Modeling 113




In our real life, our personal balance sheet will have a lot
more items. Literally, it should have a listing of all that we have,
all that we owe, and all that we own. But that full balance sheet
will have the same idea as this simplified balance sheet: what we
have as assets must equal the resources for purchasing them”
our debts and our equity. Put another way, the two sides of the
balance sheet must have the same total; they must always equal, or
balance, each other. In a more graphically oriented way, these are
the two stacks of numbers that must be the same ˜˜height™™:




Liabilities

Assets



SH Equity




DOUBLE-ENTRY BOOKKEEPING
Now we come to what makes accounting a beautiful system:
double-entry bookkeeping.
˜˜Double entry™™ refers to the fact that any change in any of
the three”what we ˜˜own™™ (shareholders™ equity), what we
˜˜have™™ (assets), and what we ˜˜owe™™ (liabilities)”will cause a
change somewhere else, in one of the other two or in itself.
So let™s again imagine the house/mortgage/equity balance
sheet we saw above:
BALANCE SHEET NO. 1
House: $100,000 Mortgage: $80,000
Equity: $20,000
TOTAL: $100,000 TOTAL: $100,000



If we suddenly won $10,000 in a small lottery, we can add
an item called ˜˜cash™™ in our assets side. This is a new thing that




TLFeBOOK
Chapter 6
114




we ˜˜have.™™ Do we also ˜˜own™™ this? Yes, we do, because the cash
is ours. So we can also add this amount to our ˜˜equity™™:
BALANCE SHEET NO. 2

Cash: $10,000 Mortgage: $80,000
House: $100,000 Equity: $30,000
TOTAL: $110,000 TOTAL: $110,000

<<

. 21
( 62 .)



>>