Dual Personality: Inventory Transfer Entry

What do you think of when I say "Inventory Transfer Entry"? If you are like most folks, you think of the window that allows you to transfer an item from site to site. But, actually, that window is Item Transfer Entry (Transactions>>Inventory). Inventory Transfer Entry is actually a project accounting window (Transactions>>Project) with a variety of uses.

If you use Project Accounting with Inventory, you are most likely familiar with this window as the next step after receiving an item associated with a project. The purchasing and inventory process for project accounting looks something like this:

• Record purchase order for inventory item and associate it with a project and cost category, which increases the committed cost on the project budget.
• Receive the purchase order, which decreases committed cost. Item is received in to inventory (not on to the project).
• Transfer item from inventory to project using Inventory Transfer Entry window.

This process involves a bit of a learning curve for new users, since the receiving process receives the item in to inventory NOT on to the project. It is the inventory transfer transaction that moves the item from inventory to the project. For non-inventory items, the receiving process DOES post the item directly to the project.

But what if you do not use inventory? Why would you care about the Inventory Transfer Entry window? You can use it to "transfer" non-inventory items to a project. Look at this example:

• Payables transaction entry used to record an expense for office supplies.
• After posting the payable, it is discovered that the transaction should have been associated with a project.
• Use Inventory Transfer Entry to "transfer" the non-inventory item to the project.

This can be very useful when "fixing" issues with costs that were not posted to projects properly.

Inventory Transfer Entry has two different transaction types, Standard and Return. Standard is used for the scenarios above, to transfer an item TO a project. A return can be used to transfer an item FROM a project (thereby reducing the project costs). This can be very useful when an item (inventory or non-inventory) is posted to the wrong project. Consider this:

• Item 123 is a non-inventory item that has been received to Project ABC.
• It is then discovered that it should have been received to Project XYZ.
• Record an Inventory Transfer Return to remove the item from Project ABC.
• Then record a Inventory Transfer Standard to add the item to Project XYZ.
• Easy peasy fix!

Well, hope you enjoyed some ramblings on the Inventory Transfer Entry window in Project Accounting :) I just wanted to drive home the point that the window can serve a variety of purposes, not just the typical inventory item transfer scenario.

Project Accounting Timesheet Recalc Version 2 Released

Based on a customer request, I have added new functionality to my Project Accounting Timesheet Recalc Utility and have released Version 2.0.

The PA Timesheet Recalc utility is useful for Dynamics GP customers who use Project Accounting Timesheets for salaried employees.

The salaried employees have fixed payroll costs, but if they work more or less than the standard number of hours in a given pay period, the project timesheet costs posted to the general ledger will not match the actual payroll expenses for the employee.

Some companies ignore this discrepancy, some companies post a summary adjustment to correct the difference, and others try and manually adjust the costs on each Project Accounting timesheet to try and get the total to match the employee payroll amount. Each of these has its own challenges that make them less than ideal.

The PA Timesheet Recalc utility adjusts the unit costs on PA timesheets so that the amounts match the employee payroll exactly. It averages the unit cost based on the hours entered on the timesheet, handles the rounding issues caused by the average cost calculation, and also recalculates the timesheet distributions.

The initial version of PA Timesheet Recalc was designed for companies whose employees submitted a single timesheet for the entire pay period. In this situation, the total timesheet cost would always equal the employee pay, plus any overhead.

However, some companies have employees submit multiple timesheets during the pay period. For companies that wish to have the timesheets routed and approved by different managers responsible for different projects, employees may have to submit a separate timesheet each day for each project.

To accomodate this requirement, PA Timesheet Recalc Version 2 can now recalculate multiple timesheets for a given employee for any date range. For example, if an employee submits 20 timesheets for the period of January 16 - January 30, the utility summarizes all timesheets to determine the appropriate average cost and recalculates all of the timesheets. The user is able to specify the date range, and select multiple employees to include in the recalculation process.



PA Timesheet Recalc is definitely a niche product, but for those companies that are using Project Accounting Timesheets and trying to get their project costs to match their payroll expenses, it can be a significant time saver.

Since it is somewhat difficult to explain and understand, I've posted an updated video demo of the product.

Dance With The One Who Brung 'Ya - Correcting Transactions in Dynamics GP

