Find Specific Text Within Saved Search Formulas

Use an “Analytics Audit Trail” type Saved Search to find references within other Saved Searches

Introduction

Saved Searches are probably the most widely-used reporting tool in NetSuite. They are often used to stay in control of what is happening in the environment using email alerts and dashboards and the like. Sometimes, though, Saved Search criteria or results include references to specific text values that may be subject to change, posing a risk that the search can eventually become obsolete or misleading.

Use Case

For example, consider a custom field on a Purchase Order “Custom Status” with a drop down list of values. If the text in the drop-down is referenced in a search formula, and later someone changes the wording of the text, the saved search will no longer be accurate. This article will show how to prevent and detect record changes that will adversely impact existing Saved Searches.

Best Practices: Use Internal IDs

Before the solution, I would like to point out that it’s never good practice to reference specific text in a formula, if it can be avoided. It is much better to use the internal id as a reference. Here’s a handy tip: You can usually add “.id” in a formula to get the internal id of the list value or record value, even when the field/record is more than two joins away. For example, to reference a Location value of the Sales Order transaction on an Invoice search, you can use “{createdfrom.location.id} = 3” instead of something like “{createdfrom.location} = ‘Main Warehouse’.” (Hat tip to Sean G., my colleague at Prolecto who taught me that.)

Solution: Analytics Audit Trail Search

Now for the solution, in cases where the best practice advice described above was not followed and specific text was used in formulas: Use an Analytics Audit Trail Search! Navigate to Lists > Searches > Saved Searches > New and select “Analytics Audit Trail” as the search type. Below is sample criteria and results for a search I created where I was looking for formulas in Saved Searches that were referencing fields that contained a number from 1-7 with a hyphen afterwards:

Criteria Tab

FilterDescriptionFormula
Formula (Numeric)is 1CASE
WHEN {newvalue} LIKE ‘%1-%’ THEN 1
WHEN {newvalue} LIKE ‘%2-%’ THEN 1
WHEN {newvalue} LIKE ‘%3-%’ THEN 1
WHEN {newvalue} LIKE ‘%4-%’ THEN 1
WHEN {newvalue} LIKE ‘%5-%’ THEN 1
WHEN {newvalue} LIKE ‘%6-%’ THEN 1
WHEN {newvalue} LIKE ‘%7-%’ THEN 1
ELSE 0 END
Component Namecontains formula

Note: the “Component Name — contains formula” is telling the search to look for saved search “components” (= criteria, columns, or filters) that are of the “formula” type.

Results Tab

FieldSummary TypeFormula
FormulaGroup‘<a href=https://system.netsuite.com/app/common/search/search.nl?e=T&id=’ || {savedsearch.internalid} || ‘ target=_blank>Link</a>’
Record TitleGroup
New ValueCount

Note that the Formula in the Results tab creates hyperlinks to the saved searches that contain the text we are looking for. For more help in creating hyperlinks in saved searches, see this article by Marty Zigman.

Next Step: Stay ahead of the curve

If there are custom lists or other records in the environment that are likely to be referenced in Saved Searches, it also can be valuable to use the above saved search as a link in a second saved search which can drive an email alert to warn a user about the change and provide an actionable list. For example, if you know that the location names are often referenced in Saved Searches, you can set up an alert every time a Location name changes with a list of other saved searches that are impacted.

Conclusion

The Analytics Audit Trail Search is a really handy resource to stay on top of changes that affect existing reports. If you would like to learn more about this feature or would like creative solutions for NetSuite problems, I’d be happy to hear from you!

Accurate Kit Costing

Learn how to use SuiteQL to achieve advanced reporting beyond the grasp of Saved Searches

Introduction

A recent client gave us a challenge: is it possible to create a report that shows what the average cost of Kit items were as of a given point in time? As will be explained below, this was not a simple request, and the data was not reachable using Saved Search functionality. Instead, we used a combination of the recently accessible “SuiteQL” SQL query ability in NetSuite and custom reporting to solve the request. If you would like to see how to expand your reporting toolkit using these tools, read on!

“Kit” Items

Kit items in NetSuite are essentially a group of other items that can easily be sold together on a Sales Order, without the need to formally “assemble” the components into an Assembly. Many clients that sell packages or bundles of different products prefer to use Kits rather than Assembly Items, since unlike Assembly Items, with Kits, there is no need to maintain work orders, bills of material, and the like. The component items remain as separate items in inventory and are available to be sold on their own. For this reason, a “Kit” does not really have a cost in NetSuite. It can almost be thought of as a bill of materials without any actual assembly taking place until the “Kit” is sold – at that point, NS consumes the component items based on the quantity of each component in the Kit and the quantity of Kits sold.

Given the above, when we talk the “cost” of a Kit, what we really are after is the cost of all the component parts multiplied by the number of each component within the Kit. We essentially want to artificially construct the Kit and project what the cost would be if it were sold at a given point in time.

