Embedded Files
Process Section:
In this class chapter we examine the calculation flow between tables and then create our first four calculation tables.
Calculation Flow
Calculation Flow
At right is a flow diagram showing which tables feeds which tables. When we begin building our model we will start with tables that have no precedent calculations (arrows going in). Those are the Calendar, Period Actuals & Assumptions Calculations, and Depreciation Schedule Calculations table. We will finish with the table that has no dependent calculations (arrows going out) which is the Balance Sheet Calculations.
I omitted the Calendar table from this diagram because it has no precedent and no dependent calculations. It is referenced in the Data Model so we can provide outputs with specific years instead of generic periods. It is the simplest of the tables with no precedents so we will start with it first.
Calendar Table
Calendar Table
The Calendar Table has just two columns. The columns translate periods to dates. This allows us to work with numbers (periods) instead of dates making accessing information using INDEX() far easier. It also allows us to move a project's start date, which happens frequently, without any changes to our model. This table is only used in outputs and not in any other calculations.
The two formulas in this table are:
Period =ROW()-ROW([#Headers])
Year =Start_Year+[@Period]-1
The Period formula is used in every table so we won't discuss it again.
Period Actuals and Assumptions
Period Actuals and Assumptions
This table is a bit of an oddity.
Actual Revenue and Cost of Goods Sold
These two columns have identical formulas:
=SUMIFS(tblActuals[Value],
tblActuals[Period],[@Period],
tblActuals[Account],@tblPAA[#Headers])
It is just one function with five references. The first reference is what SUMIFS() will sum provided the next two lines equate to true. The second line checks if the period in the actuals table (imported data) equates to the period in this table. The third line checks if the account in the actuals table equates to the account in this column's heading.
Revenue Growth
Our Income Statement calculates each period's revenue from the Previous Period's Revenue times the Revenue Growth Percentage. Revenue Growth Percentage is an assumption for projections. For actuals, we must calculate Revenue Growth Percentage from the actual revenue amounts. This table calculates Revenue Growth Percentage for actuals then appends Revenue Growth Percentage from assumptions using this formula:
=IF([@Period]<Forecast_Period_Start,
[@[Actual Revenue]]/[@[Previous Actual Revenue]]-1,
SUMIFS(tblPA[Revenue Growth],tblPA[Period],[@Period]))
The first line checks to see if the current row should come from actuals or from assumptions. The second line satisfies calculation from actuals. The third line retrieves from assumptions for the row's period.
Cost of Goods Sold Percent of Revenue
Our Income Statement calculates Cost of Goods Sold (COGS) from Revenue times Cost of Goods Sold Percentage of Revenue. COGS Pct. of Rev. is an assumption for projections. For actuals, we must calculate COGS Pct. of Rev. from the actual revenue amounts and actual COGS. This table calculates COGS Pct. of Rev. for actuals then appends COGS Pct. of Rev. from assumptions from this formula:
=IF([@Period]<Forecast_Period_Start,
[@[Actual COGS]]/[@[Actual Revenue]],
SUMIFS(tblPA[COGS],tblPA[Period],[@Period]))
The first line checks to see if the current row should come from actuals or from assumptions. The second line satisfies calculation from actuals. The third line retrieves from assumptions for the row's period.
Cost of Goods Sold Percent of Revenue
Our Income Statement calculates Cost of Goods Sold (COGS) from Revenue times Cost of Goods Sold Percentage of Revenue. COGS Pct. of Rev. is an assumption for projections. For actuals, we must calculate COGS Pct. of Rev. from the actual revenue amounts and actual COGS. This table calculates COGS Pct. of Rev. for actuals then appends COGS Pct. of Rev. from assumptions from this formula:
=IF([@Period]<Forecast_Period_Start,
[@[Actual COGS]]/[@[Actual Revenue]],
SUMIFS(tblPA[COGS],tblPA[Period],[@Period]))
The first line checks to see if the current row should come from actuals or from assumptions. The second line satisfies calculation from actuals. The third line retrieves from assumptions for the row's period.
Debt Schedule
Debt Schedule
The Debt Schedule is used by the Income Statement (Interest) and Balance Sheet (Debt Closing as part of Total Liabilities).
Debt Opening
This is a corkscrew calculation. The first period gets its value from assumptions. All subsequent periods are the previous period's Debt Closing. Here is the formula:
=IF([@Period]=1,
Debt_Opening,
INDEX([Debt Closing],[@Period]-1))
Debt Repayment
This appends assumptions to actuals using this formula:
=IF([@Period]<Forecast_Period_Start,
SUMIFS(tblActuals[Value],tblActuals[Period],[@Period],
tblActuals[Account],@tblDebt[#Headers]),
SUMIFS(tblPA[Debt Repayment],tblPA[Period],[@Period]))
The first line determines if we need to pull from actuals or assumptions.
The second and third lines pull from actuals using SUMIFS() because SUMIFS() can aggregate data using multiple keys. Pulling from actuals requires two keys: the period and the account. Period comes from our table's first column. The account comes from our table's heading.
The last line also used SUMIFS() which works just as well with just one key: Period. Period is all we need to pull a value from the period assumptions input table.
NOTE! This formula has a complexity score of 14 (3 functions plus 1 operator plus 10 references). That is a high complexity score. It is worth considering whether or not to place the second and third lines in their own columns in order to reduce this column's complexity.
Debt Closing
This is simply the Dept Opening less Debt Repayment columns which is obvious from the formula:
=[@[Debt Opening]]+[@[Debt Repayment]]
Interest
This is almost identical to Debt Repayment. Here is the formula:
=IF([@Period]<Forecast_Period_Start,
SUMIFS(tblActuals[Value],tblActuals[Period],[@Period],
tblActuals[Account],@tblDebt[#Headers]),
[@[Debt Opening]]*Interest_Rate)
The only difference is in the last line which calculates interest from Debt Opening times the Interest Rate entered in assumptions.
Depreciation Schedule
Depreciation Schedule
The Depreciation Schedule is used by the Income Statement (Depreciation & Amortization), Cash Flow Statement (Investments in Property & Equipment) and Balance Sheet (Depreciation Closing).
Depreciation Opening
This is another corkscrew calculation linked to Depreciation Closing.
=IF([@Period]=1,
PPE_Opening,
INDEX([Depreciation Closing],[@Period]-1))
Depreciation & Amortization
This is another append assumptions (with calculations) to actuals:
=IF([@Period]<Forecast_Period_Start,
SUMIFS(tblActuals[Value],tblActuals[Period],[@Period],
tblActuals[Account],@tblDebt[#Headers]),
[@[Depreciation Opening]]*Depreciation)
Investments in Property & Equipment
And yet another append assumptions to actuals calculation:
=IF([@Period]<Forecast_Period_Start,
SUMIFS(tblActuals[Value],tblActuals[Period],[@Period],
tblActuals[Account],@tblDebt[#Headers]),
SUMIFS(tblPA[Capex],tblPA[Period],[@Period]))
Depreciation Closing
This is a simple calculation that explains itself.
=[@[Depreciation Opening]]+
[@[Investments in Property & Equipment]]-
[@[Depreciation & Amortization]]
Page updated
Google Sites
Report abuse