UW - Laramie, Wyoming | University of Wyoming



PlanningThe 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.AnalysisStudents 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 CategoryJANFebMarAprJunJulAugSepOctNovDecAuto – 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.DateCategoryDescription Amount Month1/1/2012Auto - Expenses (gas, insurance, etc.)Gasoline 35.00 11/1/2012Auto - PaymentPayment 300.00 1DevelopmentStudents 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 TitleTutorialExcel Help Topic ReferenceRevenue Revenue and ExpensesRename a WorksheetFill seriesFormatting cellsVBA(go to VBA editor help in Excel for all VBA help topics) Input Box functionApplication.ActiveCell propertyModule and class module commandsMsgBox functionIf/Then/Else statementMacros dialog boxExpensesRevenue and ExpensesSheet tabsFill seriesFormatting numbersActual ExpensesActual ExpensesSUM functionChange column widthVarianceVariance I (SUMIFS); Variance II (VLOOKUP)SUMIF functionSUMIFS functionVLOOKUP functionMONTH functionDefine and use names in formulasSwitch between relative, absolute, and mixed referencesPivot AnalysisPivot AnalysisCreate or delete a PivotTable or PivotChart reportPivot data in a PivotTable or PivotChart reportTestingEnter 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).ImplementationOnce testing is complete and workbook is fully developed, assignment will be submitted to the instructor. MaintenanceMake any revisions required by the instructor and continue to modify the workbook to accommodate your personal budget needs. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download