Understanding the Table Structure

Before we figure out how to solve the problem, we need to map out where the data lives. I like to use Excel to create sample tables to visualize the work that NetSuite will need to do to present the data in the desired output. The sample tables that I create in Excel do not necessarily need to line up perfectly with NetSuite’s tables at this point; I actually find it more efficient to initially create tables based on my general assumptions. I then can think about my “attack plan” – where my main report data lives and what elements I can bring in using joins. Once I have a general idea of how to build a search or query, I then get the specific tables using NetSuite tools such as the Analytics Browser or the Record Catalogue, and then can think about whether a Saved Search can get to the data, or I need to use a Query Tool (more about the Query Tool below…).

Kit Avg Cost Tables

In our example, we need to bring together the following tables:

  1. The “transaction” table
  2. The “transactionLine” table
  3. The “transactionAccountingLine” table
  4. The “account” table
  5. The “item” table
  6. The “itemMember” table

Looking at the above, it quickly became clear that Saved Search would not be possible, since if I would start from a transaction search I would not be able to hop over to the item table and from there to the “item member” table to find the quantities and parent kits. Starting from an item would also not be possible, since it would require starting from the Kit items, joining to another table to get the components and their quantities within each kit, and then a third hop to the transactions of the component items.

Prolecto SuiteQL Query Tool

Thankfully, we have a couple of new tools at our disposal that are in many respects much more powerful than Saved Searches. These are the Prolecto SuiteQL Query Tool (free of charge to our clients as part of the Prolecto Utilities Bundle) and more recently, the Prolecto Query Renderer (see Marty Zigman’s article here). These tools build upon the “N/query module” that NetSuite has made available in 2019 to provide the ability to reach virtually any data in the database. In my opinion, these tools and others like them that are emerging in the marketplace may eventually replace Saved Searches completely.

After much trial and error, I was able to get accurate as-of date reporting, showing the Average Cost of Kit items. Here is the main Query used to create the report:

SELECT
type
,internal_id
,kit
, SUM(avg__kit_cost*comp_qty) as kit_avg_cost

FROM

(	SELECT
	BUILTIN.DF(im.parentitem) as kit
	, BUILTIN.DF(i.itemtype) as type
	,i.id as internal_id
	, BUILTIN.DF(im.item) as component
        , MAX(t.trandate)
	, AVG(im.quantity) as comp_qty
	, SUM(tl.quantity) as trx_qty
	, SUM(tal.amount) as trx_amt
	,(SUM(tal.amount)/NULLIF(SUM(tl.quantity),0)) as avg__kit_cost

	FROM
	itemMember im
	INNER JOIN transactionline tl ON tl.item = im.item
	INNER JOIN transactionaccountingline tal ON tl.id =    tal.transactionline AND tl.transaction = tal.transaction
	INNER JOIN transaction t ON t.id = tl.transaction AND t.id = tal.transaction
	INNER JOIN account a ON tal.account=a.id
	INNER JOIN item i on im.parentitem = i.id


	WHERE
	BUILTIN.DF(i.itemtype) = 'Kit/Package'
	AND
	a.sspecacct='InvtAsset'
	AND
	t.posting = 'T'
       
	GROUP BY
	BUILTIN.DF(im.parentitem),BUILTIN.DF(i.itemtype), i.id, BUILTIN.DF(im.item)

)

GROUP BY
type, internal_id, kit

Content Renderer

Here is a snippet of what the output looks like using the Prolecto Query Renderer:

Postscript

In the end, due to the data volume in this particular client’s account, the query could not run quickly enough and would frequently time out. I then worked with Boban D., my senior colleague at Prolecto who created a custom record structure to hold monthly results, allowing the query to run smaller chunks of data at a time. That solution deserves an article in its own right, though.

Conclusion

I hope this article helped demonstrate the power of using tools that are possible using the SuiteQL methods, and more importantly, the strength of being part of a very talented group of NetSuite consultants. If you would like to learn more about how our creative custom tools can help you break out of the limits of native reporting, we’d be happy to hear from you!

Solved! 4-4-5 Accounting Periods and Fixed Asset Management (FAM) Reports

Learn how creative reporting tools can go beyond NetSuite limitations

Photo by Olya Kobruseva on Pexels.com

What is a “4-4-5” Accounting Period?

First, a brief background: Some companies opt to divide up their quarterly and annual reporting into units that always start and end on the same day of the week. In other words, instead of using standard calendar months as accounting periods, each period consists of a set number of weeks. Typically, the 1st 2 periods of each quarter consist of 4 weeks, and the last period of each quarter is 5 weeks. This allows for better year-over-year comparisons, since each specific period will have the same number of weekdays every year.

