News!

A rant on Excel and Bad Ideas.

The great thing about the internet is that anyone can share their thoughts, the problem with the internet is that anyone can share there thoughts. A classic dichotomy if you will.

We see this all the time, people talking utter, utter rubbish about things they don’t understand. As “organisations” have started to blog more and more this issue has increased. The way I see it is like this… If you run “Mega Corps” would you want me bloging for you? I probably wouldn’t. You see I’m not a talented writer, my spelling and grammar are poor and I’m not adept at making my point – as I will now prove. So “Mega Crops” ask the people who can do these things well to blog for them. What actually makes for a good bloger is someone who can think well! See, a bad idea expressed well is still a bad idea. What makes a great bloger is someone who can think and write well – and these types are rare. But still, what would you rather have, an ill-conceived observation expressed elegantly or a quality observation just expressed?

Which leads me to these two blog post, the first one suggests that people who use Excel in the supply chain are doomed, and the second one adds more fuel to that fire.

Beware Supply Chain Excel Users—YOU are DOOMED!!!!

Excel doesn’t excel in all cases…

Now I need to be careful here, I don’t what to offend anyone.

Equally I’d like to address the lack of understanding and insight, or at least offer a counter view. I have some grounds to make these observations, being somewhat familiar with Excel and having worked in supply chain for the last decade or so.

Basically both articles point out the limitations of Excel, principally by comparing the differences between Excel and a Generic ERP system. For example:

“Excel has features that can calculate safety stock by using prebuilt basic formulas such as moving average, standard deviations forecasts etc. Keep in mind that by doing this, an organization will not have the ability to see the entire supply and demand relationship. On the other hand, when the same safety stock is calculated within an application, it will provide visibility to outstanding supplies that need to be replenished; orders can be planned for production; and it can calculate how quickly organizations can turn a forecast into a deliverable product.”

Khudsiya Quadri, Technology Evaluation Centers

And

“I completely agree with the author that there is a big risk to SCM Professionals who rely too heavily on Excel.  There are all the reasons listed in the article such as  lack of collaboration, visibility, control and no ability to perform “what-if” scenarios.  I would like to add some additional thoughts to this discussion.”

Monique Rupert,  21st Century Supply Chains

So, Excel has limitations creating what if scenarios, and pre built basic functions!! Clearly not Excel experts! Another common theme is that Excel is not very good for collaboration, information sharing and data security…

It is almost impossible to control the integrity of spreadsheet data and access to the spreadsheet.  With multiple people accessing the spreadsheet and no security, how can anyone have any confidence in the data?

Monique Rupert,  21st Century Supply Chains

Well true. Sharepoint (etc.) might help here, but that’s another issue. So basically don’t use Excel as a database. Fair enough but…

Comparing Excel to an ERP system is like comparing a Motor Bike to a Train Network. It’s just stupid. Telling people not to use Excel because it does not have the same capabilities as an ERP system is likewise really bad advice. Excel is different to an ERP system, we could easily re frame the argument the other way around and draw the conclusion that ERP system are the work of the Devil!

In fact, lets do just that.

Beware Supply Chain ERP Users—YOU are DOOMED!!!!

ERP systems are slow expensive complex beasts, with poorly documented calculation methods, inflexible font ends, and limited reporting capabilities. I recently talked to a number of supply chain professional and was shocked by how many of them are using their ERP systems in blind faith that the system are optimised for their needs. In the fast passed global supply chains of today, how can these default settings and calculation models possibly be right for your business?

Consider yourself doomed if you ever find yourself using an ERP system for any of these:

Reporting and Analysis: ERP system can out put reports in a number of formats, but typically they cant build well constructed dashboards, which are tailored to your companies specific needs, and/or ones that can be quickly adapted and changed over time as the needs of the business change. And forget it if you want to do some sort of analysis that the System Architect didn’t think you’d need to do in the 2 month he was specifying the system for your company 3 years ago. (Not that that would ever happen of course…).

Changing the model: ERP systems provide various forecasting tools, is getting the best results from them a skill or a science? Do you even know what equations are being used, it’s unlikely because this is the IP of the vendor, and what about data sources, is that data from the Spanish plant accurate, if it’s not can you do anything about it? Not all that flexible are we!

Your team just grew: Better get your wallet out…

You want someone form out side the organising to use the system: No.

You have a new data source that you’d like to add to the model: Humm, can you see where we’re going…

And so on…

