Project 8 : Personal Budget

A spread sheet is useful software for a personal computer. They actually came from homework assigned in Harvard Business School. There was such a need for this kind of a program that a student wrote the first spread sheet software to help him do his homework. The uses of spread sheets are extensive, and they are one of the five most used software packages available. In Windows one can easily copy a spread sheet or its graphs into a word processing document.

The beauty of spread sheets is the ability to HANDLE and CHANGE DATA easily. It is like a number processor! Each location in the spread sheet is called a CELL and acts like a VARIABLE in a programming language. (Spread sheets represent 2-dimensional arrays, and using them is an easy way to learn this concept.) CELLS can hold a LABEL (text preceded by a ') or a VALUE or a FORMULA (math operation) which will compute its value based on the contents of other cells. All formulas start with either a + OR an @ based on the software. When you have what you desire typed in a cell, press either <-arrow > key to set it into the cell.

Project Steps

1. Load the BUDGET.xls file into the spread sheet from your Q:\StudentFiles directory.

2. Add more data to the spread sheet and complete the formulas for the new columns.

3. Graph the total expenses vs the months, add titles and labels.

4. Print out the graph and the data from the spread sheet

5. Prepare two other graphs and print them out.

6. Prepare a column for yearly totals and a grand total cell.

7. Experiment with printing options: Density and Portrait/Landscape, graphics or text printer.

8. You can CUSTOMIZE this spread sheet for your own budget.

Uses of Excel

Instructions and Help for Excel

Project Instructions :

• Log on to the network. The Exel spread sheet is found under the Messiah College menu, under Office Suites , under Microsoft Office , select and open Excel.
  
• Use the FILE menu. Select the OpenOptions and loadthe file ,BUDGET.exl on your Q: drive.
  
• The spread sheet will be displayed as shown below

A                  B      C     D     E     F     G     H     I
             Personal Budget for (Enter Your Name)
             Last Revised (Enter Date)

ITEM              Jan    Feb  March April  May   June  TOTALS

Transportation
     Repair      234.50   0.00                          234.50
     Gas          76.00  82.00                          158.00
     Loan        250.00                                 250.00
Education
     Books       213.50   0.00                          213.50
     Materials    56.00  25.00                           81.00
     Recreation   22.00  24.00                           45.00
Clothes           98.75 250.00                          348.75

Household
     Furniture   125.00 500.00                          625.00
     Utilities    75.00 125.00                          173.00

Food              98.00 125.00                          223.00

TOTALS           998.75 1364.00                (Total Each Column)
                                                (@sum( range ))

• Fill Out The Spread sheet through June i.e the first half of the year.
  Add data (make up any amounts) to all the appropriate cells of the spread sheet and complete formulas for the new totals columns.
  
  To add data to a cell use the mouse to point and then click to bring the cursor to the cell, or move (cursor keys) to a cell and type the amount. These cells are all formatted so that if you type 10 the spread sheet shows 10.00.
  
  If the cells are not formatted for $ 0.00.  Click the right mouse button and a menu will appear.  Choose Block Properties from the menu.  Choose Format and Choose the Currency option. Set the format to 0.00 <enter>.
  
  
• Now copy the top half of the spread sheet from transportation to Totals over to Half Year Totals.
  To copy the spread sheet
  
  Block the cells that you want to copy (A7 ..H22) by Holding the Left mouse button down on cell A7 and quickley moving the mouse to H22 (If a hand appears redo)). the whole area should be black.
  
  Click on right mouse button and choose Copy from the Menu. This copies the bolcked area into the buffer.
  
  Move the cursor to the cell you want to copy to (A 24)
  
  Now click on the right mouse button and choose Paste.
  
  Note Copy duplicates the text and numbers but changes the formulas to their relative value. (Cell located relative to the Source cells.)
  
  
• Erase the numbers in the second half of the year and enter in new, made up numbers for the second half of the year. Now complete the headings for the second half of the year in the correct cells: July  <arrow> August ...<arrow> December:
  
  
  If you ever get a row of ****** in a cell it is due to the column width being too small for the number. Be sure to check your printouts and widen columns as needed to avoid the ***** format.
  
  Correct the ******* error by increasing the width of the column.
  
  
• Next Save your work!
  
  
  Click on File. The click on Save. See the dialogue box with options!
  Type
  MYBUDGET <enter>
  The program automatically adds the extension. The first time you save a spread sheet, the steps above are all you need. The second time you save this file you click on Save and Excel prompts you that you already have that file name:
  
  Save as... allows you to change the name and location of the saved file.
  
  
• Print out the spread sheet.
  
  
  Click on Print print button and print out the whole spread sheet.
  
  You may want to check print preview to see how your output will look.
  
  
• Generate a BAR graph of the Recreational Spending for the months of Jan to June and print out a copy.
  
  
  
• Print out the graph
  

Make a 3-D Double Ribbon Graph that compares your Recreational spending and your total

spending for the months of Jan to June.

• Add Yearly totals to your spread sheet
  
  
  Make a column I at the second half of the sheet for yearly totals.
  
  The formula to type into cell I27 is +H5+H27 i.e. the cells that are the two half yearly totals.
  
  Once you have the formula correct for one cell, copy it for all the other cells in the Yearly Total column.
  
  Now sum up this column to a cell that you label Grand Total.
  
  Save and print out your spread sheet. Check for any errors or ****'s.

• Generate a Pie Chart for all the half yearly spending for Jan. to June.
  
  

• Print out a copy of the graphs generated above. Experiment with options of printing the spread sheet and the graphs on the same page.
  
  
• As an application of what you learned, alter and CUSTOMIZE this spread sheet for your own budget.

Report

Your report has at least the print out of the following:

(2) spread sheets and (3) graphs and your experiments. In your report be sure to write with pencil or pen comments about what you did, so that later the project will make sense to someone new to your work!

For further study:

In addition one can Name a graph so that several graph can be saved with the spread sheet. When a graph is named in can be Inserted into the spread sheet so that it shows at the same time as the actual numbers in the cells. This inserted graph reflects immediately changes to the cell referenced! You could try this Insertion procedure for extra credit.

Do a pie graph of the yearly spending for each category. You will need to make a new column with out spaces for this graph.

           Return to CSC 195 Home Page

This page is Copyright by J. Barry DeRoos and Robert Barrett. All Rights Reserved.
This page may be distributed and used freely, provided that the copyright notices remain intact.
Last Modified: Jan 10 ,2002.