NetSuite supports 4-4-5 periods (and any other alternate accounting period setup). For more advice and tools regarding general 4-4-5 periods in NetSuite, please see the following articles on Marty Zigman’s blog:

How to set up an alternative NetSuite 4-4-5 accounting period

4-4-5 accounting period definition consideration

Effect of 4-4-5 on NetSuite transactions and reports

When an alternate accounting period setup such as 4-4-5 is used, NetSuite transaction date and period defaulting respects the non-calendric start and end dates of the periods, as can be expected. In this respect, FAM is no different, and Depreciation History Records (which are custom records that act as placeholders for past and future depreciation entries) will correspond with the 4-4-5 period end dates, as opposed to calendar month-end dates. Likewise, NetSuite’s reporting tools will reflect posting periods correctly – with one notable exception: FAM Depreciation Reports.

FAM (Fixed Asset Management) Reports

As mentioned, Depreciation History Records respect alternate Accounting Period dates (see image #1). However, the out-of-the-box NetSuite FAM reports that show monthly depreciation uses calendar dates to determine the monthly amounts to present. This results in some months showing zero depreciation, and others showing double depreciation (see image #2). Our client wanted report outputs that mirrored the native NetSuite reports but that had the monthly amounts in the correct columns based on the accounting period dates.

Solution: Prolecto Content Rendering Engine

As part of the Prolecto Team, I am privileged to work with a number of talented software developers who have the right expertise to solve this sort of challenge. In addition to a diverse set of skills, we also have the benefit of many years of collective experience in extending NetSuite beyond its natural constraints, and thus have built up an impressive toolkit which makes it easier to provide quality results to our clients at a faster turnaround. (All of our bundles are free to our clients; contact Marty Zigman to learn more.)

In this particular situation, the best tool to use was the “Content Renderer Engine (“CRE”)” application, which uses SQL Query and SuiteScript 2.0 and can bring together virtually any data points in the NetSuite database into a customizable output. First, I created 2 saved searches: 1) Financial data from the Depreciation History Records, including the date of each record and 2) The Accounting Period start and end dates. I used the saved searches to present a “proof of concept” that the data was “reachable”, and then asked my colleagues Matthew and Elena to help with the scripting to convert the saved searches into the right type of queries and to use the CRE tool to join them together and to create the desired outputs.

See below for the report output with the depreciation amounts lined up correctly (note that the larger depreciation for 2 of the assets in their final month of service is a function of a custom depreciation method with a final month set write-off amount):

Build on existing tools

Initially, the report was designed to be executed with a suitelet that would email the user a link to the report results. However, the client found that the link did not consistently take them to the right place, as it depended on the browser and the user needed to already be logged in. To get around these problems, Matthew leveraged technology used previously and created a custom record that gave visibility to the user to the report processing status, contained links to the reports generated, and captured data to allow easy auditing of when a report was generated and by whom. This is a pattern we have commonly used in the past together with “CRE” to provide helpful context and control around reporting.

Conclusion

Helping to solve this problem was particularly satisfying because the 4-4-5 issue with FAM reports is a known issue (there purportedly is an enhancement request filed for this, but there is no expected resolution date yet), with no obvious workaround, and by using our past experience and existing technology we were able to help our client in a very meaningful way.

If you would like to learn how the CRE Report engine can be used to meet your NetSuite needs, please reach out! We would be happy to help.

NetSuite Vendor Bill Variance Journals

Note: Please also see my later article describing how to craft a Saved Search that can support Vendor Bill Variance maintenance

“IRNB” Clearing account

Readers of this blog with experience in using NetSuite’s advanced receiving feature (which separates billing and receiving to separate transactions) may be familiar with the Inventory Received Not Billed (“IRNB”) account. That account is a system account which acts as the “clearing” account for Item Receipts and Vendor Bills. Item Receipts credit this account, and Vendor Bills debit the account. When the value billed equals the value received, the account nets to zero and all is well. Often, however. the transactions do not equal. When this happens, a mechanism is needed to correct any errors, and ultimately to write off variances, with the goal of clearing out the IRNB account.

Vendor Bill variances

NetSuite has a great tool that in my experience is not widely used, which automatically calculates variance amounts and posts Journal Entries for the differences. The tool is called “Post Vendor Bill Variances” and can be found either in the Payables menu or the Purchases menu, depending on which “Center” the user’s role is configured to use.

There are two ways to use the tool, and these are well documented in NetSuite help and in SuiteAnswers Article 11157

Deficiencies

The tool is great and solves an important accounting problem, but has two drawbacks which lessen its usefulness. This article will show how you can use out-of-the box functionality to overcome these drawbacks.

1st Drawback: Know before you post

The first drawback is that the tool does not provide visibility into what the variance amounts are for a given Purchase Order prior to posting the variance journal. It merely provides a list of Purchase Order Lines which have variances, and the user can select the ones that need write-offs generated. There is no way to tell within the tool how the resulting JE’s will impact the Variance accounts.

