I managed to avoid using spreadsheets for about a decade after they became widely available. In
the past, I had written computer programs that were essentially spreadsheets, but without
anywhere near the flexibility of even a Visicalc, let alone an Excel. In the 1970's, when I bought
my first house, I kept track of what the bank was doing to me by generating my own mortgage
list which included for each month's payment how much went to principal and how much went
to interest. (This is amazing information; you should try this on your own mortgage someday.)
This was done with a simple Fortran program. And it did catch an instance of where the bank
did mess up with one of my overpayments.
I would guess that, after word processing, spreadsheets are the primary reason for the PC
revolution. Your boss three levels removed may not know how to type, but he probably does
understand numbers real well, and if he has a computer on his desk at all (those that don't are
dying off, even at World Wide Widgets), he will probably be using a spreadsheet.
I got seriously motated to use a spreadsheet three months after I married, two years ago. It was
about April 12th. My taxes for my last year of singleness were long since done, and I had
suggested to my beloved that maybe I could help her with her taxes. Her taxes are somewhat
complicated in that she owns her own business, and employs a couple of people in a store. She
had always used an accountant, but for some reason that year the accountant got buried. (Well, he
was 97.) So she brought in the overflowing shoebox of mostly business related material, and
said that if I knew enough to write a Computorlink article about taxes, then I could really have a
ball doing hers.
Business taxes get complicated because you don't have just the Federale sitting there with his
hand out, you have the City of Spokane, the County, the State, the Department of
Unemployment, the Department of Labor and Industry, B&O Taxes, Sales Taxes, Social Security
Taxes, FUTA, etc etc. They used to say that a conservative is a liberal that got mugged, but now
I believe that a conservative is a liberal that tried to start her own business.
So somewhere in this pile of scraps was the information needed by all these agencies, and of
course, they all needed different pieces of information. Somehow in the course of the next
couple of days I sorted out all the numbers and filled out all the forms and, while the statute of
limitations has not quite passed yet, I think I did get all the stuff filled in correctly.
On April 16th, I informed my beloved that we were going to get into the spreadsheet business.
The solution to all this nonsense is simply having one place to go to get the information you
need, rather than to have to dig through piles of burger wrappers, envelopes, napkins, and the odd
receipt or two. It also helps if some of the math could be done for you in advance, so you can
look at a summary rather than a huge list of details that you have to add up (usually incorrectly)
with a calculator. Some of these forms require monthly entries, some quarterly, some yearly,
some all three. But all the data can be generated from one repository.
It should be noted here that I did suggest that there were computer programs available on the
open market to deal with this stuff. Things like Quicken's Quickbooks, for instance. However,
the sharp businesswoman in her determined that there was no reason to spend $120 for a program
when she had a self proclaimed highly skilled programmer by the short, um, leash.
I happened to have a copy of Quattro laying around for some reason. (Cute name. [Uno, Dos,
Tres,] Quattro. There was another spreadsheet named As Easy As [1,2,3].) Any spreadsheet can
do the simple math required for a simple business layout. The more sophisticated spreadsheets
have graph generating capability, financial projections, 'what if' modeling, and a bunch of stuff
that I certainly did not need at this point. The Windows version of Quattro is three dimensional,
in that you can define a series of topics across the page (taxes, wages, net loss, etc) and a series
of dates down the page, and then have this template duplicated over several pages. That way you
could do data entry for each employee on a separate page, and on the top page have the totals for
all the employees in the company. (This would probably not work for the 10,000 employees of
World Wide Widgets, but it seems quite adequate for a three person beauty shop).
First we have to figure out just what a spreadsheet is. It is an electronic means to enter data of
various sorts, and to apply mathematical functions on groups of that data. So, let us say that we
have a shop where the employees are paid by the hour, and that the shop is open six days a week.
The employees might work a different number of hours each day, and of course, a different
number of days each week. One could arrange a spreadsheet where the columns are as follows:
Week_Date Mon Tues Wed Thurs Fri Sat Total_hours G_Wage Taxes SSI Net
The first column would be the date of the start of the week, just for informational purposes. (The
spreadsheet can generate this for you, by the way.) The next six columns have for a particular
employee the number of hours worked each day of that week. That is the only data that you have
to enter. When that data is there, the following columns would be automatically calculated for
you: the number of hours worked that week, the gross wage paid, based on the hourly wage and
the number of hours worked, the taxes to be withheld (the IRS kindly provides a nice formula in
one of their fine readable publications that will fit in a spreadsheet), the Social Security taxes to
be withheld, and the Net Wages to be paid after withholding (gross less all the gimmies).
If you did this for the 52 weeks of the year, and then totaled all the columns at the end, you
would have the yearly hours, wages, withholding, SSI, and net wages for each employee on
separate pages of the spreadsheet. You could further subtotal all this by month, and quarter for
those bureaucracies that require those kinds of numbers. And, in a multipage spreadsheet, the
top (or bottom) sheet could be the total for all employees.
So, how do you get this thing to do these neat calculations? There are entire books written on
this subject, but the short answer is, each value appears in a Cell, and you can apply
mathematical functions to each Cell. Presume that each Column (Mon, Tues, Wed) is addressed
by a letter (A,B,C) and each row (week) is addressed by a number (1,2,3). The hours worked for
the first Monday of the first week would be B1 (Column B, row 1). The hours worked for the
week would be the sum of the numbers from column B to column G of that row (expressed as
@sum(B1..G1)). If you enter this formula into Cell H1 (the Total_Hours column), what will
appear when the spreadsheet runs and the data gets entered will not be the formula itself, but the
result of applying the formula to the data. Likewise, the other columns (taxes, SSI, net) can be
calculated with similar formulas. So, all you have to do is to enter your six items of data every
week, and your totals and generated values are automatically updated for you.
Life of course gets somewhat more complicated if you (or your loved one) cares about aesthetics.
You might want to format these money values with dollar signs and two decimal places always.
You might want to color encode some of the information. (I color encode the various months,
for instance). Sometimes a particular calculated item gets fiddled with by the state. This year,
for instance, the Labor and Industries people changed the tax rate starting in April, requiring me
to go fiddle with the spreadsheets for a few minutes. Each year, there is a slightly different
withholding formula that has to be entered.
The spreadsheet that I have generated, and which contains 99% of all the data needed for all the
silly reports we have to fill out, takes 20 columns and goes for 86 rows on four pages. But for
the wage information, I (yes, I) only have to enter one item of information for each day for each
employee.
This was my first spreadsheet of any complexity, so there was some learning curve involved.
Generating it took maybe a couple of nights before we had something useful, and some more
fiddling to get everything looking good and making sure we had all the columns we needed.
And then there is IRS Schedule C, which sort of started all this. This Schedule is used by
anybody who has a small business and needs to declare business expenses. The IRS has
provided on the Schedule some "suggested" topics for these expenses, and we feel that if the IRS
went to all that effort to suggest some topics, well by gosh, who were we to tell them that they
didn't get it quite right. So on a separate page of the spreadsheet, with its own layout and
formulas, I have a different set of columns:
Date Maintenance Supplies Office_Expenses Utilities Taxes Rent Insurance Fees
and a few other things. Whenever we write a check, we enter the item as a new row, with a date,
and put the value of the check in one of these columns. Sometimes it takes some creativity to
figure out just which column to use, but that is part of the fun of the Tax Game. At the end of the
year, we simply add a final row to the spreadsheet that adds up all the values in each column, and
that is what goes into Schedule C.
The next year's taxes after this spreadsheet effort, which were more complicated because we
were now filing jointly, were in fact far less complicated just for having this spreadsheet
available. I also have some slumlord property, and we have generated a similar spreadsheet for
dealing with Schedule E of our favorite government form.
Not only do these things keep the taxman happy, but they provide very valuable information to
you, the happy owner of the business, as to just how far in the hole you are so far this year, and
maybe just how you got into that hole in the first place. For most people, I would suggest going
and buying one of the professionally produced programs. But for the people who don't really
want to part with that kind of money, or think that you can do it better than some generic
program when it comes to your particular business, this is a fairly painless way to get organized.
Read Next Article --> Return to Home Page ^