Upload Assignment: Assignment 13
Page 6 of 6
INSTRUCTIONS
Create an Excel Spreadsheet with your Annual Budget. Information can be fictional. I don’t need to see your actual income and expenses. Use the picture file attached to get a visual of what I’m expecting. Use the rubric below for the specific details to complete this assignment.
You may use your file from Assignment 12 to build on to complete this assignment.
1. Title the Assignment with your last name + Annual Budget (example: Johnson Annual Budget)
2. Use that title in the first row that is “Merged and Centered” for the width of your data
3. Use Fill Color in the header field
4. Change the font style and size of the header text
_______________________________________________________________________________________________________
5. Display all grid lines in the area being used with the “All Borders” tool.
6. Use a horizontal bold line to separate the month titles across the top below the page header (January, February, March, etc.) and make all Column Headings and Data Row titles in bold font.
7. Use a Thick Box Border to separate the item list on the left from the sheet data
8. Enter data into the fields as whole numbers (This data can be made up information as I don’t need to see your true information). You can enter whole numbers and highlight all data and right-click and format cells. Format the cell numbers for Number and select comma for 1000 separator and 2 decimal places and for Currency use default dollar sign $ symbol. This sets formatting all at once for 2 decimal places,comma for 1000 separator and dollar sign $ to keep you from changing these number formats one at a time in your home tab menu.
_______________________________________________________________________________________________________
9. Use a horizontal bold line to separate the month columns across the bottom below the last row of data.
10. Use fill color in the column with data items (example: Rent, Food, Gas, Electric, Child Care, etc.)
11. Use a SUM tool to total each column as well as each row of data items. Use the Help feature to learn how to do this if necessary. I’m grading the formula; not the actual numbers that display.
12. Skip a row below the totals line.
13. Tab over to the last column of totals and enter your total annual income (again, this can be a fake number value)
_______________________________________________________________________________________________________
14. In the field below that, enter formula to calculate the total annual income minus your total expenditures. I will be looking for the actual formulas here too, so don’t use a calculator to enter that number. Annual Income will be a static number. I will be grading on the formulas in the field that corresponds to the Annual Cost and for the Differential.
15. In the 3 fields that are separated, format the cell numbers for Number and select 1000 comma separator and 2 decimals and for Currency use default $ symbol.
16. Put a bold black box around these 3 separated fields and a line to separate the balance of the final field. That field should show you what you have left over as surplus income or how much you are living in deficit.
_______________________________________________________________________________________________________
Formulas:
You can highlight each row of data and click on the AutoSum command tool to add values for total
To add each row (for example Rent) for total, formula should be =SUM(B3:M3) cells change for the next row
You can highlight each column of data and click on the AutoSum command tool to add values for total
To add each column (for example January) for total, formula should be =SUM(B3:B11) cells change for the next month
Type in Annual Income
Highlight all data in spreadsheet and right-click and format cells. Format the cell numbers for Number and select comma for 1000 separator and 2 decimal places and for Currency use default dollar sign $ symbol. This sets formatting for all data cells at once in one place for 2 decimal places,comma for 1000 comma and dollar sign. Annual Expenses formula should be =N10 to bring that data down. Differential formula should be =N12-N13.
Your sheet should be automated to the point of when you change monthly values then your monthly and annual totals will change. If you see ###### in for values inyour sheet, that means you need to widen columns to see your data.
See the attached file for a visual example.
Save as assignment module number – your