Solution

To surmount this problem, we can create a saved search to identify the POs with variances so that users can review the amounts before posting the variance Journals. A recommended search should use the “Special Account Type” criteria to identify Inventory Received Not Billed account(s) and should summarize by grouping the “created from” fields. See Marty Zigman’s article “Explaining NetSuite’s Inventory Received Not Billed Account” which describes how such a saved search can be used. Prolecto provides the saved search as part of our Financial Saved Search Library (we provide the bundle free of charge to our clients). A well-crafted saved search can mimic the list of POs that the variance tool provides.

2nd Drawback: Trace Variance JEs back to the source

The second drawback with the tool is that the resultant Journal Entries do not have a native link or sublist back to the POs that were the underlying cause of the variances.

Solution

Fortunately, there is a native “created from” value that is linked to the Variance Journal Entries. The tricky part is that this field, although available in Saved Searches, is not a Header Field, and thus is difficult to expose on the Journal Entries. There does not seem to be a way to expose the field on the JE Lines either.

There is a solution, though. We can create a custom sublist based on a saved search and add it to the Journal Entry form so that users can see the underlying POs and variance amounts that the Journal Entry represents.

Step 1: Create Saved Search

First, create a search that will display the results desired on the Journal Entry sublist. The key here is to set “Internal ID” as an “Available Filter”. This will be the way NetSuite’s custom sublist feature understands which results to show on each Journal. The checkbox “available as sublist view” also needs to be checked. See below images for recommended criteria and results.

Step 2: Create Custom Sublist

The next step is to create a custom sublist which can be made visible on the Journal Entry forms. The navigation in the “classic center” view would be Customization > Forms > Sublists > New.

Assign the Saved Search created in Step 1 as the related Search, and select “Journal” as the transaction type to apply the Sublist to. See image below, and see SuiteAnswers Article 10120 for general Custom Sublist guidance.

Conclusion

What I like most about this use case is that it does not require any advanced development work, and is low cost. All we did was combine some native tools with a little customization, and we’ve greatly improved how Purchase Variances are posted. If you’d like to learn more about ways you can combine features to get the most out of NetSuite, I’d be happy to hear from you!

Automate Depreciation without FAM

In Shorthand
Use Amortization Templates to Automate Depreciation Entry
Setup Part 1: Setup Amortization Templates using Contra Account
Setup Part 2: Associate Templates to Asset Purchase Transaction
Asset Disposal: 2 techniques to remove amortization schedule
Midlife Asset: Override default end dates, and use residual value to ensure only remaining net value is depreciated

If you’d like to understand different NetSuite options to managing fixed asset depreciation, this article may be of interest to you.

Introduction

Readers of this blog may already be familiar with NetSuite’s Fixed Asset Management (FAM) SuiteApp Module, which is a full-scale way to manage Fixed Assets. It acts a sub-ledger system to the Fixed Asset General Ledger accounts (though not a true sub-ledger in many respects – see my Fixed Asset Management article for more detail), it automates depreciation, can handle various depreciation methods, as well as Alternative Depreciation methods for the same Assets (e.g. Tax vs. Book), and allows companies to stay in control of their asset “inventory”. It contains a plethora of reports and users who are inventive can customize the asset records to contain any meta-data they wish to track. NetSuite also recently expanded the module to ease compliance with the recent changes to how long-term leases need to be accounted for under ASC 842.

However, many companies do not need the full scope of the FAM App, especially when there is only a limited number of Assets and there are limited purchases, sales and/or revaluations of Assets. In such instances, the main benefit that FAM would provide would be the automated Depreciation Journal Entries, and for many companies, this benefit doesn’t justify the effort of implementing and maintaining FAM, or its cost. Fortunately, there is an easier way to accomplish automated Depreciation, by using NetSuite’s Amortization tool.

Amortization Tool

While this space won’t allow an in-depth description of the Amortization tool, the following is a brief outline to help users understand how it can be used for Depreciation:

The Amortization tool uses “Templates” which contain rules about how a deferred expense should be recognized (amortized) over time. When a template is selected on a transaction such as a Vendor Bill or Journal Entry, NetSuite generates an “Amortization Schedule”, which is a list of future Journal Entries that will post each month to recognize the expense over time. Selecting an Amortization Template also changes the impact of the actual source transaction. The source transaction would typically have an expense account selected as the debit, and by selecting the amortization template, the impact changes so that the debit hits the Deferred Expense account used on the Amortization Template.

Amortization Template
Source Transaction
GL Impact of Source Transaction

