Personal Budget Exercise - MS Excel Use the project description HERE to complete this activity. For a review of the complete rubric used in grading this exercise, click on the Assignments tab, then on the title Personal Budget (Excel). Click on Show Rubrics if the rubric is not already displayed. Creating a spreadsheet to track personal expenses is an excellent use of Microsoft Excel. For this exercise, you will create a spreadsheet to enter a personal budget and track actual expenses for the year. You may choose to use real data with a projected monthly income amount that reflects your real data OR create a fictitious budget using a monthly income amount of $3,500.00 Here are suggested budget categories if you are not using a real budget. At a minimum, you must have 9 budget categories: In your projected budget amounts you would enter here what you HOPE to spend (or save) every month. In the actual expenses, the amounts would most likely vary each month. Housing (Mortgage or Rent) Food Utilities Miscellaneous Car Payment Entertainment Insurance Gas Student Loans Savings Note: there are several tutorials on Excel functions that can be found in the topic labeled "Optional Tutorials - Excel project" in the Content (Readings) list for Week 1. Requirement Points Allocated Open Excel and save a blank worksheet with the following name: 1 \"Student's First InitialLast Name Excel\" Example: JSmith Excel 0.1 Comments Use Print Preview to review how spreadsheet would print. Set Page Layout to Landscape 2 In the worksheet, insert a Custom Header titled, "My Personal Budget." 0.25 This Custom Header text must be Arial 14 Requirement Points Allocated Comments point, Bold, and be centered on the page. 3 Add a custom Footer with your name in the Left Section and automatic pagination in the Right Section. Put a fixed date (use the date this assignment is due) in the center portion of the footer. Text format is Arial 10 point 0.3 Enter column headings : All column headings must use the following text formatting: BUDGET ITEM, PROJECTED COSTS and the 12 months for the year: JANUARY THROUGH DECEMBER 4 You may abbreviate the months as follows: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC Arial 10 point 0.3 5 All capital letters 0.25 A minimum of 9 categories is required. Format all cells containing numeric data to Currency, using two decimal places. If you have this set up correctly MS Excel will automatically insert a "$" in front of the amounts that you enter. 6 7 8 0.25 In the column under your PROJECTED COSTS label, enter a monthly budget goal amount for each BUDGET ITEM category. This would be the limit of what you want to spend in each category (or put into savings) in one month's time. Enter the label "PROJECTED BUDGET TOTAL:" in the next row in the BUDGET ITEM column. Bold Text centered in column Use all capital letters Enter your personal budget categories/labels (either your own or the list provided) in the rows under the column heading BUDGET ITEM. Normal text Text format is Arial 10 point, Normal. Align text Left in the cell. I will be able to tell if you simply typed in the "$." This will result in zero (0) points for this item. If any other format is used besides Currency, the score will be zero (0) for this component. Arial 10 point 0.25 Bold Black Align values Right in column 0.1 Use the following formats: Arial 10 point Requirement Points Allocated Comments Bold Blue Align text Right in the cell 9 In the cell to the right of the PROJECTED BUDGET TOTAL label, use the SUM function to calculate the total amount of the PROJECTED COSTS column. (This amount should equal your income amount of $3,500.00 or your selected budget goal.) Note: do not enter each cell in the column individually when using the SUM function. Note: this amount should appear in ONLY ONE cell (not copied to remaining cells in the same row). 10 Under the heading for each Month, enter an actual expense amount for that item for that month. (For example, in the winter months, your utility bills might be higher). While some items might be the same from month to month, DO NOT enter the same amount for all items across the months. In each month you want to be close to you monthly income number but do not always have to match it exactly. Arial 10 point Normal Black 0.3 Use the following text format: 0.3 12 0.1 For the cells in this Total Monthly Expenses row, insert a formula that will calculate the total actual expenses for each month. Use the SUM function to add the amounts in each column and show the result. The sums for each month should not always equal your projected budget total. It would be rare to actually spend exactly what you budgeted for the month. Note: do not enter each cell in the column individually when using the SUM function. Arial 10 point Normal Align values Right in the cell In the next row in the BUDGET ITEM column (under the PROJECTED BUDGET TOTAL label) enter the label "Total Monthly Expenses." 11 Align values Right in the cell Use the following text format: Arial 10 point Bold Green Align text Right in the cell 0.5 NOTE: do not include empty cells in your formula. 13 In the next row under the \"Total Monthly Expenses\" 0.1 Use the following text Requirement Points Allocated label put the label \"Projected versus Actuals.\"` Comments format: Arial 10 point Bold Black Align text Right in the cell Then in the cell under the Total Monthly Expenses for each month, use a formula that will subtract the actual total expenses for the month from the projected budget total (the target amount in the PROJECTED BUDGET TOTAL column). You must use Absolute Reference in your formula 14 If the result of your calculation is a positive number, then you are under budget for the month. (You have money left over). If the number is negative, then you are over budget for the month. (You didn't have enough money to pay all of the expenses that month). 0.5 *You will use the result of this calculation to answer Question 3 below Enter a column label titled \"Total\" to the right of your last month. 15 16 Format : 0.1 Enter a formula using the AutoSum drop-down option on your tool bar and insert the Sum function in the first budget item row, under Total. Then copy this formula down for all the other categories. This will calculate the total expenditures for each BUDGET ITEM in your budget list over the span of the year. 0.5 Arial 10 point Bold Blue Align center in cell Use the following formats: Arial 10 point Bold Blue NOTE: Be certain to total just the months; do not include the PROJECTED COSTS column. Align values Right in the cell - NOTE: do not enter each cell in the row individually when using the SUM function You must use Excel to build a formula for adding the item amounts. If you simply type in a total, I will be able to tell and will award zero (0) points Requirement Points Allocated Comments for this component. Enter a column label titled "Item Average Expense" to the right of the Item \"Total\" column. 17 18 0.1 Enter a formula using the AutoSum drop-down option on your tool bar and insert the Average function of your expenses from January through December in the first budget item row, under the label "Item Average Expense." Then copy this formula down for all the other categories. 0.5 Note: do not enter each cell in the row individually when using the AVERAGE function 19 Apply All Borders to the cells with data. This means that there will be lines around all the individual cells that are used in your spreadsheet. Format your first row (column headings) by shading it to distinguish the headings from the number entries. These headings should already Bold. 0.5 Ensure that all column headings and row labels are fully visible. Either use Wrap Text OR expand the column width so that no labels are truncated. 20 Create a pie chart that shows the items listed in your total PROJECTED COSTS column as slices of the pie chart. (Note: This is budget not actual expense items.). You will use two columns for your chart - the BUDGET ITEM column and the PROJECTED COSTS column. Center the chart in the space below your budget numbers & expenses on the first page (the spreadsheet might take up multiple pages in Print View. You may need to resize the chart to do this. Check the Print view to ensure that the chart is centered below the spreadsheet cells on page 1. 0.5 21 Title the pie chart: \"My Personal Budget\" 0.2 22 Show dollar amounts on each segment of the chart. 0.25 23 Chart Legend 0.5 Ensure that all segments are clearly identifiable from your legend (on the right-hand side). The Format this label: Arial 10 point Bold Black Align center in cell Use the following text format: Arial 10 point Bold Black Align text right in the cell Overall, format the spreadsheet for readability and clarity. Be sure font size and type are used consistently. Use color appropriately to improve the appearance. Requirement Points Allocated Comments legend should contain your BUDGET ITEM list and be color-coded to match the chart. 24 Rename your sheet tab from \"Sheet 1\" to "Budget 2016" in the Sheet Tab area at the bottom left side of the spreadsheet. Delete unused sheets. 0.25 For the questions below, present your answers in a very readable format under the chart. These answers may be placed on the page below your chart if there is not sufficient room on page 1. Do not let the answers be "split" over more than one page. You can type your answer in one cell (in Column A), then highlighting and selecting several rows and columns, selecting merge cells and selecting Wrap Text. You will want to change the text from Center to Left justification. Play with this a bit. If you simply type your answer on a single line in Column A, that will also be ok. But be sure the entire answer can be read without the reader having to change any formatting. 25 25 Question1: If you received a $1000.00 bonus one month, how would you divide it to spend among the nine budget categories (in your PROJECTED COSTS) and why? Label your response Question 1. Answer this question in 2 to 3 sentences after the last row of your spreadsheet. DO NOT change your spreadsheet. Just respond to the question. Question 2: If your car unexpectedly needed a $500.00 repair, explain how you would reduce your MONTHLY BUDGET to pay for your car repair. Be sure to include the categories from which you will take the $500 in your explanation. Use the following text format: Arial 10 point 1.0 Normal/Black Align text left in the cell Use the following text format: Arial 10 point 1.0 Normal/Black Label your response Question 2. Answer this question in 2 to 3 sentences in a new row under your response to Question 1. DO NOT change your spreadsheet. Just respond to the question. 25 Question 3: State the amount that you were over or under budget for the month of August. (See the highlighted text above for how you determined if you were over or under budget for August.) What caused it? Align text left in the cell Use the following text format: Arial 10 point 1.0 Normal/Black Label your response Question 3. Answer this question in 2 to 3 sentences in a new row under your response to Question 2. DO NOT change your spreadsheet. Just respond to the question. TOTAL Align text left in the cell 10