MSIS Final Exam 3223 Ali Okstate

Systematic approaches to what-if analysis that are available in Excel make the process easier and more useful. What are they?

-Data tables -Scenario Manager -Goal Seek

In the context of simulation, random numbers are numbers that are uniformly distributed between

0 and 1.

RAND() returns a random number between

0 and 1.

Use the following information to answer the question(s) below. Cerebro Manufacturing produces four types of structural support fittings-plugs, rails, rivets, and clips-which are machined on two CNC machining centers. The machining centers have a capacity of 250,000 minutes per year. The gross margin per unit and machining requirements are shown in the spreadsheet below.Using the spreadsheet data above, what is the maximum possible production of rails based on the machine capacity? Rails were at 2 minutes.

125,000

In the Solver options, you can check the box Use __________ ___________ and Solver will rescale the values of the objective and constraint functions internally to minimize the effects of poor scaling.

Automatic Scaling

______________ _______________ essentially state that input = output and ensure that the flow of material or money is accounted for at locations or between time periods.

Balance Constraints

Balance Constraints.

Balance constraints essentially state that input = output.

_______ ___________ is the process of projecting and summarizing a company's cash inflows and outflows expected during a planning horizon, usually 6 to 12 months.

Cash budgeting

A binding constraint is one for which the Cell Value is greater than the right-hand side of the value of the constraint T/F

False

Adding more realism to a model reduces its complexity T/F

False

Decision variables in an optimization model are known values T/F

False

In predictive modeling, verification refers to how well a model represents reality (T/F).

False

Models can capture every detail of a real problem T/F

False

One useful Excel tool is the data error feature, which allows you to define acceptable input values in a spreadsheet and provides an error alert if an invalid entry is made T/F

False

Simple bonds constrain the values of multiple variables T/F

False

T/F: In the context of simulation, random numbers are numbers that are uniformly distributed between 0 and 9.

False

Which of the following is a visual representation of a descriptive model that shows how the elements of the model influence, or relate to, others? Precedence diagram Influence diagram Scatter chart

Influence Diagram

__________ __________ is the parameter that specifies when the Solver algorithm will terminate an optimization process with integer constraints.

Integer Tolerance

Which of the following best defines Monte Carlo simulation

It is the process of generating random values for uncertain inputs in a model and computing the output variables of interest

Limitations.

Limitations usually involve the allocation of scarce resources.

-the process of generating random values for uncertain inputs in a model, computing the output variables of interest, and repeating this process for many trials to improve the results.

Monte Carlo Simulation

_______________ models have been used extensively in operations and supply chains, finance, marketing, and other disciplines.

Optimization

_______ scaling can cause Solver engines to return different error messages.

Poor

______________________ __________________ are often found in problems involving mixtures or blends of materials or strategies.

Proportional Relationships

Proportional Relationships.

Proportional relationships are often found in problems involving mixtures or blends of materials or strategies.

Requirements.

Requirements involve the specification of minimum levels of performance.

Simple Bounds.

Simple bounds constrain the value of a single variable.

The _________ Answer Report provides basic information about the solution, including the values of the original and optimal objective function and decision variables.

Solver

unbounded solution

The objective can be increased or decreased without bound (i.e., to infinity for a maximization problem or negative infinity for a minimization problem)

alternative (multiple) optimal solutions

The objective is maximized (or minimized) by more than one combination of decision variables, all of which have the same objective function value.

Unique optimal solution

There is exactly one solution that will result in the maximum (or minimum) objective.

______________ _______________ are used in the absence of data and depend on three parameters, minimum, maximum, and most likely. _______________ ____________ is often used when one can reasonably estimate these three parameters based on managerial knowledge and judgment.

Triangular distribution

A constraint function is a function of the decision variables in the problem T/F

True

A wide variety of practical problems in business analytics can be modeled using spreadsheets T/F

True

Any solution that satisfies all constraints of a problem is called a feasible solution T/F

True

Balance constraints essentially state that input = output and ensure that the flow of material or money is accounted for at locations or between time periods T/F

