K303 Module 4 Chapter 6 & 7

spreadsheet model

"In simple terms, a mathematical model is a quantitative representation, or approximation, of a real problem. This representation might be phrased in terms of mathematical expressions (equations and inequalities) or as a series of interrelated cells in a spreadsheet" -We can use mathematical models to show how different factors (represented as variables) interact with one another in a specific situation.

Assignment model

- characterized by a 2D variable cell range, where each cell in the range can contain only a 0 or a 1 value, representing the answer to a "yes/no" question. - The question, broadly, is whether to place each member of 1 set with each member of another set-we might use Solver to assign employees to work schedule blocks, for example. - often w/the goal of maximizing effectiveness, or overall satisfaction, or minimizing the resources required to construct a satisfactory schedule.

Sensitivity Analysis

- evaluates how changes to the inputs would impact the optimal solution Optimization results in the one BEST answer for the assumptions in the model

4. Complete the model

- select a solution

Model development Optimization

2 Steps to Solving Optimization Problems

deterministic model

A _____________ will determine the values for any decision variables through the combination of inputs and outputs to reach a specific objective. We can do this using simple tools, like Excel's Goal Seek feature. However, this tool can produce some unrealistic answers

Infeasible Solution

A solution where at least one constraint is not satisfied

situation-dependent

According to their website, EuSpRIG asserts that best practices are _________. This is true, as different corporations, organizations, or even managers or professors may establish their own best practices suited to their specific work environment.

"what-if" analysis

Adjusting the input values to see how the outputs would change is a distinct advantage to using spreadsheets to create mathematical models. This is a process commonly referred to as ______________ where the model can quickly recalculate outputs to show what would happen if the input values were changed. This leads us to other types of models that help us answer additional types of questions.

inputs, outputs, decision variables

All models will contain _______ (given values or already known information) and _______ (mathematical expressions); some will also have __________ where one or more inputs are unknown and the model helps to determine them (e.g., how many units to produce).

Feasible Solution

Any set of values of the decision variables that satisfies all of the constraints.

- Applying specific colors in cell shading - Using clear labels and headings - Using conventional named ranges for the most important pieces of the spreadsheet model

Applying a uniform standard that users can understand can be accomplished by following a few simple rules:

accurate, flexible & clear

At the core of all of the standards for good spreadsheets, the primary goal remains the same: you should always strive to create a model that is ?

Transportation

Commonly used to determine the most efficient means of moving product to the customer

Product mix

Commonly used to maximize profits by finding an optimal production plan, determining products of each type to assemble based on resources available (e.g., component parts, labor hours) without exceeding those resources.

objective variable cells constraints

Components of an optimization model

- Read the problem carefully and highlight all key information. - Set up the model with information from the problem, identifying all inputs and outputs with clearly defined labels. - Color-code the inputs and outputs. - Create named ranges. - Add formulas as needed to calculate all output variables and the objective of the problem.

Constructing a spreadsheet model

constraints, changing cells, overall objective

Determining the difference between optimization model types can be tricky at times, because all optimization models share the same core elements: _____________ they're trying to satisfy.

5. Analyze the results

Evaluate and monitor = - At this point in the process, it will be possible to identify the current outcome in descriptive modeling, find the optimal output in prescriptive modeling, & finally make an educated decision about the decision variable in predictive modeling

Objective cell

Gray shaded cells are used to indicate the goal of the problem or the ? - Target/objective/quantity of interest/output.

policy & physical

Identifying Constraints 2 types

guaranteed to find an optimal solution

It is important to keep models linear whenever possible - this allows us to take advantage of solving methods for linear problems, such as the Simplex LP method. - Such solution methods are ____________ .Solution methods for non-linear problems are not

50

K303 policy: If all of your non-zero variable cells are greater than _______, integer constraints are not necessary--even if partial values don't make sense. However, if Solver returns values <=_______ in any non-zero variable cells and partial values don't make sense, you should add an integer constraint (assuming that our variable cells are something like chairs, and not drink syrup).

constraints

Most often in deterministic model reaching our desired objective realistically will require us to impose some boundaries, called ?

output

Once you have all the inputs, you can calculate your grade, using mathematical expressions, to determine the _________ for the model. - Elements of model that are negotiable, as long as you stay within the rules of the problem

1. objectives 2. decision variables 3. constraints

Optimization problem core elements

cost revenue profit

Outputs Examples include ?

predictive(stochastic) model

Rather than determining a single value for the solution, a model of this type will result in several possible outcomes and probability distributions for each to help you see which is most likely to occur.

