Common-Size Income Statements In Excel

A common technique among financial analysts is to examine common-size
financial statements. Common-size financial statements display the data not as
dollar amounts, but as percentages. These statements provide the analyst
with two key benefits:

1. They allow for easy comparisons between firms of different sizes.

2. They can aid in spotting important trends which otherwise might
be not be obvious when looking at dollar amounts.

A common-size income statement is one which shows all of the data as a
percentage of the firm’s total revenues. Excel makes the building of
common-size financial statements easy, as we’ll see with the EPI data.

To begin, we need to make room for the common-size income statements.
Select any cell in column B, or all of column B, by clicking on the column
header. From the menus choose Insert Columns which will insert a
new column to the left of the selected column. This new column will
need to be resized so that it is approximately the same size as column C,
which was formerly column B. Now, repeat this process with column D
(the 2003 data). In B4 and D4 enter the labels: 2004% and 2003%,
respectively. We will start building our common-size income statements
with the 2004 data. In B5 enter the formula: =C5/C$5.4 The resulting
display is likely to be nonsensical

because the formatting will be the same as the cells in column C.
So change the number format (Format Cells) to a Percentage
format with 2 decimal places. You should now see that the result
is 100.00%. Copy B5, select cells B6:B15, and then
choose Edit Paste. You have now created a common-size
income statement for 2004.

To create the common-size income statement for 2003, simply copy
B5:B15 and then paste into D5.

You can easily see why this is a useful tool for analysts. By looking
at row 8, you can instantly see that Selling and G&A expenses
have risen quite sharply in 2004 relative
to sales. Also, looking at row 15 instantly shows that the firm’s net profit margin

Accounting In Excel

Accounting In Excel, or the term “spreadsheet” that
covers a wide variety of elements useful for
quantitative analysis of all kinds. Essentially,
a spreadsheet is a simple tool consisting of a
matrix of cells that can store numbers, text,
or formulas. The spreadsheet’s power
comes from its ability to recalculate results
as you change the contents of other cells.
No longer does the user need to do
these calculations by hand or on a calculator.
Instead, with a properly constructed spreadsheet,
changing a single number (say, a sales forecast)
can result in literally
thousands of automatic changes in the model. The freedom
and productivity enhancement provided by modern
spreadsheets presents an unparalleled opportunity for
learning financial analysis.
Building an Income Statement in Excel(accounting in excel)

The image below presents the income statement for Elvis Products
International (EPI) for the year ending December 31, 2004. We
will build this income statement first, and then use it as a base
for creating the 2003 income statement.(click the image to enlarge)


Principle 1:
Make Excel do as much of the
work as possible. Whenever
possible, a formula should be
used rather than entering
numbers. In the long run this
will minimize errors.


Principle 2:
Format the worksheet so that
it is easy to understand.
Borders, shading, and font
choices are more than just
decorations. Properly chosen,
they can make important
numbers stand out and get the
attention they deserve.

While we are building the income statement, we want to keep
a couple of general principles in mind. Principle 1 says that we
want to make Excel do as much of the work as possible.
Any time a value can be calculated, we should use Excel to do
so. The reasoning behind this principle is that we want to avoid
mistakes and increase productivity. A little thought before
beginning the design of a worksheet can help to minimize data
entry errors, and increase productivity by reducing the
amount of data that needs to be entered. Principle 2 says
that we should format the worksheet in such a way as to make
it easy to comprehend. There are many times that you will be
creating a worksheet for others to use, or for your own use at a later
date. Properly organizing the cells and judicious use of color and
fonts can make the worksheet easier to use and modify.
1 Worksheets that are disorganized and sloppily formatted do not
engender faith in their results.

It is usually helpful when working with multiple worksheets in a
workbook for each sheet to be given a name other than the default.
With the right mouse button, click

Tag: Accounting In Excel

Calculating Your Print Ad’s Cost

I have good news and bad news about print media ad costs. The good news is
that most print media actually have easy-to-read (albeit somewhat difficultto-
understand) rate cards that list the various costs for assorted ad sizes — a
welcome relief from the way radio advertising is negotiated

