Proposal Summary
This proposal covers the scope of creating a KPI in the dashboard based on some Saved Searches on various measures. The KPI indicators include the Sales, Inventory, Customers, and Employee measures.
Requirement
Graph Tech would like to implement a KPI dashboard with the details below:
Our Solution
We will create saved searches for each KPIs in the provided file. And create KPI Score Card in the custom dashboard with these saved searches. Please find the comments below for each KPIs
- Sales Revenue – We will consider the Sales Order amount as the target amount and Invoice amount as the Actual amount. So, two saved searches will be created to include both cases in the KPI score card.
- Cash On Hand – We will create a saved search to pull the amounts of bank accounts. We will include the following accounts in the search –
- 12200 RSCU – Cdn Chequing
- 12210 RSCU – U.S. Chequing
- 12400 CIBC – Cdn
- 12410 CIBC – U.S.
- 12510 PayPal – USD
- 12600 PayPal – Cdn
- 12710 Royal Bank – U.S.
- 12700 Royal Bank – Cdn
- Outstanding Payables and Receivables – We will consider the amounts in Bills and Invoices which are due.
- Delinquent Rec. – We will create separate AR Aging saved searches for 60 days, 90 days and >120 days.
- Back Order – A saved search of sales orders to pull the Back Order Quantity.
- Customer replacement – We will consider total quantity in all the return authorization records.
- Overall Utility Cost – We will be considering the amounts of the accounts – ‘66100 Hydro & Gas’ and ‘92910 General & Admin: Hydro & Gas – general & administration’.
- Inventory Over Sales – We will consider the Total Value field in the Item record (field id- totalvalue). For SO Invoiced – we will take the invoice amount.
- DSI (days sales of inventory) – For Inventory Quantities, we will consider the quantity in Sales Orders.
The KPI Dashboard will look like the below screenshot (The sample contains only some KPIs).
Assumptions
- Raw Material Yield, IM Defect, Reported Injury, Customer Contacted, Workforce Utilization, and Critical Machine shutdown – We can exclude these KPI for now as there is no related data in NetSuite.
- In the Sales Revenue KPI, no need to consider the amount of Cash Refunds and Credit memos.
- For the Date Ranges, the Target 2022 will only be applicable for Sales Revenue and the remaining date ranges will be applicable for all other KPIs.
- The users with the admin role can access the KPI and no role restrictions will be applied for the dashboard.
Risk
- Cannot include the Unit, Owner and Review frequency in the KPI Score Card.
- Cannot display all the saved searches in a single KPI score card. This can be included in multiple KPI score cards