Decision Variable

Red background color will indicate _______ data

inputs, outputs & decision variables

Remember, spreadsheet models have 3 main parts ?

Breaking down optimization problems

Step 1 Identify the target cell (objective). What is the problem trying to accomplish/what is the goal? Identify the changing cells (decision variables); what business decision needs to be made? Step 2 Identify the input variables; what data is necessary to solve the problem? Step 3 Identify all the constraints presented in the problem. What limits are being placed on this problem? Step 4 Build the model Include formulas for target cell and constraints Test your model Step 5 Solver Settings Make Unconstrained Variables Non-Negative Select "Simplex LP" as the solving method Run solver to optimize the problem Verify that the answer makes sense

(1) Develop a model that will allow you to explore the consequences of different order quantities. Color-code your model properly (2) Test model (3) Name the appropriate cells & ranges of cells & configure Solver appropriately (4) Run Solver

Step in Product Mix Model

1. define the question 2. Identify required inputs and additional information 3. Develop model logic and layout 4. Complete the model 5. Analyze the results

Steps in building a model in excel

descriptive model

The example of calculating your course grade would be an example of a simple ______________. It simply describes how your letter grade is determined in a course. -very simple model

Optimal Solution

The feasible solution that optimizes the objective.

assignment type of model

The following problem is an ___________, where the focus is on constructing a satisfactory schedule while maximizing effectiveness or satisfaction and minimizing the resources needed

1. define the question

The question should contain a specific goal to meet within your model in what step? -define the problem

layout

The questions answered while figuring out the logic for our model are reflected in the ?

Feasible Region

The set of all feasible solutions

label all cells clearly

The use of color-coding and named ranges can enhance spreadsheet models greatly, but including some simple documentation ensures that even users who are initially unfamiliar with your standards can quickly get acclimated to your models First ? Next, document the color code using a simple legend allowing any user to quickly identify all of the moving parts and individual components in the model Document the named ranges used in the model

input

These data would represent ________ variables, as the values have already been assigned. - Typically facts, non-negotiable elements of the model

modeling

This is where you'll spend most of your time, thought and effort! - Determine Objective, Decision Variables & Constraints - Build a spreadsheet model to relate all the variables with formulas and functions

- Labor scheduling - Inventory management - Selection of advertising media - Blending - Product mix

Types of problems linear programming can solve: (5)

decision variables

Typically the model consists of inputs at the top of the model, followed by ______________ then calculations and/or constraints & finally the objective

binary constraint (bin)

Use a __________ ("_____" in the constraint operator drop-down list) when you want variable cells to take on 0 & 1 values only, usually to indicate that the variable cells are the answers to yes/no questions - can be applied to variable cells only

GRG Nonlinear Simplex Linear Programming Evolutionary

Using the Solver add-in for Microsoft Excel, optimization models can be solved using 1 of 3 algorithms:

decision variables

Variable cells are also referred to as changing cells, or __________ bc the decision maker can control or set the variables; they're neither constant assumptions nor random variables (RED FILL)

Objectives

always start with a clearly defined objective/target/goal EX: maximize profit or minimize cost

Transportation Models

characterized by a 2 dimensional variable cell range EX: we might be trying to determine how many products to ship from each of 4 production facilities to each of 6 distribution centers

1. Modeling 2. Optimization modeling 3. Solver Add in

modeling review components

Decision variables

or changing cells, values that we can adjust in order to optimize the objective cell EX: how much of a particular unit to produce

Optimization modeling

used to find the best possible choice out of a set of alternatives

Product Mix Models

we consider production quantities for a # of diff goods in 1 single production period - variable cells can be arranged in a row or column EX: we might model a 1 month production plan for 5 diff cell phone cases

Inputs

(information given to you in the story problem or in the model). - necessary to solve the problem. - cells containing are color coded Blue

3. Develop model logic and layout

- Analyze the data, think creatively, and let the problem rest - recognizing how each variable affects the others - Once the logic has been determined, the model can be designed in the spreadsheet

transportation model

- Characterized by a 2D variable cell range. - EX: Determine ideal quantities to ship from a # of different origins into a # of different destinations in 1 time period. - If there are three origins and five destinations, then the variable cell range will be a 3 x 5 matrix representing quantities shipped along all possible routes. - Typically, each origin has an upper bound on total units shipped (representing, for example, the maximum possible production), while each destination has a lower bound on total units received (representing the minimum needed at that destination).

best practices in spreadsheet design