The bad news is that the rate card is nothing more than a starting point in
the media-negotiation and buying process, because newspapers, especially,
have created so many permutations of their basic (or open) rates that even a
professional media buyer has trouble deciphering them in order to come up
with the most frugal media buy. And after you discover the nuances of one
publication’s rates and myriad discounts, you can then call the next publication
and start all over again — no two publications’ rates and discounts are
alike. (I think they do this just to totally confuse you and to give their salespeople
a reason for being.)

After deciding where you’re going to put your ad, you need the help of a sales
rep, But before you go into a meeting with a rep, you need to know a few things
about how print ads are priced.

For example, somewhere in the mists of time, all newspapers made the diabolical
decision that no two advertising pricing schemes would ever be the
same. Newspapers and other publications generally price print ads by multiplying
the number of columns wide, by the number of inches high, by a dollar
amount for each column inch. For example, a quarter-page ad in most newspapers
is 3 columns wide by 11 inches high, which makes it a 33-column-inch
ad. So if the open rate for your local paper is $50 per column inch, you have
an ad that can cost you $1,650 for one insertion.

Unfortunately, it’s not always that simple. Here are just a few of the seemingly
infinite variations possible to that simple pricing structure:

1, The initial $50 per-column-inch rate can change for numerous reasons,
because it’s the open rate (the rate paid by a new advertiser who runs an
ad only one time).

2 If you’re willing to commit to running your ad multiple times over a certain
time period, you can reduce the open rate by as much as 50 percent.

3 Newspapers and other publications often give discounts for new businesses,
minority-owned businesses, first-time advertisers, political advertisers, nonprofit
groups, and so on.

4 If you’re willing to commit to three ads per week, and if you’re also willing
to make a substantial dollar commitment over an extended time
period, you can dramatically reduce your rate per ad.

5 You may qualify for more than one of these discounts — for example, if
you’re willing to commit to a long-term buy and you’re also a nonprofit.

Usually, newspapers offer what they call pickup rates. A pickup rate is a discounted
rate newspapers give in return for running the same ad two or more
times in the same week. For instance, if your first ad runs in the Sunday
paper, your newspaper rep may quote you a pickup rate as follows: “Our
pickup rates are 20, 30, 40, then 50, 50, and 50.” That’s her way of saying that
if you run your ad a second time in the same week, you receive a 20-percent
discount; a third insertion in that week gets you a 30-percent discount; a
fourth insertion gets you a 40-percent discount; and for every time you run
the ad in that same week after that point, you receive a 50-percent discount.
And the discounts apply to all ads you run Clearly, the discounts definitely
have a way of adding up! The ad in my example earlier in this section, which
I priced at $1,650 for a single insertion, ends up costing $594 at the end of
one week if you run it multiple times — which gives you a discount of 64 percent!

Ad pricing is complicated, confusing, convoluted, and intimidating. The only
way you can be sure you’re getting the best rate possible is to tell your rep,
in no uncertain terms, “Give me all available rates.”

Double Entry Accounting System Design For Single-Entry Folks

Businesses and nonprofit entities use double-entry accounting.
But I’ve never met an individual
who uses double-entry accounting in personal bookkeeping. Instead, individuals use single-entry accounting. For example,
when you write a check to make a payment on your credit card balance, you undoubtedly
make an entry in your checkbook to decrease your bank balance. And
that’s it. You make just one entry — to decrease your checking account balance.
It wouldn’t occur to you to make a second, companion entry to
decrease your credit card liability balance. Why? Because you don’t keep a
liability account for what you owe on your credit card. You depend on the
credit card company to make an entry to decrease your balance.

Businesses and nonprofit entities have to keep track of their liabilities as well
as their assets. And they have to keep track of all sources of their assets.
(Some part of their total assets comes from money invested by their owners,
for example.) When a business writes a check to pay one of its liabilities, it
makes a two-sided (or double) entry — one to decrease its cash balance and
the second to decrease the liability. This is double-entry accounting in action.
Double-entry does not mean a transaction is recorded twice; it means both
sides of the transaction are recorded at the same time.

Double-entry accounting pivots off the accounting equation:

Total assets = Total liabilities + Total owners’ equity

The accounting equation is a very condensed version of the balance sheet.
The balance sheet is the financial statement that summarizes a business’s
assets on the one side and its liabilities plus its owners’ equity on the other
side. Liabilities and owners’ equity are the sources of its assets.

