3305 Main Street Ste 019 Vancouver, WA 98663

# How to Obtain Financial Values from Sage 100 Contractor

Background

Parameters
The formula examples below identify parameters that may be replaced with cell references (to cells containing the needed value) or literal values. The parameters used by these formulas include:

• LedgerAccount
• PostingYear
• PeriodNumber

Balance Sheet Accounts
Use the following Excel formula to obtain financial values for balance sheet accounts:

• Balance As Of Year &Period
`=DBFind("Sage 100 Contractor", "lgrbal", "balnce", "lgract=? AND postyr=? AND actprd=?", "First", LedgerAccount, PostingYear, PeriodNumber)`
• Activity for a given Year & Period
Use the above formula to obtain the balance as of the desired year and period. Then use the same formula to obtain the balance for the prior period (or year) and subtract the two in order to determine the net change.

Income Statement Accounts
Use the following Excel formulas to obtain financial values for income statement accounts:

• Period Activity
`=DBFind("Sage 100 Contractor", "lgrbal", "balnce", "lgract=? AND postyr=? AND actprd=?", "First", LedgerAccount, PostingYear, PeriodNumber)`
• YTD Activity
`=DBSum("Sage 100 Contractor", "lgrbal", "balnce", "lgract=? AND postyr=? AND actprd<=?", LedgerAccount, PostingYear, PeriodNumber)`
• Period Budget
`=DBFind("Sage 100 Contractor", "lgrbal", "budget", "lgract=? AND postyr=? AND actprd=?", "First", LedgerAccount, PostingYear, PeriodNumber)`
• YTD Budget
`=DBSum("Sage 100 Contractor", "lgrbal", "budget", "lgract=? AND postyr=? AND actprd<=?", LedgerAccount, PostingYear, PeriodNumber)`