E3, Ch 11: Spreadsheet Modeling/Analysis

Question 1

in modeling:
data input
formulas
outputs

Answer 1

data input –> ok to touch
formulas –> do not touch
outputs –> view only

Question 2

predictive modeling

Answer 2

heart and soul of business decisions; more of an art than a science
In building models, start simple and enrich as necessary

Question 3

predictive vs prescriptive

Answer 3

predictive = what will happen
prescriptive = how can we make it happen

Question 4

back-of-the-envelope calculations

Answer 4

they are quick informal calculations that can provide basis for a more formal model

Question 5

do not use _____ ____ in model formulas, rather reference the cells specifically (ex. Do not type 13, reference ‘=C14’ which contains 13 for # of units)

Answer 5

input data

Question 6

spreadsheet engineering

Answer 6

building spreadsheet models

Question 7

verification

Answer 7

process of ensuring a model is accurate and free from logical error

Question 8

many practical models incorporate _________ time periods ________ linked together

Answer 8

multiple
logically

Question 9

single-period purchase decisions

Answer 9

often must be made during uncertain demand

Question 10

validity

Answer 10

how well a model represents reality

Question 11

sources of data (4)

Answer 11

subjective judgement
existing databases
analysis of historical data
surveys, experiments, other forms of data collection

Question 12

steps to get to data validation

Answer 12

Data → Data Tools → Data Validation

Question 13

explain use of Range Names

Answer 13

a name you use to reference a cell/group of cells

select cells –> Name box –> type reference

ex. =B13 as QuantitySold

Question 14

Form Controls

Answer 14

buttons/boxes/etc. for changing data that is used to design user-friendly spreadsheets

Question 15

Form Controls are located in the __________ tab, but most excels need to have the tab manually installed

Answer 15

Developer

Question 16

forms of What-If Analysis (3)

Answer 16

data tables
scenario manager
goal-seek

Question 17

data tables
one-way
two-way

Answer 17

data tables summarize impacts of 1-2 inputs
one-way tables evaluates an output over a range of values for a single input
two-way tables use two inputs

Question 18

scenario manager

Answer 18

allows creation of scenarios

Question 19

Scenarios

Answer 19

sets of values that are saved and can be substituted in spreadsheets

Question 20

goal-seek

Answer 20

if you know the result you want but aren’t sure of what input value the formula needs to get that result

Question 21

In goal-seek,
1. Set ‘cell’ to
2. ‘Value’ =
3. ‘Changing cell’ =

Answer 21

1. the cell reference you want to resolve
2. numerical, target value you want the formula to return
3. input value to change

Question 22

parametric sensitivity analysis

Answer 22

term used by Analytic Solver Platform, ASP, for systemic methods of what-if analysis

parameter: input data

Question 23

tornado charts

Answer 23

shows impact of a variation of a model input has on an output while holding all other inputs constant

shows which inputs are least/most influential

Question 24

ASP automatically identifies all _____ in PSA

Answer 24

inputs

Question 25

In a good model, you separate the ____ from the _____ ________ and ______, clearly defining each of those 3 segments (usually with color).

Answer 25

data
model calculations
outputs

Question 26

know how to check formula logic and what should you see

Answer 26

Formulas → Formula Auditing → Trace Precedents/Dependents

what you want to see: a straight linear arrow. If the arrows are all over the place they are not laid out well

Question 27

put in as much _____-________ as possible

Answer 27

error-checking

Question 28

break-even point modeling will NOT give you an __________, aka ‘highest profit”

Answer 28

optimum

Question 29

break-even point

Answer 29

using goal-seek and setting ‘cell’ to 0

Question 30

Influence diagram

Answer 30

logical, visual representation of key model relationships

Question 31

many predictive models in business analytics use _________ ___ in conjunction with logical model
development.

Answer 31

empirical data

Question 32

using empirical data means

Answer 32

observing some phenomenon and
then try to develop a model that best reflects the data
that we observe

Question 33

predictive analytical models are based on assumptions about the ________ and incorporate variables that most likely will not be known with ________

Answer 33

future
certainty