Expert answer:Use excel to solve problem 3 (a)-(d) on page A30 b


Solved by verified expert:Read two files. The problem is in SpreadsheetModeling file on page A30. My name is “Xiaodi Li”, you will use it when you do the excel. Write comments for each question, the requirement is in the document.


Don't use plagiarized sources. Get Your Custom Essay on
Expert answer:Use excel to solve problem 3 (a)-(d) on page A30 b
Just from $10/Page
Order Essay

Unformatted Attachment Preview

Spreadsheet Modeling:
An Introduction
Before studying this supplement you should know or, if necessary, review
1. Familiarity with spreadsheets.
2. Knowledge of basic Excel commands.
After completing this supplement you should be able to
Explain what models are and why they are used.
Identify the main types of models.
Describe the different components of mathematical models.
Identify the recommended steps in the spreadsheet modeling process.
Explain the importance of model correctness, flexibility, and documentation.
Construct spreadsheet models applying sound modeling principles.
Enter key Excel formulas and functions in models.
Use the Goal Seek and Data Table features of Excel to perform meaningful analysis.
Develop meaningful charts representing the results of analysis.
What Are Models? A2
The Spreadsheet Modeling Process A4
Evaluating Spreadsheet Models A5
Useful Spreadsheet Tips A26
Important Excel Formulas A27
Spreadsheet Modeling within OM: How It All Fits
Together A28
SuppA.indd 1
27/07/12 10:33 PM
ave you ever approached an intersection as the traffic light changed from green to
yellow? What do you do? You need to make a quick decision, whether to stop or
go. Further, you probably need to consider several factors before making this decision,
such as your current speed or the presence of police cars. In an instant of time, you
need to transform these factors into measures such as the odds of an accident if you
stop or go, or being stopped by a police officer if you go through the light. How can
you accomplish transforming these factors into a decision? Whether you realize it or
not, you use a model.
In business, models are used every day to aid in the decision-making process. Models can help executives make strategic decisions about acquisitions and expansions, for
example. They can also help clarify tactical decisions such as production and employee
scheduling, route planning for vehicles, and product mixes. Sometimes models are
embedded in complex information systems, and other times they are simpler and
implemented separately. Spreadsheets have become a common platform for the development and use of many business models because they provide the end-user with tremendous flexibility and analytical tools.
What is a model? It turns out that everyone uses models every day of their lives.
Most models are very informal, and we use them without thinking about it, such as
for the traffic-light decision. In this supplement, we focus on spreadsheet models as a
decision-making tool. Note that although we use Microsoft Excel in the examples with
this text, most of the software’s capabilities are available in competing spreadsheet
packages. This is not an Excel tutorial; it assumes you already have some familiarity
with spreadsheets. The main purpose is to help you learn to develop and use spreadsheet models more effectively and efficiently in addressing quantitative problems. A
number of spreadsheet concepts and skills will be addressed, but if you need more of
an introduction to the use of spreadsheets themselves, specific keystrokes, and menu
choices, you should consult an Excel-specific reference.
 Mental model
A decision-making process
we conduct in our heads.
 Visual model
