UW - Laramie, Wyoming | University of Wyoming



DescriptionThis advanced Excel project requires students to create their own budget workbook in Excel. Students apply different Excel functions and calculations in order to manipulate budget data and information. Learning Objective(s)Create a multi-spreadsheet workbook, use nested and user defined functions on sets of individually derived data, and analyze data using pivot tables and charts, filtering, and conditional formatting.Project NarrativeThis project introduces more advanced Excel functions. The skill set associated includes the selection/creation of functions for recording and running macros; creating Visual Basic Editor subroutines; formatting and naming of cells, columns, tables; using SUM, SUMIF, SUMIFS, and VLOOKUP functions to calculate variance; and use pivot tables and charts for data analysis. Prerequisite Knowledge – Understand and effectively work with Excel workbooks, worksheets, rows, columns, and cells. Be able to use nested Excel functions; create macros and user defined functions; and create charts and graphs. Subsequent Application – Use Excel's named range feature to create logical groupings of related cells to use in formulas, pivot tables and charts (Naming); use built-in formulas to perform simple to more complex calculations (functions); apply simple visual basic coding in order to customize Excel applications such as formatting and perform repeated calculations for the purpose of analysis (VBA); group and summarize information by creating multi-dimensional tables for displaying information and supporting decision making (analytical); create a chart or graph appropriate for an identified data set using spreadsheet software features that assist in organizing data and displaying information (analytical).RequirementsThis is an individual assignment. You may work with others on the assignment, but you may not share computer files or any other information with others. Doing so is a violation of academic honesty, and all parties involved will receive a zero (0) on the assignment.Your Excel workbook should include the following sheets:Documentation sheet – On this sheet, you should record specific information about the workbook you created as well as how to use it. The documentation sheet instructions should be very complete and include instructions on how you created/manipulated each of the sheets. Write your own instructions instead of just copying the ones from the example.Revenue – On this sheet, you should include the projected revenue you will earn for each month (record all of your revenue and what you expect your revenue for a given month to be.) You should include fictitious amounts as well). You will also be required to include a VBA module to calculate revenue percentage adjustment.When graded, the VBA code will be tested so make sure the code is accurate. Be sure to watch the entire VBA video and use the longer VBA code (as explained in the video) instead of just the sample code. Expenses – On this sheet, you should record the projected expenses for each month (You should have a minimum of 10 expense categories, estimate your expenses, you can use fabricated amounts). You should also name the table range ExpenseBudget as directed in the video instructions.Actual expenses – On this sheet, you should record all of the expenses that you actually incurred for the month (You should have a minimum of 6 expenses for each month, and because you may not know the entire year’s expenses, you should create fabricated amounts. Be sure to make some unique entries each month). Columns and rows should be named as directed in the video instructions.Variance – On this sheet, you will calculate budget variances and compare revenues to expenses. You will be using functions in this worksheet. Functions must be visible and accurate. (Functions required are VLOOKUP, SUMIFS, and functions to calculate budgeted expenses, budgeted revenue, and surplus/deficits). Pivot Analysis – For this sheet, you will create a pivot chart and conduct a simple pivot analysis to visually depict actual expenses incurred.Save your project as YourUserIDPersonalBudget. Be sure to save the Excel document as a macro-enabled workbook (.xlsm) so that the document can be opened and your VBA code enabled. If you do not save it as a macro-enabled workbook, the macros will not work or may be lost.Resources Personal Budget Introduction (Link)Formatting Revenue and Expense Worksheets (Link)Formatting Variance Worksheet (Link)Formatting Actual Expenses Worksheet (Link)Variance Worksheet Sumif and Sumifs functions (Link)Variance Worksheet V-Lookup function (Link)Pivot Analysis Basics (Link)Pivot Charts (Link)Visual Basic Message Boxes (Link)Visual Basic Input Boxes (Link)Visual Basic Percentage Adjustment (Link)Prior ResourcesPersonal Budget Project Design (Link)Personal Budget Project Video Tutorials (Link)Work ScheduleDayActivityDescriptionVideoDurationProject DocumentsRead the Personal Budget Project Document15 minutesView TutorialsView tutorials (Overall Total Viewing Time)90 minutes1Introduction View Introduction Video to see the scope of the overall project2 minutes 2Revenue and Expense View Formatting Revenue and Expense Worksheets Video and create the revenue and expense worksheets.6 minutes 30 minutes 3Variance WorksheetView Formatting Variance Worksheet Video. Create the Variance worksheet. 6 minutes20 minutes4Actual Expenses View Formatting Variance Worksheet Video and create the actual expenses.4 minutes30 minutes 5Variance SUMSView Variance Worksheet SUMIF and SUMIFS functions and create the SUMIF and SUMIFS functions on the Variance worksheet.11 minutes30 minutes 6Variance LOOKUP View Variance Worksheet V-Lookup function Video. Create the VLOOKUP functions8 minutes30 minutes7Pivot Table View Pivot Analysis Basics. Create the Pivot Table and Pivot Charts.6 minutes20 minutes8Pivot ChartsView Pivot Charts. Create the Pivot Chart.8 minutes15 minutes9VBA Message BoxView the VBA (Message Boxes) Video Create basic message box for subroutine.11 minutes20 minutes10VBA Input BoxView the VBA (Input Boxes) Video Create input box for subroutine.12 minutes20 minutes11VBA Percentage Adjustment View the VBA (Visual Basic Application) Video Create actual Percentage Adjustment subroutine.10 minutes30 minutesTotal Time (estimated)90 minutes5 hoursWorkflow ................
................

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

Google Online Preview   Download