Assignment-Basic Excel Spreadsheet
Spreadsheet Application
You are to create a personal budget spreadsheet for one year that will list expenses and income for 12 months. The purpose of this assignment is to learn basic MS Excel features.
Skills Used in This Assignment:
• Cell usage and access
• Basic formulas
• Text entry
• Numeric entry
• Cell Format
• Simple if statement
• Merge and Center
• Pie charts
Basic Requirements:
All of the following requirements must be met in order to receive any grade on the assignment. If the spreadsheet does not satisfy any of the following you may receive a zero on the assignment.
• The spreadsheet must be in Excel format 2010 or later.
• The spreadsheet will be for 12 months starting with the month the semester started on. Spreadsheets not starting on the correct month will receive a 25 point deduction.
o Fall term will be August,
o Spring term will be January,
o Summer term will be in April or June
• On the spreadsheet there will be three separate entries, one for Variable expenses, one for fixed expenses, and one for Income. See the attached example.
• In the variable expenses you must have at least 12 items over the 12 months. Examples include groceries, gas, babysitting, junk food, etc. Make up your own list. You can be creative but not obscene. Variable expense sectionsi with less than 12 items will receive additional deductions.
• In Fixed expenses you will enter at least 6 items that have a recurring expenses such as rent and cable. Each item should have the same value for each month. Again, be creative. Fixex Expense sections with less than 6 items will receive additional deductions.
• In the income section there must have at least two entries. One should be the salary from your regular job and the other from some other type of income such as part time work, investments, consulting, etc. Income sections with only one row of data will receive additional deductions.
• All of the entries must be on the same sheet for this assignment.
Assignment to Submit for Grade
1. Create a new file in Excel, and save it using your FSU username as the filename. Make sure that in the "Save As Type" field, you select "Excel Workbook (*.xlsx)". For example, if your email address [email protected], you could save the file as"dag11zAssgn4.xlsx". This is in Excel 2010 format or later.
2. Rename Sheet1 to BudgetXX. Where XX is the last two digits of the current Year. For instance if this was the year 2056 you would name it Budget56.
3. Create a Variable Expense section with the items listed on the left and months across the row. Start in row 3 or larger. There must be at least 12 items and a value for each of the 12 months. Use real or fictitious amounts. Variable expense sections with less than 12 rows of data will receive the following ADDITIONAL deductions:
1. 10-11 Items
2. 8-9 Items
3. < 8 Imtes
4. Sum the rows and columns:
1. Put a label on the row below the last item called Variable Monthly Total and sum the Column for each month. Use the SUM function.
2. Put a label after the last month called Yearly Total and Sum each row. Use the SUM function.
5. Create a Fixed Expense section with the items listed on the left and months across the row. This should be just below the Variable Expenses. There must be at least 6 items and an entry for each of the 12 months. Use real or fictitious amounts. Fixed expense sections with less than 12 rows of data will receive the following ADDITIONAL deductions:
1. 4-5 Items
2. 2-3 Items
3. < 2 Imtes
6. Sum rows and columns:
1. Put a label on the row below the last item called Fixed Monthly Total and sum the Column for each month. Use the SUM function.
2. Put a label after the last month called Yearly Total and Sum each row. Use the SUM function.
7. Create an Income Section with the items listed on the left and months across the row. This should be below the Fixed Expenses. There must be at least two entries and a value for each of the 12 months. One of course would be a regular job but think of something else like a second job. Use real or fictitious amounts. Income sections with only one row will receive an additional 30 point deduction.
8. Sum rows and columns:
1. Put a label on the row below the last item called Monthly Total Income and sum the column for each month. Use the SUM function.
2. Put a label after the last month called Yearly Total and Sum each row. Use the SUM function.
9. Format the top rows ( Careful, 25 point deduction of not done properly)
1. Fall term will be August through July ,
2. Spring term will be January through December,
3. Summer term will be in April through March for B Term or June through May for C Term
4. Merge at least four cells in the center of your data on row 1 and create a title for your spreadsheet. Include your full name (i.e. Kim Novak's Budget). Make the font at least size 24 and choose an interesting Font.
10. Create a row Called Monthly Total Expenses that shows the total expenses for each month ( add the variable and fixed expenses for each month)
11. Use a formula to calculate how much money is left over or under for each month and total for the year. Subtract the Monthly Expenses from the Monthly total Income.
12. Calculate how much money you earned in the year called Total Income.
13. Which Variable expense (rent, groceries, cell phone, etc.) did you spend most on over the 12 month period? Yours must look at all items over 12 months. Show this in your spreadsheet by inserting another label on the left part of the sheet (call it Maximum Yearly Expense) and function MAX() at the bottom of your spreadsheet that shows the maximum yearly expense. Your Maximum Expense Amount should only show the highest expense amount and not what it was for. For example, if your' largest yearly expense was $3000 for rent, your spreadsheet will show Maximum Yearly Expense
$3000.00.
14. What was the average you spent for the 12 month period (both fixed and variable)? Get the Average of the Monthly Total Expenses. Show this in your spreadsheet by inserting another label (call it Average Monthly Total) and function AVERAGE().
15. Insert a label and formula at the bottom of your spreadsheet that shows how much total money you have left over at the end of the year. You must subtract the total amount you spent from the total amount you earned.
16. Just below the cell where you show the amount left over use the IF() function to display a message based on the amount of money you have left over at the end of the 12 months. Feel free to make up your own goal. In the example, it was a trip to Paris. Yours should be something different. Insert a label identifying the goal.
Example Cell IF() Statement:
=if(b21>10000,"Buy a Plane","Broke")
17. Change all dollar amounts to CURRENCY format.
18. Sort your variable expenses items along with their associated values alphabetically
19. Change all labels in the spreadsheet to bold.
20. Change all cell contents, except the title, to one of the following fonts: Arial, Calibri, Comic Sans, Courier, Georgia, Times New Roman, and Verdana. Change the font size to 12.
21. Change column widths so that all data is visible.
22. Use cell fill color, and cell borders to give your spreadsheet an attractive appearance (use colors and style of your own choice). Both fills and borders must be used in at least one area of the spreadsheet.
23. Use conditional formatting on the cell that contains your yearly leftover amount (see step 10) to automatically change the cell fill color to red whenever the number in the cell is negative.
24. Create pie charts for each yearly totals for the Variable Expenses, Fixed Expenses, and Income. Include in each pie chart:
1. The labels
2. A title for each chart
3. Show the Values on the chart either by the actual value or percentage
25. Set the Print Area for the spreadsheet to include the cells containing data and the charts. --everything from cell A1 through the bottom right corner of your pie charts.
26. Use Print Preview to view your work.
27. Change the page orientation to Landscape. Note that your spreadsheets may require more than one page.
28. Type your full name in the Header of the spreadsheet along with the date you submit your work for a grade. Format it to bold and make sure it's centered.
29. Save your work and submit it to your Instructor for grading prior to the due date listed on the course agenda.