Posted on

Don’t Default to Excel

I’m a big fan of Excel. Heck, I’m such a fan I’ve written a couple of books around it. I have a tool built on it. It is unequivocally the glue that holds financial departments, and in some cases organizations, together. Sometimes though, people take that too far. All too often users lean into Excel because it’s familiar. Instead of learning the application sending data to Excel, they simply export to Excel. Why is this bad? Let me count the ways.

  1. Excel isn’t as good as a dedicated tool. I see this where systems have tools dedicated to a task and instead of using those tools users choose Excel. A great example of this is bank reconciliations. ERP systems have dedicated bank rec modules. They are designed with appropriate controls, imports from bank records, and math that always works. Still, a fair number of people choose to skip using bank rec modules and do it via Excel. It’s always messy. It’s easy to get the math wrong. It’s also not something that auditors are a fan of because Excel sheets are more prone to manipulation. I’ve seen at least one person fired for this when flaws in their Excel sheet were exposed.
  2. Excel may add work. This happens when users reflexively export to Excel without understanding the core system. Many times I’ve seen users export data and build a big report from scratch in Excel. This process gets repeated month after month. Later, sometimes years later, they realize that the system actually has the report they needed and they could have avoided all the wasted effort.
  3. Excel can add complexity. Similar to the last item, I see users export data from multiple reports and pull data together with vlookups. In the process, they build a mess because they don’t understand the underlying data. Just because data can be connected a certain way, doesn’t mean it is connected that way. There may other elements required for an appropriate connection. Explaining the mess tends to be hard on the ego of folks who created the mess. It’s exhausting convincing them that their Excel model is wrong. Excel is not always the best choice for connecting data, that’s why databases exist. There’s a second part to this as well. If you’ve ever inherited someone else’s Excel sheet, you understand how difficult it can be to simply understand, not mention untangle, what they’ve done.
  4. Excel can include errors. This one is pretty obvious. We’ve all made an Excel math error, formula error, something, heck multiple somethings. Experienced folks build checks and balances into their sheets and that adds another level of complexity. Sometimes this gets overblown, but it takes a lot of work to protect a sheet against errors.

I have absolutely nothing against Excel. I use it a lot, but I don’t default to it. Excel shouldn’t be a primary tool. It should be a fallback when a primary tool falls short. Take the time and energy to figure out primary systems and use Excel to fill in the gaps.

Posted on

Why are you still using the GP Stock Status Report?

couple carrying cardboard boxes in living room

It’s a valid question. Why are you still using the GP Stock Status Report? or even the Historical Stock Status Report?

The Stock Status Reports aren’t designed to tie to General Ledger. The Historical Inventory Trial Balance (HITB) is designed to tie to GL.

From Microsoft’s own: How to determine, maintain, and report accurate costing in Inventory, pg 9

In conclusion, we recommend that customers do not use the Inventory Historical Stock Status report to adjust or balance to the general ledger. The report was not designed for that purpose. While FIFO/LIFO perpetual inventories may be very close in balancing, there are issues still pending that would keep this report from balancing to the general ledger.

HITB has been out since GP 10 SP2, way back in 2008, yes 2008. HITB becomes a teenager this year. Despite this, I still see a lot of people still using the Stock Status Report. It’s long past time to move off Stock Status and on to HITB.

Posted on

Dynamics GP: Slow-Moving Inventory

couple carrying cardboard boxes in living room

I realized the other day that I’ve only posted this code in a reply, never as its own post, so I’m adding this now.

Slow-moving and obsolete inventory is traditionally a measure of inventory destined for markdown and ultimately write-off if not addressed. The nature of what is slow-moving and obsolete varies dramatically. Fresh bread is slow-moving if it hasn’t sold in a couple of days, heavy equipment could be months. Each organization determines what is slow-moving or obsolete.

This code compares inventory items to sales to determine items with quantities on hand that have not sold with the timeframe set in the date area.

SELECT IV00101.* FROM
dbo.IV00101 INNER JOIN
dbo.IV00102 ON dbo.IV00101.ITEMNMBR = dbo.IV00102.ITEMNMBR
WHERE
(dbo.IV00101.ITEMNMBR NOT IN
(SELECT
dbo.SOP30300.ITEMNMBR
FROM dbo.SOP30300
INNER JOIN dbo.SOP30200
ON dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
AND dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE
WHERE (dbo.SOP30200.DOCDATE BETWEEN
CONVERT(DATETIME, ‘2019-03-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2021-02-28 00:00:00’, 102))
))
AND (dbo.IV00102.QTYONHND > 0)

Note: I had this in my set of scripts with no notations, that usually means I wrote it, however, I see that I posted a link to a version of this written by Mohammed Doud. The link is now gone so I’m not sure if this is his version or mine and I don’t want to take the credit if it’s his. Either way, it’s useful.

Posted on

Underused Software Syndrome

Do you have low level anxiety, a feeling you’re not getting everything you could out of your existing software? Is there a nagging guilt that it’s your fault? Does it seem if only you had more time or training you could really master that piece of software and life would be so much easier?

Welcome to Underused Software Syndrome!

I’ve searched for a term for this phenomenon and not found one so I’ve decided to coin my own. Feel free to set me straight if this anxiety already has a name.

I see this low-level fear a lot. The idea that there is something more, some magic, hiding in a piece of software. Finding this magic would result in a life of unicorns, rainbows, and tropical drinks…or so we believe. Part of the challenge is that occasionally we do stumble onto a feature that solves a significant problem or increases productivity. That reinforces the syndrome. Surely the developers added more features like the one we just found if only we had time to look harder. With Underused Software Syndrome, we think software problems are our fault. While it’s right to want to be more productive and to solve problems, it’s wrong to blame yourself.

