Problem Set # 6 - Due November 22, 2002

• See the example resulting spreadsheet here. This is an example of what you're final spreadsheet may look like.
• you will be generating the same type of spreadsheet with DIFFERENT NUMBERS and different entries. The style is up to you. This is YOUR budget.
  1. You must have at least 8 columns.
     • MINIMALLY you should have one of two forms of budget, you can always add columns
     • 2 column groupings of bills and 2 column groupings of income
     A grouping is:
     • 1 column of text
     • 1 column of numbers
     • 3 column groupings of bills and 1 column grouping of income
     • It should have a minimum of two forms of bills
       • monthly bills
       • one other form of bills
         • weekly
         • annual
         • bi-annual
     • You must have some sort of income shown
       For example
       • parental income
       • weekly job income
       • monthly job income
       • financial aid income
  2. You will need an area for total Monthly bills
     • You will need to use functions here.
       • SUM adds groups of numbers
         reference the example work sheet
         • for example If I type =SUM(D2:D9) I add the cells from D2 to D9
         • empty cells do not change adversely affect the formula.
       • I can do multiple math tasks at the same time
       • =(SUM(B2:B9)*4) + SUM(D2:D9) will add my weekly bills and multiply them by four. To this value I add my monthly bills.
     • you'll want a mathematical formula describing your monthly bills.
       • For example weekly bills should be multiplied by 4, yearly bills should be divided by 12, bi-annual bills should be divided by 6, etc)
  3. You will need an area for Total Monthly Income
     • If you make one annual student loan, divide that figure by 6 in your spreadsheet calculation
       • My calculation from the spreadsheet for cell B11 =(SUM(J2:J9) + (SUM(L2:L9))/12)
  4. You will need an area showing how much money you have left over each month (it may be negative as students often go into debt)
  5. You will need to format your numbers as currency
     • Select the desired cells
     • Under the Format Menu select the Cells... option
       • Click on the Number tab
       • Choose Currency
       • Make sure the symbol shows
  6. You will need a font that is not the default somewhere.
     Some examples
     • Comic Sans
     • Arial Narrow
  7. You will need to italisize or bold face some text
  8. I would like the cells formatted
     • Notice the bold lines I have around my cells
     • MINIMAL formatting: Total Monthly income, Total monthly bills, and play money should be formatted with a box made up of a bold line
     • In Format menu choose cells
       • Brings up a dialog box
         • change the borders around cells
  9. I would like one cell to have a different color and/or pattern.
  • It should be visible when printed, so I recommend a pattern with contrasting colors
  • For the example it was cells A13:B13
  10. The monthly play money should be conditionally formatted
      • It should have a pattern or background color if the value is negative (Pattern if you plan on printing in black and white, a color if you plan on printing in color)
      • It should have a pattern or font color change if the value is positive
      • A. Click on the Cell (In the example Cell B14 )
      • B. Under the format menu, select "Conditional Formatting...>
        • Choose Cell Value Is
          • less than
          • fill in 0 in the other box
        • Click on Format....
          • click on the Patterns tab for colors and patterns
        • In the "Conditional Formatting "Add >> " another format
        • Choose Cell Value is greater than or equal
        • Format...
          • click on the Font tab to change the font
  11. PRINT THE PAGE
• Cells can be addressed by their coordinates
  • Cell A1 is the top left cell on the sheet
• Cells can be addressed by groups, this is useful for functions applied to them
  • A1:A5 references the column of cells from A1 to A5
  • B2:D2 references the row of cells from B2 to D2
  • B2:D3 references a block of cells
• It is possible to alphabetize lists AFTER you create them
  • Select the cells that you wish to alphabetize
    • make sure you get the text and the value that goes with it. (For example I would select A2:B4)
  • Notice the tool bar has a symbol which has an A on top and a Z on the bottom with an arrow beside it. If you click on this, the selected cells will alphabetize according to the contents of A2:A4)
    • If you don't like the results ^Z will reverse your last change

1. Starting Microsoft Excel
   • An empty spreadsheet should appear
   • IF IT DOES NOT show an empty spreadsheet
     • Select New from the File Menu
     • Choose a new workbook
     • Click OK
2. Click on Cell A1
   • Enter the text Weekly Bills
     • Change the font to anything other than the default. (For example Comic Sans)
     • Notice that as you type the text appears both in the box and the line at the top after the equal sign
     • It is possible to edit the text in either box
     • If the text doesn't appear in the line at the top
       • Go to the Tools menu
       • Choose options...
       • Under the View, choose to show Formula bar
3. Click on Cell A2
   • add text describing a weekly bill (for example food)
4. Click on Cell B2
   • Enter a value for the amount of money spent on the weekly bill. (For example if you live in the dorms you may buy one pizza per week. Your weekly bill would be $18.00)
5. Correct the width of column 1 to allow for all of the text to show
   • Put the mouse between the A and the B at the top of the spreadsheet.
     1. Click and release the left mouse button
        • Notice that the cursor changes to show a line and two arrows.
          • This allows us to pull column A over
        • Click the mouse button again, but this time hold it down while dragging column A to a width large enough to show all of the text
     2. OR double click the left mouse button between A and B at the top of the spreadsheet.