Session 8: KPIs and Dashboards
Financials Accelerated Session 8
During this session, you will learn to create NetSuite KPIs and custom Dashboards to get overview level data for your organization's transactions.
Dashboards can be configured to push information in the forms requested by your Executive sponsor, CFO, and Accountant.
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
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
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
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
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