Budget: Research, Analysis & Design

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. 

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