creating-the-personal-budget-in-Excel

This project and all of the content within or linked to it was developed by the University of Wyoming IMGT 2400 Instructors. Used with permission from Mike Doherty, Business Department, University of Wyoming.

Description

This 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 Narrative

This 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).

Requirements

This 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 aminimum 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 FirstNameLastNamePersonalBudget. 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 Project Design (Link)

Personal Budget Project Video Tutorials (Link)

 

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.