Personal Budget Exercise ? MS Excel

(These instructions are also attached here as a Word document)

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 or create a fictitious budget using a monthly income amount of $2,500.

Here are suggested budget categories if you are not using a real budget. At a minimum, you must have 9 budget categories:

Personal Budget Exercise - MS Excel (These instructions are also attached here as a Word document) 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 or create a fictitious budget using a monthly income amount of $2,500. Here are suggested budget categories if you are not using a real budget. At a minimum, you must have 9 budget categories: Housing (mortgage or rent) Food Utilities Misc. Car payment Entertainment Insurance Gas Student Loans Savings Requirement Points Allocated Open Excel and save a blank worksheet with the following name: Use Print Preview to review how spreadsheet would print. \"Student's First InitialLast Name Excel\" Example: JSmith Excel Set Page Layout to Landscape In the worksheet, insert a Custom Header titled, "My Household Budget." Comments 0.25 This Custom Header text must be Arial 14 point, Bold, and be centered on the page. Requirement Add a custom Footer with your name in the Right Section and automatic pagination in the Left Section. Points Allocated Text format is Arial 10 point 0.25 Enter column headings : Normal. All column headings must use the following text formatting: ITEM, MONTHLY BUDGET, MONTHS - JANUARY THROUGH DECEMBER You may abbreviate the months as follows: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC Comments 0.25 Arial 10 point Bold Text centered in column All capital letters Enter your personal budget categories/labels (either your own or the list provided) in the rows under the column heading ITEM. 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. In the column under your MONTHLY BUDGET label, enter a budget amount for each ITEM category. 0.25 Text format is Arial 10 point, Normal. Align text Left in the cell. 0.25 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 Align text Right in column Enter the label "Monthly Budget Total:" in the next row in the ITEM column. Use the SUM function to calculate the total amount of the MONTHLY BUDGET column in the cell to the right of this label (under the MONTHLY BUDGET values). (This amount should equal your income amount of $2,500.) Under the heading for each Month, enter an actual expense amount for that item for that month. (For example, in the winter months, your Use the following formats: 0.25 Arial 10 point Bold Blue Align text Right in the cell 0.5 Use the following text format: Requirement Points Allocated Comments 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 In the next row in the ITEM column enter the label "Total Monthly Expense." Use the following text format: For the cells in this Total Monthly Expense row, insert a formula that will calculate the total monthly expense for each month. Use the SUM function to add the amounts in each column and show the result. Arial 10 point Normal Align text Right in the cell 0.5 Bold Green Align text Right in the cell In the next row under the \"Total Monthly Expense\" row put the label \"Compare to Budget.\" Then in the cell under the Total Monthly Expense for each month, subtract the actual total expenses for the month from the monthly budgeted amount (the target amount in the MONTHLY BUDGET column). 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). Use the following text format: Arial 10 point Bold Align text Right in the cell .25 *You will use the result of this calculation to answer Question 3 below Enter a Column for \"Total\" to the right of your last month. Enter a formula using the AutoSum dropdown 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 ITEM in your budget list. NOTE: Be certain to total just the months; do not 1.0 Use the following formats: Arial 10 point Bold Blue Align text Right in the cell - Requirement Points Allocated Comments include the MONTHLY BUDGET column. 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 for this component. Enter a Column for "Average Expense" to the right of the Total column. Use the following text format: 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 Average Expense. Then copy this formula down for all the other categories. Arial 10 point 1.0 Black Align text right in the cell Apply All Borders to the spreadsheet area only. This means that there will be lines around all the individual cells that make up your spreadsheet. Format your first row (column headings) by shading it to distinguish the headings from the number entries. These headings should already bo Bold. 0.50 Create a pie chart that shows your total Monthly Budget column divided by items. (Note: This is budget not actual expense items.) Title the pie chart: \"My Budget\" Show dollar amounts on each segment of the chart. Bold 1.25 Overall, format the spreadsheet for readability and clarity. Be sure font size and type are used consistently. Use color appropriately to improve the appearance. Ensure that all segments are clearly identifiable from your legend (on the righthand side). The legend should contain your ITEM list and be color-coded to match the chart. Center the chart in the space below your budget numbers & expenses. Rename your sheet tab from \"Sheet 1\" to "Budget 2013" in the Sheet Tab area at the bottom left side of the spreadsheet. Delete unused sheets. 0.25 For the questions below, you can present your answers in a very readable format by typing 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 Requirement Points Allocated Comments 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. Question1: If you received a $700 bonus one month, how would you divide it to spend among the nine budget categories (in your MONTHLY BUDGET) 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 $350 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 $350 in your explanation. Use the following text format: Arial 10 point 1.0 Bold Black Align text left in the cell Use the following text format: Arial 10 point 1.0 Bold 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. Question 3: State the amount that you were over or under budget for the month of April. (See the highlighted text above for how you determined if you were over or under budget for April.) What caused it? Black Align text left in the cell Use the following text format: Arial 10 point 1.0 Bold 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 Black Align text left in the cell 10