Assignment 2 - Due October 19, 2006

This assignment will be out of 1000 possible points. You will generate a Microsoft EXCEL spreadsheet showing your monthly budget.

• See the example resulting spreadsheet here. This is an example of what your 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. Each of items 1-10 is worth 100 points.
1. You must have at least 8 columns.
• MINIMALLY you will have one of two forms of budget, you can always add extra columns
• 2 column groupings of bills and 2 column groupings of income
• 3 column groupings of bills and 1 column grouping of income

A grouping is:

• 1 column of text
• 1 column of numbers
• The budget 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
1. 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.
• You 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 will 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)
2. You will need an area for Total Monthly Income
• If you receive one annual student loan, divide that figure by 12 in your spreadsheet calculation
• My calculation from the spreadsheet for cell B11 =(SUM(J2:J9) + (SUM(L2:L9))/12)
3. 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
6. You will need to italicize or bold face some text
7. You must format cells
• 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
8. One cell should have a different pattern.
• It should be visible when printed, so I recommend a pattern with contrasting darkness
• For the example it was cells A13:B13
9. The monthly play money should be conditionally formatted
• It should have a pattern if the value is negative
• It should have a pattern 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
10. SUBMIT THE EXCEL SPREADSHEET under assignment2.xls through the assignments section of WebCT

HELP WITH EXCEL

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.

EXTRA CREDIT

1. Fibonacci number generator for next 8 numbers (50 points)
• Below the spreadsheet for assignment 1, format 10 cells in a row. Label the row "Fibonacci Series Generator".
• When you enter whole numbers in the first two cells, the remaining cells will display the Fibonacci series
• In a Fibonacci series, the next number is the sum of the previous two numbers
• For example, 0 1 then displays 1 2 3 5 8 13 21 34
• It should work for any two other whole numbers also
2. Fibonacci number generator using Binet's formula (50 points)
• Below the row of 10 cells, format two more cells in a row. Label the row "Binet's Formula".
• Look at this reference for an explanation of Binet's formula
• When you enter a whole number in the first cell the second cell displays that series Fibonacci number.
• For example, entering 7 in the first cell causes 8 to appear in the second cell (8 is the 7th number in the Fibonacci series).
• Another example, entering 10 in the first cell causes 34 to appear in the second cell.
• Phi (the greek symbol that represents the golden section) is equal to the constant number 1.618033988
• Try using different precision for Phi (1.618 instead of 1.618033988). How does this affect the result?
• Use rounding to produce results which are integers instead of floating point numbers.

---

---

Thank you to Dr. Chane Fullmer and Dr. Doanna Weissgerber for their authoring and contribution towards this (historic) assignment.