Decision Modeling with Spreadsheets Analysis - Data Science Assignment

This assignment solution addresses several decision modeling problems using spreadsheet-based techniques. It presents solutions to questions involving linear programming, sensitivity analysis, and integer linear programming (ILP) models. The assignment covers various scenarios, including optimizing production costs, project selection with budget and resource constraints, and manufacturing cost minimization. Detailed answer reports, sensitivity analyses, and optimal solutions are provided for each problem. The student utilizes spreadsheet tools to analyze constraints, objective functions, and variable cells to arrive at optimal decisions. The document also references relevant literature on linear programming and optimization methods.

Running head: DECISION MODELING WITH SPREADSHEETS
DECISION MODELING WITH SPREADSHEETS
Name of Student:
Name of University:
Author Note:

1DECISION MODELING WITH SPREADSHEETS
Table of Contents
Answer to Question: 5_25...............................................................................................................2
Answer to Question: 5_30...............................................................................................................3
Answer to Question: 6_4 Part 1.......................................................................................................4
Answer to Question: 6_4 Part 2.......................................................................................................4
Answer to Question: 6_17...............................................................................................................5
Answer to Question: 6_19...............................................................................................................6
Reference List..................................................................................................................................7

2DECISION MODELING WITH SPREADSHEETS
Answer to Question: 5_25
Answer Report
From the answer report, it is evident that the optimal solution of the Linear Programming
has a value of 273.657603 with respect to constraints 0, 0, 50, 0, 50, 0 and 200 respectively. The
time constraint is declared as binding with slack value zero such that is satisfies the equality of
the optimal solution.
Sensitivity Report
The sensitivity report is an effective tool to estimate the sensitivity of a project when
there is a change in three parameters such as changing the objective function coefficient for a
variable, changing the right-hand side of a constrain and forcing a variable that is currently zero
to be non-zero.
Changing the objective function
Currently X=273.657603 and has objective coefficient value of 1. The value for
allowable decrease or increase tells that objective function of the variables lies between 1-1
Forcing a variable that is currently zero to be non-zero
The change in the value of objective function is denoted by the reduced cost column. For
the variable A1, the objective function must change by 0.010488889 ( the value of allowable
decrease is considered since the objective is to minimize the cost) such that the profit per unit on
A1 can go down by 0. 010488889. Similarly the profit for D1 can decrease by 0.008365465. This
same for all variants with zero value.
Changing the right-hand side of a constraint

3DECISION MODELING WITH SPREADSHEETS
The shadow price determines the change in the value of the objective function due to a
change in the value of right-hand side of a constraint with respect to given limits, allowable
increase or decrease.
Answer to Question: 5_30
The optimum value for the program is 20150 where the original value was zero. The final
value has been calculated with respect to variable cells 250, 150, 600, 0, 250, 0, 150, 500, 100,
500, 0, 500, 0, 300, 100 and 100 respectively. As evident from the answer report that the time
constraint is binding for the optimal solutions with respect to constraints
Sensitivity report
Changing the objective function
The current solution value for P1Flow is 250. It has an objective coefficient value of 0.
The value for both allowable increase and decrease is 1 (Okubo et al., 2015). As the objective is
to minimize the cost function, the value for allowable decrease ids taken such that the value lies
between 0-1. Similarly, P2Fow has final value 150 with zero objective. The allowable decrease
is 1 such that values can vary between 0-1. This is similar for all the other cases.
Forcing to a zero variable to be non-zero
Value of W1in Flow is zero and in order to change the value of the variable, the value of
allowable increase or decrease will be considered. It proposes that values of the variable can be
changed by 1 as the program considers the value fir allowable decrease for minimization.
Changing the right-hand side of a constraint

4DECISION MODELING WITH SPREADSHEETS
The values of the variables can be changed with respect to right-hand side constraint of
the variable. For the P1 variable, the shadow price is zero with the right hand side constraint
being -400 such that the constraint can be changed by such values.
Answer to Question: 6_4 Part 1
The net present value of the CRT Technologies project has been calculated and the
desired project has been chosen from the process. They are able to choose values and variables
that gives the effective outcomes. The optimal solution of the linear programming is 489 dollars.
The original value of the game is zero and the final value is $489. The LPP finds the projects that
are most feasible out of the six projects. This is done by building a LPP model (Okubo et al.,
2015). The values for the projects with respect to output and time span are written in a definite
manner. The net present value is maximized with respect to changing variable cells. The
changing variable cells are binary such that if output is maximized, then condition is 1 else 0.
This is done by giving conditions or constraints such the sum of product for the required capital
is less than or equal to the available capital. The value of variable cells are also included in the
constraint portion by putting binary conditions. This gives the optimum solution of the game and
Project1, Project 4, Project 5 will be feasible.
Answer to Question: 6_4 Part 2
The optimal solution of the game decreases to $268 from 489 after several conditions
were added to determine the optimal solution for the problem. Several constraints were added in
the process of project selection and determine the feasible solution. Four conditions were added
and a model is built that measures the value of 4 conditions for the six projects.