Underused Software Syndrome manifests frequently in business software users. Users of applications like ERP systems (accounting), CRM (sales), and even Office apps like Excel are common sufferers. The software is so big and complex that user’s blame themselves for not being more efficient. This seems to be a form of impostor syndrome mixed with a little FOMO, the fear of missing out. The user feels that despite their knowledge of their job and the relevant software, they don’t know enough and they aren’t good enough. Surely everyone else is getting more out of the software.

I’m sure this feeling isn’t limited to business software. Video editing and graphic design software seem more than complex enough to generate Underused Software Syndrome feeling. I just have more experience with business software.

In some cases, there’s a financial element to the feeling of Underused Software Syndrome. The idea that software is expensive, and it’s fiscally responsible to use as many features as possible, can sometimes underly the feelings of anxiety. Much like an underused gym membership, people feel guilty if they aren’t fully utilizing it.

In other examples, anxiety may manifest itself based on unfulfilled expectations. Users believe that a software package should have a particular feature and that feature should behave a certain way. Features often manifest differently than expectations leaving users with a vague feeling they missed something.

Finally, there is the fear of missing out. For example, lots of people use Excel and lots of people use a tiny fraction of Excel’s features. Even if they know a feature is there, they have to remember the feature exists at the time they want to use it. Most people are not experts in a given software and most are not getting more out of it than you are.

People like me make this worse. For a long time, I’ve helped people get the most out of the software they own. But that was part of my job. It’s also something I really enjoy, and yet I feel Underused Software Syndrom symptoms about software I deeply understand.

I differentiate underused software from shelfware. Shelfware is software that is not being used. The organization may still be paying maintenance or fees for software they aren’t using at all. Shelfware is easier to address. Ignore the sunk costs and cancel any maintenance or monthly fees. Alternatively, revisit why the software was purchased in the first place and potentially put it to use.

Underused software is harder. The organization is getting some value from the software, maybe not enough value to match the cost, but value nonetheless. It’s hard to toss out software that is being used.

I have a couple of thoughts on options to address underused software and it’s related syndrome:

  1. Accept that value is being generated by using the software. Even if it’s only used for a small task, it is still helping accomplish the task. Accept that this software does this task and move one. Sometimes you just need to accept something and move on.
  2. Evaluate the value of that task against any ongoing costs. A small task with a big cost is not a good value proposition. In that case, it may make sense to figure out if there are additional uses or if it’s time to switch to something else.
  3. Pick one thing to improve and search for that. You’d be amazed at what’s available for any given piece of software. Maybe there’s a need to automate a process or export data, whatever. Someone else has probably already tried it and written about it. At a minimum, you’ll get an answer that something can’t be done. Even in that worst-case scenario, a quick answer makes it easier to stop obsessing and move on to the next thing.
  4. Make sure the organization has the latest version. Underused software may be neglected enough to be on an old version. Updating can reveal improvements in features and UI that help resolve anxiety.
  5. Get some help. It’s a big world. There are books, classes, training, blogs, videos, you name it, on some of the most obscure software ever made. There are resources to help. Use them.

“But I don’t have enough time” is the common refrain. There is a problem, but not a priority. People find the time for a priority. It’s okay if this isn’t a priority right now. When it becomes a priority you’ll make the time. Until then, don’t stress about it.

Posted on

Vote for my DynamicsCon Session!

DynamicsCon is a FREE 2-day virtual learning experience for Microsoft Dynamics 365 & Power Platform users and professionals. Previously, Dynamics GP was not included in the mix, but you asked for it and now GP content will be included as well.

But YOU HAVE TO VOTE!

If you want GP content, go and vote for it at https://dynamicscon.com/submissions/. Hurry, you only have a couple of days. Voting closes this Friday the 22nd.

I would love for you to vote for my session, 50 Security Tips for Dynamics GP at this link: https://dynamicscon.com/submissions/?query=polino, but ultimately vote for any of the GP content that appeals to you!

Posted on

Microsoft Dynamics GP Fall 2020 Release fixes recent Word template issue – azurecurve

Microsoft Dynamics GP Fall 2020 Release fixes recent Word template issue – azurecurve
— Read on www.azurecurve.co.uk/2020/10/microsoft-dynamics-gp-fall-2020-release-fixes-recent-word-template-issue/

Posted on

Automate full General Ledger Reconcile – Microsoft Dynamics GP Community

In case you’re confused, this new feature refers to the Reconcile routine, a troubleshooting utility, not reconciling the GL to a sub ledger.

You no longer need to select one year at a time to reconcile! You can now select “All” to have Dynamics GP reconcile through the years oldest to newest.
In the Reconcile Financial Info…
— Read on community.dynamics.com/gp/b/dynamicsgp/posts/automate-full-general-ledger-reconcile

Posted on

Microsoft Dynamics Fall 2020 Release has shipped! – Microsoft Dynamics GP Community

couple carrying cardboard boxes in living room

I don’t think I need to add anything to this.

We are pleased to announce that Microsoft Dynamics GP has released!
The October 2020 release includes over 20 new features across the system we are excited to show the functionality we have been …
— Read on community.dynamics.com/gp/b/dynamicsgp/posts/microsoft-dynamics-fall-2020-release-has-shipped

Posted on

Microsoft Dynamics GP Fall 2020 Update – Upgrade Troubleshooting – Microsoft Dynamics GP Community

From the GP Team:

Today’s blog post will be a rehash of previously published information regarding upgrade troubleshooting. Since the main process hasn’t changed for the upgrade itself, the troubleshooting poin…
— Read on community.dynamics.com/gp/b/dynamicsgp/posts/microsoft-dynamics-gp-fall-2020-update—upgrade-troubleshooting