Weekly Review: Turning a Project into a Fixed Asset

A common request is to turn a Project from the Project Accounting module, into a Fixed Asset in the Fixed Asset module. There is no native functionality to do this and based on my conversations with the Dynamics GP team, it's not going to happen so you might as well understand the workaround and it looks like this:

1) Create a Miscellaneous Log Cost Category for Fixed Assets pointing the Contra Account to the FA Clearing account and the Cost/Expense account to wherever you are collecting the project costs (a WIP account, a project FA WIP account, etc.). You'll be doing a negative misc log so the cost collection location will be credited and contra (FA Clearing) will be debited.

2) Once the project is ready to become a Fixed Asset, process a negative Misc Log transaction using the Fixed Asset Cost Category to remove an amount equal to the total cost of the project.

a. This setup makes it easy to exclude the FA Cost Category when analyzing project costs while still leaving the project at zero

b. This process provides a transactional audit trail to show that the project was closed out and moved to Fixed Assets

3) Create a Fixed Asset. The GL Transfer mechanism will move the costs from the FA clearing account to the appropriate Fixed Asset account.

The reality is that the number of fields that would actually transfer from a project is really small, single digits small. The complexities of creating a Fixed Asset from a project become large very fast. For instance, how do you deal with multiple assets from a single project, complete assets but incomplete projects, partial capitalization of a project, combining Cost Categories for capitalization, etc. The benefits of creating something to manage this are far outweighed by pain of dealing with all of the possible scenarios. Realistically, this is best handled through a process like the one described here.

Posted by Mark at 6/01/2009 02:23:00 PM

Weekly Review: Putting the Fix in Fixed Assets

Fixed sporting events are bad. Fixed transmissions are good. Fixed Assets are ...well... in between.

Today I want to look at some potential issues in the Fixed Asset module and what you can do about it. I'm using version 8 but these items should apply backwards and forward for several versions. There haven't been that many chages to FA. So here we go:

• The Fixed Asset module has it's own calendar in Tools-Setup-Fixed Assets-Quarter. You may think that this is no big deal, it's used for the mid-quater convention, yada yada. BUT if your Fixed Asset calendar is different from your financial calendar, you can have a mess! What can happen is that if you run depreciation only for the current period, because the financial and FA periods don't match, part of your depreciation may not flow to the GL! Oops. So if you're on a fiscal calendar, double check your FA calendar too. Go on. Do it now we'll wait.
  
• You think you have a problem with a Fixed Asset and the GL. Where do you look? The Inquiry-Fixed Asset-Financial Detail windows is your best friend. The batch number in this window shows the GL interface batch. If you have gaps, the data didn't go to the GL. (Of course, since you do account account recons you found and fixed this via a JE at month end. Didn't you?)
  
• Partial Retirments Just so you understand what a partial retirement transactions does, here's the scoop. Let's say I have asset 100-1 and it contains Ferraris with a quantity of 5 (it was a special bulk price). When you total one of my Ferraris, I have to partially retire the asset since it has a quantity greater than one and you only totalled one of my Ferraris. When the partial retirement is done, GP creates a second asset behind the scenes to hold the retired portion. In this case asset 100-2 would be created and it would contain 1 retired Ferrari. Asset 100-1 has it's quantity and cost reduced by the amount of the retired portion and it remains the active asset. When you look in the Retirements window, you see asset 100-1 with the partially retired flag set, but all you see is the active portion when you drill down on the asset id. To find out info about the retired portion, you have to go find the correct suffix for that asset and drill down on it via the regular FA inquiry window. More importantly, you totalled one of my Ferraris!
  
  
  

Originally Posted by Mark 4/26/2006 12:31:00 PM

Weekly Dynamic: Putting the Fix in Fixed Assets

Fixed sporting events are bad. Fixed transmissions are good. Fixed Assets are ...well... in between.

Today I want to look at some potential issues in the Fixed Asset module and what you can do about it. I'm using version 8 but these items should apply backwards and forward for several versions. There haven't been that many chages to FA. So here we go:

• The Fixed Asset module has it's own calendar in Tools-Setup-Fixed Assets-Quarter. You may think that this is no big deal, it's used for the mid-quater convention, yada yada. BUT if your Fixed Asset calendar is different from your financial calendar, you can have a mess! What can happen is that if you run depreciation only for the current period, because the financial and FA periods don't match, part of your depreciation may not flow to the GL! Oops. So if you're on a fiscal calendar, double check your FA calendar too. Go on. Do it now we'll wait.
  
• You think you have a problem with a Fixed Asset and the GL. Where do you look? The Inquiry-Fixed Asset-Financial Detail windows is your best friend. The batch number in this window shows the GL interface batch. If you have gaps, the data didn't go to the GL. (Of course, since you do account account recons you found and fixed this via a JE at month end. Didn't you?)
  
• Partial Retirments Just so you understand what a partial retirement transactions does, here's the scoop. Let's say I have asset 100-1 and it contains Ferraris with a quantity of 5 (it was a special bulk price). When you total one of my Ferraris, I have to partially retire the asset since it has a quantity greater than one and you only totalled one of my Ferraris. When the partial retirement is done, GP creates a second asset behind the scenes to hold the retired portion. In this case asset 100-2 would be created and it would contain 1 retired Ferrari. Asset 100-1 has it's quantity and cost reduced by the amount of the retired portion and it remains the active asset. When you look in the Retirements window, you see asset 100-1 with the partially retired flag set, but all you see is the active portion when you drill down on the asset id. To find out info about the retired portion, you have to go find the correct suffix for that asset and drill down on it via the regular FA inquiry window. More importantly, you totalled one of my Ferraris!
  
  
  

Weekly Dynamic: Fixed Asset GL Account Transfer

A lot people don't use Fixed Asset transfers because they think in terms of the physical movement of assets but transfers can be used for more than that. For example, if an asset has been going to a wrong account or if you decide to reorganize fixed assets and change accounts a fixed asset transfer can be used to move the GL accounts.

To transfer GL accounts for Fixed Assets in GP open the Financial Area Page. Under Fixed Assets pick Transfer. Select an asset and transfer date. Click the blue arrow next to G/L Accounts. Change the accounts that need adjustment and click transfer.

Weekly Dynamic: Turning a Project into a Fixed Asset

A common request is to turn a Project from the Project Accounting module, into a Fixed Asset in the Fixed Asset module. There is no native functionality to do this and based on my conversations with the Dynamics GP team, it's not coming any time soon so you might as well understand the workaround and it looks like this:

1) Create a Miscellaneous Log Cost Category for Fixed Assets pointing the Contra Account to the FA Clearing account and the Cost/Expense account to wherever you are collecting the project costs (a WIP account, a project FA WIP account, etc.). You'll be doing a negative misc log so the cost collection location will be credited and contra (FA Clearing)  will be debited.
2) Once the project is ready to become a Fixed Asset, process a negative Misc Log transaction using the Fixed Asset Cost Category to remove an amount equal to the total cost of the project.
    a. This setup makes it easy to exclude the FA Cost Category when analyzing project costs while still leaving the project at zero
    b. This process provides a transactional audit trail to show that the project was closed out and moved to Fixed Assets
3) Create a Fixed Asset. The GL Transfer mechanism will move the costs from the FA clearing account to the appropriate Fixed Asset account.

I'm surprised that Microsoft doesn't have any plans to add at least this basic functionality since it doesn't seem like it would be all that hard. But every indication is that it's not coming so you'll have plenty of time to get used to the workaround process.

Dynamics GP Internal Metrics

A number of folks have emailed asking for the SQL queries behind the internal metrics I described in my MSDynamicsWorld article. Well, now, it's all available here. I'll also add a link to the downloads section on the right.

In brief, there are metrics for AP, PO, Fixed Assets and a bonus one for Actual vs. Budget.

For AP there is a count of Vouchers Per Week, Vouchers Per Week excluding PO related vouchers and Payments per Week.

For PO there is a count of PO's per Week, PO Lines per Week, Receivings per Week and Invoices Matched per Week.

