I’m swamped with work work this week so it may be a light posting week and I don’t expect to get as much work done on the DynamicAccounting.net website as I would like. I have some SQL scripts and downloads ready to setup but I’m not sure I’ll have the time this week. Maybe I’ll cut back on those 6 hours of sleep a night!
I stumbled on this little gem in v 7.5 so I don’t even know what version it was added in to. If you need to look at groups of accounts this is the inquiry for you. You can even perform calculations!
What is it?
Account Rollup is an inquiry that you build which allows you to see different GL accounts rolled up together. Additionally, you can perform calculations on those accounts.
We use this for all kinds of things like:
- What’s the Fixed Asset total across all divisions?
- What’s the commission allocation based on sales?
Those kinds of things. Yes FRx will do this, but freqently, I don’t want to launch FRx and run financial statements just to get a breakdown of my FA accounts. So how does this work?
In Dynamics GP, select Inquiry->Financial->Account Rollup
Enter an Option ID, tab and click yes to add the option.
You set the number of columns you want to see up in the right side. If all you want is totals, one column is fine. For calculations, use at least 2 columns.
In the center, you set the types of columns, but we’ll come back to that. At the bottom, you add criteria like accounts, departments, divisions, etc. UNLIKE OTHER GP criteria areas, you can multiple independent criteria here. So if you want to see Chicago and New York but not LA. You add Chicago, then add New York.
Save and click redisplay. Controls on the right let you see net change or period balance totals. If you want to see what makes up the total number, select a month and click the blue column header.
Now, to get really fancy, you can ad calculated columns to a rollup. Simply create or modify (with the button) a rollup.
Change the type from Actual to Calculated. In the Selection column, click the More Info button. In v8.0 this is a little arrow.
Back to my commission comment earlier. Let’s say, I accrue 8.9% of sale as a commission accrual. My first column is my monthly sales. So I’ll pick the only available column in the column box and hit the double arrows (>>) then add the multiply sign (*) then in the constant box I’ll add .089 and hit the double arrows (>>).
You formula now looks something like C1 * 0.089. Save and refresh and you get a report with sales and the commission allocation for each month.
This is great for all those estimates you do every month. You can just take the numbers and do a JE.
DynamicAccounting.net is now on it’s new server. The move is done, so now I can get back to concentrating on content.
The website will down for very short periods of time for the next day or two (about 5-10 minutes). The new server is ready, the content is migrated but I’m having trouble getting DNN to come up properly and I need the real URL for testing. Apologies.
The upcoming versions of GP will include workflow. Satya Nadella of MBS blogs that upcoming versions of GP will include workflow based on MS’ newly anounced Windows Workflow Foundation.
I’ve been thinking a little about open source ERP systems. Compiere seems to be leader. It’s free, but they nickel and dime you on documentation, support, etc.
The biggest problem with Compiere is that it uses an Oracle (or now Sybase) database. What’s that all about? It may be cheaper to buy SQL Serve and Great Plains than to buy Oracle and get Compiere for free. Not mention the pain of running Oracle in a small to medium shop.
I think there is room for an MS based open source ERP system. I think it would push the leaders. Great Plains is great because of it’s community, backend and solidness. However, GP is behind in “run anywhere” functionality and Dexterity feels creaky. M. ore likely, I expect to see an open source Linux/MySQL (or Postgre) offering.
Lets face it, ERP systems are complex. They cover a lot of ground and they can function very differently. Solomon, GP and Oracle all process payables, and they all do it differently. I wonder whether the benefits of an open source ERP will hurt comanies? Will a bootstrapping company install it and force it to work with dire consequence? Is the growth from a small business package, like Quickbooks, into a midsize package important?
I guess we’ll have to wait and see.
I have to comment on this. Oracle is buying Siebel. I used Oracle financials and played with Siebel in some training classes. What do you get when you merge two hard to use programs? …what do you think?
Siebel’s been a mess for a while. It’s more of a pain than SAP in my opinion, it’s just not as spectacular when an implementation fails. Microsoft Business Solutions finally abanonded a partnership with Siebel after severals years of trying and failing to integrate it with Great Plains. Good luck Oracle, you’re going to need it.
Each week I’d like to cover a feature in GP. Some of them are new, others just underutilized. Ideally, I’d like to do this more than weekly but hey, I have a job.
Extreme Makeover, JE Edition
In version 8.0 Microsoft introduced Journal Entry corrections and copies. This is feature I’d been asking for for some time. I even came close to writing my own version several times. You now have the ability to undo a journal entry, easily, and with a complete audit trail.
In Transactions->Financial->General, select Correct. You now have an option to back out a Journal Entry or Back out a JE and create a correcting entry.
You will also need the JE number (you can look it up) and year of the entry you want to correct.
What happens behind the scenes when you backout an entry is that GP creates a duplicate, opposite JE with a new JE number to offset the one you want to correct. If all you need to do is undo an entry, simply post your correction and you’re done.
If you also select a correcting entry, the first entry created is the backout entry. When you post or save the backout entry, a second entry is created. This second entry is your original entry as a new, unposted JE that you can make changes to.
Both the backout and the correction are normal JE’s that can be save to a batch, changed, posted, made reversing, etc.
Some obvious uses:
- That batch of month end accruals was accidentally posted in next month. Simply pull up the JE, do a correction to back it out of next month, and change the date on the correcting entry to the correct month. Post it all an you are in business.
- The 150 line JE keyed incorrectly by an intern. Hopefully this was reviewed before posting, but if not, use this tool to backout the incorrect entry and make the intern fix the correcting entry (or re-key it, depending on how mad you are ;).
A related feature is to Copy a JE. Same process, just click copy instead of correct. This is great when you need to create a JE just like the one you did last month!
By default, Correction only works for transactions that originated in the General Ledger. However, the administrator can change this behavior.
Tools>Setup>Financial>General Ledger now has new check boxes to control this functionality. Check “Voiding/Correcting of Subsidiary Transactions” and “Back Out of Inter-company Transactions” to allow for corrections of transactions originating in a sub-ledger.
This a great feature, a critical feature, that the competition has had for a while. Now we’ve got it and I love it. But you have to be on v8.0 or better. This was one of the compelling features that pushed us to upgrade to 8.0. Detailed specifics can be found in GP Help. Happy Fixing!
Why are there so few GP books out there? An Amazon search turned up only a few old (pre-MS acquisition) or books not yet published. Many of the available offerings are weak too, spiral bound or college texts. Where is MS Press? Why no Great Plains for Dummies? How about a Great Plains Administrator’s guide?
I really don’t want to have to write one myself. That’s too much like work!
Search Amazon for “Great Plains” Accounting and see for yourself.
This is the start of my new Microsoft Dynamics GP (known aliases Great Plains Dynamics, eEnterprise, Microsoft Great Plains Standard and Pro) blog. I have some GP stuff on my website currently and I’m still figuring out how I’m going to incorporate that into the blog.
Currently, I’m the Controller for Transit Televsion Network, LLC. We used MS Great Plains 8.0 standard and are on the edge of needing the professional version. Prior to this role I was a consultant with epartners and Stanley Stuart Yoffee and Hendrix, two Great Plains resellers, for a total of about 4 years. I held all the basic GP certifications, Distribution, Financials, Integration Manager, etc. Prior to that I used Great Plains at Darden Restaurants.
I’ve been using GP since version 5.0 and have blogged about GP in my personal blog, but I wanted to separate my personal ramblings from my Great Plains halucinations.
Just to be clear, I don’t care about Axapta, Navision or Solomon. I have had a little exposure to Solomon and it’s nice, just different. But I don’t care, I use and know GP so that’s what I’ll focus on.
I hope you enjoy!