Depreciation essentially is the same concept as Amortization, in the sense that we move the amount booked to the Asset to Depreciation over time. The Fixed Asset account parallels a Deferred Expense account, and the Depreciation Expense account parallels an Amortization expense account. There are two slight differences between typical Depreciation and typical Amortization:

  1. With Amortization, typically the account selected on the actual initial transaction will be the Expense account, not a Deferred Expense account, and the Amortization tool is what will “reroute” the impact to the Deferred Expense account. With Fixed Assets, the account selected on the initial transaction normally will be the Asset account, with no need to have the tool change the GL impact.
  2. The credit side of the monthly Amortization journals typically is the Deferred Expense account. With Depreciation, the credit side of Depreciation entries is normally a Contra-account, “Accumulated Depreciation”.

Thankfully, the Amortization Template tool can easily handle both differences.

  1. There is no need to use the target Expense account on the initial source transaction, it is merely a useful part of the tool. If an Asset account is used on the actual transaction, and the same one is set as the “Deferral Account” on the Template, that is OK. The tool will not “break”, rather it will respect the Asset account selected as the Deferral account on the template.
  2. The field “Contra Account” on the Amortization Template is meant to allow this sort of entry – using this field tells NetSuite that the Credit side of Amortization JEs should be a Contra account.

Using the Tool for Depreciation

In order to set up a Template for Depreciation, the following guidelines should be followed:

  • Use the desired Fixed Asset account in the “Deferred Account” field on the Template. Note that this field will override the account selected on the Trasaction (although, as noted, it can be the same account), and therefore care must be taken to use the correct template with each transaction.
  • Use the appropriate Accumulated Depreciation account in the “Contra Account” field.

Once the template is setup, it can be selected on Asset Purchase transactions. A Depreciation Schedule will be created, and monthly Depreciation Journals will be automated.

Asset Sale/Disposal

When selling/disposing of a fixed asset, we will need to prevent the Amortization Schedule from continuing to post Depreciation Journals. Since without FAM NetSuite does not have an inherent register of the Fixed Assets, there is no way to “tell” NetSuite that a given Asset Sale/ Disposal transaction relates to an earlier asset purchase entry. There are two ways to switch off an Amortization Schedule:

  1. Edit the Amortization Schedule, and on the future lines select the “is recognized” checkbox. This will prevent the JEs from posting.
  2. Edit the underlying original JE or Bill, click on the line with the associated Schedule and click “make copy”. Remove the Amortization Schedule from the new line and click “add”. Delete the original line, and save the transaction. This will delete the original Amortization Schedule, but preserve any Depreciation Journals already booked.

Importing Mid-Life Assets

Companies that wish to use this technique – but who already have the Asset balances on the GL – have a challenge because Amortization Templates cannot generate a Schedule (which in turn generates the Depreciation JEs) unless the template is associated with a Transaction to book the total Asset value.

The way to surmount this challenge is to first reverse the Asset Values out of the GL (Credit the Asset accounts), offsetting the entry to a Clearing Account. Then re-import the assets, offsetting the same Clearing account, but this time associating an Amortization Template to each line.

On the “re-import”, the following fields should be filled in to yield the correct depreciation amounts:

  • Residual Value: Even if the assets have no residual amount, populate this field with the already recognized accumulated depreciation. This will ensure that NetSuite will not attempt to re-depreciate this value. NetSuite will only depreciate the difference between the re-imported value and the “residual value”
  • Depreciation End Date: Enter a “Depreciation End Date” corresponding to the actual end date for each specific asset, rather than allowing the default end date calculated by the Amortization Template. Since the Amortization template will have a standard number of periods reflected the normal useful life of a new asset, we need to override this for mid-life assets.

Conclusion

In supporting several clients with their Fixed Asset concerns in NetSuite, I’ve learned that although FAM is very useful, it can also be somewhat cumbersome. If you’d like help understanding NetSuite offerings like FAM, or if you’d like to explore alternative solutions, we’d be happy to hear from you!

Overcome NetSuite Account Type Restrictions (Part II)

This article is a continuation of last week’s topic. In last week’s post, we listed several transaction types with account type restrictions, here we will examine two use cases and show how the techniques listed last week can be used.

Example #1: Corporate Credit card used to pay Vendor Bill

A client of ours has an external expense reporting system which includes a corporate credit card integration with NetSuite. Each month’s credit card bill syncs with NetSuite to create credit card charges on a (non-reimbursable) expense report. Non-reimbursable expense reports result in a credit to the corporate card liability account and debits to various expense accounts, based on a mapping of Expense Categories and GL Accounts in NetSuite.

Sometimes, however, the corporate card is used to pay a vendor bill that has already been entered in NetSuite. This immediately results in two potential problems. 1) The expense already has been entered via the vendor bill, and the expense report created with the integration will include the charge, potentially resulting in double entry. 2) The vendor bill needs to be “paid” in NetSuite using the credit card account, and the expense report already creates the necessary credit card charge to that account.

Clearing Account

In theory, the solution is simple. All we need to do is use a clearing account. Map an Expense Category to the clearing account and enter a Vendor Bill Payment using the same clearing account. Result:

Credit Card Expense Report (Imported):

  • Dr Clearing Account
  • Cr Credit Card Liability

Vendor Bill Payment (using clearing account as the header account)

  • Dr Accounts Payable
  • Cr Clearing Account

Here is where we encounter an issue with Account Type restrictions. Our clearing account needs to be used on the line level of the Expense Report (a type of Vendor Bill) and also on the header of a Vendor Bill Payment. NetSuite will not allow an Account Type of “Bank” to be used on the Expense Report lines, and yet this is the type of account which the Vendor Bill Payment will need.

Solution

Instead of setting up a clearing account using an account of the type “Bank Account”, we can use an account of the type “Credit Card” as the clearing account, which can be targeted on the Expense Report line as well as on the Vendor Bill Payment header.

See the screenshots below. Notice that the net result of the two transactions is a credit to the “real” credit card account and a debit to Accounts Payable, which is exactly what the net result should be.

Example #2: Create an invoice for Cash Advances

At times, companies may borrow or lend funds and wish to Age the loans using the Accounts Payable/ Accounts Receivable subledger system, and efficiently close out the loans when paid. The most natural and error-free way to do this would be to use native transactions, rather than Journal Entries (see part I of this topic). This is difficult, however, as most item types in NetSuite cannot be configured to Debit or Credit an Account of the type “Bank”, and the impact needed is:

  • DR Accounts Receivable
  • CR Bank

OR

  • DR Bank
  • CR Account Payable

Solutions

There are two solutions for loans receivable, and one for loans payable.

Solution 1: “Payment” Item

There is an item type which can be used on an invoice, called “Payment”. This item type is meant to be used with a negative amount to reduce the net Receivable amount of an invoice, showing payment received against positive revenue lines, but we can use it differently for our purposes. This item type does allow a Bank Account to be selected as the “account”, and thus when used on an invoice will credit the bank account, achieving the desired Debit to A/R, Credit to Cash. This solution will not work for Loans Payable since “Payment” items cannot be used on Vendor Bills.

Solution 2: Bank Deposit Transaction

This solution will work for both scenarios. Enter a Bank Deposit, and on the “Other Deposit” subtab, enter the A/R Account or A/P Account. For a Loan Payable (incoming funds), enter a positive amount and a Vendor name representing the lending entity. For a Loan Receivable (outgoing funds), enter a negative amount, and a Customer name representing the borrowing entity. Note that upon “save”, the amount will show under the “Cash Back” subtab as positive, but the amount will still be tagged with the customer name if you observe the GL Impact.

Conclusion

I hope these examples were helpful. There are lots of ways to maximize NetSuite with some creativity. If you’d like to learn more about overcoming native functionality, I’d be happy to hear from you!

Fixed Asset Management

This article will be helpful to users who would like to understand the underlying structure of NetSuite’s Fixed Asset Management (“FAM”) SuiteApp and its relationship with the General Ledger and with the standard transactions that affect Fixed Asset balances.

FAM Structure

The first key in understanding how FAM works is to remember that it is not a true subledger to the Fixed Asset GL accounts, although at first glance it appears to be one. To illustrate this, we can compare it to Accounts Receivable in NetSuite. Any transaction hitting a customer’s A/R account automatically is reflected in the general A/R account, and therefore no separate function is needed to post the A/R subledger accounts to the parent A/R account. In fact, we can look at each customer’s A/R subledger as merely a “filtered” view of the general A/R account.

In contrast, the FAM module sits as a separate system to the GL. It contains custom records that represent Assets, Depreciation, and other entries, all of which are intended to be supporting records to parallel transactions hitting the general ledger. For example, a Depreciation record in FAM is a custom record which will normally have a linked Journal Entry affecting the Ledger. This may not be obvious since FAM does automate the Journal Entries based on Depreciation records, so users can be confused and can make the wrong assumption that the Depreciation record is itself what affects the Ledger. The same goes for Asset proposal, creation, and disposal. They all normally have linked transactions which affect the ledger, but the records in FAM themselves are just custom records that support and contain detail about the transactions.

Data Layers

The next important point to realize about FAM is that it contains several layers of data that help automate the various transactions and processes. In other words, the link between the GL-hitting transactions and the custom records for Assets, Depreciation and the like is not a direct link. There are hidden records that serve as bridges between the native GL transactions and the FAM records.

Once we understand how these data layers work and interact with the custom records, it will become much easier to troubleshoot discrepancies and problems with FAM reports. In my experience, many of the discrepancy issues that arise between FAM reports and the GL result from problems with these “hidden” records.

Let’s look at a few of the main functions of FAM and examine what happens under the hood during the process.

Creating New Assets

New assets start with a transaction such as a Vendor Bill or Journal Entry which affects a ledger account that has been identified as an Asset account in the FAM Asset Type setup. The transaction automatically triggers a custom record called “FAM Asset Proposal”, which drives the asset creation. The user can accept or reject the asset proposal. When a proposal is accepted, the following records also get automatically created, in sequence:

  1. Asset Record – this the user-facing record which holds all the information about the Asset.
  2. FAM Asset Value Record – this is a linked record to the main asset record and contains only the values needed to calculate depreciation.
  3. Depreciation History Records (DHRs) – these are records representing the various transactions that occur in the Asset’s life cycle such as Acquisition, Depreciation, Write-down, and Disposal. Upon asset creation, an Acquisition record and scheduled Depreciation records are created.

The FAM Asset Proposal record is what the Asset creation depends on. If an asset was created in error and the user wants to re-create the asset with corrections, the Asset Proposal needs to be deleted to start the process from scratch. There is a feature in FAM to delete an asset and all dependant records, but it doesn’t always succeed for various reasons. When there are problems deleting an asset, often the solution will be to first delete the FAM Asset Proposal.

The Asset record and the Depreciation History Records (DHRs) are visible and prominent when using FAM. However, to view the FAM Asset Value records, a saved search based on that record type is needed, as there is no menu navigation to these records within FAM. The Asset Value records are what directly drive the DHRs, and therefore if these failed to get created, no DHRs will get created, and in turn, it will not be possible to run depreciation.

FAM Depreciation

More about DHR’s

As mentioned, the Depreciation History Records consist of several types, representing the various transactions that affect an asset’s net book value. Upon creation of an asset, DHRs are created that represent all future scheduled depreciation, and one more record type is sometimes created:

  1. BG Summary Record – this is a record that will be a bridge between the Depreciation DHRs and the Depreciation Journal Entries each month.

This record holds summary information for each combination of depreciation period, asset type, subsidiary, class, department, and location. The purpose of these records is to make the monthly depreciation process more efficient. As each new asset is created, NetSuite’s FAM scripts look to see if BG Summary records already exist for the combination of dimensions relevant for the new asset. If records exist, the Depreciation DHRs get tagged with the existing BG Summary records, otherwise, new BG Summary records are created.

(Side note: I’m not sure what “BG” stands for, but I believe it’s “BackGround” based on the wording in some SuiteSolutions articles).

Monthly Depreciation

When the monthly depreciation process is triggered in FAM, the depreciation values are aggregated across all assets of the same type, subsidiary, class, department and location, and a Journal Entry is created with lines for each summary amount. Then the BG Summary records are updated with a link to the relevant Journal Entry and the aggregate Depreciation amount. The BG Summary records appear in a sublist on the Journal Entry.

This record structure makes it difficult to craft searches that show a reconciliation of Depreciation amounts posted to the GL and the records in FAM, since there is no direct link between the two.

Asset Revaluation

Let’s focus on one more FAM process. Asset revaluation is similar to Depreciation, in that it is a process initiated in the FAM module, but which triggers a Journal Entry which hits the Ledger. Here again, a DHR record is created called a “write-down”, but unlike depreciation, there is a direct link between the DHR and the Journal entry, populating a field on the DHR “Posting References”.

The Revaluation also updates the Asset Record, and its underlying “FAM Asset Value” record (see the section above “Creating New Assets”). This step is needed so that future depreciation reflects the change in Asset value.

Conclusion

FAM is an excellent module, but it can be tricky to understand because of the fact that it is really a separate system of records that indirectly links to the core NetSuite financial system. If you can use some help using or troubleshooting FAM, I’d be happy to hear from you!

Use NetSuite to ease your Accruals and Prepayments

NetSuite has some great tools to help finance teams with month-end close. One of my favorites is the Amortization tool which automates expense recognition over time when a company has paid for (or been billed for) an expense in advance, but the expense needs to be recognized slowly over time.

However, like most out-of-box tools, the Amortization tool solves for a specific business pattern. The tool works well when a vendor sends a bill for the expense entirely in advance, the bill is paid for soon afterwards, and the prepaid expense needs to be booked subsequent to the billing and payment. However, when a company has billing and expense patterns that do not fit this model, the amortization tool is not really effective on its own.

Sample Accrual / Amortization

Consider the following common scenario: A company purchases a software license which needs to be renewed each year. The company needs to pay the contract amount in 3 installments – March, June, and December; and the vendor sends a bill for each installment when it is due. Yet, the expense must be recognized equally over the course of the year, starting January.

If we analyze the situation described, we can see that the expense recognition pattern does not match the billing and payment pattern (12 equal expense parts vs. 3 billing and payment parts) and therefore does not really fit the Amortization model, as the Expense recognition begins prior to the bill and payment, whereas NetSuite’s amortization tool needs to start subsequent to an initial transaction.