One main function of the bookkeeping/accounting system is to record all
transactions of a business — every single last one. If you look at transactions
through the lens of the accounting equation, there is a beautiful symmetry in
transactions (well, beautiful to accountants at least). All transactions have a
natural balance. The sum of financial effects on one side of a transaction
equals the sum of financial effects on the other side.

Suppose a business buys a new delivery truck for $65,000 and pays by check.
The truck asset account increases by the $65,000 cost of the truck, and cash
decreases $65,000. Here’s another example: A company borrows $2 million
from its bank. Its cash increases $2 million, and the liability for its note
payable to the bank increases the same amount.

Just one more example: Suppose a business suffers a loss from a tornado
because some of its assets were not insured. The assets destroyed
by the tornado are written off and the amount of the loss decreases
owners’ equity the same amount. The loss works its
way through the income statement but ends up as a decrease in owners’
equity.

Accumulated Depreciation Accounting

The basic theory of depreciation accounting is unarguable: The amount
of capital a business invests in a fixed asset, less its estimated future
residual (salvage) value when it will be disposed of, should be allocated
in a rational and systematic manner over its estimated useful life to the
business.

A fixed asset’s cost shouldn’t be charged entirely to expense in the year
the asset’s acquired. Doing so would heavily penalize the year of acquisition
and relieve future years from any share of the cost. But the opposite approach
is equally bad: The business shouldn’t wait until a fixed asset is eventually
disposed of to record the expense of using the asset. Doing so would heavily
penalize the final year and relieve earlier years from any share of the fixed
asset’s cost.

Essentially, cost less residual value should be apportioned to every year
of the fixed asset’s use. (Land has perpetual life, and therefore, its cost
isn’t depreciated.) The theory of depreciation is relatively simple, but
the devil is in the details. And, I mean details!

Frankly, there’s not much point in discussing the finer points of
depreciation accounting. I could refer you to many books written
by accounting scholars on depreciation. But as a practical matter the
federal income tax law dictates the depreciation methods and practices
used by most businesses. The IRS publication “How To Depreciate
property” (2005 edition) runs 112 pages. You ought to read this
pamphlet— if you have the time, and the stamina.

Let me step on the soapbox for a moment. The depreciation
provisions in the income tax law are driven mainly by political
and economic incentives, to encourage businesses to upgrade
and modernize their investments in long-term operating assets.
By and large, businesses follow income tax regulations on
depreciation. As the result, useful lives for depreciating
fixed assets are too short, salvage value is generally ignored,
and depreciation is stacked higher in the early years. In
other words: fixed assets generally last longer than their income
tax depreciation lives; when disposed of fixed assets often have
some salvage value; and, a strong case can be made for allocating
an equal amount of depreciation to each year over the useful life
of many fixed assets. In short, actual depreciation practices
deviate from depreciation theory. Okay, I’m off my soapbox now.

The cost of land is not depreciated. Land stays on the books at
original cost as long as the business owns the land. Ownership
of land is a right in perpetuity, which does not come to an end.
Land does not wear out in the physical sense, and generally holds
its economic value over time. Buildings, machines and other fixed
assets, on the other hand, wear out with use over time and generally
reach a point where they have no economic value. Assume the business
decides to maximize the amount of depreciation recorded in the year,
according to the provisions of the income tax law.

Sample Of Credit Policy,How To Create And Maintain Credit Policy

Today we talk about sample credit policy and how to maintain it,

One of the chief causes of confusion not only within the credit

department but also between the credit and sales departments

is the lack of consistency in dealing with customer credit issues.

This includes who is responsible for credit tasks, what logical

structure is used to evaluate and assign credit, what terms of sale

are used, and what milestones are established for the collection process.

Without consistent application of these items, customers never

know what credit levels they are likely to be assigned, collection

activities tend to jolt from one step to the next in no predetermined

order, and no one knows who is responsible for what activities.

Establishment of a reasonably detailed credit policy goes a

long way toward resolving these issues. The policy should

clearly state the mission and goals of the credit department,

exactly which positions are responsible for the most critical

credit and collection tasks, what formula shall be used for

assigning credit levels, and what steps shall be followed

in the collection process. Further comments are as follows:

Mission. The mission statement should outline the general

