Session 8: KPIs and Dashboards

What is a KPI in NetSuite?

KPIs, or Key Performance Indicators can show results over time for your data in NetSuite.  A KPI dashboard component shows results in table format, and typically you can drill down into these results.  You can have only one KPI dashboard component on your home screen, however it can have multiple rows of information within it.  You can also create custom tabs for additional KPI information.  A KPI Meter dashboard component shows a single KPI search result as an image.  You can add up to three KPI Meter portlets to a dashboard. Each meter visually represents data for one KPI that is displayed in the dashboard's Key Performance Indicators portlet.

This document describes how to create two KPI Scorecards in NetSuite: one that shows the current distribution of Functional Expenses and one that shows the current number of days of operating cash on hand.

KPI:  Functional Expenses

Introduction

The following describes how to configure a KPI Scorecard that shows your current functional expense ratio in real time. The KPI will show your percentage of Admin, Fundraising and Programs as a percent of total expenses as tracked in NetSuite. For more information about KPI Scorecards, please search NetSuite Help for “KPI Scorecards Overview.”

To configure the Functional Expense KPI Scorecard, you’ll create one Saved Search to calculate each of the three types of functional expenses (GA, Programs and Fundraising), and a fourth Saved Search that calculates the total.  After that you will create the KPI Scorecard that calculates the percentages.

Here's how to create the four required Saved Searches:

Admin

In your NetSuite instance, go to the Reports menu and select New Search.  Choose Transaction as the Search Type, and in the Transaction Search form, click the button at the top for Create Saved Search.

• For the Search Title , enter Admin KPI
  
  
• At the top of the form, check the following boxes:
  • Available as List View
  • Available as Dashboard View
  • Available as Sublist View
  • Show in Menu

• On the Criteria Tab, Standard subtab, add the following values in the Filter field:
  
  • Type = Bill or Check
  • Functional Expense = General Administrative

• On the Results Tab, Columns subtab, edit or add the following fields:
  
  • Amount: set the Summary Type field = Sum

• On the Available Filters Tab
  
  • Add a Filter for Date
  • Click on the Show In Filter Region column, and check the checkbox.

• Click Save.  You will be taken back to the Saved Searches page.

Programs

Click on the New Saved Search button. Choose Transaction as the Search Type.

• For the Search Title , enter Program KPI
  
  
• At the top of the form, check the following boxes:
  • Available as List View
  • Available as Dashboard View
  • Available as Sublist View
  • Show in Menu

• On the Criteria Tab, Standard subtab, add the following values in the Filter field:
  
  • Type = Bill or Check
  • Functional Expense = Program Service

• On the Results Tab, Columns subtab, edit or add the following fields:
  
  • Amount: set the Summary Type field = Sum

• On the Available Filters Tab
  
  • Add a Filter for Date
  • Click on the Show In Filter Region column, and check the checkbox.

• Click Save.  You will be taken back to the Saved Searches page.

Fundraising

Click on the New Saved Search button. Choose Transaction as the Search Type.

• For the Search Title , enter Fundraising KPI

• At the top of the form, check the following boxes:
  • • Available as List View
    • Available as Dashboard View
    • Available as Sublist View
    • Show in Menu

• On the Criteria Tab, Standard subtab, add the following values in the Filter field:
  
  • Type = Bill or Check
  • Functional Expense = Fundraising

• On the Results Tab, Columns subtab, edit or add the following fields:
  • Amount: set the Summary Type field = Sum

• On the Available Filters Tab

• • Add a Filter for Date
  • Click on the Show In Filter Region column, and check the checkbox.

• Click Save.  You will be taken back to the Saved Searches page.

All Expenses

Click on the New Saved Search button. Choose Transaction as the Search Type.

For the Search Title , enter All Expense KPI

• At the top of the form, check the following boxes:
  • • Available as List View
    • Available as Dashboard View
    • Available as Sublist View
    • Show in Menu

• On the Criteria Tab, Standard subtab, add the following values in the Filter field:
  
  • Type = Bill or Check
  • Functional Expense = - None -

• On the Results Tab, Columns subtab, edit or add the following fields:
  • Amount: set the Summary Type field = Sum

• On the Available Filters Tab
  
  • Add a Filter for Date
  • Click on the Show In Filter Region column, and check the checkbox.

