waltervj@dartvax.UUCP (walter jeffries) (07/24/87)
Personal Finance Templates for Excel Version 5.5
Copyright 1987, Walter Vose Jeffries
Mountain Micro-Computer Consulting, Inc.
RR2-91J, River Road
Norwich, Vermont 05055 USA
waltervj@dartvax on UUCP
Shareware $10
Greetings fellow Mac user! This spreadsheet is what has evolved from
a set of spreadsheets I have been using for the past five years to manage
my own personal finances. Due to over whelming popular demand this is
being offered to the general public as a shareware package at a cost of
$10. That is to say that this is not free or public domain. If you use it then
PLEASE send me: shareware fee; a print out of the user log; your address;
and any suggestions you might have for making it better. If you have any
questions, and are a registered user, you may write me (enclosing a SASE)
and I will make an attempt to answer all questions as they come. If you
know anyone who might like this you are free to pass it on to them with
the stipulation that these directions, the shareware notice, and the
Copyright 1987, Walter Vose Jeffries) are passed on as a set.
In addition to this spreadsheet, which is a combination of several that I
use, I have a number of other spreadsheets available as briefly described
below. These will be made available at a shareware price of $5 for the
pair to registered users who send me a SASE and a disk requesting the
Financer and MicroFinancer.
If you should be unable to get this package from anywhere else you may
get it directly from me by mailing me: a SASE; 1 mac diskette; the $10 shareware
fee; and $1 for handling. I will return your diskette in your envelope ASAP
with the latest version of the template and documentation on it.
A Little History:
This spreadsheet evolved over the past five years from the time I first
started putting my finances onto a computer (a Columbia Data Products
with Perfect Calc.) Over the years it has been expand and refined, moving
through Multiplan and eventually onto Excel. It started as a simple way of
tracking a few expenses and grew to a system that tracked all of my major
expenses, bank accounts, gives projections, tracks budgets, and projects
taxes. It was very useful for me this spring when my wife and I bought
land and a house as we were able to take it, and my Mac, into the loan
office and give a presentation, using this and the other modules, that
helped convince the bank to loan us almost $200,000! (The payments are
why I need the $! :-})
A Little Future:
Where will these spreadsheets end up? Will they ever die or will it
become the spreadsheet that took over computerdom?!? Probably the next
thing I will do is add a set of macros for moving around within the main
spreadsheet and doing some of my more common actions. (I love macros
but never included any here.) If anyone has any suggestions I have open
ears. If the suggestions are something I might need I may implement
them. If not.... (read below) Also, at some point this year I will be updating
the tax tables to 1987. Updates will be available to registered users so
PLEASE register!
An Advertizement:
My company, Mountain Micro-Computer Consulting, Inc., is based on the
border of Vermont and New Hampshire. Although I have worked
extensively on more than 20 different computer systems, from micros to
mainframes, from IBM's to TRS-80's to Macintoshes, I am currently
specializing in the Macintosh. My company provides many different
computer and graphic services some of which I have listed below. If I can
be of service to you please don't hesitate tocompanies. Remember, no job is too large or too sm
Walter Vose Jeffries
Mountain Micro-Computer Consulting, Inc.
Rural Route 2 - Box 91, J
Norwich, Vermont 05055
Tel. (802)649-1MAC
waltervj@dartvax on UUCP
Computer Consulting Graphic Designs
Data Recovery Maps
Individual Enduser Support Cartoons
Excel Modeling & Templates Illustrations
Accounting & Business Systems Business Cards
Custom Application Programming Logos & Letterheads
Data Transfer Between Computer Systems
Disclaimer:
I have used this evolving package of personal finance management for
years and have worked very hard at perfecting it, BUT, there may still be
errors. I designed this for my own use and am only making it available to
the general public because so many people wrote me and asked for it. If
you use this, do so at your own risk. I refuse to be held liable for any
mistakes in the programming of this or in your application of this
worksheet. (On the other hand, I've been using it for a long time and think
it's the greatest thing since melted cheese. I hope you enjoy using it and
learn a little bit from the way it's all put together.)
What this is:
This spreadsheet is an intelligent checkbook ledger of sorts. You enter
information about your expenses and incomes. The spreadsheet then sorts
them out by categories and accounts to let you see where your money is
coming from and going to. It projects what your financial picture is going
to be like by month and by year.
What this isn't:
This isn't a business partner, an accountant, or a stock broker. It will
not make money magically appear where there is none to begin with.
That's your job. The projections of taxes are just rough estimates, not to
be used to argued with the IRS.
Overview:
Financer is made up of several sections as listed below:
Directions - Tells you how to use the Financer (in this separate file)
Ledger - The database of your expense & income entries
Account Summary - Summary of how your bank accounts are doing
Category Summary - What has come in and gone out of each category
Budget Summary - Summary of how your budgets are doing
Tax Table - Lookup table used for projecting tax liability estimates
User Log - A list of users who've tried this template
Database Criteria - Criteria used to find information
To get to a section just select GOTO... from the FORMULA menu and
double-click on the name of the section that you want to goto. Let's use
this and walk through the spreadsheet.
Grand Tour:
Goto the Ledger and study the example entries. The first column is for
putting checkmarks (option^v) next to checks that have come back
from the bank. The second column is for check numbers. Not all of the
accounts will have checks or numbers and the numbers are not needed by
the Financer. I use them as this is a replacement for my check ledger.
The third column is for the initial of who the entry is for. My wife and
I have some things that are joint (j) like travel, housing, food, and some
things that are separate (w & s) like education, working, etcetera. The
Financer is set up to deal with two people. To do a single person just
always enter the same initial. To do more than two people you will need
to significantly alter the spreadsheet by adding new people in the
Category Summary and adding more criteria in the Database Criteria. That
is beyond the scope of these directions so we will assume two or fewer
people are involved as that is the majority case.
The next column is the date of the entry, followed by the category (one
of the customizations you'll soon enter), what the entry is, where it was
done, the account it drew money from (soon to be defined) and the amount.
The last column is a little bit of error checking. It looks up the entered
initial, category, and account name in the appropriate lists. If the entered
values are valid a hollow bullet is shown in their place. If any of the
entries are not in the lists then a filled bullet is shown. The first symbol
is for the initial, the second for the category, and the third is for the
account. This is the same order that their listed in the ledger. This makes
it easy to spot what I found to be the most common form of data entry
error (misspelling the initial, category, or account name.) Check 889 is an
example where t was given as the initial and it should have been w, s, or j.
Check 890 shows the result of missentering both the category name and
the account name.
Now goto the Account Summary. This is where you'll see what is in
your bank accounts. Almost all of this is calculations made by the
Financer rather than entries made by you. Most of this should be
self-explanatory with the exceptions being the 'Last Period' and 'Basic
Expenses' vs 'Expenses'.
Last Period is used to make the prediction part of the spreadsheet more
accurate. What I do is enter in the dates for the last period as well as the
Basic Expenses, Expenses, and Income. These give some mass to the
predictions so that the Financier doesn't make wildly inaccurate
predictions toward the start of the new period.
Basic Expenses are those that do not end in 'ing'. I use this to separate
Working, Consulting, Transfers(ing) and such from my basic expenses. The
taxes are calculated based on Schedule G. The schedule is in the Tax Table
for the curious.
Next let's look at the Category Summary. This is where you set up the
categories that you want to divide your monies into as well as specify who
is involved (at the bottom.) Income, Expenses, and Net are shown as
actual and monthly estimates. The category Transfering is for Transferring monies between accounts.
The next section, Budget Summary, allows you to enter how much you
want to budget for each of the categories and then tracks how much is left
for the year and the month in each of the categories. At the far right you
can make some comments about the categories to remind yourself what
goes into them.
Below the Budget Summary is the Tax Tables which, while interesting,
are not important to this discussion as they are used automatically by the
Account Summary. The same can be said for the Database Criteria.
Getting Started:
To get started you will need to customize the Financer to your own
situation. To make this easier I have made all of the cells that need to be
altered bold. I'll now lead you through setting up the Financer for
yourself...
1. Goto the User Log and enter your name, the date, and your address.
This will be a fun way of seeing where the spreadsheet travels and what
far off lands it visits. It will almost be like traveling myself... :-) Save
the spreadsheet at this time so that your mark has been made and recorded
for prosperity. If you pass this spreadsheet on it is best to pass it on as
it is at that point rather than after you've modified it further.
2. Goto the Ledger and delete the example entries so that you can enter
your own expenses. Leave a column of a few of the error checking
formulas along the right hand side so that you can fill them down as
needed later.
3. Goto the Account Summary. Enter the names of your accounts and
their current balances into the Accounts and Beginnings. Enter today's
date in First day of This Period. Fill in the dates for the Last Period as
well as the Last Period values (Basic Expenses, Expenses, and Incomes).
4. Goto the Category Summary. In the first column enter the names of
the categories you wish to use. Hint: Use short names as you'll not want to
type a lot when you enter them in the Ledger. You can even use numbers if
you so please (the early versions of the Financer did.) Also enter the
names of yourself and your mate and initials in the initial column.
5. Goto the Budget Summary and enter yearly budget amounts and any
comments for the categories.
6. Save the newly customized Financer with a name that you prefer.
You are now ready to start entering items in the ledger and tracking your
monies.
Using the Financer:
What I have found works best is to enter items in the ledger in sets. It
is a pain to enter them at the end of every day so instead I keep track of
them on a note card with my checks in my wallet and then enter them when
I've filled one side of the note card. After I've finished making the entries
I select the error checking column from the last set of bullets to the end
of my entries, click in the formula bar, and press ENTER while holding
down the OPTION key to calculate just those formulas. This quickly checks
the new entries for typos. Then I save which calculates the new entries
into the Financer. Now I can go up to the Summaries and see how I'm doing.
As should be obvious from the example entries in the Ledger, negative
amounts are expenses and positive amounts are incomes.
When you receive this worksheet it should already be protected (under
the OPTIONS menu). This is so that you don't accidently damage it while
just getting to know it. All of the data entry cells are unlocked so it need
not be unprotected to be used. If you wish to change any of the protected
areas just choose UNPROTECT DOCUMENT... from the OPTIONS menu. There
is no password so it will unprotect instantly. In general I would suggest
leaving it protected so that the formulas are not accidently altered.
Rolling Over Your Accounts:
At the end of a period (ie. a year) you will want to roll over your
accounts. To do this just transfer the current values to the past values.
This involves the following steps:
1. Copy This_Period's values for dates to Last_Period's dates.
2. Copy This_Period's values for dollar amounts to Last_Period's dollars.
(Basic Expenses, Expenses, Incomes)
3. Copy final account Balances to Beginning Balances for all accounts.
4. Clear all of the entries from the Ledger leaving one (or more) of the
error checking cells with its formula in it (bullets should turn black
when calculated as there are no values in the Ledger).
5. Modify the Budget as needed for the new period.
6. Modify the PageSetUp to reflect a new year's title.
7. Save As... giving it a new filename for the new period.
8. Begin entering new Ledger entries.
Hope you all have fun using this and find it as useful as I have. I would
love letters, postcards, and comments. I can be reached at the above
address. Register so you can receive all kinds of neat upgrades, future macros, and who knows what else!
Walter Vose Jeffries
Mountain Micro-Computer Consulting, Inc.
Rural Route 2 - Box 91, J
Norwich, Vermont 05055
Tel. (802)649-1MAC