Assignment

You will generate a Microsoft Excel spreadsheet showing your monthly financial budget.

• Your assignment should be saved as a Microsoft Excel document (.xls or .xlsx). OpenOffice Calc or LibreOffice Calc (.ods) are acceptable free alternatives.

• These directions will assume you are using Microsoft Excel.

• Your file must be uploaded to Canvas by the due date.

• See an example 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, since this is your budget!

• You must have at least the following items in your spreadsheet. Feel free to add more and make this financial budget a valuable tool for you.

Item #1: Have at least 8 columns.

At minimum, you must include one of the following:

• Two pairs of columns for bills, and two pairs of columns for income, or

• Three pairs of columns for bills, and one pair of columns for income.

• For any pair of columns, the left column should say the type of bill/income (ex. Rent), and the right column should contain the value (ex. $600).

• For the bills columns, one must be Monthly Bills. The others can be any of the following:

• Weekly Bills
• Annual Bills
• Bi-annual Bills
• Quarterly Bills
• For the income column(s), it can be any of the following:
• Weekly Income
• Monthly Income
• Annual Income
• Bi-annual Income
• Quarterly Income
• Parental Income
• Financial Aid Income
• Item #2: Have an area for Total Monthly Bills.
• You will need to use a function.
• Functions are started with an equals sign.
• Typing =SUM(B3,B7) will give you the sum of cells B3 and B7 (B3+B7).
• Typing =SUM(B3:B7) will give you the sum of all the cells between B3 through B7 (B3+B4+B5+B6+B7).
• Empty cells do not affect the formula (they act like a 0).
• You can do many math operations at one.

• For example, =SUM(C2:C9)*4 + SUM(E2:E9) takes the sum of weekly bills (in column C), multiplies them by 4 to convert to monthly values, and adds the sum of monthly bills (in column E).

• You need to convert all your bills to monthly values.

• For example, weekly bills should be multiplied by 4 (like in the example above), yearly bills should be divided by 12, bi-annual bills should be divided by 6, etc.

• Item #3: Have an area for Total Monthly Income.

• Like in Item #2, you will need to use a function.

• In my example spreadsheet in cell C11, I typed =(SUM(J2:J9) + (SUM(L2:L9))/12).

• Item #4: Have an area showing how much money you have left over each month (Play Money).

• Like in Items #2 and #3, you will need to use a function.

• This number might be negative, since students often go into debt.

• Item #5: Format your numbers as currency.

• First, select the cells you want to format. You can do this by clicking and dragging your mouse over the cells.

• Then, right-click on the selected cell(s) and select Format Cells...

• In the window that pops up, select the Numbers tab.

• Choose Currency.

• Make sure the symbol shows (a dollar sign).

• Click OK.

• Note: this may differ slightly depending on which version of which spreadsheet program you're using, but there will always be a way to select the currency style.

• Item #6: Use a font that is not default.

• Some examples of fonts that are not default:

o Arial Narrow
o Courier New
o Garamond
o Tahoma
o Verdana

• Default fonts include Calibri, Arial, and Times New Roman.

• Item #7: Italicize or bold some text.Item #8: Format some cells with borders.

• Notice the thick lines I have around the cells in my example. This is an example of a border.

• At minimum, you should have thick borders around your Total Monthly Bills, Total Monthly Income, and Play Money.

• To create borders:

• Select the cells you want to format.

• Find the Borders button. It is to the right of the Bold/Italicize/Underline buttons.

• Click the down arrow on the Borders button, and select the border you want to apply (choose Thick Box Border).

• Notice how added some borders can make your spreadsheet more organized and easier to read.

• Item #9: One cell should have a different fill color.

• Notice the how I filled cells B13:C13 with gray in my example. This is an example of fill color.

• To change fill color:

o Select the cells you want to color.

o Find the Fill Color button. It is to the right of the Borders button, and looks like a paint bucket.

o Click the down arrow on the Fill Color button, and select the color you want to apply.

• Item #10: The Play Money amount should be conditionally formatted.

• Select the box with your amount of Play Money.

• Under the Home tab at the top of the window, click Conditional Formatting.

• In the dropdown menu, select Highlight Cell Rules.

• Then click on Less Than...

• In the small window that pops up, type a 0 in the left box and select Light Red Fill with Dark Red Text.

• Click OK.

• Now, if the value in your Play Money box is less than zero, the box will fill with light red and the text will be dark red.

• Create a Fibonacci series generator.

• Below the spreadsheet you have created, format 10 cells in a row. Label the row Fibonacci Series Generator.

• End result: When you enter 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, if the first two numbers are 0 1 then the generator displays 1 2 3 5 8 13 21 34.

• It should work for any numbers typed into the first two cells.