BXL Modeling Methods

Post date: May 17, 2016 2:08:39 PM

I plan to publish BXL's modeling methods soon. At right is a sneak peek at what those methods produce.

I think its time to introduce something like this, something that embraces Excel's new features and computer science.

Computer science has some long standing, proven best practices that I have not seen in XL discussions. I look at XL from a computer science perspective, which is rare because many IT professionals view XL as anything but a development platform. But that is precisely how I see it. XL has GUIs, processes, database access, publishing functions, programmable events, procedural language, APIs, and a whole lot more. While it is not appropriate for all applications (no development platform is) it is appropriate for a broad range of common business, scientific, and engineering applications. So if we treat XL as a development platform, how would it differ from existing XL community standards?

Change Management

First and foremost, change management is missing. The vast majority of XL users start XL, create our models and hand them to others without any thought of testing, auditing, or protecting calculations. And we wonder why headlines proclaim "88% of Spreadsheets have Errors". Simple question, "How did they find the errors?". Simple answer, "Somebody looked." If the XL community employed the simple, basic concept of having someone else audit their work, those errors would have been found and remedied BEFORE being published.

Embrace Advances

This is no surprise. Many in the XL community are from the finance world which, for good reasons, is about as conservative as it gets. But those from the computer science world know technology changes rabidly (intentional misspelling). XL is no exception. The most dominate XL standards don't use anything XL introduced in the last decade. Yet these XL advances address two critical components for the finance community: RISK and TRANSPARENCY.

Segregation of Inputs, Process, and Outputs

The computer science world has understood the benefits of multitier architecture since the 1980's. XL's architecture blurs those lines. Input, process, and output can easily be one and the same. That's okay as long as models aren't used by others. As soon as we give our models to others we need to protect our processes if we want to keep errors from being introduced. Thus, INPUTS must be open, and PROCESS must be closed.

Integration

Technology is morphing more and more from isolated islands to global connections. Many of XL's advances are geared towards connecting to data from the outside world and to outside users. Data integration can eliminate user entry making our models less risky. PowerQuery makes that easy. Publishing XL results to end user devices anywhere in the world makes our models more useful. PowerBI makes that easy.

Summary

BXL's modeling approach is different. While accuracy is key for all methods, BXL focuses less on presentation and more on how to make models more efficient, more transparent, less risky, more flexible, more user friendly, and better connected. BXL embraces XL's new methods which requires skilled modelers. BXL modelers should have a working understanding of XL's:

- Financial, Date and Time, Statistical, Lookup & Reference, Text, and Logical functions
- Filters/Slicers and Sorting
- Formatting and Styles
- Data Validation and Worksheet Protection
- Names, Tables and Structured References
- PivotTables
- Conditional Formatting and Charting
- Get External Data, and Power BI.

BXL's methods are for serious modelers only.