1
Conceptual Maps and Models
Learning Objectives
ā¢Use visualizations of a situation to sketch out the logic for a model.
ā¢Translate the visualization into a spreadsheet model.
ā¢Develop good procedures for building and documenting spreadsheet models.
ā¢Appreciate the prevalence of errors in spreadsheets and learn how to prevent them.
1.1 Introductory Case MoviePass
MoviePass is (as of January 2019)1 a subscription-based service offering discounted pricing for seeing movies in theaters. It was founded in 2011, initially charging $50 per month. It has gone through several pricing iterations, at one point offering unlimited admission to one movie per day, for $9.95 per month. That pricing caused a surge in subscriptions: from 12,000 in August 2017 to more than 3 million in June 2018.2 The wildly successful growth in subscribers was not matched by business success, however. Losses for 2017 were reported as $150.8 million, up from $7.4 million in 2016. While new ventures often lose money, the financial position of MoviePass seems extraordinarily shaky. The stock price of its owner, Helios and Matheson Analytics, Inc. (HMNY), had dropped to .0158 per share as of December 21, 2018.
The business case for MoviePass is typical in that uncertainty plays an important role in its financial picture and prospects. MoviePass charged its customers the $9.95 per month for virtually unlimited admissions, while it paid the theatres their full admission prices (averaging $8.97 in 2017) as compensation. Profits depend critically on how frequently its subscribers go to theatersāand even though the company likely had done market research to understand this frequency for the people that would buy the MoviePass, their actual behavior and frequency of visiting the movies was essentially uncertain.
Average movie attendance in the overall US population in 2017 was 0.77 movies per person per month (based on total box office revenues of $12 billion and 1.3 billion admissions for the year). Thus, if MoviePass subscribers view movies at the same rate as this average, there would be a āprofitā margin of over $3 per subscriber per month ($9.95 ā 0.77 * $8.97 = $3.04), which might cover its other operating expenses (e.g., marketing, corporate overhead, etc.). Additional profits were anticipated to be generated by obtaining discounted pricing from theatres and monetizing data collected from its subscriber base. But, the business case depends critically on the admissions behavior of its subscribers, and since MoviePass didnāt know yet which population would subscribe, and if and how they might change their movie-going behavior after becoming subscribers, this was subject to uncertainty.
This business situation is typical in several aspects. Myriad factors can influence what the right pricing decision is, and a model can be useful to provide insight, even though, by definition, a model is a simplification of reality and there are lots of uncertainties that affect the results. In order to provide usable insight for the decision problem, the analyst must decide which factors are important to include in a model and which can be ignored. In this case, the decision concerns the MoviePass subscription price, and we will ignore factors that are not directly germane to this decision (pricing decisions can sometimes be viewed as optimization problems, which is a subject weāll consider in Chapter 7). The value of a spreadsheet model3 typically lies in its capability to perform āwhat-ifā analyses to inform decision makers (Monte Carlo simulation, introduced in the next chapter, can be thought of as what-if analysis āon steroidsā). For example, we would like to estimate the impact of changing the subscription price from $10 per month to $20 per month. But that requires estimating how the price increase would affect the number of subscribers, and how frequently those subscribers would attend movies.
How do we begin constructing such a model? The answer is that we start with a visualization of the problem.
1.2 First Steps: Visualization
Since the integrity, and therefore usefulness, of a spreadsheet model depends on having a clear conceptual map of the appropriate relationships in the model, the question is: Where do you begin? Every spreadsheet model at some point begins with a blank spreadsheet, and the novice modeler often begins with a blank stare. The answer, however, is not to begin in the spreadsheetāit is to start on a piece of paper. It is best to create a visualization of the model you want to build. Various authors have given these visualizations different names: influence diagrams (Cleman and Reilly 2010), influence charts (Powell and Baker 2009), mental models or visual models (Ragsdale 2007), or causal loop diagrams (Morecroft 2007). The intent of these tools is identicalāto visualize the parts of the model and their relationships. We will refer to any of these as visualization tools, and they are a critical first step in model building. Indeed, if you are careful in constructing your visualization, as it can serve as a blueprint of your model and can almost automate the building of your spreadsheet model.
There is no standardized form to these visualization tools. We will use a variant of the influence chart included in Powell and Baker (2009). The diagram distinguishes between...