• Click Save.  You will be taken back to the Saved Searches page.

To create the KPI Scorecard, go to:

Customization > Centers and Tabs > KPI Scorecards > New

In the Name field enter Functional Expense Ratios

Go to the Custom tab and choose the following

• • Custom KPI #1 = Admin KPI
  • Custom KPI #2 = Fundraising KPI
  • Custom KPI #3 = Program KPI
  • Custom KPI #4 = All Expenses KPI

Go to the KPIs tab and, for each row, enter

• KPI = Custom KPI #1
  • Compare Value to = Custom KPI #4
  • Comparison Type = Ratio (Percent)
  • Label = Admin
  • Click Add
• KPI = Custom KPI #2
  • Compare Value to = Custom KPI #4
  • Comparison Type = Ratio (Percent)
  • Label = Fundraising
  • Click Add
• KPI = Custom KPI #3
  • Compare Value to = Custom KPI #4
  • Comparison Type = Ratio (Percent)
  • Label = Programs
  • Click Add

On the Date Ranges tab enter

• Row 1, Range field = this fiscal year
• Click Add

On the Highlighting tab enter the following by row (optional):

Click Save.

To add a Scorecard to the Dashboard, go to:

• From your dashboard click the Personalize link in the upper right corner
• Add KPI Scorecard
• From the Scorecard Portlet, choose Setup from the upper right corner of the portlet or from the portlet instructions
• Select Functional Expense Ratios in the KPI Scorecard field
• In the Orientation field, select Date Ranges on Left
• Click Save.
• Drag the portlet to the left column.

KPI: Days-Cash-On-Hand (DCOH) 

The DCOH KPI provides a quick, real-time snapshot of the number of days of operating cash that is on hand. The KPI divides the total cash on hand by the average daily expense over the course of the year. Note that the KPI does not exclude restricted cash from the cash-on-hand balance. With some additional criteria you could choose to limit this to unrestricted cash using the Restriction segment. This KPI also uses a rolling average over a year to calculate the daily expense amount, so as time passes your KPI will change because of the cash you have on hand, as well as when your average expense changes from the last year. You can adjust this to use a more fixed daily expense or a different range if you would like.

In this tutorial we will create Saved Searches to establish the DCOH Average Expense per Day and Cash Balances.

Average Expenses Per Day Search

In your NetSuite instance, go to the Reports menu and select New Search.  Choose Transaction as the Search Type, and in the Transaction Search form, click the button at the top for Create Saved Search.

• For the Search Title, enter Average Expense Per Day

• On the Criteria Tab, Standard subtab, add the following values in the Filter field:
  
  • Type = Check, Bill
  • Date = within last rolling year
  • Account Fields…Type = Expense
  • Memorized Fields… Transaction Type = none of Check, Bill Payment

• On the Results Tab, Columns subtab, edit or add the following fields:
  
  • Formula (Currency)
    • Summary Type: Sum
    • Function: Absolute Value
    • Formula: {amount}/365
  • Amount
    • Summary Type: Sum
    • Function: Absolute Value
• On the Available Filters tab, add the following:
  
  • Date = Show in Filter Region

• Click on Save

Cash Balance

Click on the New Saved Search button. Choose Transaction as the Search Type.

For the Search Title, enter Cash Balance

• On the Criteria Tab, Standard subtab, add the following values in the Filter field:
  
  • Type = Bank
  • User Fields… Date Created= is on or before today

• On the Results Tab, Columns subtab, edit or add the following fields:
  
  • Formula (Currency)
    • Summary Type = Sum
    • Formula = {balance}
• On the Available Filters tab, add the following:
  
  • User : Date Created = Show in Filter Region

• Click on Save

Create the KPI Scorecard

The KPI Scorecard will use the results from the two Searches to calculate and display the Days Cash On Hand. To create the KPI Scorecard, go to:

Customization > Centers and Tabs > KPI Scorecards > New

• In the Name field, enter Days Cash On Hand
• In the first row of the KPIs tab enter:
  • Formula (Numeric)
  • Formula Column: {CUSTOM2}/{CUSTOM}
  • Label: Days Cash On Hand
  • Add
• In the second row choose “Custom KPI #2”
  • Label: Cash On Hand
  • Add
