This article is relevant if you would like to better understand the patterns and possibilities for spreading (allocating) NetSuite general ledger account values.
Background
One of our clients is in the Bio-Technology industry. Their work relates to research about new cellular capacities and healing phenomenon. Much of their work represents speculative initiatives for which, if things go well, become breakthroughs in medical and other scientific realms.
While their organization is multi-subsidiary, especially because they acquire other companies that have organized research talent and developed special gene sequences, they heavily focus on project work. All kinds of research projects are in play at any given time.
Our client’s primary input cost is salaries as well as various capital equipment used to perform research. The client came to us asking for help when they were migrating to a new outside payroll firm. In their minds, they were hoping to streamline the work to enter payroll information into NetSuite. Yet, their goal was more ambitious.
Fundamental Cost Allocation Challenges
While listening to our client’s objectives, we began to appreciate that the payroll costs were only the beginning of information gathering exercise. They ultimately wanted help to properly categorize costs. Indeed, the power of the NetSuite application is to create informational dimensions that allow us to produce both financial and operational insights. With the advent of custom dimensions, we could finally get beyond the three standard out-of-the-box segments: Department, Class, and Location.
Yet, the ability to define the segment you want to organize information is just the start. Assuming we can think about what we care about to powerfully distinguish information components (I call this modeling), the next challenge is the proper coding of transactions into the target segments. We often find that during the time we craft our transaction entry, we have insufficient information to properly categorize into the target segments. This can be solved if we later gather data that can help us further refine information.
Direct and Indirect Cost Identification
To think about this challenge which may be in your organization, the common example relates to direct versus indirect costs. For my client, projects are effectively the organizing story (the direct element) that drives the reason for their existence. Direct costs relate to the production of the primary business purpose. In our client’s example, a research scientist working on a specific project allows us to easily see that his or her salary costs all belong to the project. Yet, in contrast, the accounting department, who is there to serve the overall success of the entire organization, only works indirectly on projects. We should respect the value of indirect costs because they support the larger organization narrative and purpose.
However, if we want to understand what our all-in project costs are, ideally we should take a portion of the indirect costs and apply them to the project. But how much? In an attempt to answer this question, most accountants can easily suggest a weighted average mechanism to allocate indirect costs to projects — if we can clearly identify the weights.
Weighting Based on Project Time Spent
Our client has two mechanisms to assess how they spend their time:
- Formal timesheet: every hour work is identified with a project.
- Percentage of time: on a periodic basis (e.g, monthly), each person divides their time, in percentage terms, between various projects worked.
The ability to see project time is easier for the people operating in the direct organization. They talk about the projects they are working on daily. The people working on the indirect side of the business are not necessarily organizing around projects but they play an essential support role to those that do. We could weight indirect costs by understanding the differences in project efforts based on direct efforts.
With this timesheet model, we have enough information to come up with a weighting average algorithm:
- Salary: record salary by person and by the department. Here, the departments are used to help distinguish the larger direct versus indirect functions in the organization. This pattern is common, especially when using a third-party payroll service.
- Timesheet Hours: record the time spent by each individual. If the individual is not recording time by hours, then make an assessment of the percentage of time allocated to each project. While not perfect, it should be reasonable.
Leverage Statistical Accounts to Measure Weights
Our client is not using NetSuite built-in Timesheet features. Because we knew we were going to target using timesheet hours for the spreading method, we were pleased that NetSuite statistical accounts can be used for allocation inspection. We added a new custom statistical general ledger account called Timesheet Hours. We helped the client gather up individuals’ project hours based on their internal timesheet system and used this to book a journal entry against the department and project dimension.
For individuals that did not use a formal timesheet but reported by percentage of time, we created a standard 175 hour work month concept. We then took the project percentages and multiplied it by our 175-hour work standard to create a common basis. With this, we were able to book time against the project and the department.
Creating Allocation Algorithms
Now with the inputs at hand, we defined our allocation algorithms using NetSuite tools. The key was to take department salaries and then further break it into project amounts. Thus, with our statistical timesheet hours as our weights for each project, we could have NetSuite allocate salaries from the “null” (empty) project to the target project. Department salaries would not need to be changed.
Because the nature of the solution demanded that we create more than one allocation schedule, we were able to use NetSuite’s allocation batch tool to chain together multiple allocations. As of this writing, NetSuite’s batch tool can only support 10 schedules per batch. We needed more than 10 allocation schedules so we had to create multiple batches.
Saving Time to Get Desired Allocation Outcomes
Our client took our work and compared it to their monthly manual spreadsheet and journal entry process. Indeed, we were able to make the information tie out producing trust that the tools work as planned. Not only did we solve their stated payroll import challenge, we had solved their larger allocation challenge and saved them many hours of manual spreadsheet and data entry work.
The solution represents an illustration for our NetSuite accounting understanding and concept modeling. The use of NetSuite statistical accounts opens a world of possibility that is beyond basic reporting. It can be used to help spread and allocate amounts according to dynamic weights. The key to getting more value out of NetSuite is to model your problem well in connection with what the tool can do. If you would like assistance in your NetSuite reclass efforts, or for streamlining your revenue or costs allocations, let’s have a conversation.
See Related Articles
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
