Page Comparison

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, Fund Raising 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 a Saved Search to calculate each of the three types of functional expenses (GA, Programs and Fund Raising), a Search that calculates the total and then the KPI Scorecard that calculates the percentages.

The following describes how to create the four required Saved Searches:

Admin

Reports > New Search > Transaction > Create Saved Search

• Header Checkboxes
  • Available as List View
  • Available as Dashboard View
  • Available as Sublist View
  • Show in Menu
• Criteria Tab > Standard > Filter
  • Type = Bill or Check
  • Function = General Administrative
• Results Tab > Columns > Field
  • Amount: Summary Type = Sum
• Available Filters Tab
  
  • On the Filter dropdown enter Date
  • Check the box to Show In Filter Region
• Save As “Admin KPI”

Programs

Reports > New Search > Transaction > Create Saved Search

• Header Checkboxes
  • Available as List View
  • Available as Dashboard View
  • Available as Sublist View
  • Show in Menu
• Criteria Tab > Standard > Filter
  • Type = Bill or Check
  • Function = Program Service…
• Results Tab > Columns > Field
  • Amount: Summary Type = Sum
• Available Filters Tab
  
  • On the Filter dropdown enter Date
  • Check the box to Show In Filter Region
• Save As “Program KPI”

Fund Raising

Reports > New Search > Transaction > Create Saved Search

• Header Checkboxes
  • Available as List View
  • Available as Dashboard View
  • Available as Sublist View
  • Show in Menu
• Criteria Tab > Standard > Filter
  • Type = Bill or Check
  • Function = Fund Raising…
• Results Tab > Columns > Field
  • Amount: Summary Type = Sum
• Available Filters Tab
  
  • On the Filter dropdown enter Date
  • Check the box to Show In Filter Region
• Save As “Fund Raising KPI”

All Expenses

Reports > New Search > Transaction

• Header Checkboxes
  • Available as List View
  • Available as Dashboard View
  • Available as Sublist View
  • Show in Menu
• Criteria Tab > Standard > Filter
  • Type = Bill or Check
  • Activity Function = none
• Results Tab > Columns > Field
  • Amount: Summary Type = Sum
• Available Filters Tab
  
  • On the Filter dropdown enter Date
  • Check the box to Show In Filter Region
• Save As “Fund Raising KPI”

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 = Fund Raising KPI
  • Custom KPI #3 = Program KPI
  • Custom KPI #4 = All Expenses

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

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

On the Date Ranges tab enter

• Row 1, Range = this fiscal year

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

• KPI = Admin, highlight if greater than 10 all Red Flag text color ff0000 bold = Yes
• KPI = Programs, highlight if less than 80 All Fireball
• KPI = Fund Raising highlight if less than 10 All Dollar Sign 3

Save

To add a Scorecard to the Dashboard, go to:

• From the 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 KPIRatios
• Drag the portlet to the left column
• Select Orientation = Date Ranges on Left
• Save

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.

Create Saved Searches to establish the DCOH Average Expense per Day and Cash Balances.

Average Expenses Per Day Search

Reports > New Search > Transaction > Create Saved Search

• Criteria
  • Type = Check, Bill
  • Date = within last rolling year
  • Account Fields… Type = Expense
  • Memorized Fields…Transaction Type = none of Check, Bill Payment
• Results
  • Formula (Currency)
    • Summary Type: Sum
    • Function: Absolute Value
    • Formula: {amount}/365
  • Amount
    • Summary Type: Sum
    • Function: Absolute Value
• Available Filters
  • Date = Show in Filter Region

Save as “Average Expense Per Day”

Cash Balance

Reports > New Search > Account > Create Saved Search

• Criteria
  • Type = Bank
  • User Fields… : Date Created is on or before today
• Results
  • Formula (Currency)
    • Summary Type: Sum
    • Formula: {balance}
• Available Filters
  • User : Date Created = Show in Filter Region

Save as “Cash Balance”

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 “Custom2”“Custom KPI #2”
  • Label: Cash On Hand
  • Add
• In the third row choose “Custom 1”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
• Save

Add the KPI Scorecard to the Dashboard

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

From the dashboard choose Personalize then:

• Standard Content > KPI Scorecard
• Move to the leftmost column
• 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
• 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:

Reports > New Search > Transaction > Create Saved Search

• 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>
    • accounts> 
• On the Results tab, include only the Amount field
  • In the Summary type dropdown choose “Sum”
  • Check the “Show Totals” buttoncheckbox 
• On the Available filters tab add Date and check the “Show in Filter Region” checkbox
• Check the following boxes in the header area:
  • Public
  • Available as List View
  • Available as Dashboard View
  • Available as Sublist View
  • Show in Menu
• In the Search Title enter “Income from Donations”

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
• 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
• 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.