In conclusion neither ERP systems or Excel can doom your supply chain and comparing one against the other as a way to high light a weakness is a logical flaw. Neither ERP systems or Excel are inherently good or bad, badly designed and/or used spreadsheets are a business risk, just like a poor quality ERP system or ones that are used poorly are a risk. Each “system” has strengths and areas where their use makes sense, and this are extremely well document. Likewise the “miss use” of Excel and the motivations for this are also well document, and note – not all of them are unreasonable.

I take exception to the two blog posts because as well as misrepresent the capabilities of Excel, they also give poor or misleading reasons as to why it’s use is dangerous, also they both take one or two valid, but already extremely well understood points, and extrapolate them to reach irrational end points.

So were doe this leave us?

Well, here are some of my thoughts on what you should do to help you avoid risks if your using spread sheets (for anything really)

  • Design you spread sheets well from the ground up (here, and here for help)
  • Understand connected or discounted data sources
  • Learn how to connect to enterprise data bases.
  • Learn about versions, and version control and try to apply it
  • Think about risk, and except it explicitly if you have to

And there ended the rant ;-).

64 bit VBA

In my last post, VBA Version 7  Daniel, author of Daniel’s XL Toolbox, commented that he had not come across anything untoward with the “new!” version of VBA, and reported, so far, all is well with his addin – good news! Then JP from Code For Excel & Outlook, pointed to a  VBA Compatibility tool (tool, info) from MS – thanks JP, I think this is called team work! ;-)

Anyway, I believe the major update might be support of 64 bit types in VBA?  I’m not running a 64 bit OS to test, but I found this:

Excel 2010 can load much larger workbooks. Excel 2010 made updates to use 64-bit memory addressing in order to move beyond the 2-GB addressable memory boundary that limits 32-bit applications.

The down side of this is:

Visual Basic for Applications (VBA)   VBA code that uses the Declare statement to access the Windows application programming interface (API) or other DLL entry points will see differences between 32-bit and 64-bit versions. The Declare statement must be updated with the PtrSafe attribute after inputs and outputs to the API have been reviewed and updated. Declare statements will not work in 64-bit VBA without the PtrSafe attribute. New data types are added to 64-bit Office 2010 VBA: LongLong and LongPtr. For more information about VBA, see the “64-bit VBA Overview” and “Declare Statement” articles in the Microsoft Visual Basic for Applications online Help in Office applications.

So if your using API calls in you VBA, and you hit a 64 bit versions, you could be about to hit some issue. Doesn’t sound like the fix will be to hard though, but we’ll wait and see eh!

Bonus thought: Does this mean the C API has been updated too?

The MIE Podcasts – Now on iTunes, and other ways to listen…

There are a few ways to listen to the MIE podcasts.

Right here on the blog, via your browser, just use the built in player that’s displayed whenever a podcasts posted.

player

Download it. Right under the player there will always be a link that can be used to download the podcast directly, then you can listen to it however you like!

downloadpodc

Get it in iTunes. Apples don’t make it easy, and the store is a bit slow to update, but it’s there if you want an easy way to get it onto your iPod etc. (Tip: search for “The Methods In Excel Podcasts”)

iTune MIE Poscats

Get it in Google. Use the RSS feed for the podcasts (http://www.blog.methodsinexcel.co.uk/feed/podcast/) and add it to you RSS reader, if your using Google  reader  you get a nice little player there too.

greadpc

Of course if you’re cool like me, then you’ll have Aandriod phone in which case you can add the RSS URL to Listener.

Office User Group.

Clearly having more spare time on his hand than me (or maybe he’s just not as lazy and bad at writing!) Simon has written up an excellent review of the UK OUG in Reading:

Just like Simon I really enjoyed the event. All the speakers came across really well, and the presentations where excellent.

Personally I found the Visio one a real eye opener and I can really see me using this product in the future. I am still far from sold on Grove and I think like a lot of other people InfoPath is a solution for a problem that does not exist. (I know it’s faster than using Excel and Access or PHP and MYSQL, but that’s only in the demos!!). Has anyone else noticed how slow IP forms seem to run on the server too? I think I was conceived that One Note might be a “once bitten forever smitten” type product, but I’ll wait for the bite.

It was first-rate to meet and speak to some of the guys from around the web well around this part of the web any-road. Particularly good to meet Simon (Murphy), Russell Proctor, Mike C, Nick Hodge and Andy Pope.

As Simon mention at the bottom of his post there is talk afoot about meeting up and going through some “proper” code, not a Northwind BD in sight, if anyone is interested contact me or Simon, or leave a comment we’re not fussy!

Inaugural UK Office User Group Event

Ok, this could be a good chance for a few of us to meet up. I’ve booked my place, have you?!

Inaugural UK Office User Group Event

Also check this out!