Spreadsheet Based Modeling

Intro and Objectives

We will spend the first few weeks of the semester building a basic foundation of spreadsheet modeling/engineering skills before diving into more advanced modeling and spreadsheet application development topics. We will start by focusing on:

  • Basic modeling principles
  • Basic spreadsheet modeling and spreadsheet engineering principles
  • Continued development of Excel skills to facilitate modeling

After completion of this module:

  • You will have a better appreciation for the wide variety and pervasive use of models and modeling in business.
  • You should be able to use basic model building principles such as simplification, decomposition, input/output identification, prototyping, parameterization, quieting the critic, working backwards, and focusing on structure instead of data.
  • You should start to understand the different things we do with models such as sensitivity analysis and/or simulation (what if?), optimization (what's best?), exploration, visualization and communication.
  • Understand and be able to use Excel concepts and techniques such as absolute and relative cell addressing, R1C1 notation, range names, Data Tables, Goal Seek, date/time values and functions, VLOOKUP(), SUMPRODUCT() and other advanced Excel functions.
  • Understand and be able to use spreadsheet design and engineering principles such as logical layout, avoiding hard coding, isolating input parameters, designing for use and maintainability, finding and avoiding errors, simplification through formula decomposition and range naming, documentation, use of dynamic visual cues, formula auditing and other testing techniques.

Readings

The readings for this module are intended to provide an introduction to basic modeling principles, get you used to the style of our textbook (which mixes conceptual material with hands on Excel implementation details), and increase your awareness of spreadsheet design and construction principles that will help you create better spreadsheet models. We will use these ideas throughout the semester.

  • If you have the 7th edition of our BA:DADM textbook, it now includes the intro to spreadsheet modeling material in Chapter 1 (thank you W&A for putting this material into your BA:DADM textbook). If not, you can use the Practical Management Science - Chapter 2: Intro to Spreadsheet Modeling pdf that we used for the Prep Problem.
  • Errors are rampant in business spreadsheet models. Check out a few of the horror stories at: https://eusprig.org/research-info/horror-stories/
  • Then to help you avoid similar mistakes, read: How do you know your spreadsheet is right?. You can also find this file in the Downloads file for this module.

Downloads

Session Activities

This is a pretty packed session, but will give us a great foundation for moving forward learning spreadsheet based modeling.

Excel technique review

Let's start by reviewing a very important spreadsheet concept - absolute and relative cell addressing. It is essential that you firmly master this concept before moving on to spreadsheet model building. The following short video will review this topic and give you a chance to practice using it. In addition, we will review two related topics, R1C1 reference style and range names. In the Downloads file you'll find a folder named Tutorials. In it you'll find the Excel Tutorial from Albright's Free Downloads page along with an older version that I've added some things to. We'll use the older version - it's called excel_tutorial.xlsm.

As you will see, these tutorial files provides a very comprehensive review of numerous Excel techniques that we will use throughout this course. Time spent reviewing this file will be time well spent. So, download the file and get it open on your computer. Then, watch the following short video and work along with it in the file provided above.

Intro to Modeling

Here's an introduction to basic modeling concepts, part 1. The slides, also available from the Downloads file, will be used during some of the video content in this module. I’ve also included the original Powerpoint slides that are used in this video. Very little has changed.

Influence Diagrams

The first modeling technique we will learn is called an influence diagram. It's a great technique for getting started on building a spreadsheet model. There are some guidelines for creating influence diagrams in the slides for this module and here's a short video on creating influence diagrams:

Prep Problems

Within the Downloads zip file for this module you'll find my versions of the NCAA T-shirts and the Bookshelves prep problems. Take a look at them. Here's a short video with my comments on my versions of the Prep Problems. In particular, I talk about the following:

The T-Shirt Problem

  • Inputs, output, decision variables
  • Basic spreadsheet design
  • IF, AND, OR
  • DON’T HARD CODE VALUES!
  • Range names
    • Readability
    • Creating, pasting, applying, Manager, scope,
  • Formatting to aid model understanding
  • Layout and documentation

In this part I mention a longtime Excel resource known as Ozgrid. Sadly, it was retired and has vanished from the internet as of November, 2025. I mention it in the context of custom cell formatting. An alternative resource on this topic is https://exceljet.net/articles/custom-number-formats. This is a technique well worth knowing.

The Woodworks Problem

  • Numeric and graphical “what if?”
  • Absolute and relative cell addressing for effective formula copying
  • Distinguishing base input cells
  • Row vs. column orientation for outputs
  • Charting basics

The following screencast illustrates the points above.

Building and using a break-even model

Now we will build a simple break even model and use it to do some sensitivity analysis and "backsolving". Our overall approach will be:

  • Plan general structure and format of model
    • use influence diagrams for logical structure
    • blank spreadsheet like a “blank canvas” – plan the physical structure
  • Enter inputs (parameters) and decision variables
  • Develop relationships between them via formulas to the model outputs
  • Then we can “exercise the model”
    • use it to explore situation of interest
    • do sensitivity analysis

This is Example 2.3 from the PMS Ch2 PDF (or Chapter 1 in 7ed of BA textbook). However, I demonstrate a number of additional techniques not covered in the chapter and also try to relate the model building process to some of the modeling building hueristics discussed earlier in this lesson. I've broken up the model building session into a few separate videos. You'll need the Ex0203-BreakevenAnalysis-template.xlsx file included in the Downloads zip file for this module.

In the first video, we get an introduction to the problem, start to build the model using a template, enter and format the base inputs and learn about defining custom styles.

In the second video, we build the model and learn about range names and model verification techniques.

In the next video, we do some sensitivity analysis using a one way data table.

In the third video we find the break even point using Goal Seek and see how we can automate the process by recording a simple macro. Simple conditional formatting is also demonstrated. We also do a little graphing to visualize the break even point and the form of the profit function.

Finally, you should figure out how to create a 2-way data table to see how Profit is impacted by the combination of values of two base inputs such as response rate and the fixed cost.

More modeling heuristics and spreadsheet design tips

The rest of the slides in the slides_sbm.html file have several more modeling heuristics and a summary of spreadsheet design tips (many of which we've seen in action in the Break Even model). Take a look at these as they will prove helpful throughout the semester.

Explore (OPTIONAL)

As someone who learned spreadsheeting on Version 1a* of Lotus 1-2-3, I have a fondness for the history of spreadsheets. Life was simple, just do /rnc and you can make a new range name.