|
Microsoft Excel Lesson #6-Budgeting

Essential Question:
What is the best way for me to stay out of debit? How can I
calculate Math in a spreadsheet?
NTES Standard:
4. Technology
Communication Tools
a. Students use
telecommunications to collaborate, publish, and interact with peers,
experts, and other audiences.
Students know how to
use
telecommunications tools (e.g., e-mail, discussion groups, and
online collaborative environments) to exchange data collected and learn
curricular concepts by communicating with peers, experts, and other
audiences.
5. Technology Research
Tools
a. Students use technology
to locate, evaluate, and collect information from a variety of sources.
Students know how to
conduct an advanced search
using Boolean logic and other sophisticated search functions; they
know how to evaluate information from a variety of sources for
accuracy, bias, appropriateness, and comprehensiveness.
5. Technology Research
Tools
b. Students use technology
tools to process data and report
Students know how to
identify
and implement procedures for designing, creating, and
populating a database; and, in performing queries, to process data and
report results relevant to an assigned hypothesis or research question.
Resources: Copies of
Rubric/Self Check
Student Rubric: Click Here
Points: Points
Save As:
“LastNameFirstIntial”Budget
Directions: Podcast
Budgeting
Why Should I
Budget?
Controlling
your financial affairs requires a budget.
For many people,
the word "budget" has a negative connotation. Instead of
thinking of a budget as financial handcuffs, think of it as a
means to achieve financial success.
Whether you
make thousands of dollars a year or hundreds of thousands of
dollars a year, a budget is the first and most important step
you can take towards putting your money to work for you instead
of being controlled by it and forever falling short of your
financial goals.
To those of you
who think you know where your money goes without keeping
detailed records, I issue this challenge: keep track of every
cent you spend for one month. I promise you'll be surprised and
perhaps shocked by how much some of your "small" expenditures
add up to.
For an
eye-opening illustration, try the American Express
Saving or Spending Big Calculator.
Enter the cost and frequency of a habit or indulgence and how
many years you expect it to continue. Click a button and see not
only how much you'll spend over the specified time period, but
how much that same amount would grow to if you invested it at
various rates of return. Mind-boggling!
Budgeting and
tracking your expenses gives you a strong sense of where your
money goes and can help you reach your financial goals, whether
they are saving for a down payment on a house, starting a
college fund for your kids, buying a new car, planning for
retirement, paying off the credit cards, or saving for that trip
to Aruba.
Since financial
matters are one of the leading causes of marital discord and
divorce, getting a handle on your spending, implementing a
budget, and saving for the future can also have positive effects
on your relationship with your spouse or partner.
Should I Use a
Software Program?
You don't need
to invest in fancy software in order to do a budget, but a good
software program WILL make the job easier, and being able to
print out graphs and reports from your PC can serve as a
motivation for entering all that data.
Many banks are
now offering free PC banking and free personal finance software.
You simply dial into the bank's computer (or your bank may use
web-based banking), and download the checks that have cleared
your account, directly into your personal finance software. Then
you indicate an expense category for each check.
You can do a
basic comparison of budget versus actual expenses by category,
or you can enter more detailed information such as investments,
assets, liabilities, etc., and print personal financial
statements showing your net income and net worth.
Some of
the most popular personal finance software programs for
checkbook and expense tracking are:
Quicken,
MS Money,
and
MoneyDance.
Whether you use
sophisticated personal finance software or a couple of pieces of
paper and a pencil, the important thing is that you get on the
road to financial freedom by starting a budget today.
The $ Adds
Up...
|
Habit |
Yearly
Cost |
| Daily
Cup of Coffee |
$547/yr |
| 2
Packs of Gum |
$2555
- $3285/yr |
| 1
Hardback & 3 Paperback Books/Mo. |
$690/yr |
| Lunch
Take-out 5 days/wk @ $5-$10/day |
$1300
- $2600/yr |
| 3
Drinks in Cafeteria/day |
$936 -
$1092/yr |
| 3 Bags
of Cookies |
$624 -
$936/yr |
|
Graduation Chart

