Purpose:
This assignment has two objectives:
1. To give you hands-on experience developing a budget spreadsheet that would help you track expenditures and income.
2. To allow you to do some "what-if" planning.
To Do:
Open a blank spreadsheet document and create an annual budget spreadsheet for yourself similar to that demonstrated in class. The spreadsheet must have columns indicating the months of a year and rows indicating income and expenses.
Assume you have some sort of job with a fixed monthly income.
Break up the expenses into two categories: fixed expenses and variable expenses, with at least five entries in each category. Fixed expenses must include rent and insurance. Other expenses might be car payment, tuition, etc. Variable expenses might include power, phone, credit card, food and entertainment.
Make the following modifications and/or additions to the spreadsheet:
1. Use AutoFill to complete the names of months in Row 1
2. Enter a value for the month of January for your income as being $800. Use AutoFill to fill in that row for the whole year
3. Enter a value for the month of January for $300 rent and use AutoFill to fill in that row for the whole year.
4. Enter a formula for the cells in the insurance row, computing insurance as 10% of rent and copy this formula for insurance to all months
5. Fill in reasonable values for other fixed expenses and use Fill Right to fill in for the entire year
6. Fill in reasonable and month-to-month varying values for variable expenses for the entire year
7. Create new rows calculating fixed and variable expense sub-totals and a monthly total
8. Create a new row indicating monthly net income (income - expenses)
9. Create a new row indicating the contents of your bank account. Assume you start the year with $2000 in the bank.
10. Calculate the balance of your bank account for each month as the previous month's balance plus the month's net income.
11. Calculate a yearly total for all budget items.
12. Find a monthly budget for entertainment (or other variable expense) that will result in an ending balance in your bank account of $1000. Highlight this row with a red background.
13. Change the number format to currency.
14. Place a box border around the TOTAL column.
15. Use the AutoFormat feature format the spreadsheet in an attractive format .
Submit:
Send me the final budget spreadsheet file as an attachment to an E-mail message to kklemow@wilkes.edu.
Please put your name and other information in the header. This project is worth 25 computer assignment points.
DEADLINE IS 5:00 P.M. WEDNESDAY, 13 NOVEMBER.