A model in which graphics or
diagrams are used to convey
real objects or situations.
Examples are a map or a
SuppA.indd 2
A number of different types of models exist. The most common are mental models,
which we “build” in our heads and use to make decisions. The traffic-light situation
calls for a mental model. Visual models use graphics or diagrams to represent real
objects or situations. For example, a road atlas represents a system of roads and other
key land features. Physical models involve objects that represent other objects, such as
an architect’s scale model of a new building. Mathematical models use equations and
relationships among quantities to represent situations. Many of the concepts in this
textbook are shown through the use of mathematical models. Spreadsheet models are
a means of implementing mathematical models.
Although there are a number of different model types, commonalities exist among
them. First, the use of models is motivated by a decision that needs to be made, for
27/07/12 10:33 PM
example, whether to stop or go at a yellow light or how much of a product to order at
one time. Second, all models rely on inputs. Inputs are quantities or factors that affect
the situation. Inputs can be controllable or uncontrollable. Controllable inputs, also
called decision variables, are quantities or factors that a decision maker can change
(usually within limits) for the current situation. For example, in the traffic-light situation, you obviously have control over whether to stop or go, how much pressure to
apply to the brakes (if you decide to stop), or how fast to accelerate (if you decide to
go). In an order-quantity situation, the decision maker has control over how much
quantity to order. Uncontrollable inputs, sometimes called parameters, are quantities or factors that are important to the situation but are outside the decision maker’s
direct control. Obviously, the presence of a police officer is outside your control in
the traffic-light situation, but it may affect your decision to stop or go. In an orderquantity situation, uncontrollable inputs might represent quantities such as the cost
to hold inventory of the material, the cost to place an order for the material, and the
demand or usage rate of the material.
Models also have outputs. An output is a quantity or a factor that depends on how
the inputs are related to one another. In the traffic-light example, an output might
include the probability of an accident. An output generally changes if one or more of
the inputs change. For example, an accident is probably more likely under bad weather
conditions than good weather conditions. In the order-quantity situation, the primary
output would be the total cost of the ordering policy. Secondary outputs would be the
total inventory holding cost and the total ordering cost. Some models have several
outputs, but usually one or two are considered primary.
What models do, then, is to transform inputs into outputs. In the traffic-light
example, you mentally process the inputs (including the stop-or-go decision variable),
“calculate” the outputs, and then make a decision. In the order-quantity situation, we
would use the mathematical relationships among the input quantities to calculate the
total cost. Then we might consider different possible order quantities and choose the
one that produces the least total cost.
A model, however, needs to do more than transform inputs to outputs; it must have
a purpose. That is, we need to know how a model will help us make a decision. The
mental model for the traffic-light situation represents, in a sense, a prediction of the
future course of events and helps you to make the decision whether to stop or go. The
model in the order-quantity situation represents our future orders, holding costs, and
ordering costs. Models should focus on those factors most important to the situation,
thereby ignoring other variables. In the order-quantity situation we did not directly
consider whether demand was seasonal. This could certainly be included, but it would
make the model more complicated. Whether or not we include a particular factor can
be a difficult modeling decision. The benefits of including it must be weighed against
the increased complexity of the model.
We can now more fully define a model. A model is a purposeful representation of
the key factors in a situation and the relationships among them. It is an abstraction of
the real situation, and should incorporate enough detail so the results meet the current needs, but omit unnecessary details. As Albert Einstein said, “Everything should
be made as simple as possible, but not simpler.” This statement applies perfectly to
Figure A-1 shows a generic diagram of a mathematical model. Note the two input
types: the “model” box, which is really a set of relationships among the inputs, and the
outputs. Thinking back to this diagram will be useful as we begin to develop and use
spreadsheet models.
SuppA.indd 3
 Physical model
A model in which physical
objects are used to represent
the real objects or situation,
usually on a smaller scale.
Examples are model cars and
 Mathematical model
A model in which
quantitative relationships
are used to represent a real
situation or phenomenon.
An example is a weatherprediction model.
 Spreadsheet model
A mathematical model
implemented in the form of a
computer spreadsheet.
Quantities or factors that
affect the decision-making
 Controllable inputs
(decision variables)
Quantities or factors that a
decision maker can change
for the current situation. An
example is the order quantity
in an inventory planning
 Uncontrollable inputs
Quantities or factors that
a decision maker cannot
control for the current
situation. An example is the
unit cost of a raw material
that must be purchased to
produce a product.
A quantity or factor that is
calculated from the inputs of
a model and is of interest to
the decision maker.
A purposeful representation
of the key factors in a
situation and the relationships
among them.
27/07/12 10:33 PM
Uncontrollable Inputs
Mathematical Model:
set of relationships
(Spreadsheet Formulas)
Basic mathematical model
Controllable Inputs
(Decision Variables)
You need to follow basic steps in order to develop an effective spreadsheet model.
 Base case
