Content will be updated from time to time, so please refresh frequently.1
This course formulates and solves problems to inform decision-makers within organizations using simulation and optimization. Students will develop the skills and practice the techniques to structure and analyze a wide range of complex business problems to inform and support managerial decision-making in functional business application areas such as finance (e.g., capital budgeting, cash planning, portfolio optimization, valuing options, hedging investments), marketing (e.g., pricing, sales force allocation, planning advertising budgets) and operations (e.g., production planning, workforce scheduling, facility location, project management). Spreadsheets are used to assist in modeling, analysis, and communication of results and findings.
The premise of this course is that all decisions arise from some sort of change in the organizational eco-system, internal and external. When change occurs we must learn what to do next. But learning is inference. Learning can be reading, discussing, understanding, reflecting whether in our heads or with complex computing environments and always connected with relevant others in and outside of the organization.
We begin with the following chain of reasoning:
In general there are two kinds of intelligibility (we might feature this as modeling): deterministic (what certainly has happened) and statistical (what we don’t know, haven’t modeled, can not as yet, or ever, observe).
All events, and data collected from events, have a truth value. Probability is the strength of plausibility of a truth value.
Inference is a process of attaining justified true belief, otherwise called knowledge; learning is inference. But it is of two kinds: deterministic (deductive: if \(x=2\), then \(x+2=4\)) and statistical (inductive and abductive: if it is plausible that \(1 \leq x \leq 3\), then it is plausible that \(x+2=4\) because \(3 \leq x+2 \leq 5\)). We might even call plausibility a variant of reasonability.
Justification derives from the strength of plausibility, that is, the probability distribution of a hypothesis conditional on the data and any background, prior, and assumptive knowledge.
The amount of surprise, or informativeness, of the probability distribution of data given our experiences, is the criterion for statistical decision making – it is the divergence between what we known to be true and what we find out to be true.
All decisions are choices, and there must be two choices. One choice is at least just stand pat, endure the current situation. The other choice is at least to do something different than business as usual. Thus doing nothing means it is plausible that this choice is somehow reasonable, based on justified true belief.
All statistical analysis, reasoning within analysis, and informing of decision alternatives, begin from a disturbance in the status quo. The disturbance is the outlier, the error, the lack of understanding, the inattentiveness to experience, the irrationality of actions that is the inconsistency of knowledge and action based on knowledge. The disturbance can also emanate from outside the organization, as in a major competitive or regulatory or moral disruption.
We are surprised when the divergence between what we used to know and what we come to know is wider than we expected, that is, believed to be true. The analysis of surprise is the core tool of this course. In a state of surprise we achieve insight, the aha! moment of discovery, the eureka of innovation.
The course will boil down to the statistics (minimum, maximum, mean, quantiles, deviations, skewness, kurtosis), the probability that the evidence we have to support any proposition(s) we claim, and the consistency of those claims with decision alternatives. Some notion of a best of currently possible worlds will break into a plausible range of potential decision outcomes. Our choices will then need to transcend the decision model and include the data of priorities and tolerances for risk. The lack of consistency will arise as a bias in the decision, decision maker, as well as the decision making process.
We seek evidence, the strength (for example in decibels, base 10) of our hypothesis or claim to inform a decision. The measure of evidence is the measure of surprise and its complement informativeness of the data, current and underlying, inherent in the claim, and thsu the informativeness inherent in any decision.
At the end of this course students can expect to demonstrate progress in meeting the following goals, proposed here as actions with verbs in the imperative mood.
Pose a researched business question, model the causal influences implicit in the question, simulate potential causal relationships and counterfactual inferences, and align inferences with decison alternatives and their plausible choices.
Deploy analyses which produce interactive analytical products using an industry-grade computational platform engineered according to a tradition of design principles.
Using distributional analysis summarize experience and beliefs about data and using multi-level linear and non-linear models analyze the processes that the generated data used to infer potential outcomes to answer business questions.
Practice quantitative critical thinking skills through statistical problem solving.
Understand the role of the analyst and the analytics process in the decision-making context of complex organizations and their environments.
Communicate analytical decision results to decision makers and other consumers of analytical products effectively using interactive tables and graphs.
Learning outcomes from this course are strongly coupled with the programming goals of the School of Business:
Gain experience and expertise in analytical decision making
Develop an understanding of leadership
Demonstrate an understanding of ethical issues in business
Demonstrate an understanding of organizations and the competitive environment
This course will support the attainment of these goals through various group and individual activities throughout our time together this semester. Assignments and other evidence of your work and performance in this course directly align with these goals.
There are two required texts for this course available at on-line sources.
William G. Foote 2023. Informing Decisions Using Spreadsheets , from LeanPub. You will have to register at LeanPub to access this text. This online book provides the thinking behind the models and their implementation in spreadsheets. As we proceed through the course this text may well be updated to meet your needs.
Wayne Winston. 2021. Data Analysis and Business Modeling (Office 2021 and Excel 365). Redmond, WA: Microsoft Press. for well under USD50 and as an e-book for immediate delivery to your computer. You can download the two part files also at this site (after you register your purchase). This book is an excellent reference every analyst should possess whether using Excel or not.
When reading materials in preparation for class it is essential that you take an active approach. This means that you build the spreadsheets that are being discussed in the textbook and reference book and work through them as you read. When a new method is presented, first repeat the example in the book and then try two or three new examples on your own. In live sessions, as we discuss new applications of the methods presented in the notes and the reference book, I will assume you are familiar with the material in the readings. Finally, you should expect to return to the notes and the book after class or while reviewing, in order to refine and consolidate your knowledge. Your required notebook will document your preparation, questions, and successes.
From time to time during the course I will assign articles and presentations to highlight concepts, practices, models, and approaches that have worked well for various organizations.
Several online lectures reside (quite comfortably) in my Informing Decisions YouTube channel. Read the materials, watch the videos (with others!), follow along with your own spreadsheets, each snack food if needed, take away what you need to meet your learning goals.
This course is delivered in a fully remote fashion. That means the course is online. Moodle is the primary platform to integrate the activities, schedules and communications required in the course. Live sessions will be scheduled, and recorded, for one-two hours per week and as needed all on Zoom.
The primary computer software platform will be Microsoft Excel 365 with the Solver add-in. The course platform consists of the companion files from Microsoft as well as any file in Moodle. I do not recommend the use of Google Spreadsheets, yet. They often do not support some of the more basic structures of Excel, including the charting object (plots), the pivot table object, and macro recording.
You can access free downloads of spreadsheet software and a connection to remote desktops from this link:
In any case, all workbook submissions must use the Microsoft Excel
365 platform. If you do not, then there will be come important
functions, such as, =unique(), which will be missing and
cause some of the spreadsheets to fail.
Better to illuminate than merely to shine; to deliver to others contemplated truths than merely to contemplate. - St. Thomas Aquinas, Summa Theologiae, II- II, q. 188, a. 6
Cultures will eventuate outcomes. The culture of this course is at its root collaborative. We work together to produce outcomes to meet our learning goals. Thus the very first assignment in this course is to form small groups of 2-3 to mirror ideas, frustrations, insights, dead-ends and blind alleys, and share the hopefully more than occasional success.
This course involves abstract thinking, practical decision making in organizational context, and significant interaction with technology and technique, let alone other people inside and outside of your organization or your group. You can expect that the material, content, process, and deliverables will be challenging and at times frustrating for all of us. Because the odds of success improve when you are actively engaged, we will employ three distinct strategies to help ensure your success in this course:
Frequent hands-on assignments and projects related to course modules, to help ensure active learning;
Team-based collaborative learning, to foster the exploration of diverse ideas, views, opinions and feedback and to accelerate learning {not required but recommended}; and
Individual demonstrations/presentations that explain the set-up and solution of assigned reading questions and problems, intended to create an environment where you share your thinking processes and insights with your classmates (and incidentally practice presentation skills).
A frequently used peer-learning opportunity will employ regular communication and interactions with your peers. For example, the discussion board can be a vehicle for exchanging views, questions, models, issues, and issue resolution across the class. This will allow the entire class learning community to air differences and similarities in approaches, results, and the development of spread sheet models.
Your grade in this course will depend are your aggregate performance on written case assignments, participation, and final project presentations. These components are described below.
Most professionals maintain a hand-written notebook for personal reference and to recall and to reflect on content, participants, and outcomes of meetings, both virtual and in person. The online lectures, exercises and skill-building reading questions are such meetings. During office hours I may inquire about your notebook and if it is current and reflective of the online material. You may use your notebook to scribe information gleaned from the online discussion, your own reading and reflection outside of class, and discussions with your teammates.
Grades are A-F using the following scale: A: >95%, A- >=90%, < 95%; B+: <90%, >=85%; B: <85%, >=80%; C+: <80%, >=75%; C: <75%, >=70%; D: <70%, >=65%; F: <65%, > 60%.
The course schedule is on Moodle. Here is a summary of the weekly topics.
\[ \begin{center} \begin{tabular}{ c | l } Week & Topic \\ \hline Week 1 & Spreadsheet engineering I: building simple decision models \\ Week 2 & Spreadsheet engineering II: multi-variable sensitivity analysis \\ Week 3 & Optimization I: linear programming decision models \\ Week 4 & Optimization II: multi-period planning and portfolio selection \\ Week 5 & Simulation I: primordial waiting time \\ Week 6 & Simulation II: forecasting extreme claims \\ Week 7 & Compendium: final case preparation and presentation \\ \hline \end{tabular} \end{center} \] #### Integrity, conduct, other matrers
The Manhattan College Academic Integrity Policy holds students accountable for the integrity of the work they submit. Students should be familiar with the Policy and know that it is their responsibility to learn about instructor and general academic expectations with regard to proper collection, usage, and citation of sources in written work. The policy also governs the integrity of work submitted in exams and assignments as well as the veracity of signatures on attendance sheets and other verifications of participation in class activities. For more information and the complete policy, see the Manhattan College Catalog.
Here is an example compilation of guidelines for analyst conduct that follow an analytical cycle of activities we will regularly refer to in the course. To the extent possible we will apply these guidelines to our own work together.
If you need academic accommodations due to a disability, then you should immediately register with the Director of the Specialized Resource Center (SRC). The SRC at Manhattan College authorizes special accommodations for students with disabilities. If you have a documented disability and you wish to discuss academic accommodations, please contact me within the first week of class.
Restauranteur and cuisine entrepreneur Simone Tortiere makes vegan pies of all shapes and across several culinary cultures. Her mission is simply to “feed the hungry.” To that end she wants to find a price for her products that is consistent with customer willingness to pay, vendor costs, and the cost structure of her organization. Tortiere is very concerned about highly varying unit and fixed costs and how they might affect the pricing decision.
Introductions all around. Syllabus. How to take a course like this.
Good spreadsheet practices. Not really about Excel – really about modeling decisions.
Mapping a decision problem. Building a one decision model. Living to tell the tale.
Learning how to leverage Excel to rapidly prototype a solution.
We will also explore ways to communicate our results, for example this rendering of Barbard Minto’s Pyramid Principle.
Monday 2024-08-26 to Sunday 2024-09-01 online
Live session Saturday 2024-08-31 from 10:00 to 12:00 (Eastern Time; UTC -5) on Bill Foote’s Zoom link.
We can always access course videos at this link to the Informing Decisions YouTube playlist.
View the videos for Unit 1. There are five:
Walkthrough of the reading, Chapter 1 Informing Decisions: Tortuous Pie-Making in the Sky. But really it is all about Pricing Pies for the time being. Take notes and download the workbook to follow along.
Cost model. This video covers the cost model worksheet and its relation to other worksheets.
Demand model. A simple rise over run straight line demand is revealed. Other demand models can be found in Winston.
Profit model. Scroll bars help us insert variations in assumptions into the analysis.
Price sensitivity. The whole point of this exercise is to find the profit maximizing price, here with a data table and a chart.
The files below include the presentation materials for the videos, Unit 1 - Part 1: Tortuous Pie-Making in the Sky, a set of spreadsheet modeling good practices and a workbook upon which the reading relies. All are available through the Weekly Resources link below. We will frequently draw from examples from various chapters in Winston’s reference. These are included in footnotes in the formal reading from the required course text Informing Decisions.
week1-make-a-pie-weekly.xlsx
2001-raffensberger-spreadsheets-guidelines.pdf
pricing-a-pie.pdf
Raffensberger is a good compendium of spreadsheet engineering practices in that we can apply his lists against our models. Do our models shape up? Where do they indicate single points of failure of the model to represent our concrete reality of a decision?
This week we simply dip our toes into the shallow end of the pool with a weekly profit model. Our first task is to translate the statement of decision problem into a model structure. The influence diagram helps us tremendously in this endeavor.
The Pie Pricing pdf contains a discussion of the most important part of the modeling exercise, namely, the formulation of model. Are we asking the right questions? We are if we follow a comprehensive question formation framework such as Aristotle’s four causes. Are we approaching the decision from the right perspective? We are if we follow Goethe’s whole-is-more-than-the-parts approach than Bacon’s the-whole-is-the-sum-of-the-parts approach. Goethe is good for integrating and harmonizing decisions while Bacon is sometimes good for pulling apart processes.
Read Chapter 1 of Informing Decisions: Tortuous Pie-Making in the Sky. This should take us about 15-20 minutes to skim and about 2 hours to delve into the modeling.
How to dive in? The best way is to go through the text and pictures yourself. For example, how would we rearrange the influence diagram to make it more intelligible? What would happen if we were to change one of the cost or demand assumptions, rerun LINEST() (look up LINEST in Winston too!) for example? Thus it is an active reading, with the taking of notes, the trial and error of working the underlying model by following along with the video and here the text. One takeaway from the model is the way the separate worksheets communicate with one another.
As we read, we actively reflect, and then share our experiences through three major takeaways. One of them should be what happens to profit maximizing price, and why in the model, when a unit processing cost changes? In our notebooks we would do well to map the trail of computations among the worksheets. We also have the influence diagram to help us! We should also set aside a few minutes to test our understanding.
Can we recite the three takeaways in three sentences to someone else so that they “get” what we are talking about? Second and independent opinions are the hallmark of informing decisions.
Access the Informing Decisions - Week 1 Workbook here.
The endnotes from this week’s reading in Foote reference several chapters from Wayne Winston’s compendium of business models in Excel. We can evaluate our progress simply through a set of spreadsheet practices. This week we will work with some basic spreadsheet practices: named ranges, INDEX-MATCH, LINEST, dynamic plot titles. Our challenge is to go beyond the merely mechanical use of these practices to understanding the implications of these practices in the concrete particulars of actual decision making.
This week we will focus on INDEX-MATCH and LINEST, both used in the weekly profit model. Find these chapters in Winston, and replicate the models from these chapters in one Excel workbook (also known as a spreadsheet). Upload the replicated models (all in one spreadsheet) in the Workbook.
Develop three separate comments on the usefulness of the models in solving business problems.
We use INDEX-MATCH quite a bit in spreadsheet modeling. Some might prefer VLOOKUP (or its siblings LOOKUP, XLOOKUP, and HLOOKUP). However, these simpler functions carry the weight of increased operational issues and risks. See if you can identify those risks. You may use the Raffensberger reference to great advantage in identifying these risks.
We also used the LINEST array function along with dynamic chart titles, and always using named ranges. The challenge for us is to wander through the many other chapters in Winston and find topics about which we had no idea. We will be doing this exercise for the rest of the course. Have at it!
Since last week cuisine entrepreneur Simone Tortiere discovers that she and her company is having trouble making payroll, her very efficient workforce has become much less so, absenteeism, personal days, injuries, and yes, illness, are taking their toll. Equipment does break down, but lately repairs take twice as long and perhaps not at all. Suppliers are raising prices; at the same time customers balk at prices. Feeding the hungry is getting more difficult, even when her competition is not doing so well too. She now turns to a more strategic view of her business ahead a few years partly in response to a query from a special purpose acquisition corporation which probably wants good investments at a bargain basement price.
Always about items 4 and 5 above!
We will expand on the weekly revenue, cost and profit model that allows the decision maker to find a profit maximizing price under various cost scenarios.
We will use this framework to develop a multiperiod valuation model for Tortiere’s investment against the performance of her company’s operations and markets.
We will rapidly prototype a solution we will build a well-engineered model in Excel.
We can always access course videos at this link to the Informing Decisions YouTube playlist.
View the videos for Unit 1 (Week 2 = Part 2 here). There are five:
Walkthrough of the reading: Unit 2 - Part 1 runs through what has happened qualitatively, and quantitatively, to Make-A-Pie since last time we worked together. Costs and demand have changed, not necessarily on a weekly basis. We can take notes and download the workbook to follow along. Price sensitivity. The whole point of this exercise WAS to find the profit maximizing price, here with a data table and a chart.
Walkthrough part two carries us way beyond a one-period, weekly view of this slice of the universe. We venture into the beginnings of a multiperiod planning and valuation model. We ask if Simone Tortiere’s investment is at risk? In this environment there are acquisition predators looking for bargain basement prices for otherwise healthy companies.
Walkthrough of this unit’s case study features a leasing problem for a mobile solar energy company. Here mobile does not mean a smartphone app! Why a solar energy company? Simply to jumble up our examples to help us evaluate if we “get” the main takeaways from these exercises in decision modeling. We still our only trying to understand a single decision, pricing, made at a single point in time, but with slight variations to the Make-A-Pie decision. One way to imagine this case is to think about a strategic investment by Tortiere in renewable energy, especially as energy grids become more brittle, less reliable, and thus more risky to her food-production operation. Utility professionals call this the minimization of the Lost Load and use a metric call the Value of Lost Load to help them understand the tradeoffs.
Other videos of encouraging topics will appear. Stay tuned!
As an exercise we might make a version 2 of the workbook model based on the meanderings of the instructor in these videos.
The files below include the presentation materials for the videos, Unit 2 - Parts 1, 2 and the Solar Case: Creating Order out of Chaotic Decisions? or is it the Decision Makers?, Included are a set of spreadsheet modeling good practices and a workbook upon which the reading relies. All are available through the Weekly Resources link below. We will frequently draw from examples from various chapters in Winston’s reference. These are included in footnotes in the formal reading from the required course text Informing Decisions.
2005-howard-matheson-influence-diagram.pdf
week2-make-a-pie-weekly-annual-v1.xlsm
The Howard-Matheson article might take a bit of time to digest (lest we get article-reading indigestion!). Essentially we can make an influence diagram and transform the directed graphs of vertices and edges into a decision tree, and vice-versa. The math works both ways. The decision tree will require an additional tree based on the uncertainty facing the decision makers. We will begin to get at this rather thorny problem later in the course when we encounter Bayesian decision making and Monte Carlo simulation.
Speaking of simulation, the xlsm model implements a foundational technique, sensitivity analysis. From a probabilistic point of view, sensitivity analysis assumes that all inputs, here filling cost and prices, are equally likely. How probable is that? (This is the Prior Probability question!) Not very, but it is a necessary start. Operationally, the sensitivity analysis allows us to review depth, height, and width of the model, the basic geometry of the decision. By moving the slide bars on the dashboard worksheet (tab) we can pose simple what-if questions and view the results. We can manipulate the model further by changing the minimum slide-bar settings in the settings tab (worksheet). It might also be interesting to attempt to map the decision tree buried inside of the influence diagram.
We should always have the Week 1 Raffensberger spreadsheet practices at our beck and call. As we proceed with our model-making, we might add a recommended practice or two to his list.
Read Chapter 2 of Informing Decisions: Chaotic Pie-Making in the Sky? It should take you about 15-20 minutes to skim and,again, about 2 hours or so to delve into the modeling. How to dive in?
The best way is to go through the text and pictures yourself. For example, how might we incorporate a multi-period model into, or perhaps, alongside the influence diagram? How would we align the model logic (in algebraic form) into the diagram? What would happen if we were to align the upper and lower bounds on the processing cost regression with the slider settings, or with the 2-way data tables? Review the slider settings and reset them?
We must be sure we understand the power of INDEX and MATCH. Map the interactions of the various worksheets. Thus read very actively: with the taking of notes, the trial and error of working the underlying model. One takeaway from the model would note the way the separate worksheets communicate with one another. Another might relate to the range of possible movement in the demand parameters and their impact on the decision. Yet another might point to questions which this model might not be able to answer.
As we read, we actively reflect, and now will formulate our share with others three major takeaways. Adding to our list above, yet another one of them might be what happens to profit maximizing price, and why in the model, when a unit processing cost changes? In our notebooks we could do well to map the trail of computations among the worksheets. We also have the influence diagram to help us! How do the sliders work (check what’s underneath each of them? Why use a settings worksheet (see Raffensberger) Then there’s the magic of INDEX and MATCH (see Winston).
Read the (very short) Chapter 3 Informing Decisions Case: Salmeron Solar Systems LLC. As short as it is, it is not very sweet, but an example of how we can, with some semantical changes transform a model we have labored over into a new, but similar, that is isomorphic, model. Analogies abound in modeling anything, especially human decisions. We can indeed
Transform the annual, with NPV, Make-A-Pie model into a solar leasing company decision model.
Note the use of three spreadsheet techniques (e.g., INDEX-MATCH, Data Table) which helped to communicate analytical results to the client.
Formulate three recommendations for the decision maker. Outline assumptions, criteria for prioritizing recommendations, a summary of the recommendations including timing as immediate, ongoing, and remote.
Copyright 2024, William G. Foote, all rights reserved.↩︎
On occasion, an illness or a personal emergency may preclude you from turning in an assignment on time. In cases of extenuating circumstances, please work directly with me to manage the situation. To help avoid a late submission, I encourage to develop a support group, or team, of up to three students. You are always responsible to submit your own work into the Learning Mmanagement System (we know affectionately as Moodle) on time and in full.↩︎