Step 1-
Opening Excel
For Excel 2000 click on
Start
|
Programs
|
Microsoft Excel or for Excel XP 2002/2003 click on Start |
All
Programs
|
Microsoft Word and a blank document opens were you may enter
information.
Screen Layout
Below are three toolbars
located at the top of the Excel screen. These may be used for shortcuts
in many of the activities. If you don't see one of the toolbars, select
View |
Toolbars
and select
the missing toolbar.
Menu bar - click on
any of the words in this row to see a menu of options in that category -
File and Edit are the most commonly used (saving, opening files,
copying, and pasting).
Standard toolbar
- icons in
this row provide shortcuts
to many
tasks accessible through the menu bar (opening, printing, cutting,
pasting) as well as handy functions (AutoSum, chart wizard, etc.).
Formatting toolbar
- this row
controls the format of text in cells (font, font size, style, color,
borders, alignments).
Some important
spreadsheet terms
Columns
- the
vertical divisions in the spreadsheet are called columns and are headed
by letters. Excel 2000/XP has 256 columns beginning with A and ending
with IV.
Rows
- the
horizontal divisions in the spreadsheet are called rows and are headed
by numbers. Excel 2000/XP has 65,536 rows.
Cells
- each
individual space in the spreadsheet is called a cell. Cells can contain
text, numbers, or both. Each cell has an address such as A1 or G6. There
are 16,777,216 cells in each Excel sheet.
Step 2- Beginning a Budget
Spreadsheet
|
Choose a title for
your 2002 budget and type it in cell A1. |
|
Step 3- Entering Data & Manuevering through the Spreadsheet
- Mortgage
- Car Payment
- Gas Bill
- Electricity Bill
- Water Bill
Be sure you begin with cell A3. To move down the A column,
either use the Enter key or the down arrow on your keyboard. You
may also use your mouse to click on the cell you wish to fill.
To move horizontally, use the Tab key or the right arrow key. |
|
Step 4- Entering Sequential Data
|
Cells B2-M2 will
contain the months of the year. You could type each month, but
let's use a shortcut. Type January in cell B2? Do you see a
small square in the lower right corner of cell B2? Point at this
square - the white cross becomes a black plus sign.
Click and
drag this
small square
across the
row to cell
M2. Release.
Did January
through
December
appear in
the cells? |
|
Step
5- Experimenting with Numerical Sequences
|
Excel recognized
the word January as the beginning of a sequence. Will it also
work with numbers? Move to cell A15. Begin a vertical numerical
list by placing the number 1 in cell A15. Try using the same
method you used for the months to create a numerical list of
1-10. Does it work? If not, what could you do to make it work?
Answer:
No. You must
first show a sequence! Enter 1 in A15 and 2 in A16. Highlight
both cells. Click and drag on the small square in the lower
right corner of cell A16. Drag to A24 and release. |
Step 6- Formatting Text in Cells
|
The months of the
year would look better in boldface. To make the word January
bold, click in cell B2, then click the letter
B on
the formatting toolbar. You could use this method to make the
rest of the months bold, but can you think of a more efficient
method? Make the headings in cells A3 through A7 bold, too.
Answer: Highlight the remaining months and
click
on the letter B on the formatting toolbar. |
Step 7-
Changing Font Size
|
To change font
size, be sure the months of the year are selected. Change the
font size from the default of 10 to 12. Can you do the same for
the data in cells A3 through A7?
Answer: Highlight the cells you wish to change.
Click on the Font Size dropdown and select 12. |
|
Step 8-
Altering Column Width
|
By increasing the
font size and making the typeface bold, we have made some of the
column and row headings too wide for their cells. There are
several methods of adjusting cells width. Move your white cross
to the line separating columns A and B. The white cross turns
into a bi-directional horizontal arrow with a vertical divider.
Double-click to widen the column to exact fit.
Try
another method. Move your white cross to the line separating
columns B and C. Click and drag this line to the right to widen
column B. This is a simple method, but what if you want all of
the columns to be the same width? This method would be tedious.
To adjust the width of multiple columns simultaneously,
highlight the columns you wish to alter, then select Format
|
Column
| Width.
Enter a value of 14, then click on OK. |
Step 9- Aligning Text
|
Each month is
aligned along the left edge of its cell. Would these column
headings look better centered in the middle of the cell? Select
row 2, by clicking on the 2 at the beginning of the row, then
click on the center alignment button on the formatting toolbar
(notice also the option for right alignment and the default
setting of left alignment for text). |
|
Step
10- Extending Text Across Multiple Cells
|
The title of
your spreadsheet is currently in the upper left corner.
Would you like to center it? You could type it in cell F1 or
G1, but there is a better way. Highlight cells A1 though M1.
Click on the
Merge and
Center
button on
the formatting toolbar (just to the right of the alignment
buttons). This merges the cells in row 1 and automatically
places your text in the center. You might then make the
title bold and change the font size to 14. |
|
Enter the
following amounts in the appropriate cells to begin the
budget spreadsheet:
|
|
January |
February |
March |
|
Morgage |
903.41 |
903.41 |
903.41 |
|
Car
Payment |
449.27 |
449.27 |
449.27 |
|
Gas
Bill |
125.32 |
115.67 |
95.99 |
|
Electricity Bill |
45.64 |
22.92 |
40.22 |
|
Water
Bill |
20.88 |
20.24 |
28.89 |
Step 11-
Finding a Sum
|
Select
cell A10. Type the word "Total" in the cell. Move to
cell B10. This cell will show the total amount paid
in January for the five expenses listed above it.
There are several ways to have Excel calculate the
sum of the values in cells B3 through B7. One method
is to type the following in cell B10:
=B3+B4+B5+B6+B7. However, there is a better
method. Select cell B10. Click on the AutoSum
button on
the Standard Toolbar. The AutoSum button shows an
uppercase sigma.
A box appears around cells B3 through B9. Use
your mouse to select only cells B3 though B7, then
press
Enter
on your
keyboard. Use the automatic copy feature to
calculate the sums in the rest of the columns.
Hint:
Click on B10
and drag to M10. This copies the formula! |
|
Step 12-
Finding an Average
|
Select
cell
O2.
Type "Average". Select cell
O3.
In cells O3 through O7, you want to find the average
paid for each bill. Again, there are several ways to
accomplish this. The method we will use is the Paste
Function feature. Click on the
Insert
Function
button
(Excel XP) or the Paste Function
button
(Excel 2000). This feature allows you to choose a
mathematical function to enter in a cell. Select the
function "Average" from the right-hand column.
Click on
OK. A
dialog box appears. The automatic setting should be
B3:N3, indicating Excel will find the average of
cells B3 through N3. Click in this space and change
the N3 to M3 since we do not want the empty cell N3
to be included in the average.
Click on
OK.
Use the automatic copy feature to place averages in
cells O4 through O10.
Note:
#DIV/0! in
O8 and O9 indicates division by 0 is undefined. I
would delete those.
Note:
To review
the formulas and their ranges used, press Ctrl -
`. |
|
Step 13- Adjusting Decimal
Places
|
Cells
O5 and O10 display more than two decimal places. To
adjust this, select cells
O5
through O10, then click on the Decrease
Decimal
button on
the Formatting Toolbar. |
|
Step 14- Inserting Rows
& Columns
|
Suppose you wish to add another bill to your budget
- your MasterCard Bill for instance. There is space
in rows 8 and 9 for more data, but to maintain a
space between the data and the totals, you decide to
insert a new row. To do this, select row 8 by
clicking on the 8 at the far left of the screen.
Select Insert |
Rows.
A new row is added. New rows are added above the
selected row. New columns are added to the left of a
selected column. Go ahead and add the MasterCard
Bill. Oops! The text is too big for the column. How
can you make the column fit the text? |
|
Step 15-
Manipulating Borders
|
Before
printing a spreadsheet, you may wish to place
borders around some or all of the cells. The default
setting in Excel 2000/XP includes no borders around
any cells. Highlight cells A1 through O12. Select
Format |
Cells.
Click on the
Borders
tab. In the
dialog box, you can select borders around the entire
selected area, around each cell, or only along
certain edges. You decide to place a border around
the A1:O12 block, but not to place borders around
individual cells. Click
OK.
To view how the spreadsheet will appear when
printed, click on the
Print
Preview
button on
the Standard Toolbar. Notice that the border appears
only around the outside, as specified, but that the
budget does not fit on a single sheet of paper. |
|
Step 16-
Fitting Text
|
In the
Print Preview mode, click on Setup
at the top
of the screen. The dialog box that appears includes
a Scaling section. In this section, you may select
Fit to
to fit your
spreadsheet to one page. Select this feature, and
click on OK. Your spreadsheet is
automatically reduced in size to fit one sheet of
paper.
Note:
You may
prefer Landscape orientation rather than Portrait. |
|
Step 17-
Using Charts
|
Highlight the cells A3 through B8. Click on the
Chart
Wizard
button in
the Toolbar. Select Pie from the chart
options on the left. Name the Chart "2002 Budget"
and click on Next and
OK to
accept all defaults. Click on Finish
to complete
the chart of your budget. Your pie chart appears on
your excel spreadsheet.
A picture
is worth a thousand words! |
|
Practical Budgeting Tips
|
|
In
today's world, its easy to spend "on impulse" using
credit that is soon stretched beyond a reasonable
limit. As most of us know, it's easy to spend too
much and save too little. Here, we offer practical
spending and saving tips to help you get on the
right financial track.
Give us a call with any tips or questions you have.
|
|
|
Identify your Spending Habits
Few of us
follow the experts' advice of saving 10 percent of our
paycheck each month.
-
Commit to a plan that will
help you curb your spending and produce positive
results.
-
Start by identifying your
spending habits. This will enable you to track your
spending each month and begin to budget.
Keep a
Spending Diary
-
Record the date, item and
amount of every purchase you make. This lets you
pinpoint areas where you're spending more than you want,
such as on impulse items or expensive restaurants.
-
Keep your receipts.
-
Review your spending patterns
and look for ways you can cut costs.
Establish
a Savings Plan
-
If your goal is to save 10
percent of your paycheck each month, start by signing up
for automatic payroll deduction. If you don't "see" the
money, it's much easier to save.
-
Limiting debt is also
important. No more than 20 percent of your net income
each month should be used to pay debts (this does not
include housing and living expenses).
Exercise a
Little Restraint
-
Wait 24 hours before making a
major purchase. Planning ahead of time helps you avoid
impulse buying.
-
Avoid shopping when you're
hungry, tired or depressed. You'll be less likely to buy
without thinking.
-
Distinguish between wants and
needs. Ask yourself: Is this purchase necessary?
Comparison
Shop
-
Shop around for the best
prices.
-
Avoid crowded stores. You'll
tend to buy higher-priced items just to "get it over
with."
-
Make a list when you shop and
stick to it.
Avoid
Credit Card Debt
-
Carry only one major credit
card.
-
Don't bring your credit card
with you when you shop.
-
Pay more than your minimum
payment. A card balance of $8,000 at an 18% interest
rate would take you 25 years to pay off with minimum
payments ($15,000 in finance charges).
Don't Get Discouraged!
Teens With
Checking Accounts
"Before
young people open a checking or credit card account,
financial education is vital," says Heckman. "No child
should be allowed to use financial services without
demonstrating that they understand their end of the
bargain."
- For
checking accounts, this means understanding:
- That
it may take a few days for deposits to hit the account
and the money to become available.
More
parents are introducing their teenagers to checking
accounts, ATM cards, debit cards, and even credit
cards.
- That
with electronic processing, you can't count on a delay
before checks hit the account--the money has to be there
when you write the check.
- How to
record checks in a register and reconcile the account
monthly.
- That
there are fees for overdrawing the account.
- That
some checking accounts pay interest or dividends.
For debit
cards, it means understanding:
- That
the card looks and acts like a credit card, but
withdraws cash from the checking account instead of
creating a loan.
- The
importance of safeguarding the card and PIN.
- What
to do if the card is lost or stolen.
With credit
cards, it's important to understand:
- How
compound interest works.
- How to
choose a low-rate, no-annual-fee card.
- How
grace periods help avoid finance charges.
-
That--with most cards--if you carry a balance, you lose
the grace period and pay interest from the moment you
make any new purchases.
- The
cost of making just the minimum payment.
- That
exceeding the credit limit or paying late results in
significant fees, and possibly higher interest rates.
- That
misuse of the card creates a poor credit history.
- What
to do if the card is lost or stolen.
Many credit
unions offer youth financial education classes to teach
these concepts; other courses are available online. "Jemma
took CUNA's online 'Guides to Independence' courses through
a credit union before getting her checking account and debit
card," says Sepich. "They covered how to open a checking
account, how to balance the account, and other topics."
Did you
know ...
Teenagers'
use of financial services is on the rise. They're using
checks, debit cards, and credit cards in numbers greater
than ever before--it's here to stay, so it pays to teach
them how. Consider this:
-
According to a recent Junior Achievement poll,
Washington, D.C., that asks teens about personal finance
and careers, more than 11% of its participants carry
credit cards; some are as young as 13 or 14. Three out
of 10 teenagers have checking accounts, many linked to
ATM (automated teller machine) or debit cards.
- One
out of three high-school seniors uses credit cards; half
of those have the cards in their own names rather than
being authorized users on their parents' accounts.
-
Seventy-eight percent of college students have credit
cards and the typical college student carries a balance
of $3,200. One out of 10 college students carries a
balance of more than $7,800.
- The
proportion of bankruptcy filers younger than age 25 has
risen from less than 1% 1995 to more than 5%.
Sources:
MSN Money,
abcnews.go.com
Resource links
|
vHow
Teens Get Sucked In
Between
juggling three jobs, paying her bills and trying to get out
of debt, she feels overwhelmed. "I just want to pay
everything off," she says. "I wish I didn't have to struggle
so much."
But Alba is no
debt-weary baby boomer. She's only 19 and a couple of years
out of high school.
Her financial
burdens may be heavier than other teens: She pays her own
college tuition and also helps pay the rent and utilities at
home.
But the sinker
was signing that first credit card application before she
had even graduated from high school. "It was fine at first,"
she says. "I used it mainly for gas. Then it just got deeper
and deeper."
Within a year
and a half of her 18th birthday, Alba was $2,500 in the hole
-- and a card-carrying member of the newest and youngest
group to spend beyond their means. Call them Teens in Red.
The slippery
slope
It's no secret
that many college students are quickly sucked deep into
credit card debt. But now it seems the problem can start
even before freshman year.
According to the
JumpStart Coalition for Personal
Financial Literacy, an
educational organization, nearly a third of high school
seniors reported having a credit card of their own or one
co-signed by a parent.
Because young
people under 18 technically can't apply for a credit card
without a parent's co-signature, it's hard to know precisely
how many teens have credit and how many are already in debt.
And CardWeb.com, which monitors the credit card industry,
doesn't yet track teen cardholders.
But according to surveys conducted by
Robert Manning, author of "Credit
Card Nation: The Consequences of America's Addiction to
Credit," the number of
incoming college freshmen with credit cards tripled between
1999 and 2002.
Those freshmen
carry an average of $1,585 in credit card debt, reports
student-loan lender Nellie Mae. Many, like Alba, started
building up debt even before their adult lives began.
Soliciting
teens and moms
I was shocked
to learn that kids not yet old enough to drive are receiving
card solicitations -- co-addressed to parents -- while they
are still living at home.
Janet Bodnar, author of "Raising
Money Smart Kids: What They Need to Know about Money and How
to Tell Them," is appalled
that her 16-year-old son regularly gets credit card
solicitations -- even if they include her name on the
address. She throws them out.
"I don't think
it's healthy for teenagers to have credit cards before they
go to college," says Bodnar, also deputy editor of
Kiplinger's Personal Finance magazine.
She disagrees
that young people learn financial responsibility by using a
credit card. "Most kids can't hand in a paper on time, let
alone pay a bill on time," she says.
"This is funny
money to them. It's not real. It's a license to spend, and
they're not learning how to manage money on their own."
How to
Help Teens Protect Themselves
Boost
their financial IQ:
A typical mistake
parents make is expecting children to make the leap from a
childhood savings account to managing a credit card, says
Levine.
"They need
to know how credit works: What do you do when the bill
comes? Why is it important to pay on time? How do interest
rates work?" Levine says.
Bodnar
recommends first helping kids manage cash through their own
checking accounts, then adding an ATM or debit card before
graduating to a credit card.
Set
limits:
Parents can also
step in by setting limits on credit. Erin Zimmerman was 17
when her parents co-signed for her first credit card. She
didn't mind the $300 limit, she says, because her parents
explained how interest rates worked and how quickly she
could get into financial trouble if she overspent. "They
said it was like digging a hole."
Zimmerman, 18,
is now a freshman at New Mexico State University, Las
Cruces. She says she's seen other young people get into hot
water: "They don't get the fact that you have to pay it
back."
Demonstrate the debt trap:
Bodnar says that one
way for young adults to grasp the slippery nature of
compound interest is to have them play around with a
debt calculator.
There's nothing like watching how minimum payments on a
$1,000 balance -- $20 a month at 12% interest -- mean six
years of debt. That's two years longer than it takes to
graduate.
A growing
number of groups are warning teens about the perils of
credit cards, using Web sites and basic personal finance
classes (see links at right).
Hip-hop mogul Russell Simmons launched a nationwide
Get Your Money Right Tour
with the Hip Hop
Summit Action Network. But if you can't get tickets for your
teen, that's OK. Michael Wood, of Teen Research Unlimited,
urges parents to remember that, when it comes to money
habits, you can be your child's best resource.
|
|
Rubric/Self Check |
|
|
Self Check
= |
Meets Expectations |
Needs Improvement |
Unsatisfactory |
|
1. Opening Excel |
|
2 |
2 |
2 |
|
2. Beginning a Budget Spreadsheet |
|
2 |
2 |
2 |
|
3. Entering Data & Manuevering through the Spreadsheet |
|
2 |
2 |
2 |
|
4. Entering Sequential Data |
|
2 |
2 |
2 |
|
5. Experimenting with Numerical Sequences |
|
2 |
2 |
2 |
|
6. Formatting Text in Cells |
|
2 |
2 |
2 |
|
7. Changing Font Size |
|
2 |
2 |
2 |
|
8. Altering Column Width |
|
2 |
2 |
2 |
|
9. Aligning Text |
|
2 |
2 |
2 |
|
10. Extending Text Across Multiple Cells |
|
2 |
2 |
2 |
|
11. Finding a Sum |
|
2 |
2 |
2 |
|
12. Finding an Average |
|
2 |
2 |
2 |
|
13. Adjusting Decimal Places |
|
2 |
2 |
2 |
|
14. Inserting Rows & Columns |
|
2 |
2 |
2 |
|
15. Manipulating Borders |
|
2 |
2 |
2 |
|
16. Fitting Text |
|
2 |
2 |
2 |
|
17. Using Charts |
|
2 |
2 |
2 |
DMS Comp App Home ~
MSD Decatur Home Page ~
Decatur Middle
School Home Page
Ann
Zinyemba
Decatur Middle
School Computer Applications
5108 S. High School Rd.
Indianapolis, Indiana
46224 |