Question

1. (a) What are the different types of spreadsheet modeling? Give a real world example for each type where spreadsheet modeling and analysis is useful.

(b) What is an infeasible linear optimization problem? How do we find if a given linear optimization problem is infeasible? Give a real world example of an infeasible linear optimization problem.

(c) What is a blending problem? Briefly discuss the objective function and constraint requirements in a blending problem. Give a real world example of a blending problem.

(d) Explain how the simulation process is used in business analytics models. What are the advantages of using simulation? What are its limitations? How can a simulation model be verified? Give a real world example where using simulation is appropriate.

2. Given the following linear optimization problem

Maximize 10x + 20y

Subject to

      x + y < 50

2x + 3y < 120

            x > 10

        x, y > 0

(a) Graph the constraints and determine the feasible region.

(b) Find the coordinates of each corner point of the feasible region.

(c) Determine the optimal solution and optimal objective function value.

Answer 1

Solution :

a)

Spreadsheet modelling means : Computer models of mathematical data, such as budgets which are usually done using a spreadsheet application that processes and performs calculations on the data entered by the user.

Distinct types of spreadsheets are defined by their format. For example, Microsoft Excel has three options for spreadsheet format: Simple tables, Excel tables and Pivot tables.

Simple spreadsheets are the most commonly used type, and you have to make most changes manually. For example, if you set up a simple table and want to refer to the table as a whole in a formula or instruction, you need to define border columns and rows and make sure any additional data is added between those cells.

The most distinct type of spreadsheet, though, is the Pivot table. This looks similar to a normal table, but each column has a drop-down menu you can use to filter or sort the results to suit your needs. For example, if you have a spreadsheet table listing sales volumes by product, you can easily sort the table from most sales to least. You can also filter results so that you only see one type of product – say, cell phones – by checking a box beside each eligible product.

Spreadsheets are used in

1) Financial Tracking: they allow the clear presentation of expenditures and incomes related to different departments and can be set up to display negative numbers in red. Templates for budget spreadsheets are included with spreadsheet programs to reduce the amount of setup work required.

2) Research Work/ Data Analysis and Statistics: For example, applying a formula to hundreds of data points would be a tedious, time-consuming process without some form of automation, and programs like Microsoft Excel offer just this capability.

3) Graphing and Presenting Data: Spreadsheet programs are valuable tools when you're looking to present data in the form of graphs or tables. The spreadsheet itself contains the data to be included on the graph, and spreadsheet programs have many types of graphs built in.

b)

A problem is said to be infeasible if no solution exists which satisfies all the constraints. The FICO Xpress Optimizer provides functionality for diagnosing the cause of infeasibility in the user's problem.

Before we discuss the infeasibility diagnostics of the Optimizer we will, firstly, define some types of infeasibility in terms of the type of problem it relates to and how the infeasibility is detected by the Optimizer.

We will consider two basic types of infeasibility. The first we will call continuous infeasibility and the second discrete or integer infeasibility. Continuous infeasibility is where a non–MIP problem is infeasible. In this case the feasible region defined by the intersecting constraints is empty. Discrete or integer infeasibility is where a MIP problem has a feasible relaxation (note that a relaxation of a MIP is the problem we get when we drop the discreteness requirement on the variables) but the feasible region of the relaxation contains no solution that satisfies the discreteness requirement.

Either type of infeasibility can be detected at the presolve phase of an optimization run. Presolve is the analysis and processing of the problem before the problem is run through the optimization algorithm. If continuous infeasibility is not detected in presolve then the optimization algorithm will detect the infeasibility. If integer infeasibility is not detected in presolve then, in the rare occasion where this happens, a branch and bound search will be necessary to detect the infeasibility.

Example:Diagnosis in Presolve

The presolve processing, if activated, provides a variety of checks for infeasibility. When presolve detects infeasibility, it is possible to "trace" back the implications that determined an inconsistency and identify a particular cause. This diagnosis is carried out whenever the control parameterTRACE is set to 1 before the optimization routine XPRSlpoptimize (LPOPTIMIZE) is called. In such a situation, the cause of the infeasibility is then reported as part of the output from the optimization routine.

c)

Blending problems are a typical application of mixed integer-linear programming (MILP). They involve blending several resources or materials to create one or more products corresponding to a demand. Mixed integer-linear programs are linear programs in which some variables are required to take integer values.

Objective function represents how the decision variables affect the cost or value to be optimized (minimized or maximized)

Constraints represent how the decision variables use resources, which are available in limited quantities

Example 1: To determe the optimum amounts of three ingredients to include in an animal feed mix. The final product must satisfy several nutrient restrictions. The possible ingredients, their nutritive contents (in kilograms of nutrient per kilograms of ingredient) and the unit cost are shown in the following table.

The mixture must meet the following restrictions:

• Calcium — at least 0.8% but not more than 1.2%.
• Protein — at least 22%.
• Fiber — at most 5%.

The problem is to find the composition of the feed mix that satisfies these constraints while minimizing cost.

Nutritive content and price of ingredients Ingredient Calcium (kg/kg) Protein (kg/kg) Fiber (kg/kg) Unit cost (cents/kg) Limestone 0.38 0.0 0.0 10.0 Corn 0.001 0.09 0.02 30.5 Soybean meal 0.002 0.50 0.08 90.0

Example 2: Predator-Prey Problem

d)

Business analytic model uses simulation modeling and analysis as mechanisms to introduce and link predictive and prescriptive modeling because managers can't fully assess what will happen in the future, but must still make decisions, the book treats uncertainty as an essential element in decision-making.

Advantages

• It can avoid danger and loss of life.
• Conditions can be varied and outcomes investigated.
• Critical situations can be investigated without risk.
• It is cost effective.
• Simulations can be sped up so behaviour can be studied easily over a long period of time.
• Simulations can be slowed down to study behaviour more closely.

Limitations:

• It can be expensive to measure how one thing affects another, to take the initial measurements and to create the model itself (such as aerodynamic wind tunnels).
• To simulate something, a thorough understanding is needed and an awareness of all the factors involved. Without this, a simulation cannot be created

Following are the ways to perform verification of simulation model −

• By using programming skills to write and debug the program in sub-programs.
• By using “Structured Walk-through” policy in which more than one person is to read the program.
• By tracing the intermediate results and comparing them with observed outcomes.
• By checking the simulation model output using various input combinations.
• By comparing final simulation result with analytic results.

Example:

The popular series Call of Duty and in particular Call of Duty: Modern Warfare 2 provides a glimpse of an immersive environment which parallels many real world scenarios of law enforcement as well as military. Medical: The medical field uses simulations to train practitioners in a multitude of skills and environments.

There are more than 1 questions, as per the Q&A guidelines i am answering first question. If you want to get the answers for the rest of the parts, please post the question in a new post.

Please give me a thumbs-up if this helps you out. Thank you!