middleRight - special interest

Of Special Interest...

While the main theme and emphasis of Excel Best Practices for Business is all about providing practical techniques and guidance for using spreadsheets in the business environment, it also brings into sharp focus items of interest to specific audiences. I list some of these items below.

There are two files on the CD-ROM that you should turn your attention to. One of these is the readme.xls file. On this one spreadsheet is a listing of all 75 spreadsheets on the CD-ROM, a brief description of their purpose, and anything you need to know about in using or launching them. Also, where appropriate, hyperlinks on the file will allow you to directly launch most of the files. Also take a look at the takeway.txt file.
VBA Programmers will find the Abstraction Layering pattern a useful way to write macro code where all the variables and parameters are external to the macro code itself. This can help to make code maintenance a lot easier. You will find a detailed application of this technique in Chapter 13 (Assistive Technologies).
Those of you who are working with spreadsheets that incorporate uncertain estimates will find the section on Quantifying Uncertainy (Chapter 8 - Data Analysis) to be of relevance. It involves a technique called Addition in Quadrature. I knew it was bound to happen. It seems no book, especially those with formulas, can ever be published in the first run without having a correction. In Listing 8-2 on page 217, the squared expressions involving the deltas are missing X and Y denominators. I produce the correct version of the listing below:
Some of you may long to do anything with Excel that works in conjunction with Java. Well, you're in luck. There is a nice amount of material on the CD-ROM which allows you to have Excel speak back and forth to an XML-RPC Server runnning from your desktop. You should explore the Java source files on the CD (further information can be found on the takeaway.txt file). Also, take a look at how you can have Excel issue a POST instead of a GET request, all the while using XML instead of traditional Forms data.
Those of you with interests in the financial arena may want to explore several things of value in the book and on the CD. As mentioned earlier, the section on quantifying uncertainty has important applications in financial analysis and risk management. There is also some useful information on applying uncertainty analysis at the end of Chapter 11 (Spreadsheet Auditing: Challenging the numbers of others).
If you need to put some real muscle in your financial analysis, then stay tuned -- I will be adding some info on using Excel with Monte Carlo simulation tools and Mathematica (By the way, a Trial Version of Mathematica Release 5 is included with the book CD-ROM).
Those of you whose interests veer more towards financial accounting or forensic accounting, may want to take a moment to explore the issues of Off-Spreadsheet Analysis. I show how to use state transition analysis to work around challenges when others supply you with information you cannot readily verify just by examining spreadsheets and financial statements you are given. All this can be found in Chapter 11. Also, I thought you might find it handy to have the Sarbanes-Oxley Act in its entirety. I've included it on the CD-ROM.
On the lighter side of analyzing financial information provided by third parties, some of you may be tasked with having to convert financial statement information in PDF files into spreadsheets. While copy'n pasting numbers, and then hand editing does work; it is laborious, sloppy and is definitely prone to error. The beginning of Chapter 4 provides you with a tool to control the process of translating such information with far greater control and fidelity, and allows you to take information from several similar documents, like financial statements for different periods, and transform them to the exact same presentation format; taking into account currency conversion.
If you are tasked with repeatedly analyzing updated business information and issuing revised reports every week, be it sales analysis, incident tracking, reissuing budget estimates, or whatever; then there are some tools for you to explore in Chapter 6. You may also want to look at some new features relating to Filters for Excel 2003. Additionally, there are some nice worked out examples of Advanced Filters that has Excel performing some definte bench pressing.
Those of you who routinely rely on PivotTables for financial analysis that may involve grouped data should be aware that Excel can unexpectedly reclassify items from one group to another based on data that is fed into the PivotTable. There is a potential for financial statements and analyses incorporating PivotTable information to materially misstate grouped data. All this is explained in Chapter 4.
If you need to convert and clean up lots of legacy data, then Chapter 5 (Scaling the Peaks of Mt. Data) will introduce you to the art of "data slogging" and show you practical techniques of how to clean up tons of data.
There's definitely some very interesting things going on with designing spreadsheets that are accessible to individuals with blindness. A new innovation introduced here is the use of an Assistive Portal that makes accessible the full capabilities of a complex spreadsheet to individuals with special needs.
If not anything else, one of the most important contributions made by this book is an elegant and effective methodology to prepare and maintain very large and complex spreadsheets. This approach, called the layered pattern is something you need to learn. Once you start adopting this style, it becomes addictive when working with large and complex spreadsheets. The basic concept is introduced in Chapter 2 (Spreadsheet Construction Techniques), but is put to use, chapter after chapter. I guess I really do practice what I preach.

Anyhow, I hope this book will prove as useful for you, as it has been fun for me writing it.