Doug Opel
Ann Zinyemba

Word
Access
Excel
Publisher
PowerPoint

 
 
 

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


Topic Choices

What is Budgeting? Layout Software Eye on Ethics
Typography TruType and PostScript Understanding Graphics
Storage Space File Management  

 

   

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


Steps to Follow for Lesson
1. Opening Excel 2. Beginning a Budget Spreadsheet
3. Entering Data & Manuevering through the Spreadsheet 4. Entering Sequential Data
5. Experimenting with Numerical Sequences 6. Formatting Text in Cells
7. Changing Font Size 8. Altering Column Width
9. Aligning Text 10. Extending Text Across Multiple Cells
11. Finding a Sum 12. Finding an Average
13. Adjusting Decimal Places 14. Inserting Rows & Columns
15. Manipulating Borders 16. Fitting Text
17. Using Charts    
 

  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



v

How 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