Personal Budget Design (SDLC)

Personal Budget Design (SDLC)

Planning

The primary intention of this project is for students to learn some of the advanced features of Excel. Students will create an Excel workbook that they will use to calculate their own personal budgets. It also serves as an example of the Systems Development Life Cycle (SDLC) method.

Analysis

Students will create a multiple spreadsheet workbook to keep track of Expenses - allows user to input value for each month of the year by a category; Revenue – allows users to input the value of each month of year by category; Actual Expenses – allows users to calculate the actual expenditures that were incurred for each month of the year by a category; Variance – allows users to calculate the difference between the projected and actual expense and revenue categories; Pivot Analysis – allows users to visually depict the results of the budget variance amounts; VBA – allows users to adjust a range of numeric values by a specified percentage.

Categories in the workbook may include housing (rent), car insurance, groceries, transportation, etc. Students will document the projected revenues and expenses and actual expenses (SUMIF), and then use the SUMIFS and VLOOKUP functions to calculate variance (difference between what is budgeted and what actually occurred). Students will incorporate cell formatting, sheet, cell, and range naming, Visual Basic for Applications (VBA) and data analysis using pivot tables.

Design

The Revenue, Expense, and Variance worksheets will follow the design layout below, using column headings to indicate the month and row headings to indicate the budget categories.

Budget Category / JAN / Feb / Mar / Apr / Jun / Jul / Aug / Sep / Oct / Nov / Dec
Auto – Expenses (gas, insurance, etc.)
Food (Dining out)

The Actual Expenses worksheet will follow the design layout below. The column headings indicate the date the expenditure was made, the budget category, a description of the expense, and the amount spent.

Date / Category / Description / Amount / Month
1/1/2012 / Auto - Expenses (gas, insurance, etc.) / Gasoline / 35.00 / 1
1/1/2012 / Auto - Payment / Payment / 300.00 / 1

Development

Students will create an Excel workbook that includes the following sheets: 1) Documentation; 2) Revenue; 3) Expense; 4) Actual Expenses; 5) Variance; 6) Pivot Analysis. Use the design layout as described above to create the different worksheets.

Students will incorporate their own data into each of the sheets, performing calculations as instructed. In addition, each student should demonstrate use of the VBA subroutines by incorporating the Percentage Adjustment Macro into the workbook.

For each worksheet, a tutorial has been created. These are listed in the following table and are linked under Resources below. In addition, as listed below, the corresponding help topic (located in Excel under “Help”) will assist you in completing other worksheet tasks.

Worksheet Title / Tutorial / Excel Help Topic Reference
Revenue / Revenue and Expenses / Rename a Worksheet
Fill series
Formatting cells
VBA / (go to VBA editor help in Excel for all VBA help topics)
Input Box function
Application.ActiveCell property
Module and class module commands
MsgBox function
If/Then/Else statement
Macros dialog box
Expenses / Revenue and Expenses / Sheet tabs
Fill series
Formatting numbers
Actual Expenses / Actual Expenses / SUM function
Change column width
Variance / Variance I (SUMIFS);
Variance II (VLOOKUP) / SUMIF function
SUMIFS function
VLOOKUP function
MONTH function
Define and use names in formulas
Switch between relative, absolute, and mixed references
Pivot Analysis / Pivot Analysis / Create or delete a PivotTable or PivotChart report
Pivot data in a PivotTable or PivotChart report

Testing

Enter your data; review formatting so all values in a cell are visible; perform calculations and check for accuracy; perform a pivot analysis of the data; create VBA code that allows you to automatically adjust values by a percentage (Percentage Adjustment Macro).

Implementation

Once testing is complete and workbook is fully developed, assignment will be submitted to the instructor.

Maintenance

Make any revisions required by the instructor and continue to modify the workbook to accommodate your personal budget needs.