Fixed Assets has Fixed Assets added per week and the bonus is a list of overbudget accounts along with the values and differences as of today.

These items are historical, meaning if you plug them in, you'll get data already in your system assuming you are keeping history. All of the items are useful today but have lots of room for improvement in efficiency and scale. For example some of these could be monthly and tied into your fiscal calendar. You could used activity tracking to break the metrics out by user. The Week End Date code could be rolled into a function for simplicity. My goal was to give you something understandable to build on.

There's more info in the read me file that's included. There are no AR metrics because we used an industry specific AR package that was not GP. I have metrics for it but it's not relevant to GP users.

Special thanks to Steve Gray of 4Penny.net who wrote parts of these under contract and to Dwight Specht of I.B.I.S., Inc. who gave me the current Week Ending Date code.

Dynamics GP Fixed Assets Depreciation and Reporting

Steve Chapman continues his look at fixed assets with Dynamics GP Fixed Assets Depreciation and Reporting

Feature of the Day: Fixed Assets to General Ledger - Inside Microsoft Dynamics GP - Site Home - MSDN Blogs

I told you that Fixed Assets was getting some nice features in 2013 including Fixed Assets to General Ledger Batches

Weekly Dynamic: Count of Fixed Assets Added

Continuing a look at metrics to analyze GP transactions, this week’s Weekly Dynamic looks at a count of Fixed Assets added by week. As with other metrics, this can also show past data in Microsoft Dynamics GP for trends.

The code is below and it’s available for download at: http://www.box.net/shared/vopqx25elb

--Select Fixed Assets Added by week
--Could be switched to month but you'll need to use either the FA Calendar Month or the Fiscal month if you don't use a calendar year
select Case  
 when datename(weekday,dateadded) = 'Friday' then dateadded
 when datename(weekday,dateadded) = 'Saturday' then dateadd(Day,6,dateadded)
 when datename(weekday,dateadded) = 'Sunday' then dateadd(Day,5,dateadded)
 when datename(weekday,dateadded) = 'Monday' then dateadd(Day,4,dateadded)
 when datename(weekday,dateadded) = 'Tuesday' then dateadd(Day,3,dateadded)
 when datename(weekday,dateadded) = 'Wednesday' then dateadd(Day,2,dateadded)
 when datename(weekday,dateadded) = 'Thursday' then dateadd(Day,1,dateadded)
 else '01/01/1900' end as WeekEndDate, count(Assetindex) as FAAddedCount
	from fa00100
Group by
Case  
 when datename(weekday,dateadded) = 'Friday' then dateadded
 when datename(weekday,dateadded) = 'Saturday' then dateadd(Day,6,dateadded)
 when datename(weekday,dateadded) = 'Sunday' then dateadd(Day,5,dateadded)
 when datename(weekday,dateadded) = 'Monday' then dateadd(Day,4,dateadded)
 when datename(weekday,dateadded) = 'Tuesday' then dateadd(Day,3,dateadded)
 when datename(weekday,dateadded) = 'Wednesday' then dateadd(Day,2,dateadded)
 when datename(weekday,dateadded) = 'Thursday' then dateadd(Day,1,dateadded)
 else '01/01/1900' end 
order by 1

 

Feature of the Day: Reporting Ledger Functionality for Fixed Assets Management

Present a new Feature of the Day, Pam Misialek looks at Reporting Ledger Functionality for Fixed Assets Management in GP 2010 R2. This is a fantastic addition as Fixed Assets are one with with some notable differences between GAAP and IFRS.

Fixed Assets “History” Report

Omar takes a shot at SQL code to try to replicate some Fixed Assets History data. The problem is that Fixed Assets doesn’t really keep complete history so getting all of the information is problematic.

Significance of Fixed Assets REQUIRE ACCOUNT Setup Option

Vaidy dug up a useful old Fixed Assets post and it helped him solve an issue. That’s why I do the Weekly Review segment to bring some of the posts you may have missed back to light. Check out Vaidy’s issue in Significance of Fixed Assets REQUIRE ACCOUNT Setup Option