So often it seems like many of the questions on the Dynamics GP forums deal with how to correct transactions. Or, even more often, how correcting a transaction has led to more issues. As a trainer, I teach my students to "Dance with the one who brung 'ya" which is my way of emphasizing the importance of correcting transactions in the module where they originated from. And when I am lucky enough to find myself on phone support duty for the day, I always end up with a few "how do I fix this or that" cases. So I always ask my standard questions..

• What steps have been taken so far? Often times, I find that attempts to correct the issue (sometimes even multiple posted corrections) have contributed to the current issue.
• What is the current state of the issue? What remains to be corrected (e.g., the GL is incorrect but the RM aging is correct)? Getting to this bottom line can often be helpful in solidifying the real issue.

But we want to avoid these issues in the first place, right? So getting back to the dancing. Here are the common correction points that I emphasize to students and users, to point out the importance of correcting transactions where they originated.

Sales Order Processing

• To correct a posted invoice (Transactions>>Sales>>Sales Transaction Entry).
• Enter and post a return (Transactions>>Sales>>Sales Transaction Entry).
• Updates inventory (if applicable), sales history by item, receivables management, and general ledger.
• Do not void using Receivables Management (Transactions>>Sales>>Posted Transactions), as this will only update the general ledger and receivables management.

Purchase Order Processing

• To correct a posted receipt or invoice (Transactions>>Purchasing>>Receivings Trx Entry, Enter/Match Invoices).
• Enter and post a return (Transactions>>Purchasing>>Returns Trx Entry).
• Updates inventory (if applicable), purchasing history by item, payables management, and general ledger.
• Do not void using Payables Management (Transactions>>Purchasing>>Void Open), as this will only update the general ledger and payables management.

Project Accounting (Employee Expenses, Miscellaneous Logs, Timesheets, and Equipment Logs)

• To correct a non-purchasing transaction (purchasing transactions are corrected in POP per the example above), return to the original entry window (Transactions>>Project>>Timesheet Entry, Employee Expense Entry, etc).
• Enter a Referenced type transaction and use negative quantities to decrease the original posting. You can use the PA Trx Adjustment tool to assist with this process (Transactions>>Project>>PA Trx Adjustment). Using either method will result in an update to project accounting, payables management (if applicable), payroll (if applicable), and the general ledger.
• Do not correct the transactions in payroll, or by voiding the transaction in payables, as these methods will not update the project accounting module.

Well, that's it for tonight. I promise that my next post will get in to the finer points of correcting project accounting billings and inventory transactions, which have more variations than the examples I have provided above.

But, in the meantime, please share any tips and tricks you have gathered over the years to assist users in understanding how to dance with the one who brung 'em :)

Simple solutions: Keeping the ego in check

I tend to think that I am the sort of curious person who comes up with creative solutions to complicated problems. And in that way, I am somewhat proud of my troubleshooting abilities. Give me some time, peace, and quiet and I can figure out an issue through my powerful logic and reasoning capabilities--if you didn't catch the sarcasm in that statement, it was dripping with it :)

Today, I came across one of those issues that had me stumped. I assumed it had to be a problem report, after all I couldn't figure it out (again, sarcasm here). So here was the issue...

1. Client has used project accounting since 1/1/2009, there has been no change to their fiscal period setup
2. I needed to export project periodic balances for reconciliation
3. I ran the project utilities (Utilities>>Project>>Reconcile Periodic, Recreate Periodic)
4. I checked the results in the PA01304 and there are records, but no balances at all
5. I checked the actual project data in GP, and there are detail transactions and project balances but no periodic balances
6. I searched the knowledge base on PartnerSource
7. No luck
8. Tried PA check links
9. Tried more PA reconciles
10. Tried in other companies, same results
11. Tried on my system, different results
12. Hmmm

So, I started a case, knowing that there have been past issues with the reconciles and thinking that maybe there was a known issue I was not finding in my search. Maybe, it's a service pack issue but I really wanted to avoid having to apply service packs just to get the data we needed. And, maybe, if I am honest with myself, I was being a bit lazy about it all.

So Brady at Microsoft has me check the period setup table for project accounting entries, select * from SY40100 where SERIES = 7. And, lo and behold, it returns nothing for project for the year 2009. So, he has me go in to Fiscal Period Setup (Tools>>Setup>>Company>>Fiscal Periods) click Calculate and Save to create the records.