- Spreadsheet Standards Review Board: a voluntary organization dedicated to maintaining a document on best practices in spreadsheet modeling - EuSpRIG (European Spreadsheet Risk Interest Group): hosts conferences and web forums on spreadsheet risk management and helps corporations to manage risks associated with using spreadsheets for decision making and reporting are EXs of ?

1

- There can only be ______ objective cell - The objective cell must be related (through formulas / functions) to the changing cells

0

- This constraint can be applied to changing cells only - If you do need an integer constraint add 1 & ALSO set Integer Optimality to ? EX: Integer Optimality setting allows Solver to do so. If you set Integer Optimality to 5%, for ex you're allowing Solver to stop once it determines that it's w/in 5% of the optimal solution value - makes the problem much more difficult to solve

output

- calculations that will involve the changing cells & inputs - These cells are not color coded

modeling review & breaking down an optimization problem

Building optimization models—two examples

descriptive model

By using a spreadsheet to model this situation, we can easily substitute other values to see how the output would change for different inputs

minimal impact to the objective

Even if partial values don't make logical sense, large-scale numbers can be rounded up or down by management with _________? However, if you are dealing with small-scale variable cell values, requiring an integer solution may significantly change the results.

Product mix model

Find optimal production (or order) quantites for a # of different products at once. - objective is often to maximize profit or minimize cost. - common constaints involve budget limitations, workforce (e.g., employee hours; employee skills) limitations, and demand limitations (or demand guarantees).

- add integer constraint to only variable cells - uncheck the "Ignore Integer Constraints" box - change "Integer Optimality (%)" to 0

K303 requirement: If an integer constraint is warranted you must (3): -If variable cells contain fractional values, determine if that would be a feasible solution. (If you're producing drink syrup, a half ounce might be fine. If you're selling chairs, half a chair is probably not useful.) (Use an integer constraint only when necessary)

non-negative

Make unconstrained variables _________ tells Solver that changing cells that don't already have lower bounds can't be negative. - This setting should be used for all problems that we work on in this class. (Note: this is not the same as saying that no values in the problem can be negative.) This

- Feasible Solution - Optimal Solution - Feasible Region - Infeasible Solution - Unbounded

Possible solutions (5) =

Breaking down an optimization problem

Step 1: Read the problem carefully and identify key information: inputs, target or objective cell, and the changing or decision variable cell(s). Step 2: Fill in the missing information from the story problem (if any exists). Step 3: Color-code cells for easy identification of the different elements of the model. Step 4: Identify all of the constraints presented in the problem. Step 5: Add formulas to the model and identify constraints (algebraic expressions) in the model. Step 6: Test the logic of the model using different values in the decision variable. Step 7: Complete the Solver window and then run Solver to optimize the problem. Step 8: Add named ranges to clarify the Solver window. Step 9: Confirm that a feasible solution was found by Solver.

profit or cost

Successful modeling always begins with a clearly defined goal, which in this chapter we will often refer to as the objective or target in many business optimization problems the objective will be ______ bc ultimately businesses often seek to maximize their _____ or _______ their cost (GREY FILL)

decision variables

The changing cells must contain numbers that can change freely (no formulas or functions) - can change, as long as you stay within the rules of the model. - These cells cannot contain functions or formulas. - RED

- Simplex LP - "non-negative" - an Integer constraint

We use an Excel Add-in called Solver Some settings you need to know when to choose (3) :

input

We will use a Blue background color to indicate ______ data

consistent

When building spreadsheet models, clarity can be greatly enhanced by simply being ________. Establish a uniform standard and apply that standard to all models you create

Unbounded

When the model has been formulated in such a way that the objective can be as large or small as we like

prescriptive(deterministic) model

With modifications, this descriptive model of your grades could be adapted to a _____________, which would tell you the minimum amount of points you would have to earn on the remaining assignments to achieve a specific objective (or target), in this case your desired course grade

predictive(stochastic) model

You could transform your descriptive model into a __________- Because your performance on future assignments is uncertain, this type of model would use your performance on past assignments to establish a probability of you earning certain scores on each remaining assignment. Then the model is used to simulate your performance on later assignments and predict your grade for the end of the course.

color-coding

________ can provide visual cues to help viewers quickly orient themselves to the information that is being presented in the model.

constraints

