Budget: Inputs
Now that we know our model's requirements and design we can construct it. This project's development will consist of these phases:
Creating input tables
Creating calculations (formulas)
Transforming calculations into a detailed, de-normalized dataset for outputs (power query)
Identifying data hierarchies in the dataset (data model)
Creating measures over the dataset for outputs (data model).
Aggregating the dataset in summary tables (PivotTables)
Creating interactive analytics (Pivot Charts, Drilldown, and Slicers).
In this section we will learn how to construct various input tables including:
Data Validation Tables - to help users make correct entries and prevent bad entries.
Named Constants Tables - to better document our formulas
Data Entry Tables - with data validation
We can further categorize input tables as assumptions/model properties, imported data, model configuration settings, named constants, named labels labels, and data validation tables. Of these, we must decide who can enter them. Some must be entered or controlled by the modeler. We call these Modeler inputs. Others are entered by end users which we call User Inputs.
Model Inputs by Modeler
Add a worksheet to hold our modeler inputs. Name this worksheet ADTs for Application Data Tables. Add these tables to it.
tblRC- Recurrence codes
We want our end users to tell us how often each budget item occurs so we can schedule it for them. There are several common periodic frequencies. We want to accommodate the most likely which are: once per year (Annually), twice each year (Semi-Annually), each quarter, each month, every two weeks, weekly, each workday and daily.
This table provides a list of allowable recurrence codes from which users may select. It will be used for data validation in tblBI (Budget Items) to restrict user entries to what we will code for.
tblNC - Named Constants
Each recurrence code will require values. An example is, calculating quarterly recurrences requires the number of months in a quarter (3). Rather than add the literal "3" to the quarterly calculation, we will use the mnemonic, MPQ.
Mnemonics are things that help us remember something else. I often use acronyms as mnemonics to help us easily identify things like Months Per Quarter with its abbreviation of MPQ. Using mnemonics helps self-document our code by replacing hard coded values with named values. We can use Excel's Names to create mnemonics which I like to store in a table.
Add table tblNC to the ADTs worksheet. tblNC holds our model's named constants.
NOTE! Two of these named constants are not acronyms:
Mon2Sun is a mnemonic for Monday to Sunday. Its value, 2, instructs the WEEKDAY() function to represent dates on Mondays with 1 and dates on Sundays with 7.
Friday is the last day of our work week (normally in the US). It holds the value 5 which is the value WEEKDAY() returns for Friday when using WEEKDAY()'s #2 numbering scheme. We will use this to identify which days are workdays.
After entering this named constants, select the first two columns and use Excel's Create from Selection option to name the values.
Model Inputs by User
The next step is to add worksheets for our user inputs. The first worksheet is called Inputs. It contains three input tables: tblBC (Budget Categories) and tblMP (Model Properties) and tblBI (Budget Items).
tblBC - Budget Categories
Create the tblBC as shown as shown above. These are allowable budget categories that our user can change. Add data validation to the Type column to restrict entries to a list: Income, Expense.
tlbMP - Model Properties
Create the tblMP as shown as the second table at right. I like to organize all user changeable model properties into a table. Usually tblMP has many properties but only one for this simple model.
tblBI - Budget Items
This is the main table. Here we select budget categories, add budget items, estimate their costs, and schedule when they occur. This table has seven columns:
ID
The first column is the only column with a formula. Because it contains a formula, format it as cell style Normal to protect it (the default protection setting for the normal style is LOCKED). I very often add an ID column to tables. Sometimes the ID is labeled Row or Period but the formula is always the same. We can enter it as:
=ROW()-ROW(#Headers])
Category
Add data validation to this column to restrict entries to tblBC (Budget Categories). See the data validation dialog at right.
Format this, and all other columns in this table with cell style Input. For more on cell styles see here.
Item
Items provide the necessary detail for our budget.
Estimate
This is how much we think each item will cost.
First Due Date
This is the first date in the model year when this expenditure is due.
End Date
Some items span only a few months such a lawn mowing services which start in spring, are paid weekly or monthly, and end in fall.
Recurrence
Add data validation to this column to restrict entries to tblFreq (Frequency) Add the following data validation to the Recurrence column as shown at right. For more on data validation see here.
This will add a dropdown from which user can select one of the recurrence codes in our model configuration worksheet.
We have our inputs upon which our calculations rely. Creating our calculations is next.
