Budget
Introduction and Requirements
Our first project is awesome! We are going to build a budget planner that you can use for personal finances or any business. I use a version of this in my own departmental management. And though this is our first project, we are going to explorer many of Excel's more advanced features.
Lesson Objectives
At the end of this lesson we will:
Have a project we can use in our personal and business lives. And because every business needs a project planner, learners will have something to give to their management, or freelancers can give to prospective clients as a means to showcase their skills.
Know how to use some of Excel's less known functions: MOD(), SWITCH(), MONTH(), WEEKDAY(), DAY(), and LET().
See how to use some of Excel's newer features: Dynamic Arrays, Tables, Power Query, Data Model, Pivot Tables, Power Pivot, Pivot Charts and Slicers.
See how to structure an Excel project into Inputs, Process, and Outputs.
Have an introduction to an abbreviated software development life cycle (SDLC), which in this project will be: Requirements Definition, Model Design, and Construction/Development.
Above is what our project outputs will look like.
Lesson Sections
Introduction and Functional Requirements (this page)
Create Calculations
Create Transformations for Outputs
Create Reporting Hierarchies and Aggregated Measures for Outputs
Create Summary Tables
Create Charts
Add Slicers
Requirements Definition
A personal budget planner itemizes income and expenses and schedules when they occur in order to know if we have enough money to cover our expenses or make plans to adjust expenses and/or income to accommodate our plans.
Outputs
We will want to know the following about our budget:
Do we have enough money each month to cover our expenses?
If we go over budget in one or more months, can we transfer enough from other months to cover those overages?
If we need to cut expenses, from which categories could we cut spend and how much?
A departmental budget planner is similar but it does not have the income requirement. The purpose of a departmental budget is to declare to finance how much money the department will need to operate and when that money is needed. After a department's budget is approved, Finance incorporates all department budgets into the organization's cash flow projections and during the year, the department will check expenditures to know if the are within budget, and if they have enough cushion to accommodate unplanned needs. With a little modification, such as replacing income with actual expenditures for comparisons, and replacing categories with GL accounts, this budget planner will work well for departmental needs. That said, we will concentrate on the personal budget planner.
Categories
We will categorize budget items that help us identify what we must spend money on and what we would like to spend money on. For example, we must spend money on food and we would like to spend money on entertainment. And even though we must spend money on food, we can substitute high price food, such as steak, with lower priced food if we find ourselves in a real pinch.
Schedules
Budget items occur over time. Some occur periodically and regularly such as mortgage and rent payments. Some expenses occur less regularly, but somewhat periodically such as going out to eat when we are feeling well off. Some items occur just once within the budget year such as a birthday.
Functional Requirements
Our projects requirements are thus:
Accommodate Income and Expenses
Itemize expenses
Categorize items
Schedule expenses
Show:
How does our income compare to our expenses
Which categories consume most of our money
How much money do we need for each period
Which months have an excess that we can hold to cover shortages
Next Steps
With our requirements defined, we move on to the next phase of our project: Model Design.