• In the third row choose “Custom KPI #1”
  • Label: Avg Exp per Day
  • Add
• On the Date Ranges tab
  • Range = this month
• On the Custom tab
  • For Custom KPI #1 choose the Saved Search “Average Expense per Day”
  • For Custom KPI #2 choose the Saved Search “Cash Balance”
• On the Highlighting tab
  • Row 1 KPI Days Cash on Hand
    • Highlight if = Greater Than
    • Threshold = 100 (days)
    • Date Range = All
    • Text Color = Green
    • Bold = Yes
  • Row 2 KPI Days Cash on Hand
    • Highlight if = Less Than
    • Threshold = 100 (days)
    • Date Range = All
    • Text Color = Red
    • Bold = Yes
• Click on Save
  
  

Add the KPI Scorecard to the Dashboard

   NOTE:  The standard dashboard will only accommodate one KPI Scorecard.

From the dashboard choose Personalize then:

• Standard Content > KPI Scorecard
• Click “Set Up from the portlet instructions
• Choose Days Cash on Hand from the KPI Scorecard dropdown
• Choose “Date Ranges on Left” from the Orientation dropdown
• Move to the leftmost column
• Click on Save

Other Key Performance Indicators and Trend Graphs

NetSuite provides several built-in KPI and Trend Graphs that require no customization. From the dashboard, you can select the Personalize link and choose from the different types to add them.

When a Key Performance Indicators portlet appears on a page, you can click the portlet's Set Up link and define its contents and layout in the Set Up Key Performance Indicators popup window.

The KPIs available in the Set Up Key Performance Indicators popup window depend upon the role your role and the features that are enabled.

For more information, please search NetSuite help for “Key Performance Indicators Overview,” “Setting Up Trend Graph Portlets” and the related Help articles linked at the bottom of the Help pages for each.

The following standard indicators are available:

• All Expenses, This Month vs. Last Month
• Income, This Period vs. Last Period
• Expenses, This Period vs. Last Period
• Profit, This Period
• Sales This Month vs. Same Month Last Fiscal Year
• Income from Donations, This Month vs. Last Month

The following describes creating a KPI that shows total Income from Donations.

To create the Saved Search used in the KPI, go to:

In your NetSuite instance, go to the Reports menu and select New Search.  Choose Transaction as the Search Type, and in the Transaction Search form, click the button at the top for Create Saved Search.Reports > New Search > Transaction > Create Saved Search

• In the Search Title enter “Income from Donations”

• Check the following boxes in the header area:
  • Public
  • Available as List View
  • Available as Dashboard View
  • Available as Sublist View
  • Show in Menu
• On the Criteria tab add:
  • Type = Cash Sale or Invoice
  • (If you setup Revenue Type Custom Segment) - Revenue Type (Custom Column) = <choose your donation revenue types - should be contributions>
    • (If you didn't do custom segment) -Account = <choose your donation Income account from the chart of accounts> 
• On the Results tab, include only the Amount field
  • In the Summary Type dropdown choose “Sum”
  • Check the “Show Totals” checkbox 
• On the Available Filters tab add Date and check the “Show in Filter Region” checkbox
• Click on Save

Adding the KPI Portlet and Indicators

• From the dashboard, click Personalize
• From the Standard Content panel, click Key Performance Indicator
  

• Go to Setup, which appears in the upper right corner of the portlet when you hover over it
• Click the Standard KPIs button and add and the following indicators:
  • Income
  • Expenses
  • Profit
  • Sales
• Click the Add Custom KPIs button and add the following:
  • All Expenses
  • Income from Donations
• Click on Save

Adding a Trend Graph

You can add up to five trend graph portlets to any standard or custom page, by clicking or dragging and dropping KPIs in the Trend Graphs folder of the page's Add Content panel. Each trend graph portlet includes a Set Up link that you can click to set display options for that trend graph only.

To add a Trend Graph

• From the dashboard, click Personalize
• Click the Trend Graphs panel selector on the left
• Click any one of the Trend Graph icons
• Click the Setup menu for the Trend Graph portlet that appears
• From the KPI dropdown choose Operating Cash Flow
• Click on Save

Note that the Functional Expense KPI works best in the narrow, left column. The other KPI work well in either the wide, center or narrow left areas of the dashboard.