How to Use Spreadsheets to Make Decisions

How to Use Spreadsheets to Make DecisionsMaking decisions when the situation is simple or the tradeoffs are not overly difficult is easy. We all do it all the time. But when many parameters must be considered and when the consequences of a wrong decision can be painful, then decision making can become a frightening chore. We can dither about and agonize over which option to take, or blindly select one. We do this to make a decision, any decision, and eliminate our mental anguish. Is there a better way that is cheap to implement and easy to use? Yes, and the best part is that you probably already have it on your computer. It is called a spreadsheet. You might not think of spreadsheets as tools for decision making, but by laying out data in an organized way, that is exactly what they do.

One of the most valuable tools for making financial decisions ever developed is the common spreadsheet. It is so valuable that long before computers existed, highly trained specialists worked with large sheets of paper, pencil, and erasers making and modifying spreadsheets. After setting up a model for the operation of a business, they would try “what if” scenarios by changing various parameters and manually propagating the changes through the spreadsheet by erasing and updating entries as they went. A typical paper spreadsheet might take up a whole drafting table. It might have several sheets that need to have data copied from one to another. Changing a single parameter to see “what if” could take hours and be prone to errors. It was mind-numbing work, but the rewards were worth the effort. A financial spreadsheet is a model of the state of the business at a point in time. To predict the future and make operational decisions, one need simply change some of the parameters in the model such as the total sales or the cost of materials and propagate the results of these changes through the spreadsheet to see what the effects of the changes would be. Based on past experience and the results of several “what if” computations, a decision can be made about how to direct the business.

Paper spreadsheets are too cumbersome for normal use. To make the power of spreadsheet available for a broader range of activities, we needed personal computers. Early applications such as VisiCalc and Lotus 1-2-3 were developed so everyone could make spreadsheets and take the drudgery out of the computations. While these early applications were popular, today most people use Microsoft’s Excel. [Note: LibreOffice’s Calc spreadsheet has some features lacking in Excel. These can be important for logic design.] But for some reason, many users of Excel do not think of it as a decision-making tool. They will use it for organize lists and maybe make a budget or a calendar, but that is it.

One problem is that using a spreadsheet effectively is not intuitive. I have several books on them and am still learning. By no means do I consider myself an expert, but the beauty is that you do not have to be an expert to benefit from some simple spreadsheet apps. There are ways to get started easily without investing a lot of time learning the ropes. For those who are not familiar with setting up a spreadsheet, Microsoft has provided tutorials and hundreds of premade templates that can be downloaded and used as is or modified to meet your needs (see here). However, be aware that some of the listed templates are free and others are commercial products. Also be aware that I am not able to easily download selected templates using Firefox. Internet Explorer does it easily — strange how that works. For those of you who prefer LibreOffice, I have good news and bad news: good news — they have a premade template download site (templates); bad news — it is not very well-populated compared to the excel offerings. Extra good news: all template submissions are under the open document protocol.

There are many ways to get started using spreadsheets for decision making. If you really want to get a full-up decision-making application ready-made, it will cost you big bucks (e.g. Treeage, among others ). These are for specialists or professionals, but they are fun and educational to use. However, there is an excellent alternative that will appeal to the do-it-yourself types. There’s a great white paper available online entitled Decision Making Using Excel. It will introduce you to using Excel as a decision making tool with complete screenshots and descriptions. I suggest at least skimming through it.

Another level of entry is to buy a book which comes with templates and explanations. These can also be pricey. Consider searching on author S. Christian Albright. Some commercial books are good, but many of the templates used in them are available online — both his and others. An example of what is available can be found at decisions. These come with certain limitations, but generally are free for non-commercial use.

This is a very rich subject that cannot be covered in depth in one blog post. I hope this introduction will inspire some brave souls to try something new. If so, and if a dialog develops, perhaps I can present some specific examples with screenshots of what can be done easily. The main thing to keep in mind is to avoid being overwhelmed by the options and strange nomenclature and stick to some simple applications at first. Using spreadsheets and premade templates is more like learning a new language rather than doing rocket science. Once you understand the terminology, the rest comes naturally.