concept of how the credit department does business: Does it

provide a loose credit policy to maximize sales, or work toward

high-quality receivables (implying reduced sales), or manage

credit at some point in between? A loose credit policy might

result in this mission: ‘‘The credit department shall offer credit to

all customers except those where the risk of loss is probable.’’

Goals. This can be quite specific, describing the exact performance

measurements against which the credit staff will be judged.

For example, ‘‘The department goals are to operate with no

more than one collections person per 1,000 customers, while

attaining a bad debt percentage no higher than 2 percent of

sales, and annual days sales outstanding of no higher than 42 days.’’

Responsibilities. This is perhaps the most critical part of the policy,

based on the number of quarrels it can avert. It should firmly state

who has final authority over the granting of credit and the assignment

of credit hold status. This is normally the credit manager, but the policy

can also state the order volume level at which someone else, such as

the CFO or treasurer, can be called upon to render final judgment.

Credit level assignment. This section may be of extreme interest to

the sales staff, the size of whose sales (and commissions) is based on it.

The policy should at least state the sources of information to be used

in the calculation of a credit limit, such as credit reports or financial

statements, and can also include the minimum credit level automatically

extended to all customers, as well as the criteria used to grant larger limits.

Collections methodology. The policy can itemize what collection steps shall be

followed, such as initial calls, customer visits, e-mails, notification of the sales

staff, credit holds, and forwarding to a collections agency. This section can be

written in too much detail, itemizing exactly what steps are to be taken after a

certain number of days. This can constrain an active collections staff from

taking unique steps to achieve a collection, so a certain degree of

vagueness is acceptable here.

Terms of sale. If there are few product lines in a single industry, it is useful to

clearly state a standard payment term, such as a 1 percent discount if paid in

10 days; otherwise full payment is expected in 30 days. An override policy can

be included, noting a sign-off by the controller or CFO. By doing so, the

sales staff will be less inclined to attempt to gain better terms on behalf

of customers. However, where there are multiple industries served with

different customary credit terms, it may be too complicated to include this

verbiage in the credit policy.

Company management can experience significant losses if it loosens the

credit policy without a good knowledge of the margins it earns on its products.

For example, if it earns only a 10 percent profit on a product that sells for $10

and extends credit for one unit on that product to a customer who defaults,

it has just incurred a loss of $9 that will require the sale of nine more units

to offset the loss. On the other hand, if the same product had a profit of

50 percent, it would require the sale of only one more unit to offset the loss

on a bad debt. Thus, loosening or tightening the credit policy can have a

dramatic impact on profits when product margins are low. Consequently,

always review product margins before altering the credit policy.

When economic conditions within an industry worsen, a company whose

credit policy has not changed from a more expansive period will likely

find itself granting more credit than it should, resulting in more bad

debts. Similarly, a restrictive credit policy during a boom period will result

in lost sales that go to competitors. This latter approach is particularly

galling over the long term, since customers may permanently convert to

a competitor and not come back, resulting in lost market share. To

prevent these problems, schedule a periodic review of the credit policy

to see when it should be changed to match economic conditions. A

scheduled quarterly review is generally sufficient for this purpose.

To prepare for the meeting, assemble a list of leading indicators for

the industry, tracked on a trend line, that show where the business

cycle is most likely to be heading. This information is most relevant

for the company’s industry, rather than the economy as a whole,

since the conditions within some industries can vary substantially from

the general economy. If a company has international operations, then

the credit policy can be tailored to suit the business cycles of specific

countries.

If a company’s products are subject to rapid obsolescence, a tight

credit policy can result in limited product sales that leave excess

quantities on hand that will be scrapped. In such cases, loosen the

credit policy on those inventory items most likely to become obsolete

in the near term. The logic is that, even if inventory is sold to

customers with a questionable ability to pay for the goods, this at

least presents higher odds of obtaining payment than if the

company throws away the goods. To implement this approach,

keep the credit department informed of the obsolescence status of

inventory items, usually by having the sales, marketing, and logistics

staffs flag potentially obsolete items in the inventory database

and giving the credit department online access to this information.

When customers send in orders, the credit staff

accesses this information, verifies the obsolescence status

of the items ordered, and modifies the credit policy as needed.

Tags: sample credit policy