3305 Main Street Ste 019 Vancouver, WA 98663

How to Obtain Financial Values from Sage 100 Contractor

Background
This article provides examples of Liberty Reports formulas that can be used in Excel to retrieve financial reporting values from Sage 100 Contractor. This article is written assuming the use of Sage 100 Contractor version 19 or higher.

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)