Operations Management & Spreadsheets - Databases

Updated: Nov 13


I used this picture in a presentation but do not know the source - please email bruce@bkaufmann.com so I can give proper attribution if you know who created this.


In this post I want to discuss why my clients get stuck when they try to create a management report using a spreadsheet program like Excel or Google Sheets. The short answer is they are using the spreadsheet like a sheet of paper instead of like a database.


On its face, a management report appears to be only rows and columns of numbers, which are manipulated by mathematical operations to produce a result that indicates how well, or how poorly, the company is performing.


You can recreate a report like this by taking a sheet of paper, putting numbers in rows and columns, doing the math, and getting the results you need. Instead, you use a spreadsheet to do this because it is much faster and - usually - more consistently accurate.


But as the business grows, or you try to keep track of more factors, or your report has to draw data from more data sets, the Spreadsheet As A Sheet of Paper method becomes unwieldy. It takes too long to update and is too hard to modify when changes are required. You get stuck. What was once a useful tool is now so frustrating to use that you either get stressed every time you use it or you just stop using it altogether.


What happened? Why did a useful, simple tool become so hard to use? Because using a spreadsheet to add rows and columns of numbers is different than using a spreadsheet to collect data for the purpose of creating and periodically updating management reports.


When you use a spreadsheet to add rows and columns of numbers, you are using the spreadsheet like a sheet of paper.


When you use the spreadsheet to collect and store data that will be used to create, modify or update a report, you are using the spreadsheet like a database.


Let's look at the difference between using a spreadsheet versus using a database to create a management report.


Definition


Spreadsheet – An electronic document in which data is arranged in the rows and columns of a grid and can be manipulated and used in calculations. A spreadsheet analyzes data.


Database – A structured set of related data that can be accessed quickly. A database stores data.



Types of Databases


A database is not a spreadsheet, but a spreadsheet is a database. There are essentially three types of databases.


1) Flat-File Database - Data is represented as a single table of rows and columns.


2) Relational Database - A collection of flat file databases, called tables, that are related to each other by a common data point. There is no set structure as to the arrangement of tables, but each table must be related to at least one other table through a common data field.


3) Hierarchical Database - Files are related to each other in a parent/child relationship. The structure of the database resembles a tree.



The Spreadsheet as a Flat File Database


A spreadsheet is a flat file database. The format is the same as a table that would be used in a relational database.


Each column in a spreadsheet represents a field, which contains only one data point, and which can only be one specific type of data.


Each row in a spreadsheet contains a collection of fields that, together, contain information on a unique transaction. In database language, each row is called a record.



Numbers


In a spreadsheet numbers are numbers. Yes I know they can be formatted as text, date or time, but they are still just numbers.


In a database numbers are a data type. Before you start storing any data, you have to specify what type of data a number is. It can be any of the following:

  • Integer

  • Character

  • String

  • Floating point number

  • Array

  • Null

  • Variable

  • Character

  • Boolean Value (0/1, True/False, Yes/No)

  • Date

  • Time


Process


In the case of using a spreadsheet for math, the process is simple and very much like using a sheet of paper. The spreadsheet merely serves as a means to perform the task more quickly and with less chance of error.


In a spreadsheet, where you essentially tabulate data, you don’t have a lot of rules because math allows you to add and multiply in any order. There are rules that must be followed if your process involves division, subtraction, or a combination of arithmetic functions.


However, creating management reports involves more than just putting numbers in rows and columns and doing the math. There are many steps required to consistently and easily produce an effective management report:

  1. Set up a place to store your data (a database).

  2. Establish what type of data will be in each field in the database - integer, date, time, Boolean, for example.

  3. Collect data.

  4. Verify the data is correct.

  5. Store the data in the database.

  6. Retrieve the data.

  7. Place the data into a pre-defined format (the report).


To summarize, clients get stuck when creating management reports because they need to use the spreadsheet as a database but they do not know how to do so properly. To correctly and effectively use the spreadsheet as a database, the data must be placed in the spreadsheet in a very specific way. Otherwise the client will either:

  • Be able to produce the reports he needs but will find that it is increasingly difficult to scale, i.e., add to the dataset and/or

  • It will take increasingly longer to update the management report

8 views