Embedded Files
Process Section:
There are 8 tables in the Calcs (Process) worksheet. These are the fifth and sixth tables. .
Income Statement
Income Statement
This provides the final calculations needed for our income statement. It is NOT our income statement. It merely is a working set of calculations that, along with previous calculations linked together by the data model, will be aggregated by a PivotTable and formatted to comply with Income Statement formats.
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