The model containing the
“default” or “given” values for
the inputs. This is normally
the starting point for the
SuppA.indd 4
1. Ironically, the first step is to turn off the computer and instead draw a picture to
better understand the situation. Identify the uncontrollable inputs, the decision
variables, and the outputs. Define the logic necessary to transform the inputs into
the outputs.
2. On paper, sketch out an overall plan for the model. In general, group the inputs
together. Determine where the inputs, intermediate calculations, and outputs
will go. Plan to highlight the key inputs and outputs to make the model easier
to use for what-if analysis. Determine the formulas relating the inputs to the
intermediate calculations and outputs. This can be very simple for some models
(i.e., Profit 5 revenue 2 expenses), or it may be quite complicated. In general,
the time spent planning a model in this step is normally much less than the time
spent debugging an unplanned, completed model.
3. Develop the base case spreadsheet model. Group the inputs together logically.
It usually helps to use a color-coding scheme so the user can quickly determine
what are the inputs and outputs of the model. Break down the intermediate
calculations so that each formula is relatively simple. You can then more easily
spot and correct errors. Research has shown that most spreadsheet model
developers believe their products to be error-free, but this assessment is usually
wrong! Thus, you need to scrutinize formulas and results during and after the
spreadsheet development effort. Use specific text labels, including units of
measure, so that others reading the model can follow your thought process. The
outputs should also be clearly labeled and color-coded. For large models (loosely
speaking, those that do not fit in a window screen), it is often very helpful to
provide a summary of the outputs next to the inputs.
4. Test the spreadsheet model using trial values. Verify the results by hand, if
possible. If you have broken down the intermediate calculations into relatively
simple formulas, this step is much easier.
5. Use the model to perform the needed analysis. This may involve a relatively simple
calculation, preparation of a chart, or more substantial analysis. Two common
types of analysis are scenario analysis and sensitivity analysis. A scenario is a
specific set of conditions that could occur in a real situation. A common practice
is to look at the base-case, best-case, and worst-case scenarios. Scenario analysis
helps a decision manager gain additional insight into a situation. Sensitivity
analysis involves studying the changes to the output of the model (e.g., profit)
as one or more of the inputs (e.g., demand) change. Sensitivity analysis helps to
identify the inputs that cause the most change in the output. Since the values for
the inputs are often just estimates, it is important to understand this sensitivity.
27/07/12 10:33 PM
Break-even analysis is one special case of sensitivity analysis. No matter how
simple or complex the analysis, the time invested developing a high-quality basecase model will pay off when you need to do additional analysis. Spreadsheet
programs contain a number of tools to assist the analyst; we will provide an
overview of some of these later in this supplement.
6. Document the model so that others can easily understand it. Remember, others
may not think of the problem in exactly the way you do, so descriptive labels and
a logical layout are extremely important. Indeed, ideal spreadsheet models are
almost “self-documenting” as a result of the way you organize and label them.
Cell comments can also be used to document and explain key formulas.
Do not become discouraged if, after you follow these steps, you find that your
model does not work or appear the way you intended. All modelers experience this.
Realize that modeling is a learning process, and this learning process itself often leads
to better decisions than you would make had you not developed a model. Sometimes
the things you learn about the real situation during the modeling process are even
more important than the numerical results of the model. In any event, models often
need substantial revision before you’re pleased with the results.
After you develop a spreadsheet model, how do you evaluate it? One useful way to
think about the quality of a spreadsheet is to assess it along three dimensions. Specifically, a spreadsheet model should be correct, flexible, and documented. A spreadsheet model should produce the correct answer for the information given. Usually we
think of this in the context of the base case: for the “given” values for the inputs, does
the model calculate the correct results? However, a spreadsheet model needs to do
more than simply calculate the correct answer.
A spreadsheet model must be flexible in producing accurate results even if the
user changes any of the inputs (controllable or uncontrollable). To provide this flexibility, users should enter each input only once in the model. For example, consider
a model that includes a unit cost as an input. This value, say $3.50, would be entered
into a single cell, for instance, B8. Any other cells using unit cost in their calculations would then reference cell B8 rather than having the $3.50 value “hard-coded”
inside its formula. In this way, the user only needs to change the data item in a single
cell to analyze a new problem. Flexibility is often ignored by people developing a
spreadsheet they think will only be used once. Most models in the real world are
used repeatedly, with different input data. Even if you think yours is a model that
will be used only once, your model will be easier to explain to others if every input
is shown explicitly and only once. It is vital to get into the habit of building flexible models from the start. The key is to keep all the inputs of the model separate
from the formulas of the model. That is, never embed a numerical input inside a
spreadsheet formula. If you do, and that problem input changes, then the user must
(1) know how to edit formulas, and (2) remember to edit the formula in order for
the solution to be correct.
Finally, a spreadsheet model should be well documented. To ensure that others
understand the ability of a model, follow these guidelines:

