Chapter 20 Flashcards
What is a Model
A model is a simplified version of reality. A model may be presented in many different forms: as a physical model, a graphical model, a mathematical model etc. A model is typically smaller than the reality it represents; however, it shows the relationship between different key components of this reality. Modeling is helpful in understanding the
relationship between these components. It is helpful in evaluating relationships that may be impossible to consider in the real environment.
Model
In spreadsheet modeling, we present the variables in a model in different cells in a worksheet, and set up relationships between these variables. We then vary some of these variables to see the impact that a change may have on other variables.
Model
A model may have different sets of variables. The most frequently used variables are Input Variables, Decision Variables, and Output Variables.
- I: Input variable: Input variables have values that are constants, i.e., these values do not change in the model.
- D: Decision variable: This variable represents the decision we are trying to take, based on the data. The value for the decision variable can be changed to achieve the objective of the model.
- O: Output variable: An output variable represents the value resulting from the relationship between the input and the decision variables.
Guidelines to follow while building a model in excel
1) Accuracy: It is evident that a model should be built accurately. Accuracy refers not only to the mathematical formulas and calculations, but also to the logic underlying these relationships. It is quite possible to base accurate calculations on faulty logic. In a model, it is easier to catch calculation errors than errors in logic.
Guidelines to follow while building a model
2) Transparency: The model should be simple and transparent. Calculations should be laid out step by step and made as simple and clear as possible. Rows,
columns, and different sections of the model should have clear headings.
It should be easy to understand the relationships between different variables. If the model-builder has made assumptions, these should be
clearly identified as such. Further, the basis for the assumptions should also be provided. Making a model transparent means making it easy for
a third party to understand and follow the calculations easily. Color coding of variables (discussed below), and the use or range names, are techniques that will help in this regard.
Guidelines to follow while building a model
3) Focus on audience: Who is the audience for our model? Will the model be printed, incorporated in a PowerPoint presentation, or distributed as a soft copy?
We need to consider the audience for each of these outputs.
Guidelines to follow while building a model
Organize your model such that it follows a logical flow of thought.
Sensitivity analysis
As data tables show the sensitivity of one or more
variables to changes in another variable(s) building a data table is also referred to as sensitivity analysis.
Data tables are of two types: a one-way data table and a two way data table.
One-way data table
A one-way data table takes one input, and shows the effect of changes in this variable on other variables that we will call output variables. The number of output variables is not limited (within reason) and depends on the model.
How to invoke Data Table:
Data ribbon -> Data Tools group -> What-if Analysis -> Data Table. We then choose row input cell or column input cell based on how our data is organized.
Two-way data table
A two-way data table takes two inputs, and shows the effect of changes in these variables on one output variable. Changes in one input are shown along the row axis of a table; changes in the other output are shown along the column axis of a table.
Goal Seek
As the name suggests, Excel uses Goal Seek to achieve a target value while changing one variable. Before we can use Goal Seek, we need to create our model.
How to invoke Goal Seek
DATA ribbon -> Data Tools group -> What-if Analysis -> Goal Seek.
Set cell: B4
To value: 1500
By changing cell: $B$1
A two-way data table:
a. Takes two inputs and provides only one output
b. Takes one input and provides two or more
outputs
c. Takes two or more inputs and provides two or
more outputs
d. Takes one input and provides one or more
outputs
a. Takes two inputs and provides only one output.
Elaine wants to set up a lemonade shop. Her fixed cost for setting up the stand is $50. Her variable cost is $0.50 per glass of lemonade. How many glasses of lemonade should Elaine sell to earn a profit of $50 if her selling price is $0.75?
a. 100
b. 200
c. 300
d. 400
Number of glasses of lemonade = Profit + Fixed cost/ SP - Variable cost = 50 + 50 / 0.75 - 0.50 = 100/0.25 = 400 glasses d. 400
