CMPE 3 Assignment 1 - Spreadsheet

Assignment 1

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

This assignment will be out of 1000 possible points. Each item is worth 100 points.

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(K2:K9) + (SUM(M2:M9))/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:
- Arial Narrow
- Courier New
- Garamond
- Tahoma
- 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 how I filled cells B13:C13 with gray in my example. This is an example of fill color.
To change fill color:
- Select the cells you want to color.
- Find the Fill Color button. It is to the right of the Borders button, and looks like a paint bucket.
- 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.

EXTRA CREDIT: Create a Fibonacci series generator (100 points extra credit).

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.

SUBMIT YOUR EXCEL SPREADSHEET named assignment1.xlsx (or assignment 1.ods if you use OpenOffice/LibreOffice) through the Assignments section of Canvas.

Thank you to Dr. Chane Fullmer and Dr. Doanna Weissgerber for their authoring and contribution towards this (historic) assignment. Revised by Jeremy Baumgartner, Spring 2013.