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.”