Weekly Dynamic:Change the Fixed Asset Calendar at Year End

I don't think that validating the Fixed Asset calendar is in the Year End checklist for Dynamics GP, but it should be. I've talked about the Fixed Asset calendar before and why it's important to make sure that it matches your fiscal calendar. At year end, you want to make sure that you've created the Fixed Asset calendar for the new year. Otherwise your January depreciation run will fail with a date related error.
So when you ensure that you have your 2007 calendar created and correct before closing 2006, take a look at your FA calendar as well. The FA calendar is set by quarters and is located at Tools->Setup->Fixed Assets->Quarter.
You also need to make sure that the current fiscal year is correct in the FA books. Tools->Setup->Fixed Assets->Book.

Dynamics GP 2010 – Fixed Assets Low Assets Pool

Amy Walsh shows us new GP 2010 functionality around Fixed Assets Low Assets Pools. This is great for companies with Australia, New Zealand or German operations among others. Also, courtesy of IFRS this may be coming to you too.

Weekly Dynamic: Asset Numbering

A little Fixed Asset thought this week.

When numbering Fixed Assets each asset gets a suffix with numbers 1-99 possible. You can also create master assets to associate assets. I asked about this quite a bit so I thought that I would my general guidance.

• Use Asset Master numbers to associated items purchased together that might later be separated.

For example: You buy a compute plus a monitor and the cost is over the threshold to be an asset. Since an asset and a monitor can be separated down the road you might choose to make each piece an asset. This is a perfect place to use Master Asset numbers to associate assets but allow them to be separated later on.

This also prevents issues in bulk buy scenarios where the asset ID’s might exceed 99 assets.

• Use Asset Suffixes for parts of a larger asset purchased separately that generally aren’t separable later.

For example: Asset is purchased and given suffix 1. Months later the freight costs for that asset arrive. One option is to set it up with the same asset number and give it suffix 2. Since you’ll never separate the freight from the asset in the future this keeps them together. It’s also highly unlikely that these scenarios will exceed 99 assets.

Weekly Review: Fixed Asset/GL Best Practices

If you use GP Fixed Assets, you know that if you run all your Fixed Asset processes (Add, Retire, Depreciate, etc.) together and then run the GL Posting routine, you get one big journal entry with all of your transactions. This makes is very difficult to find and correct any errors.

The easiest way around this is to separate asset changes from depreciation. Make all of your asset additions, retirements and changes, then run the GL Posting routine (Tools-Routines-Fixed Assets-GL Posting) before you run depreciation for the period. This will give you a much cleaner GL transaction making it easy to ensure that the your GL clearing account is properly cleared. After that, run depreciation and do a separate GL Posting for depreciation.

When processing your GL Posting routine, set the periods from the beginning of the year to the current period. This way if something didn't post properly in a prior period it will come out for cleanup. For example, if someone added a fixed asset before you closed the month but after you ran your GL Posting interface, it's still sitting in the prior month. Running the GL Interface for only this period, won't cause this transaction to post. But if you run the GL interface from the beginning of the year, it will at least make it into this month's transactions.

Using this technique will get you good, clean FA to GL transactions every month.

Posted by Mark 5/09/2006 02:24:00 PM

Weekly Dynamic:Fixed Asset/GL Best Practices

If you use GP Fixed Assets, you know that if you run all your Fixed Asset processes (Add, Retire, Depreciate, etc.) together and then run the GL Posting routine, you get one big journal entry with all of your transactions. This makes is very difficult to find and correct any errors.

The easiest way around this is to separate asset changes from depreciation. Make all of your asset additions, retirements and changes, then run the GL Posting routine (Tools-Routines-Fixed Assets-GL Posting) before you run depreciation for the period. This will give you a much cleaner GL transaction making it easy to ensure that the your GL clearing account is properly cleared. After that, run depreciation and do a separate GL Posting for depreciation.