I run the reconciles again, and I have data! I had faith the periodic data would return, because the detail was still there but for it to be such a simple fix was both good and bad. Good because it was quick to resolve, bad because I didn't get there myself :)

So I thought I would share this little nugget with you, just in case you run across this specific issue. And even if you don't, maybe it will remind you of the fact that most issues have simple causes and resolutions...it's just a matter of finding them.

Have a great weekend!

Non traditional uses of existing products

A longtime client of ours wanted an online expense report system. Of course, I trotted out the normal list of third party products as well as eExpense from Concur. They had already been through a demo of eExpense with their payroll provider and had experienced, how shall I say it, sticker shock. The conversation went something like this...

Me: eExpense is a really great product, lots of functionality.
Client: Yeah, but we really just need a basic product with basic functionality. And the price tag just is not realistic for our need, we can't justify it.
Me: Let me see what else I can find that might be a compromise.

I did some searching and found most of the other third parties fall in about the same general price range. Some were a bit cheaper, but still had monthly subscription costs. So what to do? Then, I started to think creatively. The client already owned Project Accounting, and used it in one of their companies. They did not need to capture expenses by project, but...

Why not implement PS Time and Expense with some basic project setup, and use it to meet their needs? Now, this client was not on Business Ready Licensing, but if they were the savings would be even better! Bottom line is that in a little over two days of consulting time, and with less than $2000 in licensing costs for PS time and expense with user licenses, they have an expense reporting system.

We did a quick configuration of project accounting, and documented the setup of projects and cost categories (which will be the bulk of the additional setup they may expand in the future). The trick was to keep it simple, and not worry about full blown training on project accounting which was not needed. And the interesting part is that the simplicity is what the client likes (which is sometimes a complaint among full project accounting users).

Just thought I would share this as a reminder (one that I need from time to time) that the solution to a complex issue may lay right under your nose, and that sometimes simple is best. I think we as consultants inadvertently lean towards the "coolest" or the most functional solution. But we must remember that the ultimate goal is to meet the client's current needs and anticipate their future needs but not overstate or oversell them.

Did you know? Project Macros

How many of you have been caught off guard after installing project accounting on existing Dynamics GP installation, when it begins asking users if they want to add project info for vendors every time they try to enter purchase orders?

Many of you may already realize that GP support has a set of macros, one for Customers and one for Vendors, that will run through and save the default settings for project. A little thing that saves users from having to either populate the records manually, or respond to the message every time.

Feel free to email me directly for more info, christinap@theknastergroup.com.

Upgrade Issue with Project Accounting and Purchase Order

This week I had an interesting issue pop up after an upgrade from GP9 to GP10. We have done many upgrades at this point, including ones involving project accounting, so this issue was a surprise and makes me wonder what caused it.

There were no errors during the upgrade, but post upgrade a user noticed that there were no longer any account numbers on purchase order line items. When she went to receive the purchase orders, the receipts did not default a Cost of Goods Sold account since there was no account on the line item on the purchase order. All of their purchase orders are project purchase orders, with projects and cost categories on the line items.

I looked in the database, and sure enough, in the POP10110 there were no values for the INVINDX field which stores the account index for PO lines. None. Zero. Zilch. Nada. For 7500 lines. To make sure I was not going crazy, I manually entered a purchase order and sure enough, the INVINDX populated fine. So then, to make sure it was an upgrade issue, I restored a backup from GP9 (when I knew POs and receipts were functioning, and accounts were defaulting). And in GP9, the INVINDX was blank as well.

So...I started to wonder if perhaps this was somehow related to the feature pack and changes made to the line item functionality with regards to accounts in purchase order. I still do not know for sure, but it seems like a likely reason (feel free to share if you know, or can disqualify this theory).

But I still needed to fix the issue, so I looked in the PA10601, the PA Purchase Order Line table. And the PACogs_Idx field was still populated. I checked my test purchase order that I had entered, to make sure that it populated both POP10110.INVINDX and PA10601.PACogs_Idx with the same values to make sure I was not making a bad assumption. Then it was easy enough to do a join on the PO number and ORD fields in order to update the POP10110 with the correct account index.

All is well now, and the user is able to enter and post (with accounts defaulting). Anyone else come across this?

Project Accounting Cost Allocation By Unit

