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.

NOTES AND DISCLAIMER

Please be aware of any changes you made in "Rename Records/Transactions". The document assumes that the name of other standard record types such as Bills have not been changed. Also note that the thresholds set for highlighting KPI in the scorecards should be determined by your organization. For example, the Functional Expense KPI is set to turn red if Admin is greater than 10%. You can set these numbers to higher or lower values as you see fit. Threshold highlights and other formats are optional.

Finally, please verify the results manually to ensure accuracy, and consult your CPA if you have questions about variables not included in these KPI, such as restricted 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

Note: If you want to share this search with other NetSuite users, also check the box for Public

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

Note: When the Type selection box pops up, use your Ctrl key on your keyboard to select multiple items.

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

Note: At the bottom of the Field listing in the Filter field are links to related lists of fields available to that search type. These are shown as the related object name with ... after it. Clicking on one of these will bring up the list of related fields you can then choose from.

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.

Homework: Prior to the next session:

Create the above KPIs and add to your dashboard
Create a list of additional KPIs of use to your organization

Documentation

Session 8: KPIs and Dashboards

Financials Accelerated Session 8

What is a KPI in NetSuite?

KPI: Functional Expenses

Introduction

Admin

Programs

Fundraising

All Expenses

To create the KPI Scorecard, go to:

To add a Scorecard to the Dashboard, go to:

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

Average Expenses Per Day Search

Cash Balance

Create the KPI Scorecard

Add the KPI Scorecard to the Dashboard

Other Key Performance Indicators and Trend Graphs

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

Adding the KPI Portlet and Indicators

Adding a Trend Graph

Homework: Prior to the next session: