Excel, NetSuite, Reporting
March 16, 2020
2 min Read
The SUMIFS formula is a powerful formula that every biotech accountant should be able to use. Today, I am going to walk through how to use this formula with a NetSuite saved search to automate an accrual monthly reconciliation. This setup has worked well for preparing preclinical, clinical and CMC accruals.
We are leveraging SUMIFS functionality to evaluate multiple criteria. The first parameter of the formula is the column we want to be summed (Invoices!$F:$F). In our example, this is the invoice amount. For each parameter after, we include (1) the column to be evaluated and (2) the evaluation criteria.
In my example spreadsheet, I have a summary reconciliation in the ‘Summary’ tab that is utilizing the SUMIFS formula. In the ‘Invoices’ tab, I have a sample data output from NetSuite of all invoices that I want to be assessed in my reconciliation. In my reconciliation, I also assign each invoice to the given project in the ‘Summary’ tab and have added a third criterion statement to ensure only invoices associated with that project are included in the given row.
To assess whether the invoice activity should be included during the period, we will evaluate the date column twice. First, to determine if the transaction date is greater or equal to (<=) the period start. Second, to determine if the transaction date is less than or equal to (>=) the period-end. Transactions should be included if the transaction date is after the period-start and before the period-end.
In order to use the comparator functionality within a formula, include the comparator in parentheses and include the ‘&’ symbol after the to set the criterion value. See example screenshot above for the formula and the table below for all the comparator operators available in Excel.
This functionality is really powerful when you are able to build a NetSuite saved search to export all the invoice activity for a given reconciliation. Using this formula, users reduce the risk of making a mistake when manually adding invoice activity to their reconciliation. In addition, all invoices are included for reference for auditors. The NetSuite saved search saves time by avoiding the need to pull detail on a vendor-by-vendor basis.
If your team is looking for assistance leveraging NetSuite or Excel to improve your close process, send a note to info@intheblksoftware.com. Our team has helped implement and maximize NetSuite functionality at several biotech organizations and can help your team improve efficiency and effectiveness.