Okay, so this is what happens when I am stranded in a hotel room in Denver during a blizzard, I gnaw on something until I manage to understand what it is doing.

Since the feature pack came out last summer, I have been aware of the Project Allocation feature but have not had cause to use it. I had played around with it some, but really did not understand the capabilities relative to the Unit allocation method at the Cost Category basis. So, tonight I did some testing and I think I have it figured out (at least partially).

From what I can tell, both fields deal with timesheet cost categories. So, let's assume that I accumulate vacation time in an overhead project and I want to allocate the hours (and cost) to other projects based on their billable time (assuming that the more billable time there is, the more vacation there would be as well).

Considering this all came from testing (the documentation I could find was not particularly clear), please feel free to post comments, clarifications, and corrections :)

So, let's assume that I have the following setup:

• VAC is my vacation cost category for timesheets
• BILL is my billable cost category for timesheets
• MISC is my cost category for miscellaneous logs
• ALLOC is my miscellaneous ID
• OHPROJECT is my overhead project
• PROJECTA is one billable project
• PROJECTB is another billable project

I have posted timesheet transactions that result in the following actuals on the projects:

• OHPROJECT: 100 hours VAC for a total of $10000 in cost
• PROJECTA: 20 hrs BILL for a total of $2000 in cost
• PROJECTB: 30 hrs BILL for a total of $3000 in cost

I can set up my project allocations as follows:

From:

• Project: OHPROJECT
• Cost Category: VAC
• Misc ID: ALLOC
• Misc Log Cost Category: MISC

To:

• Project: PROJECTA
• Misc ID: ALLOC
• Misc Log Cost Category: MISC
• Cost Category Basis: BILL

Method: Unit

Then enter a second line with the following allocation...

From:
Project: OHPROJECT
Cost Category: VAC
Misc ID: ALLOC
Misc Log Cost Category: MISC

To:
Project: PROJECTB
Misc ID: ALLOC
Misc Log Cost Category: MISC
Cost Category Basis: BILL

Method: Unit

This allocation setup will result in the following allocations being created by taking the cost category basis and determining the portion of the whole. For example, the total quantity for the cost category basis for PROJECTA and PROJECTB is 50 hrs. So PROJECTA will get 40% of the allocation (20/50) and PROJECTB will get 60% of the allocation (30/50). As Mike Lupro pointed out, this will allocate the full amount in VAC on OHPROJECT to PROJECTA and PROJECT B.:

• Allocated to PROJECTA: $4000
• Allocated to PROJECTB: $6000

Well, there you go! Pretty nifty I think, and I could definitely see applying this in the future. Let me know what you think.

Project Accounting Timesheet Costs

Recently Steve and I have been working on a customization project to address one of my ongoing "sticking points" when using project accounting timesheets for salaried employees. It is one of those issues that bugs me enough that I routinely have to turn to others...other project accounting consultants, trainers, MS support folks, etc to give myself a reality check :)

So what is the issue? Well, let's work through the following example...

• Employee 1 is a salaried employee making $1000 per weekly pay period, making their hourly rate $25/hr based on 2080 hrs/year
• Let's assume that the pay period is the same as the timesheet report period (weekly)
• Employee records 45 hrs on their timesheet for the week

When the timesheet posts, it will post 45 hrs of cost to the project at $25/hr -- $1125. This $1125 also posts in the general ledger:

• $1125 Debit to Work in Progress or Cost of Goods Sold/Expense as appropriate
• $1125 Credit to Contra Account for Costs

The intention of this is that it records the project-related expense, and is then offset with the salary expense when it is recorded via payroll or a journal entry. Of course, do we all see the issue with the example above? $1125 of cost is recorded, although we are only going to pay the fabulous Employee A $1000/pay period. So we are effectively overstating the cost on the project and in the general ledger.

The customization we are working on resets the unit cost on the timesheet. So, in the example above it would do the following:

• $1000 pay/45 total hours = $22.22
• Reset unit cost per line on timesheet to $22.22
• Reset distributions so that $1000 is debited to WIP (or COGS/Exp) and credited to Contra account

It should work, and will save clients a lot of frustration. I just laugh at myself that I didn't think to rope Steve in to it sooner. Now let's not even talk about what would happen if the pay period and timesheet reporting period were not the same!

Project Accounting Integration Frustration: Customer Project Info

By Steve Endow

