KSH Budgeting Tool
INSTRUCTIONS
Intro
The KSH Budgeting Tool can be a very simple money management tool. Like most
spreadsheet applications, you should know that spreadsheets are error-prone. It is easy to
make mistakes, accidentally delete things that should not be deleted, forget to copy formulas,
etc. If you are comfortable using Excel, know how to identify and fix formulas when needed,
understand how to use basic row operations (delete, copy, inserted copied rows, etc.), are
okay with the level of risk you take on by using a spreadsheet, and follow the instructions
and guidelines, you should find this spreadsheet very useful.
General Tips
- Except for customizing the categories, you normally only edit cells with a gray border:
- Some of the labels include cell comments (marked with little red triangles) to provide
extra help information.
- You can add your own cell comments! This is especially useful in the Budget worksheet,
where you can create notes to explain irregular or variable expenses.
- If you see "#####" in a cell, widen the COLUMN to display the cell contents.
- This workbook uses a lot of conditional formatting. Look up "conditional formatting" in the
Excel help system (F1) if you want to know how it works.
- You can add a limited amount of security by password protecting your workbook, but that
can be easily bypassed by anyone with malicious intent. The security of your data is your
responsibility.
- Backup your file regularly to avoid losing data! Excel files DO get corrupted occasionally.
Step 1 Customize Categories (or don't)
The easiest way to get started is to just use the default set of budget categories.
If you need to make changes to the categories, make sure that you make corresponding
changes in ALL the other worksheets.
TIP: If you are going to customize the categories, it may be easier to start from the Budget
worksheet and then make sure that the Report and Categories worksheets correspond.
A. If you ADD or DELETE Categories, make sure that you use row operations (copying or
deleting entire rows) to help ensure that all formulas are copied correctly. Always insert
new rows between the first and last item in the category. If you insert a row immediately
above the "Total" or "Subtotal" row, formulas will not stretch to include the row you inserted.
B. Category names in all the worksheets must match exactly. Conditional formatting is used
to highlight the category names if the categories are not found in the Categories worksheet.
C. No Duplicate Category Names
Using duplicate category names like "Other" will result in errors, but you may not easily
notice the errors. Make sure that each category is UNIQUE.
D. Do Not Remove or Add Major Categories
If you add or remove major categories (like Housing, Food, Health, etc.), many formulas
will need to be updated and it is very likely you'll miss something and end up introducing
errors. Try to use the default set of categories or RENAME the categories, but avoid adding
or removing the major categories.
Step 2 Define Your List of Accounts
List the accounts that you want to include in the Transaction History worksheet in the
list to the right. This list will be used to populate the dropdown box in the Accounts column
of the Transactions worksheet. If you want to include more than
TIP: If you start each account name with a different letter, Excel's autocomplete feature
will make entering the account much faster for you.
To add more accounts, insert rows above this line.
Step 3 Define your Yearly Budget
Define your budget using the Budget worksheet. Edit only the cells with the gray outline.
- You can copy and paste the input cells within the Budget worksheet as needed. For example,
enter an average fuel cost in January, then copy the value to other months.
- Use formulas to do basic calculations like "=245/6" to divide 245 by 6 or "=34*2" to multipy
34 by 2, or "=34+12+45" to add a bunch of numbers. Formulas are entered using the
equals "=" sign.
- First Month Must be January: Even though it is possible to change the labels in the yearly
budget, formulas used in the Report worksheet assume that January is in column B, February
is in column C, etc.
Step 4 Record Transactions
The Transactions worksheet lets you record transactions for multiple accounts. Think of a
PAYMENT as money leaving the account and a DEPOSIT as money entering the account .
Examples of different types of transactions are given below.
IMPORTANT: You must copy and paste entire rows when adding new rows, to ensure that all
the formatting, data validation, and formulas get copied correctly.
THE MOST COMMON ERROR is inserting a new row and forgetting to copy formulas,
especially the hidden formulas in columns J-L.
When you insert a blank row, some formatting is copied from the row above it automatically,
but formulas and data validation are not copied. Remember: COPY - PASTE - EDIT
Use the screenshot below as a guide for how to enter transactions.
Num: This column is usually used to list the check number, but you can also use it to enter
"DEP" for deposit, "TXFR" for transfer, "EFT" for electronic funds transfer, "ACH" for
Automated Clearing House transactions, etc.
Category: The Category field is essential to the functionality of this workbook. The Category
is a dropdown list that gets its info from the Categories worksheet.
The Category column uses conditional formatting to highlight the category PINK if the
category is not found in the Categories worksheet, BROWN if the category is not found in
the Yearly or Monthly worksheet, and GRAY if the category is blank, [Transfer], or [Balance].
If the formatting isn't copied correctly, you'll lose this double-check feature. So again, this
spreadsheet is not error-proof.
IMPORTANT: If you have chosen a category such as "Ksh's Fund" and then later remove
"Ksh's Fund" from the list in the Categories worksheet, it will not be changed in the Transactions
table automatically. You will need to make sure that you find all the records that have used
"Ksh's Fund" as the Category and change them to something else.
Step 0: Delete the Sample Rows
Step 1: Enter the beginning balance(s):
The Beginning Balance on JANUARY 1st for each account should be the first lines in the
register. If an account has a Negative balance (like a Credit Card), enter the balance as a
Positive value in the PAYMENT column. If an account has a Positive balance, enter the balance
as a Positive value in the DEPOSIT column. Use the Account Balance column to compare to
the balance shown on your bank or credit card statements.
Step 2: Add New Transactions:
You will probably find that the fastest way to add new transactions is copy and paste similar
previous transactions. You can select one or more rows, copy them, and then paste them
below the last transaction. Then, you just need to edit the cells that need to be changed.
TIP: Always leave the last row in the table BLANK so that you can easily add new rows. To
add new rows, select the last row of the table (row 57 in the image below) and drag the fill
handle down to copy the row down to create as many new rows as you need.
Fill Handle
Recording a SPLIT Transaction
If a single transaction needs to be allocated to multiple budget categories, you need to create
a SPLIT transaction. You can do this by splitting the transaction into multiple transactions -
one for each category. You can use the MEMO field to indicate that the transaction is a "Split".
ACCOUNT DATE NUM PAYEE MEMO CATEGORY PAYMENT
Checking 1/1/10 2032 Target Split Clothing 2000
Checking 1/1/10 2032 Target Split Groceries 1500
Checking 1/1/10 2032 Target Split Supplies 1000
Tip: If you want to verify the total amount of the split transaction, you can do a quick
calculation off to the side of the table using an Excel formula, like "=SUM(H13:H15)"
Recording a [Transfer] Between Spending Accounts
Record a transfer by listing two transactions (one PAYMENT, one DEPOSIT) to offset each other.
Choose "[Transfer]" as the Category for both transactions. For example, a Rs. 250 credit card
payment would be recorded as a transfer FROM your checking TO your credit card account:
ACCOUNT DATE NUM PAYEE CATEGORY PAYMENT DEPOSIT
CreditCard 1/1/10 TXFR [From Checking] [Transfer] 150.00
Checking 1/1/10 TXFR [To CreditCard] [Transfer] 150.00
NOTE: The above example assumes that the "payment" to your Credit Card is to pay off the
charges that you have already recorded earlier in the Transaction History table for the
CreditCard account. If you are NOT recording individual CreditCard transactions using the
Transactions worksheet, or part of the Rs. 150.00 was to pay down an outstanding debt, then a
credit card payment would look like one of the following, where "Credit Card #1" is a category
under Obligations.
Example 1: Not using Credit Card #1 any more, but still owe money on it.
ACCOUNT DATE NUM PAYEE CATEGORY PAYMENT
Checking 1/1/10 Credit Card Credit Card #1 150.00
Example 2: A portion of a credit card payment used to pay down outstanding debt.
ACCOUNT DATE NUM PAYEE MEMO CATEGORY PAYMENT DEPOSIT
CreditCard 1/1/10 TXFR [From Checking] [Transfer] 150.00
Checking 1/1/10 TXFR [To CreditCard] Split [Transfer] 125.00
Checking 1/1/10 TXFR [To CreditCard] Split Credit Card #1 25.00
Recording a [Transfer] To SAVINGS
When budgeting, you treat a transfer to Savings as an expense. You'll notice in the Budget
worksheet that there are multiple savings goals listed as sub-categories under the main
"To Savings" category. Instead of just throwing money into Savings, you should allocate the
money to specific savings goals based on percentages. For example, 50% to your Emergency
Fund, 25% to Retirement, etc. You do this by recording the transfer as a SPLIT transaction:
In the example below, the CATEGORY for the Savings account transaction is "[Transfer]"
while the CATEGORY for the Checking account transaction(s) are budget sub-categories.
ACCOUNT DATE NUM PAYEE CATEGORY PAYMENT DEPOSIT
Savings 1/1/10 TXFR [From Checking] [Transfer] 200.00
Checking 1/1/10 TXFR [To Savings] Emergency Fund 100.00
Checking 1/1/10 TXFR [To Savings] Retirement 50.00
Checking 1/1/10 TXFR [To Savings] College Fund 50.00
Step 3: Record "Cleared" Transactions.
When you see that charges have been processed or "cleared" by your bank, you should
enter a "c" in the reconcile (R) column for that transaction.
The Cleared Balance in the transaction history table shows the Account balance for the
transactions marked "R" for reconciled or "c" for cleared. This allows you to compare the
Cleared Balance with the current balance shown on your bank statements.
The Account Balance reflects your actual or effective balance and is the one you should be
looking at to stay on budget. The Cleared Balance is for comparing to your bank and credit
card statements. For example, when you write a check to a friend, it won't show up in your
bank account until they cash it. You should record the transaction immediately, to help you
stay on budget, but until the check shows up on your bank statement, your Cleared Balance
will be different from the Account Balance (until you enter a "c" or "R" in the reconcile column).
Step 4: Reconcile Your Accounts
You should reconcile your accounts at least one a month. Reconciling is also commonly known
as "balancing your checkbook".
A. Get your bank and credit card statements
B. Review your statements for errors and fraudulent charges
C. Your statements may include ATM fees, bank fees, or other transactions that you forget
to include in the Transactions worksheet. Add them.
D. Verify that the Cleared Balance (as of the Ending Statement Date) in the Transactions
worksheet matches the Statement Balance.
E. Enter an "R" in the reconcile (R) column for every transaction that shows up on your
bank statement.
TIP: If you are familiar with using Excel lists or tables, you can use autofiltering to filter the
transaction history table to show a single account at a time.
Step 5: Check Formulas
If you ever have reason to suspect that you forgot to copy formulas, you should UNHIDE
columns J-L and copy the formulas down.
Step 5 Track Your Progress
A very important part of good money management is keeping track of how your current
spending compares to your budget . You might check your budget status on a weekly or even
daily basis, but you should at least manage your money on a monthly basis.
Use the Report worksheet to compare your budget to your actual spending throughout
the month. Change the month by entering the month number (1 for Jan., 2 for Feb., etc.).
The Report pulls the budget info from the Budget worksheet and the actual spending from
the Transactions worksheet, so you can view the report at any time during the month and
see how much you have left (or how much you have overspent) in each category.
KSH Tools
opy formulas,
Input Cell
Label
** ACCOUNTS **
Checking
Savings
Credit Card 1
Credit Card 2
[End of List]
For example,
tomatically,
[Transfer] : Normal background color to identify Transfers
[Balance] : Normal background color to identify Beginning Balance
Blah Blah : Category not found in the Categories worksheet
Blah Blah : Category not found in the Budget or Report worksheets
e Transactions
r the balance
eed to create
n is a "Split".
et each other.
T DEPOSIT
150.00
g debt, then a
is a category
PAYMENT DEPOSIT
150.00
NT DEPOSIT
200.00
ncile column).
monly known
Yearly Budget Planner
KSH Tools
Starting Balance 0 [42] Total Avg
Total Income 0 0 0 0 0 0 0 30,000 30,000 30,000 30,000 30,000 150,000 12,500
Total Expenses 0 0 0 0 0 30,000 0 22,300 22,800 22,300 22,300 22,800 142,500 11,875
NET (Income - Expenses) 0 0 0 0 0 -30,000 0 7,700 7,200 7,700 7,700 7,200 7,500 625
Projected End Balance 0 0 0 0 0 -30,000 -30,000 -22,300 -15,100 -7,400 300 7,500
Monthly
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Total Average
INCOME
Wages & Tips 30,000 30,000 30,000 30,000 30,000 150,000 12,500
Interest Income 0 0
Dividends 0 0
Gifts Received 0 0
Refunds/Reimbursements 0 0
Financial Aid 0 0
Rental Income 0 0
INCOME-Other 0 0
Total INCOME 0 0 0 0 0 0 0 30,000 30,000 30,000 30,000 30,000 150,000 12,500
TO SAVINGS
Emergency Fund 1,000 1,000 1,000 1,000 1,000 5,000 417
Retirement Fund 0 0
College Fund 0 0
Investments 0 0
Taxes 0 0
Vacation Fund 1,000 1,000 1,000 1,000 1,000 5,000 417
SAVINGS -Other 2,000 2,000 2,000 2,000 2,000 10,000 833
Total TO SAVINGS 0 0 0 0 0 0 0 4,000 4,000 4,000 4,000 4,000 20,000 1,667
% of Income - - - - - - - 13.3% 13.3% 13.3% 13.3% 13.3% 13.3% 13.3%
CHARITY/GIFTS
Tithing 0 0
Charitable Donations 0 0
Religious Donations 0 0
Gifts 500 500 500 500 500 2,500 208
Christmas 0 0
CHARITY - Other 0 0
Total CHARITY/GIFTS 0 0 0 0 0 0 0 500 500 500 500 500 2,500 208
% of Income - - - - - - - 1.7% 1.7% 1.7% 1.7% 1.7% 1.7% 1.7%
HOUSING
Mortgage/Rent 2,500 2,500 2,500 2,500 2,500 12,500 1,042
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Total Average
Home/Rental Insurance 0 0
Real Estate Taxes 0 0
Furnishings/Appliances 0 0
Lawn/Garden 0 0
Maintenance/Supplies 0 0
Improvements 0 0
HOUSING - Other 2,000 2,000 2,000 2,000 2,000 10,000 833
Total HOUSING 0 0 0 0 0 0 0 4,500 4,500 4,500 4,500 4,500 22,500 1,875
% of Income - - - - - - - 15.0% 15.0% 15.0% 15.0% 15.0% 15.0% 15.0%
UTILITIES
Electricity 500 500 500 500 500 2,500 208
Gas/Oil 0 0
Water/Sewer/Trash 0 0
Phone 1,300 1,300 1,300 1,300 1,300 6,500 542
Cable/Satellite 500 500 1,000 83
Internet 0 0
UTILITIES - Other 0 0
Total UTILITIES 0 0 0 0 0 0 0 1,800 2,300 1,800 1,800 2,300 10,000 833
% of Income - - - - - - - 6.0% 7.7% 6.0% 6.0% 7.7% 6.7% 6.7%
FOOD
Groceries 500 500 500 500 500 2,500 208
Dining/Eating Out 2,000 2,000 2,000 2,000 2,000 10,000 833
Pet Food 0 0
FOOD - Other 0 0
Total FOOD 0 0 0 0 0 0 0 2,500 2,500 2,500 2,500 2,500 12,500 1,042
% of Income - - - - - - - 8.3% 8.3% 8.3% 8.3% 8.3% 8.3% 8.3%
TRANSPORTATION
Vehicle Payments 0 0
Auto Insurance 0 0
Fuel 500 500 500 500 500 2,500 208
Bus/Taxi/Train Fare 0 0
Repairs/Tires 300 300 300 300 300 1,500 125
Registration/License 0 0
TRANSPORTATION - Other 0 0
Total TRANSPORTATION 0 0 0 0 0 0 0 800 800 800 800 800 4,000 333
% of Income - - - - - - - 2.7% 2.7% 2.7% 2.7% 2.7% 2.7% 2.7%
HEALTH
Health Insurance 0 0
Disability Insurance 0 0
Doctor/Dentist/Optometrist 0 0
Medicine/Drugs 200 200 200 200 200 1,000 83
Health Club Dues 0 0
Life Insurance 0 0
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Total Average
Veterinarian/Pet Care 0 0
HEALTH - Other 0 0
Total HEALTH 0 0 0 0 0 0 0 200 200 200 200 200 1,000 83
% of Income - - - - - - - 0.7% 0.7% 0.7% 0.7% 0.7% 0.7% 0.7%
DAILY LIVING
Education 0 0
Clothing 0 0
Personal Supplies 500 500 500 500 500 2,500 208
Cleaning Services 0 0
Laundry / Dry Cleaning 0 0
Salon/Barber 0 0
DAILY LIVING - Other 3,000 3,000 3,000 3,000 3,000 15,000 1,250
Total DAILY LIVING 0 0 0 0 0 0 0 3,500 3,500 3,500 3,500 3,500 17,500 1,458
% of Income - - - - - - - 11.7% 11.7% 11.7% 11.7% 11.7% 11.7% 11.7%
CHILDREN
Clothing 0 0
Medical 0 0
Music Lessons 0 0
School Tuition 0 0
School Lunch 0 0
School Supplies 0 0
Babysitting/Child Care 0 0
Toys/Games 0 0
CHILDREN - Other 0 0
Total CHILDREN 0 0 0 0 0 0 0 0 0 0 0 0 0 0
% of Income - - - - - - - 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
OBLIGATIONS
Student Loan 0 0
Other Loan 2,000 2,000 2,000 2,000 2,000 10,000 833
Credit Card #1 2,000 2,000 2,000 2,000 2,000 10,000 833
Credit Card #2