Keystone FX

Formulas

Date Presentation

This Year:=ThisYr "2019"

This Month:=ThisPer "6"

This Month Name:=VLOOKUP(ThisPer,TblPDQty[#Data],3,FALSE) "June"

This Month and Year: =VLOOKUP(ThisPer,TblPDQty[#Data],3,FALSE)&" "&ThisYr Example: "June 2019"

G/L Values

Keystone FX returns G/L values using the SUMIFS formula. SUMIFS sums up a specified value with as many filters as you need. For G/L we extract data from the table "TblGLPDSUM" and filter by both periods and accounts.

Common Formulas

This Per Amt for a Core Account: =SUMIFS(TblGLPDSUM[PER_AMT],TblGLPDSUM[S1],"="&$A10,TblGLPDSUM[PERIOD_YEAR],"="&ThisYr,TblGLPDSUM[PERIOD_NO],"="&ThisPer)

YTD Amt for a Core Account: =SUMIFS(TblGLPDSUM[PER_AMT],TblGLPDSUM[S1],"="&$A10,TblGLPDSUM[PERIOD_YEAR],"="&ThisYr,TblGLPDSUM[PERIOD_NO],"<="&ThisPer) (Simply change the "=" to "<=" for the period filter)

This Per Amt for a Group: =SUMIFS(TblGLPDSUM[PER_AMT],TblGLPDSUM[S1],"="&$A10,TblGLPDSUM[PERIOD_YEAR],"="&ThisYr,TblGLPDSUM[PERIOD_NO],"="&ThisPer)

Values

Period Amount: TblGLPDSUM[PER_AMT]

Budget Amount: TblGLPDSUM[BUD_AMT]

Year Begin Amount: TblGLPDSUM[YR_BEG_AMT] Requires the filter (TblGLPDSUM[PERIOD_NO],"=1",TblGLPDSUM[PER_AMT],"=0") or newer (TblGLPDSUM[PERIOD_NO],"=0",TblGLPDSUM[PER_AMT],"=0")

Also Available but rarely used...

Y-T-D Amount: TblGLPDSUM[YTD_AMT] (Instead use "PER_AMT" with a <=ThisPer filter.)

Y-T-D Budget: TblGLPDSUM[YTD_BUD] (Instead use "BUD_AMT" with a <=ThisPer filter.)

Account Selection

Account Selection Filters are commonly combined. For example on a plant income statement you'll likely combine a core account filter and a plant filter.

These examples are based on these Assumptions:

S1: Core Account, typically segment 1

S2: Plant, typically segment 2

S3: Division. typically segment 3

This can vary depending from system to system but the concept is still the same.

Account Selection Filters:

The Core Account: TblGLPDSUM[S1],"="&$A10

Range of Core Accounts:TblGLPDSUM[S1],">="&$A10,TblGLPDSUM[S1],"<="&$B10

Keystone GL Group: TblGLPDSUM[GRP],"="&$A10

Specific Plant:TblGLPDSUM[S2],"="&PlantNo (Here PlantNo is a named value, typically tied to the worksheet)

A Single G/L Account: TblGLPDSUM[GL_ACCOUNT],"="&$A10 (Rarely Used - Normally combine Core Account and Plant Filter etc.)

Divison: TblGLPDSUM[S3],"="&DivisionNo (Here DivisionNo is a named value, typically tied to the worksheet)

Period Selection

Period Selection relies heavily on the named values on the setup tab:

ThisYr: The current reporting year

ThisPer: Current Period

LastYr: Last Year

PerCapx12: Normally the same as ThisPer - Limits which periods display on a 12 month report.

Periods Filters:

Current Period: TblGLPDSUM[PERIOD_NO],"="&ThisPer

Current Year: TblGLPDSUM[PERIOD_YEAR],"="&ThisYr

Last Year: TblGLPDSUM[PERIOD_YEAR],"="&LastYr

The magic comes from combining filters...

Current Period Last Year: TblGLPDSUM[PERIOD_NO],"="&ThisPer,TblGLPDSUM[PERIOD_YEAR],"="&LastYr

Year to Date This Year: TblGLPDSUM[PERIOD_NO],"<="&ThisPer,TblGLPDSUM[PERIOD_YEAR],"="&ThisYr

Per Unit Sales

Similar to returning g/l data, certain sales data can be returned in with a formula. The common case for this is Cubic Yards of Concrete but FX can be customized to return other values as well.

Common Formula:

Qty Sold This Period: =SUMIFS(TblSLSQTY[QTY SOLD],TblSLSQTY[PERIOD_YEAR],"="&ThisYr,TblSLSQTY[PERIOD_NO],"="&ThisPer)

Qty Sold Year to Date: =SUMIFS(TblSLSQTY[QTY SOLD],TblSLSQTY[PERIOD_YEAR],"="&ThisYr,TblSLSQTY[PERIOD_NO],"<="&ThisPer)