(This is a two-part post. In this first article, I'll describe the issue I ran into, and in the second article, I'll propose a more complete solution for dealing with the issue that can be applied to other situations where unique document/transaction/record numbering is required.)


I recently developed a project accounting integration using eConnect. The integration reads 4 columns from an Excel file, and then creates all of the records in GP to fully setup the project, from the customer, to the contract, to the PA accounts, all the way through to the project budget and status flags. There were a few interesting learning experiences along the way, but the most challenging was one that I least expected: the PA Customer Options window. When you create a new customer and have PA installed, there is a Project button in the lower right corner of the customer window. This record is normally setup automatically when a customer is created in GP when PA is installed. But it is not setup automatically by eConnect.

I know, you're thinking "How hard could that be? There's only ONE required field!". That's exactly what I thought too!

The first bump occurred when I found that eConnect does not have a transaction to create the PA Customer Options record. Okay, not a big deal, I just traced the data back to the PA00501 table. It's a simple table, and I was able to just use the zDP_PA00501SI stored procedure to insert my record. I only had to pass in two pieces of data: customer number and customer alias. Simple!

After a few records imported, I had fleeting touch of self-pride, until I got this error:

Violation of PRIMARY KEY constraint 'PKPA00501'. Cannot insert duplicate key in object 'dbo.PA00501'.

After checking the PKPA00501 index, I saw that it was complaining that I had a duplicate customer alias. And that's where the arcane fun starts.

The PA Customer Alias field is a very annoying field that is limited to 5 characters. Yup, just 5. Normally, when you open the PA Customer Options window, the customer alias defaults for you, so you typically don't notice it, don't pay any attention to the default value, and care how it is generated. If your customer ID is ACME001, your alias will default to ACME0. If your customer ID is 123456, the default alias is 12345. Simple, right? Not so fast, grasshopper!

As I'm importing 50,000 customers with blocks of sequential, 6 digit customer numbers, guess what. I have customer ID 123456, and 123457, and 123458. So...clearly I can't just use the first 5 characters of the customer ID, as all would have an alias of 12345.

So I did some tests in GP to see how it generates the alias. I found that if alias 12345 is taken, it will use 12341. If that is taken, it just increments the last digit, so 12342, 12343, etc. This is fine and dandy if you have customer IDs that are fairly distinct and well distributed, like ACMEROCKETS, or ABCMETALS. But if you have sequential, numeric customer numbers that are 6 digits or longer, you start to have some challenges.

Here's an example of customer IDs, and the default alias generated by GP. Think of this as a big train wreck occurring in very slow motion:

123450 = 12345
123451 = 12341
123452 = 12342
123453 = 12343
123454 = 12344
123455 = 12346 (12345 is already used)
123456 = 12347
123457 = 12348
123458 = 12349
123459 = 12340 (GP doesn't actually use zero, but for arguments sake, I included it)

Looks fine, right? 10 customers, 10 aliases. Simple and easy, right? Well, no, the train is definitely wrecking, it's just taking its time.

What happens for customers 123401 - 123410? In that case, the default numbering scheme then goes from using the first 4 characters of the customer ID, to the first 3. So customer 123401 will get an alias of 12310. But then what will customer 123101 use? See the problem?

This all leads to a preposterous situation where a customer 123700 might receive a default alias of 11000. It's just stealing numbers from another series, attempting to have the alias resemble the customer ID, and hoping they won't all need to be used.

So at first, before I realized how many customers I was dealing with, I thought I would just write a routine that would loop through alias numbers to find an available value--I basically mimicked the GP default alias generator. I used the first 4 characters of the customer ID, and if those 10 aliases weren't available, I moved on to the first 3 characters of the customer ID.

But then that resulted in duplicate aliases, as all of those 100 alias values were taken. So then I realized that I would then have to look through the aliases starting with the first 2 characters of the customer ID. That's 1,000 values. And even then I ran into situations where that wasn't enough.

The next step would be to use the customer's first 2 digits, and check 10,000 possible alias values. The train is definitely off the tracks at this point.

It became clear that there HAD to be a better way.

The quick and dirty approach first came to mind is to throw a letter into the mix. If my options included 12340 to 12349 and also 1234A to 1234Z, that gives me 26 more options--basically a "base 36" numbering scheme. Naturally that would work, right? Maybe as a temporary solution, but as thousands of more customers were created, I could still run into an issue. So I could do something like 123AA, where the last two characters could be alphanumeric. But if you try and write such a routine, it looks like a looping circus.

And there is another issue. This alias generation routine was in my .NET app, and in order to validate the alias, I have to make a call to SQL Server to check if the alias is in use already. So with every number I try, it's a query against SQL. Just plain bad design. If I were checking just 10 values, I'd let it slide, but thousands of values is out of the question.

So what's a better solution? I want to:

1) Generate a 5 character alias that "resembles" my customer ID
2) Make sure the alias does not already exist in PA00501
3) Generate the available alias values sequentially so that I don't have unecessary gaps
4) Eliminate looping in my code
5) Make one query against the database

