Concept: Calculate YTD Based on Periodic Values
Periodic versus Cumulative Accounts
Often, values from Income Statement accounts feed values in Balance Sheet accounts. Example: Net Income is a periodic account that drives the YTD Net Income account on the Balance Sheets. You can use dot notation with time modifiers to calculate the YTD account correctly through the months.
Example Accounts
Sample Accounts | Sheet and Type | Sample Account Codes |
|---|---|---|
Net Income | Income Statement - Periodic | ACCT.NetIncome |
YTD Net Income | Balance Sheet - Cumulative | ACCT.YTDNetIncome |
Example Formula
For Acct.YTDNetIncome, you can enter this formula in the cells or in the default formula:
=iff(this.year.positionof(this.month) = 1, ACCT.NetIncome, ACCT.NetIncome + ACCT.YTDNetIncome[time=this-1]).
The breakdown:
Formula Expression | Meaning |
|---|---|
=iff(this.year.positionof(this.month) = 1, | If this month is the first fiscal month in the current year. |
ACCT.NetIncome, | Use the value in the Net Income account on the Income Statement. |
ACCT.NetIncome + ACCT.YTDNetIncome[time=this-1]). | Otherwise, add the value of Net Income to the previous month of YTD Net Income |
Example Results on Sheets
Here's what it looks like when January is the first month of the fiscal year:
Accounts | Jan | Feb | Mar |
|---|---|---|---|
Net Income | 500 | 200 | 400 |
YTD Net Income | 500 | 700 | 1100 |