ANS-0721 · FINANCIAL REPORTING
How to Calculate Financial Ratios in NetSuite Using KPIs and Saved Searches
NetSuite users can leverage KPI screens and saved searches to derive essential financial performance indicators.
Short answer
Financial ratios can be calculated in NetSuite using KPI screens and saved searches. For complex ratios like EPS or Dividend Payout, statistical accounts may be required. Custom formulas can be implemented within saved searches to achieve specific percentage and ratio calculations, including handling division by zero.
Scenario
Organizations often need to calculate various financial ratios to assess performance, such as profitability, liquidity, and efficiency. While some standard metrics are available, specific or custom ratios may require tailored calculations within NetSuite. This often involves combining different financial data points and handling potential division by zero errors.
Solution
Financial ratios can be calculated in NetSuite through two primary methods: KPI screens and saved searches. For advanced ratios like Earnings Per Share (EPS) or Dividend Payout, the creation of statistical accounts for metrics such as shares outstanding may be necessary.
For custom percentage and ratio calculations, saved searches offer flexibility to define specific formulas. Below are examples of formulas that can be used in NetSuite saved searches, demonstrating how to calculate percentages, handle division by zero, and perform conditional sums:
Percentage/Ratio with Divide by Zero Handling and Conditional Sum:
(SUM(CASE WHEN {account}'440050 Culling of Bins' THEN {Amount} ELSE 0 END)*-1)/nullif(SUM(CASE WHEN {accounttype}'Income' THEN {Amount} ELSE 0 END),0)Sum of Income Account Type:
SUM(CASE WHEN {accounttype}'Income' THEN {Amount} ELSE 0 END)Sum of Specific Account:
SUM(CASE WHEN {account}'440050 Culling of Bins' THEN {Amount} ELSE 0 END)Ratio of Overtime Wages to Total Wages:
sum(case when {account} '611010 Overtime' then {Amount} else 0 end)/nullif(sum(CASE WHEN {account} '611000 Wages' THEN {Amount} ELSE 0 END),0)Rounded Quantity Ratio (e.g., Shipped vs. Ordered):
Round(SUM(NVL({quantityshiprecv},0)) / NULLIF(SUM(NVL({quantity},0)),0),2)Expert NetSuite Support
Need help with this NetSuite issue?
Financial Reporting consulting and configuration support