Background

NetSuite is a cloud-based Enterprise Resource Planning (ERP) platform that provides businesses with a suite of applications to manage their financials, operations, and customer relationships, all in one integrated system. After the success deployment of the Roast App, I built and automated reports that utilized sales data for the Production and Sales team.

NetSuite has a native data attribute called "Preferred Stock Level" which determines optimum quantity to maintain in stock of an item. By combining the product weigh calculated from a recipe, I was able to calculate the quantity of coffee in poundage and stock shortage. From those calculations, I ran a report for the Production Manager daily for all of the products that are manufactured. This allows the Production Manager to create a production schedule to allow for efficient manufacturing by meeting stock level.

Furthermore, as Hawaiian Paradise Coffee are making sales, I was able to utilize the sales data and NetSuite to create a sales report that ties the sales rep to their customer and their last date of purchase. This report is important as it allows the Sales team to determine whether or not their customers are adhering to their contractual terms.

Examples of Reports

Below is a sales representative's report for their customers' Date of Last Sales:

Below is an example of a stock shortage report that display the amount of coffee to manufacture the shortage by one month and two weeks:

How to Create and Automate Stock Level Shortage Report:

1. In NetSuite, go to List > Search > Saved Search > New

2. Select the Item option
   

3. Define your Criteria for the returned query dataset of your Items:
   
   a. Formula (numeric) - This line evaluates whether or not the current stock inventory meets the preferred stock level. It ensures that the items that are being returned are all short stockage items

   b. Type - This line evaluates that the Items that we are querying are all manufactured/ Assembly items

   c. Inventory Location : Name - If you have multiple warehouse locations, then this line ensures that you are only querying the stock inventory of those items at a certain location

   d. Class - This is an attribute to further distinguish other items that are not manufactured/ Assembly

   e. Name - This line is to evaluate whether or not the product is an In Room item or not. This is operation specific as Hawaiian Paradise Coffee make In Room products for their customers

4. Define the returned dataset attribute results:
   

   a. Name - The name of the product

   b. Description - The description of the product. E.g, Kona coffee vs. regular Arabica coffee

   c. Location On Hand - The current quantity of items stocked at a location/ warehouse

   d. Location Preferred Stock Level - The NetSuite calculated quantity of the optimum quantity to maintain in stock of an item

   e. Formula (Numeric) - {weight} * ({locationpreferredstocklevel} - {locationquantityonhand}) this calculates the amount of coffee needed for one month stock of an item. This is dependent on the Location Preferred Stock Level is set to one month

   f. Formula (Numeric) - {weight} * ({locationpreferredstocklevel} - {locationquantityonhand})/2 this calculates the amount of coffee needed for two weeks stock of an item. This is dependent on the Location Preferred Stock Level is set to one month

5. Running the report will yield the following results:
   
   As you can see that each item starts with "IR-", a column that displays the current quantity at hand, the preferred stock level, and the amount of coffee needed to restock for one month and two weeks.

6. I saved this Saved Search and made it available for my Production Manager as a Dashboard. However, I also utilized the email function in Saved Search to automate an email daily for the entire Operations team

How to Create and Automate Stock Level Shortage Report:

1. In NetSuite, go to List > Search > Saved Search > New

2. Select the Customer option
   

3. Define the Criteria:
   

   a. Transaction : Type - Sales Order. The transactions that we want to look at are Sales Orders of the Customers

   b. Date of Last Sale - This ensures that we are looking at the time frame of the last sales' transaction is more than one month to the relative date:
   

   c. Sales Rep - This is where you filter all of the customers that are attached to the Sales Rep

4. Define the returned dataset attribute results:
   
   a. Name - The name of the Customer Record

   b. Email - The email attribute of the Customer Record

   c. Phone - The phone attribute of the Customer Record

   d. Sales Rep - The sales rep tied to the Customer Record

   e. Date of Last Sale - The date of the last sales transaction of the customer

   Note - Sort by the "Date of Last Sale" descending to display the customer that hasn't brought any products recently by one month

5. Running the report will yield the following -
   

Conclusion:

The following reports are utilized by the Production and Sales team to prioritize and make decisions. The VP of Sales at Hawaiian Paradise Coffee is cc'd on the automated email of the sales report and this allows them to see the activities of the customers of their sales team linked by the sales representative. The VP can engage with the Sales team to follow up on certain customers that aren't meeting their contractual terms.

Meanwhile, the Production Manager has access to stock level reporting. The Production Manager can make better decision and prioritize the production of items that are currently low in inventory. Furthermore, the quantity of coffee needed to manufacture the items is readily available and calculated for quick communication with the roasters.