SuppA.indd 5
 Correct model
A model is correct if it
produces the numerically
correct values for the outputs
for the current values of the
 Flexible model
A model is flexible if it
produces the numerically
correct values for the outputs
for any legitimate values of
the inputs, without making
any changes to the formulas
in the model.
 Documented model
A model is documented
if someone else generally
familiar with the situation
can understand the model
without having the model
developer explain it in detail.
A documented model can
be put away for months at a
time, and when viewed again
by the modeler, is readily
Include descriptive text labels for all numerical inputs and calculations. Include
in the label the units of measure of the quantity (e.g., money, hours, pounds,
square feet).
27/07/12 10:33 PM

Use numerical formatting (Format/Cells/Number) to display the numerical information in the model. The most common useful formats are number (fixed number of decimal places), currency, and percentage. Others are also useful in certain
situations (e.g., date, time, custom formats).
Apply appropriate cell formatting, such as fill colors, font colors, text attributes
(e.g., bold, italic), and cell borders. As noted, color-code the inputs and the outputs
of the model. A judicious use of coloring can make certain items in a model stand
out for the user. Do not overformat, however, as too much is distracting. In the
examples provided in this supplement, we use a standard formatting convention.
Insert Cell Comments for key cells. To use Excel’s Comment feature, right-click
any cell. From the submenu, click Insert Comment. You can type anything you
want into the box that pops up. This box will remain “attached” to the cell and can
even be printed with the model. Use comments to add explanatory information
about a calculation or assumption that does not have to be in the model itself.
They can help remind you of the logic behind a calculation or the justification for
a particular value of an input. In many of the spreadsheet examples in this supplement, we used the Cell Comment feature to show the formula in a cell.
Print a copy of the spreadsheet itself with row and column headings, gridlines,
and a footer. Also, print a copy of the spreadsheet formulas.
Ideally, a spreadsheet model should be self-documenting. That is, there should
be little work involved in documenting a spreadsheet model if you adopt a logical
structure for the data and calculations, add descriptive labels that include the units
of measure, use numerical and cell formatting appropriately, and provide additional
comments to highlight or explain key, or possibly more difficult, aspects of the model.
Before You Go On
A model is a purposeful representation of a real situation, designed to address a particular situation. We use mental
models every day to make many decisions. Visual and physical models help people to better understand a situation. Mathematical models comprise a set of relationships linking inputs to outputs. Mathematical models are
often implemented using spreadsheets. The spreadsheet modeling process consists of understanding the problem,
planning the spreadsheet on paper, developing the base-case model, testing the model, using the model for analysis,
and documenting the model. Scenario and sensitivity analysis are useful tools used to …
Purchase answer to see full

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
The price is based on these factors:
Academic level
Number of pages
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 30% with the discount code ESSAYSHELP