Now this is interesting. I remember searching a few years back for spreadsheet design patterns. That combination of keywords never led me to the Best Practice Modeling Standards upon which BPM – a company founded in 2002 by ex-Salomon Smith Barney investment bankers – provides its modeling products and services. The base version is apparently maintained by an independent Spreadsheet Standards Review Board:
Spreadsheet Standards Review Board Charter
- To promote the Best Practice Spreadsheet Modeling Standards to ensure that the Best Practice Spreadsheet Modeling Standards are recognized, used and accepted as the highest professional spreadsheet modeling standards in the world;
- To develop and maintain the Best Practice Spreadsheet Modeling Standards;
- To bring together global Spreadsheet modeling skills in order to develop and maintain the Best Practice Spreadsheet Modeling Standards;
- To facilitate and manage participation of interested parties and the general public in developing the Best Practice Spreadsheet Modeling Standards; and
- To evaluate proposals to add, delete or modify the Best Practice Spreadsheet Modeling Standards.
You may remember the day you were introduced to Excel’s pivot tables. It might have happened through a co-worker, a book or an online tutorial but the effect was probably along the lines of: “Wow! How did I ever analyze data without them?”
Well, I sort of went through the same experience again several months ago reading about Dermot Balson’s Merge pattern (sample file here) – thanks to a post by Jim Johnson. I would love to elaborate some other time on Dermot’s idea of applying the concept of design pattern to spreadsheets but I will delve here specifically into his merge pattern, which makes use of Excel data tables.
According to Microsoft, data tables allow you to “test different input values for a formula without having to retype or copy the formula for each value” (Q282852). This is typically useful for sensitivity analysis, but it also works great for running complex calculations against multiple records in a data set.
I’ve argued before that most (structured) data benefits from being stored in a a flat file format. One of the downsides of doing so, however, is the amount of work involved in running a complex Excel model against all rows in a flat file. If you don’t intend to migrate your model to a relational or multidimensional database, then data tables as a wonderful way of achieving just that – free-form spreadsheet computations applied to structured data.
Additional links of interest
Happy Excel modeling!
You’ve probably noticed that you shouldn’t trust this blog for real-time news tracking. The following are essentially timeless, however, so here we go…
Rob van Gelder (of DailyDoseOfExcel fame) shared a tip back in May on how to build a simple Gantt chart in Excel. I’m posting a link here because it’s the easiest I’ve seen so far.
Jeff Smith exposes his Golden Rule of data Manipulation over at sqlteam.com. While he elaborates on his statement from a programmer’s standpoint, it’s all applicable to knowledge workers and spreadsheets:
“It is always easier and more flexible to combine data elements rather than to break them apart”
From a data analysis standpoint, Jeff’s examples are essentially related to what I would call attributes (such as phone numbers). His rule still holds true with values, though. As you work towards summarizing a data set (say, daily financial transactions that you want to analyze by month), you’ll want to aggregate values as late as possible instead of running the risk of losing valuable information by aggregating too early. Spreadsheet programs hit a limit between 65k and 1M records, but there are tools to take it from there – which brings us to Paul Steynberg’s advice for considering OLAP tools as part of a financial system manager’s toolbox.
OLAP technologies are particularly well suited to handling large amounts of data. I personally share Paul’s opinion of Microsoft’s SQL Server Analysis Services, which I would describe to the non-initiated as Excel on steroids. On lots of steroids, that is. SSAS gives you access to summaries and advanced computations based on millions of underlying records, usually responding in just a few seconds.
EDIT (Sep. 10, 2008): Using a definition as crude as “Excel on steroids” for SSAS left me feeling a little guilty. I’m over it now, having just read Andrew Fryer’s post on business intelligence for small business;-)
Thanks to Dick Kusleika‘s article on EuSpRIG 2007 and after browsing the latter site a bit, I located a couple interesting papers I want to share here. These are full of good high-level tips related to spreadshet design:
A recent post at Accounting Mechanics suggests accountants should do anything necessary to fill the gap between Finance and IT. While I fully agree accountants are in a better position than IT specialists to provide useful management information, I can think of a few issues with that positioning, though:
- Old-school finance people might see you too much like an IT resource
- IT will most definitely see you as competition;-)
- The added value is obvious once you’re part of an organization and start to deliver results, but it’s difficult to “sell” that particular skill set without sounding too much like “an IT guy”
- You run the risk of ending up maintaining systems instead of using them to answer the very questions you built them to find answers to in the first place
Still, there seems to be quite a few of us out there tackling those issues and getting involved with a whole set of typically IT-only technologies. Feel free to let me know of any experience you wish to share in the comments.
Posted for future reference. This article by Jeff Smith “covers different ways to create sequence numbers. It starts with the basic identity and GUIDs and quickly moves to much more complicated sequence numbers including those with auto-incrementing mixed letters and numbers such as “A0001″, etc.”
I just happened to discover the Accounting Mechanics blog which focuses on “tools and techniques of the management accountant” and happens to have a link back to my own blog. I’ll catch up with posts there as soon as possible since there seems to be quite a few interesting articles. I’ve already spotted the following:
There’s also a link to another potentially interesting blog, that one focusing on “professional spreadsheet development stuff”.