This is actually a fairly common issue with business apps and databases, but there are many different nuances and business requirements around numbering, so there isn't necessarily "a solution" for all situations.

After thinking about the issue for a few minutes, I eventually remembered a story that a friend told me about a SQL Server guru that could magically generate a range of sequential numbers with a single SQL statement. That story led me to my solution, which I'll share in part two.


Link to Part 2:  https://dynamicsgpland.blogspot.com/2009/02/project-accounting-integration_20.html

Project Accounting Cost Category and Fee ID issue

Well, I stumbled across a known issue last week that I thought I would share. A client called last week with a strange issue. Generally, this client solves most of their own issues, so when they call, I know it is going to be an interesting and complex issue.

So here is the scoop..in Project Accounting, actual billings equaled payments/receipts received. Great. And the detail supported this summary. However, they ran reconcile on Cash Apply (Tools>>Routines>>Project>>PA Reconcile) and the payments/receipts were recalculated to be less than the actual billings. The difference was the same as the FREIGHT fee billed on the project. This was most apparent on a simple project that had one invoice, one payment. The client had noticed that it was happening on all projects with the fee called FREIGHT.

We started poking around in the database, looking at the underlying setup of the FREIGHT fee and comparing it to other fees, trying to determine what might be different. We could not see anything different. We ran a dexsql log during the PA reconcile process, to see what stored procedures/tables were being referenced. We looked through all the tables again, still no luck. So then we started to wonder what was unique about the fee ID of FREIGHT. At that point, the client said something like "you know what, this fee is named the same as a cost category". Bingo!

So we made some quick changes in the tables (test environment, of course) so that that fee ID was FREIGHT2 instead of FREIGHT. We re-ran the PA reconcile, and the payments/receipts received were now correct! What an odd little issue.

It is actually a recently documented quality report (#49611). I thought I would share the wisdom that, for now, don't name your fees and cost categories the same. For those that already have the issue, there are some scripts available from MBS Professional Services that allow you to change Cost Category IDs (and therefore correct the problem for projects that already have activity).

Co-author credit on this one has to go to Dave (the client), for the teamwork to determine the root cause :)

Project Accounting Asset Tracking

Due to Business Ready Licensing (where clients receive a "suite" of modules when purchasing Microsoft Dynamics GP), I have found more and more clients using project accounting in non-traditional ways simply because they already own the software. One of the most common non-traditional approaches is to use the module to track capital expenditures. Project accounting works great for the capturing of the myriad of costs associated with a capital project like labor, materials, consulting, and even indirect expenses like overhead and equipment usage.

One of the questions that always comes up in discovery is how to handle the "recognition" of the asset, when a capital project has reached a specific stage of completion and the construction in progress can be capitalized. I have found that by using the WIP (Work in progress) functionality of project accounting with a time and materials project, we can easily emulate the transfer of expense from a CIP (Construction in progress) account to an asset account.

Let's walk through an example of typical WIP using a cost of $100 that is billed for $150:

$100 purchase
$100 Debit Work In Progress
$100 Credit Contra Account for Cost (Accounts Payable)

$150 billing
$100 Debit Cost of Goods Sold/Expense
$100 Credit Work in Progress
$150 Debit Accounts Receivable
$150 Credit Project Revenue

Okay, so that is all well and good, but with a capital project there would be no billing, right? Well, in our process we will do a "dummy" bill as outlined below.

$100 purchase
$100 Debit Work in Progress (Construction in Progress)
$100 Credit Contra Account for Cost (Accounts Payable)