Another challenge that this use case presents is that the billing is taking place before the expense is completely accrued. In most accrual situations, the expense is eventually entirely accrued prior to the bill entry, which allows for simple monthly accruals using a reversing technique. Since in this example the bills arrive mid-stream, the reversing technique will result in incorrect expense amounts in the periods billed and in the final accrual period.

In situations like these, it is easy to get confused about the different parts to the business flow. Typically, companies will resort to spreadsheets to stay on top of accrual and prepayment balances, book manual journal entries, and then have to worry about spreadsheet versions and be sure that it is an accurate representation of the transactions booked in NetSuite.

Fortunately, NetSuite is flexible enough to tackle this sort of problem and get us out of the spreadsheet quagmire. The trick to solving this problem is to parse out the components into distinct parts. What we really have here are three separate flows going on concurrently:

  1. Expense Accrual Pattern
  2. Billing Pattern
  3. Payment Pattern

Next, we think about GL Accounts. Which GL accounts does each entry type need to hit? E.g:

  1. Expense Accrual: DR: Software Expense, CR Accrued Expenses
  2. Billing: CR Accounts Payable, DR Prepaid Expenses
  3. Payment: DR Accounts Payable, CR Cash

Now that we have an idea of how each transaction should look, we can think of a way to link these transactions together and automate as much as possible.

Step 1. Enter a PO for the expense

Using Purchase Orders for Expenses is good practice and allows for transparency in the Procure to Pay cycle, it also lends itself to good controls and approvals. In our example it can serve as a repository of all the different transaction types needed for the Accruals and Bills.

Step 2. Set up a Memorized Journal Entry and Link it to the PO

Memorized JE’s are fairly easy to set up. The way to link them to the related PO is to create a custom sublist on the PO record, and a custom field on the JE form to serve as a “key” to associate the JE’s to the correct PO. An underlying Saved Search will also be needed. See SuiteSolution articles 10120 and 48803 for help on this.

For even stronger capacity, instead of Journal Entries, you might consider using a Custom Transaction Type specifically for Accrual Journals. Custom Transaction Types are a great tool for a variety of purposes and I hope to expand on this more in a separate article.

The below images show how the transactions and sublist can appear. Note the “Scheduled” and “Posted” statuses, which are possible using the “Custom Transaction Type” approach.

Accrual Journal
Accrual Journal Sublist on a Purchase Order

Step 3. Bill against the PO, but use a GL Reclasser

Here’s the fun part of the process. We want to enter bills against the PO and allow users to understand what is being billed, yet the GL impact needs to hit a Prepayment Account in our model (see above). A great way to accomplish this is to use NetSuite’s GL Reclasser plugin which allows us to reroute the Bills to our Prepayment Account instead of to the normal Expense account on the PO.

Out of the box, this feature takes some scripting, but Prolecto has a great bundle that makes this tool easy to use, even for non-developers. See Marty Zigman’s article here. The key to making this efficient is to associate the GL reclass “profile” to the PO. Then, any bill created off of the PO will automatically get the right GL impact, while preserving the presentation we want – showing which expense the Bill ultimately relates to.

There’s even a simpler way, too. An item can be set up that can be routed to a Prepayment account, yet the item name can be descriptive enough to explain the nature of the charge. This method would work if Expenses of the type being accrued are only and always used in the same pattern of prepayment/accrual.

Step 4: Pay the bills

From here on this is native – the Bills will be paid as part of the normal bill payment process.

Step 5: Reconcile the Prepayment and Accrual Accounts

When we take a look at the GL impacts, we see that the transactions would result in a balance in the Accrued Expenses Account and the Prepaid Expenses Account. (Recall that the Accruals are hitting the Expense GL and our Bill hits Prepayments.) We want to offset the Accrued Expenses Account against the Prepaid Account. This can be done in a number of ways. Some companies prefer to keep things simple and use the same account for both Prepayments and Accruals, using the polarity of the account balance (Credit or Debit) to indicate a net Prepayment or net Accrual. However, since in our technique we are tagging the Accruals with a PO number, we can leverage this field in a saved search to summarize balances in the Prepayments and Accruals accounts and assess when to enter a Journal Entry to offset the balances and adjust a balance if necessary.

Conclusion

Once we clarify an approach such as the above suggestions, we can extend the solution with scripting as well – if appropriate. For example, I would consider automating the memorized Accrual Journal based on information on the PO, perhaps using some custom fields or a suitelet to hold the info needed to generate the Accrual Schedule.

Of course, the above is just one example of a use case – there can be many variations. For example, payments may occur in advance of billing, in which case I might consider using one of several Prepayment solutions in tandem with the above proposal (see related article here).

If you found this article interesting and would like to learn more about creative ways that NetSuite can be leveraged to improve your accounting practices, feel free to reach out to me at Prolecto. I’ll be happy to hear from you.