________ could be physical (no more room in the warehouse), - logical (can't produce negative numbers of products), or economic (budget) restrictions that must be met or - managerial (desire for a particular product mix).

clarity

________ is crucial to a sound spreadsheet model. Most important factor bc it's exceedingly more difficult to determine the accuracy of or revise the assumptions in a model that's unclear & more difficult to interpret the results

Optimization

________ is the science of obtaining the highest level of achievement through maximizing desirable factors like revenue or profit or minimizing undesirable factors like cost.

Named ranges

__________ are another formatting convention that makes models easier to understand. The use of _________ can enhance the readability of formulas and functions to make it easier for users to interpret the calculations

Spreadsheet models

__________ are used to describe and solve many different types of problems, from the very simple to the very complex.

Flexibility

__________ is important, as many times the situation you are modeling can change. - Building a model that is ______- means allowing assumptions to be modified easily. This may take longer to build initially but can save countless hours when modifications are necessary. - Using cell references rather than applying values directly to calculations is an easy way to build in ?

Optimization

__________ modeling is used to find the course of action that produces the best result, while working within a given set of constraints such as time, money, and labor

GRG Nonlinear

___________ optimization models can find a locally optimal solution to a reasonably well-scaled, non-convex model. Locally optimal solution means that there is no other set of values for the decision variables close to the current values that yields a better solution for the objective function. This means that Solver has found a "peak" (if maximizing) or "valley" (if minimizing). But if the model is non-convex, there may be other taller peaks or deeper valleys (i.e., better solutions) far away from the current solution.

Simplex Linear Programming

____________ optimization models are useful when your objective and constraints use linear functions, & therefore you can be confident of finding a globally optimal solution. - Globally optimal solution means that there are no better alternatives that meet the given criteria or constraints. - As these models must be linear, any function or formula that breaks linearity should not be used. - EXs: MIN & MAX functions, IFs & VLOOKUPs

Evolutionary

______________ optimization models will be able to find good solutions to reasonably well-scaled models but can't determine whether a given solution is optimal—so they never really know when to stop. - The decision maker makes few or no assumptions about the nature of the problem functions & can never be sure whether the best solution has been found. - It's not uncommon in practice to start the GRG Solving method from the point where the _______ method stops. This usually will yield a better solution

Constraints

__________—limitations or restrictions—are an important factor in optimization models bc they determine the possible courses of action. - In spreadsheet optimization modeling, it's important to begin the modeling process by taking stock of limitations that apply to the scenario being modeled - it's necessary for the model to maintain realism & thus add value to the organization

Accuracy

a critical factor. - If there are errors in the model, then the outcomes we generate with that model are not likely to be accurate or realistic. When building a model, it is very important to check for ____ -Checking for this involves reviewing formulas & functions that are being used, as well as validating the assumptions that are being made; that is, testing with different inputs to see that outputs are as expected. - important to ensure that data entry errors are minimized through the use of form controls or data validation when possible

Modeling

a quantitative representation of a real business problem

Assignment

a special case of the transportation problem is commonly used to place each member of 1 set w each member of another set. - dif w _______ is that each cell in the range can contain only a 0 or a 1 value, representing the answer to a "yes/no" question. - The question, broadly, is whether to assign employees to work schedule blocks, for example. - This is often with the goal of maximizing effectiveness, or overall satisfaction, or minimizing the resources required to construct a satisfactory schedule

optimization

a type of prescriptive modeling =

assignment model

characterized by a 2 dimensional variable cell range, where the meaning of each variable cell is a "yes or no" decision. ( _______ models thus involve binary constraints) EX: trying to determine which K303 classes to assign each of our TAs to. We might have 30 classes & 20 TAs if we use ________ approach, we'll set up a 30 x 20 variable cell range, where each cell in the range answers the question, should this TA be assigned to this class?

Qualitative data

deals with descriptions that are observed, not measured, such as colors, textures, smells, tastes, appearance, beauty, and so forth. At the end of the semester, students are asked to complete teaching evaluations. Open-ended questions that ask what was most and least enjoyable about the course are examples of qualitative data.

Quantitative data

deals with numbers that can be measured, such as length, height, area, volume, weight, speed, time, temperature, cost, ages, and so on. Box scores from baseball, football, soccer, or any other type of sporting event would be considered quantitative data.

Accounting/Finance Management Marketing Operations

functional areas of business for spreadsheet models

2. Identify required inputs & additional information

gather data, - gather all relevant information that will help it get answered. In addition to listing all the information, it may be helpful to highlight or bold the relevant information in the text itself in what step

Constraints

limitations or restrictions/allocating resources EX: budget, time, people, or other limited resources

Product mix Transportation Assignment

linear programming optimization models =

deterministic data

spreadsheet model turns descriptive data to ? (ON QUIZ)