Financials Accelerated Session 8
| Info | ||
|---|---|---|
| ||
Dashboards can be configured to push information in the forms requested by your Executive sponsor, CFO, and Accountant. |
During this session, you will learn to create NetSuite KPIs and custom Dashboards to get overview level data for your organization's transactions. | Info | ||
|---|---|---|
| ||
|
What is a KPI in NetSuite?
KPIs, oir 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.
| Info | ||
|---|---|---|
| ||
NOTES AND DISCLAIMER Note that the instructions presume the following dimension names have been changed: • Class to Function • Department to Restriction The document assumes that the 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, 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 one Saved Search to calculate each of the three types of functional expenses (GA, Programs and Fund Raising), and a fourth Saved Search that calculates the total and then . After that you will create the KPI Scorecard that calculates the percentages.
The following describes Here's how to create the four required Saved Searches:
Admin
Reports > New Search > Transaction > 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
Header Checkboxes.
- 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
| Info | ||
|---|---|---|
| ||
| Note: If you want to share this search with other NetSuite users, also check the box for Public |
- On the Criteria Tab > Standard > Filter, Standard subtab, add the following values in the Filter field:
- Type = Bill or Check
- Function = General Administrative
| Info | ||
|---|---|---|
| ||
| Note: When the Type selection box pops up, use your Ctrl key on your keyboard to select multiple items. |
- On the Results Tab > Columns > Field, Columns subtab, edit or add the following fields:
- Amount: set the Summary Type field = Sum
- On the Available Filters Tab
- On the Filter dropdown enter Date
- Check the box to Add a Filter for Date
- Click on the Show In Filter Region column, and check the checkbox.
- Save As “Admin KPI”Click Save. You will be taken back to the Saved Searches page.
Programs
Reports > New Search > Transaction > Create Saved Search
Header Checkboxes
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 > Filter, Standard subtab, add the following values in the Filter field:
- Type = Bill or Check
- Function = Program Service…Service
- On the Results Tab > Columns > Field, Columns subtab, edit or add the following fields:
- Amount: set the Summary Type field = Sum
- On the Available Filters Tab
- On the Filter dropdown enter Date
- Check the box to Add a Filter for Date
- Click on the Show In Filter Region column, and check the checkbox.
- Save As “Program KPI”
Fund Raising
Reports > New Search > Transaction > Create Saved Search
- Header CheckboxesClick 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 > Filter, Standard subtab, add the following values in the Filter field:
- Type = Bill or Check
- Function = Fund Raising…Fundraising
- On the Results Tab > Columns > Field, Columns subtab, edit or add the following fields:
- Amount: set the Summary Type field = Sum
- On the Available Filters Tab
- On the Filter dropdown enter Date Check the box to
- Add a Filter for Date
- Click on the Show In Filter Region column, and check the checkbox.
- Save As “Fund Raising KPI”Click Save. You will be taken back to the Saved Searches page.
All Expenses
Reports > New Search > Transaction
Header Checkboxes(image)
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 > Filter, Standard subtab, add the following values in the Filter field:
- Type = Bill or Check
- Function = none- None -
- On the Results Tab > Columns > Field, Columns subtab, edit or add the following fields:
- Amount: set the Summary Type field = Sum
- On the Available Filters Tab
- On the Filter dropdown enter Date
- Check the box to Add a Filter for Date
- Click on the Show In Filter Region column, and check the checkbox.
- Save As “Fund Raising KPI”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 = Fund Raising 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 = Fund RaisingFundraising
- 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)
- 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:
Click Save.
To add a Scorecard to the Dashboard, go to:
- From the 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
- Drag the portlet to the left column
- Select Orientation = 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.
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
- Formula (Currency)
- 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}
- Formula (Currency)
- 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 “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
- Row 1 KPI Days Cash on Hand
- Save
Add the KPI Scorecard to the Dashboard
| Info | ||
|---|---|---|
| ||
 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>
- 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
- 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.
| Info | ||
|---|---|---|
| ||
|