When processing your GL Posting routine, set the periods from the beginning of the year to the current period. This way if something didn't post properly in a prior period it will come out for cleanup. For example, if someone added a fixed asset before you closed the month but after you ran your GL Posting interface, it's still sitting in the prior month. Running the GL Interface for only this period, won't cause this transaction to post. But if you run the GL interface from the beginning of the year, it will at least make it into this month's transactions.

Using this technique will get you good, clean FA to GL transactions every month.

Weekly Dynamic: Asset ID’s Beginning With “Z” Don’t Appear on Some Reports

We found a bug the other day that I swear I’ve seen reported somewhere but I can’t find it so I’m noting here for everyone. I have reported this to MS via PartnerSource and MS Connect.

For Dynamics GP 10 SP 5 and GP 2010 SP 1 at least, if the Fixed Asset ID begins with the letter “Z” the asset will not appear on at least some reports. I haven’t test this with R2 yet. To duplicate in Fabrikam, create a new fixed assets starting with the letter Z like Z1234 and assign it to structure 100. Create a second asset starting with the letter Y like Y1234 and assign it to structure 100.

Run the Inventory by Structure ID report using structure 100 as the only limiting criteria. Item Y1234 will appear on the report, item Z1234 will not.

Microsoft has indicated that this is a known bug reported in KB 863264  but the KB seems to be unavailable. No wonder I couldn’t find it. MS is working to update the KB to ensure that it’s available. There are no plans to fix this issue. If you want it fixed, vote for it on MS Connect.

You’ve two options to deal with this:

1) Add a Fixed Asset ID end range of “ZZZZZZZZ” to reports

2) Change the Fixed Asset ID via SQL. Fortunately, FA uses asset indexes, not the ID to link assets in various tables so the change only needs to happen in one table. However, some ISV solutions use the Fixed Asset ID (like Multi-Entity Management from Binary Stream) so you’ll need to check those too.

Weekly Dynamic: Rerun FA GL Post Routine

So lets say that you run the GL post routine in Fixed Assets for Dynamics GP. We all know that this creates an unposted batch in GL. What if someone deletes that batch by mistake? Think about the thousands of depreciation line entries that could be in that file, now they’re gone. Not so fast. After a related incident at a client, Microsoft sent a us script to allow recreation of the GL batch. With their permission I’m reposting it here. Just to be clear, if you don’t run separate GL posting routines for additions, retirements and depreciation, this won’t let you undo that. You’ll still get one big file to sift through. But, if you run the GL routine to process additions and while you’re working on retirements someone whacks the unposted additions batch, you’re not stuck.

 

Delete  batch prior to posting then run this script. Afterward, rerun the GL Posting routine.

/*
** Procedure Name:  FA_Undo_GLBatch_Interface
**
** Description:
**
**    Resets the Batch ID to '' (blank) for the selected Batch ID in
**    the Financial Detail File (FA00902) so that the GL Interface can be run again
**    to pick up those details.
**
**    **WARNING: This does not change any GL tables
**
** Special Instructions:
**
**    set @BatchName = 'XXX' --XXX must be replaced with the Batch Name to be affected
**   
** Database:    Company
**
**
******************************************************************************************
*/

declare @BatchName char(15)
set @BatchName = 'XXX'

UPDATE FA00902
    SET GLINTBTCHNUM = '', GLINTTRXDATE = '1900-01-01 00:00:00.000'
    WHERE GLINTBTCHNUM = @BatchName

DELETE FA15000
    WHERE GLINTBTCHNUM = @BatchName

DELETE FAINDEX      

 

On  a related note, we had a client going live with Fixed Assets. One of their people was supposed to run the GL Posting interface through 12/31 and then delete batch prior to posting. This would take care of all of the integrated assets which were already in GL. They didn’t. When the first depreciation was run in January we got all the FA additions plus all of the fist month’s depreciation. This script bailed us out. We deleted the batch and reran the posting routine through 12/31. This created a batch of additions which we deleted. We then ran the GL process through 1/31 and got a batch with just January depreciation.

Dynamics GP 2010 “DIY” Fixed Assets Excel Report

Amy Walsh has a new take on Fixed Asset reporting with a Dynamics GP 2010 “DIY” Fixed Assets Excel Report