For this to work, its important to note that the billing type is set to STD (standard) and the profit type is set to Billing Rate $0.00. These settings are very important. You cannot make the items NB (not billable) or NC (no charge), or set the profit type to NONE-- the process will not work with these settings as no WIP distribution will be generated.

Then, when we do a billing (using cycle biller or manually, choosing what needs to be recognized-- either all the costs, or just a portion), the items will show up to be billed at $0.00 resulting in a $0.00 bill and no effect on receivables management. However, the costs will be moved out of WIP in to the appropriate asset account. The billing you print serves as a record of the expenses that were capitalized.

$0.00 billing
$100 Debit Cost of Goods Sold/Expense (Asset account)
$100 Credit Work in Progress (Construction in Process account)

As an additional tip, if you are using the fixed assets module. The debit above (to Cost of Goods Sold/Expense) would actually go to the FA Clearing account, and the report from the billing would be given to the user who sets up fixed assets. They can set up the fixed asset for the total amount that was moved from CIP, the action of setting the asset up in fixed assets would then move the balance from FA clearing (credit) in to the FA cost account (debit).

If anyone has any other unique processes they have accommodated in project accounting, I would love to hear about them and will post them on this blog.

Project Accounting Default Account Sourcing

Currently I am working on several project accounting implementations. When I have several similar projects going on at the same time like this, I tend to focus on the common themes. So, lately, I have been thinking about the default project account setup through Tools>>Setup>>Project>>Project>>Accounts. This is always an interesting thing to explain to clients when they ask, how does project determine what accounts to use? And my answer is, normally, it depends on your setup.

In the core modules of Dynamics GP, the defaulting of posting accounts is fairly straightforward. The system looks first to a master record, and if no accounts are specified, it then looks to the posting account setup. So, for example, when entering a payables transaction, the system will first look to the vendor card for default posting accounts and whatever it cannot find there, it will attempt to pull from the posting accounts setup (Tools>>Setup>>Posting>>Posting Accounts).

Project accounting presents users with a more flexible defaulting model, where default locations can be specified by transaction type and by account. Additionally, segment overrides can also be specified at the contract and project levels. This creates tremendous flexibility in terms of how the system can default accounts, and where accounts need to be set up in the first place.

So, to outline the different options...

For each cost transaction type in project accounting:

• Miscellanous Logs
• Equipment Logs
• Purchases/Materials
• Employee Expenses
• Timesheets

And then for each account type (I only list a few of the basics below for the example that follows):

• Cost of Goods Sold/Expense
• Contra Account for Cost
• Project Revenue/Sales
• Accounts Receivable

For each account type, you can specify a "source" to pull the default accounts from:

• None (do not create distributions for this account)
• Customer (use Cards>>Sales>>Customer>>Project>>Accounts)
• Contract (use Cards>>Project>>Contract>>Accounts)
• Project (use Cards>>Project>>Project>>Accounts)
• Cost Category (use Cards>>Project>>Cost Category>>Accounts)
• Trx Owner (depending on transaction type, can pull from Employee, Equipment, Miscellaneous, or Vendor)
• Specific (enter one specific account to always be used)

So, to take you through an example from a project I am working on...in the example we are working with timesheets. The clients expressed the following requirements:

• The expense account used on timesheets should vary by the cost category, but the project determines which department segment should be used.
• The offset to the expense (the contra account for cost) is determined by the employee's home department.
• When the labor is billed, the revenue account should vary by the cost category, but the project determines which department segment should be used.
• When the labor is billed, the AR account will be different based on the customer.

Sometimes it takes a bit of discussion to get items distilled down to the bullet points I show above. I actually start with a cost category worksheet that I have clients complete as homework that documents the cost categories and accounts they need in project accounting as a way to help us think through the requirements. Then, from that worksheet, we can discuss how to best set up the account sourcing. Feel free to post on this blog and I am happy to share the worksheet I use in case anyone is interested.

So, the outcome we came to for timesheets in the example above:

• Cost of Goods Sold/Expense- Source: Cost Category
• Contra Account for Cost- Source: Trx Owner
• Project Revenue/Sales- Source: Cost Category
• Accounts Receivable- Source: Customer

Then, at the project level, we will specify a sub-account format (Cards>>Project>>Project>>Sub Account Format Expansion Arrow>>Account Segment Overrides) that includes the department segment value and we will mark to override the segment on the Cost of Goods Sold/Expense and Project Revenue/Sales account. So this setting will substitute in the department value specified on the project, while pulling the remaining account segments from the cost category.