5DECISION MODELING WITH SPREADSHEETS
The first condition is that out of projects 1, 2, 4 and 6, exactly two projects are to be
selected. Out of six projects, four projects will be chosen (Guastaroba et al., 2016). Therefore,
places corresponding to Project 1, Project 2, Project 4 and Project 6 are represented with 1. Rest
of the places for Project 3 and Project 5 will be represented by zero. The limit for the condition
will be 2 as two projects will be selected.
The second condition is that Project2 will be chosen only when Project 3 is chosen and
vice versa. This implicates that the two variables has equal values such the sum of the
coefficients are zero which is possible only if both the values are zero or coefficient of one
variable is 1 and the other is -1. The condition is that all the project has value zero except for
Project 2 and Project 3 that as values 1 and -1 respectively with limit being zero. The third
condition says that Project 5 cannot be taken if both Projects, 3 and 4 are not taken. This
proposes that twice the value of fifth variable is the summation of variable3 and variable4
respectively. Thus, the coefficient of variable 3, variable 4 and variable 5 are -1, -1 and 2
respectively, where the coefficients of other variables are zero as they are non-performing. The
limit given for the condition is zero.
The fourth condition is that Projects, 2 and 4 are undertaken when Project 5 is
considered. This condition is similar to the third condition which shows that sum of values of
second and fourth variable is equal to the twice of fifth variable. The values for other variables
are assumed as zero and the limit is considered as zero due to the sum of the variables. The sum
of the product of these new conditions are calculated with initial variable cells (Tempelmeier &
Hilger, 2015). All these conditions are added in the constraint parameter of the total net present
value by conditioning the changing variables equal to the limits. This proposes the solution as
268 dollars and the chosen projects will only Project 1 and Project 6 respectively.

6DECISION MODELING WITH SPREADSHEETS
Answer to Question: 6_17
The manufacturing costs are formulated with respect to brake producing shoes on each
machine of the Radford Castings. This has been formulated by using an ILP model which is
represented in excel sheet 6_17. The fixed cost, variable cost, capacity and availability of
machines are given. The company received an order for 1,800 brake shoes. This is evaluated by
changing the objective of the variables with respect to the capacity. The optimal solution of the
LP is 42300 dollars that is the minimized cost for the project. The initial value was zero that
changed to 42300 dollars after the mode has been solved in the solver. The machine will operate
for units 1, 2, 4 and 5 respectively. It would not operate on quantity 3 and 6 as the cost is not
minimized for these capacities that is not effective for generating the optimal solution of the
game.
Answer to Question: 6_19
The budget for the new project is limited to 950,000 dollars and has only 20 programmers
to assign the new projects. The returns, financial requirements and number of programmers
required were provided the paper. The condition is that Project 2 and Project 6 require special
programming knowledge that is gained by only one programmer. ILP model has been designed
for the project and the solution has been solved in the solver. The capital required is provided
which is effective to estimate the required programmers for the project. The net present value has
been minimized with respect to changing variable cells where decision for selection is
represented by 1 and decision for non-selection is represented by zero (Tempelmeier & Hilger,
2015). The cost has been calculated and the budget has estimated with subject to budget,
programmers and specialized programmer such as 735, 19, 1 with limitation less than or equal to
950, 20 and 1 respectively. The value of NPV is maximized for projects 1, 6 and 7. Theretofore,

7DECISION MODELING WITH SPREADSHEETS
the chosen projects are 1, 6 and 7 respectively where the value of revenue will be maximum with
the optimal value being $1925.

8DECISION MODELING WITH SPREADSHEETS
Reference List
Guastaroba, G., Mansini, R., Ogryczak, W., & Speranza, M. G. (2016). Linear programming
models based on Omega ratio for the enhanced index tracking problem. European
Journal of Operational Research, 251(3), 938-956.
Okubo, H., Sasaki, S., Murakami, K., Yokoyama, T., Hirota, N., Notsu, A., ... & Date, C. (2015).
Designing optimal food intake patterns to achieve nutritional goals for Japanese adults
through the use of linear programming optimization models. Nutrition journal, 14(1), 57.
Tempelmeier, H., & Hilger, T. (2015). Linear programming models for a stochastic dynamic
capacitated lot sizing problem. Computers & Operations Research, 59, 119-125.