Back to BASIC

As an Assistant Professor and professional trainer, I always look for exciting technologies to work with.

I recently started delivering a class on building business Models in MS Excel. It is based on the best practices for building Excel models laid out by Read and Batson in 1999. (Read and Batson, 1999). Read and Batson outline a six-step process approach to creating an excel model.

Scope→ Specify→ Design → Build→ Test→ Use

And 6 Au rules to follow.

1. Separate Inputs, Calculations & Results
2. Use one formula per row or column.
3. Refer to the left and above.
4. Use multiple worksheets.
5. Use each column for the same purpose.
6. Include a documentation sheet.

They created this best-known method as an IBM survey found that many excel modellers had no formal training in good modelling techniques and that their organisations did not even have the most basic standards for creating excel models. Sound familiar?

The attraction of excel is that it is cheap and ubiquitous, and most users are familiar with it. Excel is a common language in data processing that covers the whole world, from corporate behemoths to one-person pico companies.

So lately, I have been dipping my toes into VBA, visual basic for applications, and I am impressed. VBA has come a long way since I first attempted to write programs in BASIC back in the 1980s.

VBA is a programming language developed by Microsoft that is built into Excel and can automate tasks and create macros. It is based on the BASIC programming language I learned in the 1980s and is set within MS office applications.

A macro is a collection of commands performed in a set order. Many ambitious excel modellers think they need to be proficient in macro development to become an excel modeller. Though it's not a bad idea to know how to create and use macros—and the language they are built in, Visual Basic for Applications (VBA)—it's certainly not critical for an excel modeller to be a talented VBA programmer. (Fairhurst, 2019)

So, I have opened that Developer tab in excel, and I will be getting back to BASIC.

Do you use macros in excel, and what stories do you have?

Disclaimer

A human being wrote this article.

References

Fairhurst, D.S. (2019) Using Excel for business and Financial Modelling: A practical guide. John Wiley & Sons.

Read, N. and Batson, J. (1999) Spreadsheet modelling best practice. Institute of Chartered Accountants in England and Wales London.