Sample Financial Model
In this class chapter we will introduce you to a simple 3 way financial model. Download it from here: BXL 3Way.xlsx. By the end of this section we will know the model's structure, it's sections (Inputs, Process, Outputs), the components (worksheets, PivotTables, Pivot Charts, Data Model, and Tables) that make up each section, how they support each other, and if you are new to financial modeling, some financial concepts. Starting with the next class chapter we will learn how to construct this model.
3 Way Financial Model
A 3 way financial model is a model with three financial statements: Income Statement, Cash Flow Statement, and Balance Sheet (shown above). Not all Excel models are financial models but this is a good place to start because almost all business models require some understanding of accounting and this will provide a good introduction. As we mention financial topics, we will provide links that explain them. The three statements mentioned at the beginning of this paragraph can be clicked to get an explanation of what they are.
When designing any project, we always start at the end: our deliverables. We work back from what we must give to our client (Outputs), to what that needs (Process/Calculations), to what we have (Inputs). So to explain this model, we start with outputs.
Outputs
The three statements are our outputs. The Balance Sheet is shown at top. The Income statement and Cash Flow Statement are shown below.
They each share the same structure known in IT circles as a crosstab format. In finance, this format usually contains accounts going down the left, and periods going across the top. The periods are repeating elements. Repeating elements do not work in Excel's table columns but they work very well in Excel's PivotTable columns and PivotTables read Excel's tables beautifully. So when we create these statements, we will create them using PivotTables that aggregate our calculation tables and format them properly. Each statement will be on its own worksheet to allow them to grow horizontally if we add more periods, and vertically if we add more accounts, with nothing to run into.
We will also add interactive charts which can be called and Executive Dashboard or Interactive Analysis Visualizations (shown below).
The charts change when we select periods in the slicer which is what makes them interactive. These charts are Pivot Charts. Pivot Charts graph what is in PivotTables so we will, once again, build PivotTables over our calculations (see below) over which we will product our charts.
All of these PivotTables, including the three financial statements, pull from the calculations. The calculations are our one source of the truth and all outputs pull from it so all outputs stay in sync.
Data Model
As mentioned, all outputs pull from a single version of the truth which is our calculation tables. In a moment we will see these calculation tables. But first I want to introduce the Data Model.
The data model combines all of our calculations sections into one view. From this single view we build all of our PivotTables. This not only insures our outputs stay in sync, no matter how we slice things, but also saves us from joining these table through formulas savings us a lot of time and greatly reducing chances for error. Each of these boxes is a calculation table, so let us get to know each calculation table a bit better.
Calculations
The calculations section is where we spend most of our effort. This section contains tables that calculate various blocks for our model.
This is also the most controversial portion of TBM because time series go down, not across. Traditional financial modelers are used to time periods going across. Asking them to give up this aspect of modeling is too much for some. But fear not! I can say unequivocally, horizontal has no advantage in calculations and vertical has many. Vertical is how every financial system on the planet stores dates and fiscal periods because dates indicate a record in a table and records expand down. This brings us to the first of many advantages. By simply adding a row to a table, all formulas extend automatically. No copy/paste. No drag and fill. No chance to have inconsistent formulas.
Every cell in each table contains a formula. The tables are arranged in "reading order" based on precedents making it easier to trace calculation chains. The last table (farthest right) on this worksheet is the table that has no dependent calculations and in this model, that is the Balance Sheet Calculations table.
Balance Sheet Calculations
At the far right of our calculations section is this table. It is last because it depends on most of the other calculation tables and no other calculation table depends on it. This table basically checks that all assets, less liabilities, matches our equity which is the basis for our Balance Sheet Statement (PivotTable).
Cash Flow Calculations
This requires results from most of the other tables, but not the Balance Sheet Calculations table. This table confirms that we always have enough cash reserves to run our business which is the basis for our Cash Flow Statement (PivotTable).
Working Capital Schedule Calculations
Accounting schedules provide support for financial statements. The Working Capital Schedule is used mostly by the Balance Sheet Calculations table, but Changes in Net Working Capital is required for the Cash Flow Calculations table.
Depreciation Schedule Calculations
This table's calculations are crucial to each of our three statements. Depreciation Closing is required by the Balance Sheet Calculations table. CAPEX (Capital Expenditures) is required by the Cash Flow Calculations table. Depreciation and Amortization is used by the Income Statement Calculations table and Cash Flow Calculations table.
Debt Schedule Calculations
This table's calculations are required by two of our statements. Interest paid on Debt Balances is an expense which is part of the Income Statement Calculations table. Debt is a liability which is required by the Balance Sheet Calculations table.
Period Actuals & Assumptions Calculations
This table is a bit of an oddity. It is neither a schedule calculations nor statement calculations. It is the result of this model's requirement to show actuals mixed with projections. The actuals provide revenue figures for the first five periods but no revenue growth percentages. Projections use revenue growth percentages entered as assumptions to calculate revenue for the last five periods. The first three columns of this table calculates revenue growth percentages from actuals and appends the revenue growth percentage assumptions from user inputs.
The same basic process is used to create the Cost of Goods Sold Percent of Revenue figures.
Revenue Growth and Cost of Goods Sold Percent of Revenue are both required by the Income Statement Calculations table.
Calendar Calculations
This table translates our generic Periods column to calendar dates. While this table is not used in any of the other tables' calculations, it is linked to every one of these tables in the data model; thus, when PivotTables use these calculations, they can display dates instead of generic periods.
Inputs
This model's inputs section contains three worksheets: Constants, Imports, and User Inputs.
User Inputs
This section contains two tables that end users can change. One is a list of single values like Rent and Overhead costs. The other contains values that change with each period. Values that can be changed are gray which is how I set my Input Style. The Input Style's protection setting is unlocked. All other styles are locked. The worksheet protection is turned on allowing only selection of unlocked cells, thus, users can only change the gray input cells and nothing else.
Imports
This section contains one table that simulates a Power Query download or an Export-to-Excel from an ERP. This particular import includes end-of-year values from our general ledger for five years. Using imported data greatly reduces modeling effort and eliminates keying errors when compared to manual entry.
Constants
This section contains three tables that only the modeler can change.
The first table contains named constants which we will use in formulas instead of literals. The name helps clarify what the value means which greatly improves model understandability.
The second table contains named labels. This allows us to set repeated labels in one place, use everywhere needed, and if necessary, change them in one place and have all instances update. Thus, if we want to change date formats from US to European standard, we have just one place to make the change.
The last table is for formatting only. Financial statements look better with spaces between sections. This table provides those spaces to the data model and, thus, to the PivotTables used to create financial statements and executive dashboard analytics.
