Budget: Research, Analysis & Design
Excel modeling is software development. Every Excel project can benefit from following the well established concept of a software development life cycle (SDLC). At right is graphical representation of one version of it.
In the previous section we defined our projects requirements. That is part of the Planning Phase. In this section we will learn how to translate requirements into a model design. Let us remind ourselves what our project's requirements are.
Functional Requirements
Accommodate Income and Expenses
Itemize expenses
Categorize items
Schedule expenses
Show:
Which categories consume most of our money
How much money we need for each period
Our next phase is Analysis. In this phase we start be determining what we have to work with. In some versions of the SDLC, this referred to as the Research phase. But for our purposes, we will make this part of the Analysis phase.
Research and Analysis
In this phase we look at our requirements and determine what we have to work with (inputs) as it relates to what we must produce.(outputs).
Available Information for Inputs
The two central bits of information in a budget planner are the budget year and budget items.
Budget Year
Start Date - Budgets are typically for a single year and that year does not need to start on January 1.
Budget Items
Each budget item as attributes such as:
Name - or brief description
Amount - income or expense amount
First Due Date - when this item will first be received (income) or spent (expense)
Recurrence - which is how often the income of expense occurs. If we take a moment to consider how often budget items repeat we can codify them to make entry easier.
A - Annually - These are things that occur just once in the budget year or ever. An example is someone's birthday or a new phone.
S - Semi-annually - These are things that occur twice each year. This includes some taxes or insurance plans.
Q - Quarterly - This occur four times a year. An example is filling estimated income tax for freelancers.
M - Monthly - Many budget items repeat each month like rent or car payments.
B - Biweekly - Some people are paid every two weeks.
W - Weekly - Many people are paid every week.
X - Workday - I buy my lunch when I'm working.
D - Daily - If you are one of those people who always hit the café in the morning, this schedule may be useful to you.
Category - which helps us identify what is a required expenditure versus and discretionary expenditure. We can use category to also group items into
Income - things which add funds
Expenses - items that subtract funds
Required Outputs
We will want charts and tables that aggregate:
Expenses by Category - to show their relative totals so we have some idea as to where most of our money is going
Expenses by Period - to show how much money we need for each period
Balance by Period - to see if we have enough income to cover expenses in each period
Cumulative Balance by Period - to show where we have excess that we may be able to save to cover shortfalls.
Bridging the Gap: Calculations
The missing piece between our inputs and our outputs is linking each expenditure to a specific date. In other words, scheduling our income and expenses. This will be the focus of our calculations. Things we need to calculate are:
Budget Calendar - This will calculates and lists all dates within the budget year starting with the user's input: Start Date
Days - Remember, not all years have 365 days. Some are leap years. This will calculate how many days are in the budget year.
Budget Schedule - This will determine in which date or dates each budget item occurs.
Design
Using our research we conceptualize how we want to structure our model. We start with inputs.
Inputs
An input in the Excel context is anything that has no precedents. They are things entered or imported. They include constants, labels, data validation tables and user entries. We can divide inputs into two categories: User Inputs and Modeler inputs.
User Inputs
User inputs are those things we want the user to change and that is most of this model's inputs. The things we want users to change are:
tblBI - Budget Items
We need users to input items and amounts into a table. Each row in the table will be an item. The user can also enter into this table each item's category, first due date, and how often it repeats.
tblBC - Budget Categories
Users can enter the budget categories that makes sense to them. For business budgeting this is usually a general ledger account description and the account type is always Expense. The model example is setup for personal budgeting which also tracks Income.
tblUI - Model Properties
This model only has one property: Start. This is the first day of the budget year.
Modeler Inputs
There are somethings the user must not change usually because they are part of our formulas, which users must also not change. Modeler inputs usually include labels, named constants, and data validation tables.
tblRC - Recurrence Codes
In this model we will be creating calculations to schedule amounts based on how often they recur. Thus, the recurrence code that users may use must be one, and only one, of the codes that our formulas recognize. Those codes go in the Recurrence Codes table.
tblNC - Named Constants
A best practice is to name literals, also known as constants, used in formulas. This makes our formulas more readily understood. Since these are used in formulas users must not change them. In this project these names will likely be mnemonics for for values used to calculate recurrences. An example is MPY for Months Per Year. We will use MPY instead of 12 in formulas that need that value. Usually we won't know what named constants we will need until we start writing formulas. So as we have a need for a named constant, we simply add them to this table. But from experience, I know the table is almost always needed.
Calculations
In the research and analysis section we listed most of our calculations. There is one calculation we didn't consider. If we continued with what we have, we would have discovered it, eventually, and then changed our design. But because I've done this sort of thing a few times, my experience reminds me that we need some way to link our calculations to our budget items. For this we will create an ID column in both the budget item entry table and the budget schedule calculations.
Days - This calculates the number of days between between the user's input: Start Date, and same day of the month, 12 months out:
Date - This will be our budget calendar. It will start with the user's input: Start Date, and continue for Days:
ID - This is in the budget items entry table and provides an ID for each expenditure item row.
ID - This extends the budget item entry table's ID to the budget item schedule calculations.
Scheduled Amount - This calculates when an expenditure will occur in the budget year. This will be a monster as this one formula, in just one cell, calculates all expenditures for all dates using any of the 8 recurrence methods.
We will determine exactly how we will calculate these things in the construction phase of our project, which is next.
Outputs
This section includes those things that have no dependent cells and are formatted and focused on providing information needed by end users - not the model. End users may make changes in this section that do not alter the model's calculations. Having done this sort of thing many times, my experience is the easiest and most flexible means of displaying actionable information to clients is via PivotTables, Pivot Charts, and Slicers.
PivotTables aggregate our models calculations
Pivot Charts display PivotTables graphically which many users prefer.
Slicers allow users to 'play' with the model and focus on areas of interest.
For this project we will need four PivotTable/PivotChart combos. They are
pvtExC and chtExc - Expenses by Category
pvtExM and chtExM - Expenses by Month
pvtBxM and chtBxM - Balance by Month
pvtCBxP and chtCBxP - Cumulative Balance by Month
We will also need three slicers
slcCategories - to focus all pivots by selected category or categories
slcItems - to focus all pivots by selected item or items
slcMonth - to focus all pivots by selected month
Here is a graphical version of our model's design
In the next section we start construction in what our SDLC calls the Development phase. Click Next below.