True

Constraints are limitations, requirements, or other restrictions that are imposed on any solution, either from practical of technological considerations or by management policy T/F

True

Excel Formula Auditing tools (in the Formulas tab) help you validate the formulas and check for errors T/F

True

In predictive modeling, validity refers to how well a model represents reality T/F

True

Infeasible problems can occur when the demand requirement is higher than the available capacity T/F

True

Proportional relationships are often found in problems involving mixtures or blends of materials or strategies T/F

True

Requirements involve the specifications of minimum levels of performance (T/F).

True

T/F: A value randomly generated from a specified probability distribution is called a random variate.

True

T/F: Risk analysis seeks to examine the impact of uncertain inputs on various outputs.

True

T/F: Uniform or triangular distributions are used in the absence of data.

True

T/F: as Monte Carlo simulation is essentially statistical sampling, the larger the number of trials used, the more precise the results.

True

The process of developing good, useful, and correct spreadsheet models is known as spreadsheet engineering T/F

True

There are three types of organization models, linear, integer, and nonlinear T/F

True

Two-way data tables can evaluate only one output variable T/F

True

Verification is the process of ensuring that a model is accurate and free from logical errors T/F

True

______________ is the process of ensuring that a model is accurate and free from logical errors.

Verification

A feasible solution to an optimization problem is any solution that satisfies ____ of the constraints.

all

If a model has ______________ _________ ___________, the objective is maximized (or minimized) by more than one combination of decision variables, all of which have the same objective function value. Solver does not tell you when alternative solutions exist and reports only one of the many possible alternative optimal solutions. ____________ __________ _____________ exist when any of the allowable increase or allowable decrease values for changing cells are zero.

alternative optimal solutions

An optimal solution is the ______ of all the feasible solutions.

best

A ________ __________ is one for which the left-hand side is equal to the right-hand side of the constraint at the optimal solution.

binding constraint

The ____ _____ also shows the monthly cash balances and any short-term borrowing used to cover cash shortfalls.

cash budget

One useful Excel tool is the ______ _____________ feature, which allows you to define acceptable input values in a spreadsheet and provides an error alert if an invalid entry is made. This can help you avoid inadvertent user errors.

data validation

Quantity produced is a decision option typically based on

demand.

Identify the formula which would be used to calculate earnings before taxes using both the data and model values in the spreadsheet?

earnings before taxes = net operating income - interest expense

Many optimization models require binary variables, which are variables that are restricted to being

either 0 or 1.

The set of feasible solutions is often called the ___________ ___________. The ___________ ___________ is the set of points that satisfy all constraints simultaneously.

feasible region

A ___________ ____________ is a set of values for the decision variables that satisfy all of the constraints. Linear programs generally have an infinite number of feasible solutions.

feasible solution

Practical business models focus on predicting ___________ ___________, ___________ ______________, and ________ ____________

financial performance, customer retention, and product sales.

Total cost formula

fixed cost + variable cost * quantity produced also written as C = F + VQ

Predictive models focus on understanding the ______

future.

Decision variables that are forced to be integers are called __________ integer variables.

general

Identify the formula which would be used to calculate the gross profit using only the data values in the spreadsheet?

gross profit = sales - cost of goods sold

The value of Integer Tolerance should be zero in order to find the

guaranteed optimal integer solution

Demand has an influence on profit by predicting

how many units of a product will be sold.

When a problem is ________, Solver will report "Solver could not find a feasible solution."

infeasible

Spreadsheet models allow you to easily evaluate what-if questions by changing

input values and recalculating model outputs.

Sales levels are often used to plan ________ ________

inventory levels.

A constraint function is the _______-_______ side of a constraint.

left-hand

Many types of financial investment problems are modeled and solved using ________ optimization.

linear

Objective function is the quantity that is to be

minimized or maximized.

Requirements involve the specification of ________ levels of performance.

minimum

Predictive models usually involve _________ time periods.

multiple

To evaluate multiple output variables, you must construct

multiple two-way tables.

Identify the formula to calculate net income based on the data values shown on the spreadsheet?

