Personal Budget Exercise – MS Excel
6 Pages2247 Words399 Views
Added on 2019-10-18
About This Document
Learn how to create a personal budget and track actual expenses for the year using MS Excel. This exercise provides step-by-step instructions to create a spreadsheet to enter a personal budget and track actual expenses for the year. It includes suggested budget categories, column headings, formatting instructions, and formulas to calculate total expenses and item average expenses. The exercise also includes instructions to create a pie chart to show the items listed in the total projected costs column as slices of the pie chart.
Personal Budget Exercise – MS Excel
Added on 2019-10-18
ShareRelated Documents
Personal Budget Exercise – MS ExcelUse 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 monthlyincome 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)FoodUtilitiesMiscellaneousCar PaymentEntertainmentInsuranceGasStudent LoansSavings 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.RequirementPointsAllocatedComments1Open Excel and save a blank worksheet with the following name:“Student’s First InitialLast Name Excel”Example: JSmith ExcelSet Page Layout to Landscape0.1Use Print Preview to review how spreadsheet would print.2In the worksheet, insert a Custom Header titled, "My PersonalBudget."0.25This Custom Header text must be Arial 14 point, Bold, and be centered on the page.3Add 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.0.3Text format is Arial 10 point Normal text4Enter column headings :BUDGET ITEM, PROJECTED COSTS and the 12 0.3All column headings must use the following
RequirementPointsAllocatedCommentsmonths for the year: JANUARY THROUGH DECEMBERYou may abbreviate the months as follows: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DECUse all capital letterstext formatting:Arial 10 pointBoldText centered in columnAll capital letters5Enter your personal budget categories/labels (eitheryour own or the list provided) in the rows under the column heading BUDGET ITEM.A minimum of 9 categories is required.0.25Text format is Arial 10 point, Normal.Align text Left in the cell.6Format 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.0.25I 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 besidesCurrency, the score will be zero (0) for this component.7In 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. 0.25Arial 10 pointBoldBlackAlign values Right in column8Enter the label "PROJECTED BUDGET TOTAL:" in the next row in the BUDGET ITEM column.0.1Use the following formats:Arial 10 pointBoldBlueAlign text Right in the cell9In 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.)0.3Arial 10 pointNormalBlack
RequirementPointsAllocatedCommentsNote: 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).Align values Right in the cell10Under 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 mightbe 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.0.3Use the following text format:Arial 10 pointNormalAlign values Right in the cell11In the next row in the BUDGETITEM column (underthe PROJECTED BUDGET TOTAL label) enter the label "Total Monthly Expenses."0.1Use the following text format:Arial 10 pointBoldGreenAlign text Right in the cell12For 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 toadd 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.NOTE: do not include empty cells in your formula.0.513In the next row under the “Total Monthly Expenses” label put the label “Projected versus Actuals.”`0.1Use the following text format:Arial 10 pointBoldBlackAlign text Right in the cell14Then 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 0.5
End of preview
Want to access all the pages? Upload your documents or become a member.