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.