Exam 2   Chapters 6, 8, and 9 · The exam is due Tuesday, March 28th and will be accepted through Friday, March 31, 2023

Exam 2

Chapters 6, 8, and 9
· The exam is due Tuesday, March 28th and will be accepted through Friday,
March 31, 2023.
· The exam will be submitted through Canvas Assignments.
· Total points: 70
· there is only one graded attempt for the exam.
· Carefully read the naming conventions for the Excel workbooks that you
will submit for the exam.
· Please make sure your work is clear and easy to follow.
· This exam is the property of Dr. Marcy Jance and is not to be posted to
any web site or used outside of this class without permission from Dr.
Jance first.
· You are to work on this exam by yourself. No groups and no outside
assistance. If you have questions regarding the exam, please contact
your instructor.
· Students are to maintain academic honesty and professionalism while
working on this exam. Any use of another individual’s work will
result in a zero for the exam and possible other academic ramifications.
Problem 1: (Chapter 6 Material) 12 possible points
Please use a separate Excel workbook for this problem. Name the Excel workbook
as follows: Your last name_Problem1. For example, your instructor would name
it as follows: Jance_Problem1.

2
Suppose you are trying to maximize NPV (net present value). You have five (5)
potential projects to choose from. There is a maximum amount available each
year for project expenses, at least three projects must be chosen, and if Project B is
chosen then Project D must also be chosen. Here is the problem information:
Projects A B C D E
Net Present Value $600 $1,000 $1,700 $1,200 $1,500
Year 1 Expenses $100 $300 $400 $800 $300
Year 2 Expenses $200 $600 $600 $1,000 $600
Year 3 Expenses $300 $800 $300 $500 $900
Year 4 Expenses $400 $300 $200 $300 $1,200
Year 5 Expenses $600 $200 $100 $200 $1,500
Here is the total amount available for project expenses for Years 1 through 5:
Year Total Amount
Available
1 $1,200
2 $2,500
3 $3,000
4 $2,500
5 $1,500
1) Write out the mathematical model for this problem. Be sure to include the
objective function, decision variables, and constraints (4 points).
2) Setup the binary integer linear programming model in Excel to determine the
optimal solution (which projects should be chosen) and the resulting NPV.
Use Solver to find the optimal solution (8 points).
Please note, that your instructor will be checking your Solver settings for this
problem.
Problem 2: (Chapter 6 Material) 12 possible points
Please use a separate Excel workbook for this problem. Name the Excel workbook
as follows: Your last name_Problem2. For example, your instructor would name
it as follows: Jance_Problem2.

3
An organization services 12 different areas. It plans to setup tech centers to help
its clients with various needs. There are seven possible tech centers (A, B, C, D, E,
F, and G) that the organization can use. The organization needs to ensure that
each area is covered by at least one tech center. The organization wants to
determine the minimum cost. The following shows the proposed tech centers and
areas that they would service. The cost per tech center is also shown.
Tech
Centers
Cost Areas
Covered
A $ 2,500 2, 4, 10
B $ 2,700 3, 6, 12
C $ 3,520 1, 4, 5
D $ 1,790 4, 7, 9
E $ 2,600 1, 8
F $ 1,950 2, 5, 8
G $ 2,820 6, 9, 11
1) Write out the mathematical model for this problem. Be sure to include the
objective function, decision variables, and constraints (4 points).
2) Setup the binary integer linear programming model in Excel to determine which
tech centers should be selected and the total minimum cost involved. Use Solver
to find the optimal solution (8 points).
Please note, that your instructor will be checking your Solver settings for this
problem.
Problem 3: (Chapter 6 Material) 12 possible points
Please use a separate Excel workbook for this problem. Name the Excel workbook
as follows: Your last name_Problem3. For example, your instructor would name
it as follows: Jance_Problem3.
Your company is considering seven new products (A, B, C, D, E, F, and G).
There is a capacity constraint for each product and a setup cost is incurred if the
product is produced. The company wants to determine which products to
produce in order to maximize net profit. The net profit is defined as total profits
for the products produced – total setup costs incurred. The company wants to
limit setup costs to no more than $12,000 and all products produced need to be

4
integers. Here are the setup costs, profit per unit, and capacity constraints for the
products.
Products Setup Cost Profit per Unit Capacity
A $2,500 $30 530
B $3,000 $40 700
C $1,800 $25 690
D $2,300 $29 400
E $3,200 $31 650
F $2,600 $38 750
G $2,800 $43 700
1) Write out the mathematical model for this problem. Be sure to include the
objective function, decision variables, and constraints. (4 points)
2) Setup the integer and binary linear programming model in Excel to determine
which products to produce and the total net profit. Use Solver to find the optimal
solution (8 points).
Please note, that your instructor will be checking your Solver settings for this
problem.
Problem 4: (Chapter 8 Material) 12 points possible
Please use a separate Excel workbook for this problem. Name the Excel workbook
as follows: Your last name_Problem4. For example, your instructor would name
it as follows: Jance_Problem4.
Your company is building a new warehouse to serve six cities. It would like to
minimize the total distance. The company wants to ensure that the warehouse is
within 35 miles of each city. Here are the longitude and latitude coordinates for
each city.
Longitude Latitude
City 1 90 80
City 2 75 63
City 3 67 89
City 4 45 102

5
City 5 103 120
City 6 57 89
1) Write out the mathematical model for this problem. Be sure to include the
objective function, decision variables, and constraints (4 points).
2) Setup the nonlinear programming model in Excel to determine where the
warehouse should be placed and the total distance. Use Solver to find the optimal
solution (8 points).
Please note, that your instructor will be checking your Solver settings for this
problem.
Problem 5: (Chapter 9 Material) 11 points possible
Please use a separate Excel workbook for this problem. Name the Excel workbook
as follows: Your last name_Problem5. For example, your instructor would name
it as follows: Jance_Problem5.
You want to run a simple linear regression to see if there is a significant
relationship between promotions and sales. Let Total Sales be the dependent
variable. Provide the following:
Number of Promotions Total
Sales
10 2157
9 1851
10 1934
2 2588
6 1894
8 2132
7 2189
1 2737
11 1800
2 2928
1 2645

6
· Create a scatter diagram. Be sure to include axis titles on your graph and a
trend line with the equation (2 points).
· Run a linear regression using Excel’s Data Analysis regression tool. You
need to show the Excel Data Analysis regression results in your work (5
points).
· Construct the linear regression equation (1 point).
· Determine the predicted total sales value if the number of promotions is 5 (1
point).
· Is there a significant relationship? Clearly explain your reasoning using the
regression results (2 points).
Problem 6: (Chapter 9 Material) 11 points possible
Please use a separate Excel workbook for this problem. Name the Excel workbook
as follows: Your last name_Problem6. For example, your instructor would name
it as follows: Jance_Problem6.
You want to run a multiple linear regression. Let y be the dependent variable and
x1 and x2 are the independent variables.
x1 x2 y
32 13 1500
48 8 1800
23 11 1357
58 3 2700
35 4 1900
41 6 1700
56 5 2305
49 3 1899
47 13 2158
61 15 2678
57 2 1697

7
Provide the following:
· Run a linear regression using Excel’s Data Analysis regression tool. You
need to show the Excel Data Analysis regression results in your work (3
points).
· Construct the linear regression equation (1 point).
· Determine the predicted y value if x1 = 43 and x2 = 13 (1 point).
· Is there an overall significant relationship? Clearly explain your reasoning
using the regression results (2 points).
· Should x1 be included in the regression model? Why or why not? (2 points).
· Should x2 be included in the regression model? Why or why not? (2 points).