net income = earnings before taxes - taxes net income = sales - cost of goods sold - administrative expenses - selling expenses - depreciation expenses - interest expense - taxes

Identify the formula which would be used to calculate the net income value using information in both the data and model sections in the spreadsheet?

net income = earnings before taxes - taxes net income = sales - cost of goods sold - administrative expenses - selling expenses - depreciation expenses - interest expense - taxes

Identify the formula which would be used to calculate the net operating income using only the model values in the spreadsheet?

net operating income = gross profit - operating expenses

Infeasibility

no feasible solution exists

Two-way data tables can evaluate only ____ output variable.

one

Identify the formula which would be used to calculate the operating expenses using only the data values in the spreadsheet?

operating expenses = administrative expenses + selling expenses + depreciation expenses

The ________ _________ includes any set of decision variables that optimizes the objective function. To guarantee finding an ____________ __________, some type of systematic mathematical solution procedure is necessary. Fortunately, such a procedure is provided by the Excel Solver tool.

optimal solution

An ______________ ___________ has the following elements: 1) Decision variables, 2) An objective to maximize or minimize, and 3) Constraints.

optimization model

Solver is an add-in package with Excel that was developed by Frontline Systems Inc. and can be used to solve many different types of

optimization problems.

A ________ _________ model is one that computes values of the objective, constraints, or intermediate results that differ by several orders of magnitude. As a result, Solver may detect or suffer from "numerical instability.

poorly scaled

Optimization is the most important tool in

prescriptive analytics.

Multiple outputs may be evaluated using one way data tables. For example,

profit and revenue.

Problem statements such as production must be sufficient to meet promised customer orders would be categorized as a ________ constraint.

requirements

The spreadsheet model for profit is

revenue minus variable cost minus fixed cost Profit = Revenue - Variable Cost - Fixed Cost

A binding constraint is one for which the Cell Value is equal to the _________-_______ side value of the constraint.

right-hand

Limitations usually involve the allocation of _______ resources.

scarce

Simple Bounds constrain the value of a ________ variable.

single

Building ____________ ___________, often called spreadsheet engineering, is part art and part science. Spreadsheets need to be accurate, understandable, and user friendly

spreadsheet models

A solution is _____________ if the value of the objective can be increased or decreased without bound (that is to infinity for a maximization problem or negative infinity for a minimization problem) without violating any of the constraints. This generally indicates an incorrect model, usually when some constraint or set of constraints have been left out.

unbounded

Predictive models incorporate the _________ element

uncertainty

When a model has a __________ optimal solution, it means that there is only one solution that will result in the maximum (or minimum) objective.

unique

Variable cost formula

unit variable cost * quantity produced

The best way to avoid scaling problems is to choose the "________" implicitly used in your model.

units

Optimization is the process of selecting

values of decision variables that minimize or maximize some quantity of interest.

In an integer linear optimization model (also called an integer program, or IP model), some of or all the variables are restricted to being ___________ _____________.

whole numbers

In an integer linear optimization model some or all of the variables are restricted to being .

whole numbers

Developing Linear Optimization Models

• 1.Identify the decision variables - the unknown values that the model seeks to determine. • 2.Identify the objective function - the quantity we seek to minimize or maximize. • 3.Identify all appropriate constraints - limitations, requirements, or other restrictions that are imposed on any solution, either from practical or technological considerations or by management policy. • 4.Write the objective function and constraints as mathematical expressions. • 5.Implement the mathematical model on a spreadsheet.

Solving a linear optimization model can result in four possible outcomes:

• A unique optimal solution • Alternative (multiple) optimal solutions • An unbounded solution • Infeasibility

Constraints generally fall into one of the following categories:

• Simple Bounds constrain the value of a single variable. • Limitations usually involve the allocation of scarce resources. • Requirements involve the specification of minimum levels of performance. • Proportional Relationships are often found in problems involving mixtures or blends of materials or strategies. • Balance Constraints essentially state that input = output and ensure that the flow of material or money is accounted for at locations or between time periods.