With the sources set as noted above, the client needs only specify accounts as follows:

• On the cost category (Cards>>Project>>Cost Category>>Accounts), enter the Cost of Goods Sold/Expense and Project Revenue/Sales accounts.
• On the employee (Cards>>Payroll>>Employee>>Project>>Accounts), enter a Contra Account for Costs.
• On the customer (Cards>>Sales>>Customer>>Project>>Accounts), enter an Accounts Receivable account for timesheets.

And, more importantly, the accounts do not have to set up repeatedly in multiple places. And, because the project determines the department segment, there is no need for cost categories to be set up for each department, only for each main account needed.

Although the effort involved in coming to this conclusion may make you feel like accepting the default of all accounts defaulting from the cost category, taking the time to set this up in the most efficient way possible will minimize mistakes in posting and simplify the setup in the long run.

As always, please feel free to share your experiences and perspectives. Happy holidays!

Project Accounting Ramblings

Okay, so I will admit that it is sometimes a lonely life as a trainer for Microsoft Dynamics GP Project Accounting as there does not seem to be many of us, and I would be hard-pressed to name two clients who use the module in the same fashion. In that fact lies the contradiction of GP Project Accounting, that it is indeed a focused module but it serves a variety of goals. In my time using, implementing, and training on the module, I have learned that thorough discovery is absolutely essential to a successful implementation. The discovery process, in my humble opinion, should include as many pre-purchase "reality" discussions as possible to ensure that the module is indeed a sufficient fit and proper expectations are set.





I do not want to come across as negative about the module, as it meets many requirements and can provide significant productivity gains. However, I think it is important to know the parameters you are working with so that they can be planned for and addressed during the design and development stages of an implementation as opposed to coming to light during training or in a setup session. Some key limitations I have found that can impact the satisfaction with the module:


1. Reporting capabilities: Project comes with a variety of standard reports, however I have found that many companies require some degree of customized reporting. I think this is related to the fact that people approach project accounting differently, and analyze data differently. Also, consider that the project accounting hierarchy of Customer/Contract/Project/Cost Category must, in turn, support the reporting that is required.


2. Milestone billing: Although this is not standard functionality, milestone billing can be accomplished by scheduling the fee amounts for billing. This can be a process adjustment for users, but can work nicely in many situations.


3. General Ledger reporting: In some cases, users want GL reporting by project. Although project does have a trial balance report, be careful about assuming other reports can be created easily that combine GL activity with project information. The links between GL and PA are a bit complicated, so you just need to plan for any GL reporting carefully. If users want FRx style reporting (and the associated flexibility) for projects, it often leads to a discussion regarding adding a segment in the GL for projects (the easiest, albeit not always the most desirable, solution).


4. Cost Category Transaction Usage: Cost categories in project accounting can only be used with one transaction type. This can cause issues when budgeting projects, as you might end up with one cost category TRAVEL-EE (for travel expenses on employee expense reports) and TRAVEL-PM (for travel expenses from outside vendors recording using the purchasing module). So in that case, the budget for travel would have to be divided between the two cost categories. Not an ideal situation for users who plan on using the cost budgeting functionality, but it is something that users do get used to over time. If PS Time and Expense (the Business Portal based time and expense entry tool for employees) is not being used, the issue can be easily addressed by recording both employee expenses and outside vendor purchases using the purchasing module rather than using project's employee expense entry window. However, if you are using PS Time and Expense, the expenses will automatically integrate with employee expense entry not the purchasing module.


5. Budget input and updates: Currently, this is a manual process per project. There is not an import tool, which is a common request. This is particularly true when the users want to interface project accounting with other project management systems. If it is a critical need, you want to accomodate customization/development of a tool in your implementation.




When these items come up in discovery, it is important that plans be made to address them. In some cases this may mean additional costs to the clients in terms of report development and customization, or it may mean a change in business process to better align with the software. In either situation, planning ahead can spare everyone frustration.






Some popular non-traditional project accounting uses include:


1. For law firms, accumulating costs on cases

2. Tracking costs and budgets for internal projects and activities (development, marketing, etc)

3. Tracking internal construction costs for building stores, etc.



Please share your thoughts and experiences with the project accounting, I would love to hear them!