QBA 3000 Case Study Exam-Linear Optimization
Please carefully follow the following instructions when submitting your paper:
Please submit two files:
A report file in MS Word format.
All solutions in MS Excel format.
Save the files as "lastname_Case Study Exam 4".
The completed files must be submitted to the D2L dropbox by the due date.
Late submissions will not be accepted and result in a grade of 0.
Your report file must include all necessary output/graphs. Ensure that all
graphs, figures etc. have captions.
You must turn in your Excel spreadsheet separately with clear spreadsheet
titles e.g., Question 1: XXX, etc.
The paper must be presented in a standard 10- to 12-point font, with 1-inch
margins, and include APA format citations to any references used
The paper must include a title page that displaying student’s name, email
address, and your major
The paper must be submitted to the correct D2L Assignments/Dropbox folder
by the indicated due date.
The submitted final report and Excel file must be your original work, (see the
Academic Integrity policy in syllabus). Signature Assignments (Projects) and
other assignments in this class are to be completed independently.
Only the following resources may be used: the textbook, assigned readings, and other
course materials linked directly through the course D2L shell or the Excel Help files.
If you have any questions about the assignment, please email me.
Good luck!
By submitting this homework assignment, I hereby certify that I have abided by the exam rules and the course Academic Integrity
Policy
1
QBA-3000 Analytics and Applications -
QBA 3000 Case Study Exam-Linear Optimization
Case Description
As a business analyst in a consulting firm, you are asked to analyze
1. Two investment problems for PENNWEST-BUS Mutual Funds. The upper management of PENNWEST-BUS Mutual Funds
is willing to determine the best strategy to maximize the total profit.
2. Create a linear programming/optimization model for an investment planning problem. The linear programming model
should consist of
a. The decision variables
b. Objective function
c. Constraints
Your manager has provided you with the available information, leaving it to your discretion to determine the most effective methods
for addressing their inquiries. It's essential that you elucidate your analysis and calculations (or the reasons for not applying certain
procedures) in a manner comprehensible to individuals unfamiliar with quantitative analysis. The expectation for this project is not
solely a numerical response to their queries; your explanation and presentation of findings are equally important.
Deliverable
Your manager has specified that you are to respond to their inquiries through a business report in MS Word format and all
solutions in Excel Solver Answer report format. Alongside this, you are required to provide the solution file(s). Presenting plain
answers directly in the document will be seen as unprofessional. The content must be error-free and comprehensible, even to
individuals unfamiliar with quantitative analysis. While the report's design is at your discretion, the upper management emphasizes
organized and coherent answers over aesthetics.
2
QBA-3000 Analytics and Applications -
QBA 3000 Case Study Exam-Linear Optimization
In the report, you should outline the details of the linear programming mathematical model, including decision variables, the objective
function, and constraints. The upper management specifically requests the inclusion of any output files created in MS Excel to
ensure transparency.
It's advisable to follow a similar design in the Excel solution file as it's covered in Linear Optimization chapter.
To sum up, your report should encompass accuracy, clarity, and transparency, in line with professional standards.
Questions (Please note that there are two parts in this question: Part a and b)
1. Linear Optimization
PennWest-Bus Mutual Funds, Inc., headquartered in Pittsburgh, has recently acquired $10,000,000 by liquidating industrial bonds for
cash. Presently, PennWest-Bus is seeking alternative investment avenues for these funds. Considering the firm's existing investment
portfolio, the principal financial analyst at PennWest-Bus proposes that all new investments be directed towards the oil industry, steel
industry, or government bonds across the United States and Europe. The investment options, associated locations, and corresponding
rates of return are detailed in the subsequent table.
Investment Opportunities for PennWest-Bus Mutual Funds
Location Investment Projected Rate of Return %
B&E Steel 7.3
City Pittsburgh
Oakland Oil 8.6
Pittsburgh Steel 6.8
Atlantic Oil 8.9
York
New
Pacific Oil 10.5
3
QBA-3000 Analytics and Applications -
QBA 3000 Case Study Exam-Linear Optimization
Huber Steel 6.8
Midwest Steel 6.5
Government Bonds 4.5
Buckingham Steel 8.2
Europe
Eiffel Oil 7.2
Florence Oil 7.6
EU Government Bonds 5.3
To maximize the projected rate of return, portfolio recommendations in terms of investments and their respective amounts (e.g. how
much should be invested in each investment opportunities) need to be determined for the available $10,000,000. It's important to note
that two distinct mathematical models should be developed for parts A and B, and these should be implemented in separate Excel
spreadsheets.
For further clarity, it would be helpful to have more context or information about parts A and B, as well as any specific requirements
or constraints that need to be considered while developing the mathematical models and the Excel spreadsheets.
Part A. The budgetary and managerially imposed constraints are listed below.
PennWest-Bus should be investing all available $10,000,000.
The Europe investments cannot exceed the 30% of the overall budget.
Total Oil Investments cannot exceed the 50% of the total budget.
Total Steel Investments cannot exceed the 50% of the total budget.
PennWest-Bus should invest on Pittsburgh funds at least 20% of the budget.
PennWest-Bus should invest on New York City funds at least 25% of the total budget.
PennWest-Bus should invest on Europe funds at least 15% of the total budget.
At most 15% of the budget can be spent on government bonds.
Oil sector investments in Europe should be more than or equal to Steel sector investments in Europe.
4
QBA-3000 Analytics and Applications -
QBA 3000 Case Study Exam-Linear Optimization
In your report (MS Word File), please include your mathematical model, excel output, and list the amount of investments for
each investment opportunity in a table and interpret your findings.
Part B. The upper management of PennWest-BUS Mutual Funds also considering another scenario with the following budgetary and
managerially imposed constraints
PennWest-Bus should be investing all available $10,000,000.
The Europe investments cannot exceed the 20% of the overall budget.
Total Oil Investments cannot exceed the 30% of the total budget.
Total Steel Investments cannot exceed the 50% of the total budget.
PennWest-Bus should invest on Pittsburgh funds at least 50% of the budget.
PennWest-Bus should invest on New York City funds at least 25% of the total budget.
PennWest-Bus should invest on Europe funds at least 15% of the total budget.
At most 45% of the budget can be spent on government bonds.
Oil sector investments in Europe should be less than or equal to Steel sector investments in Europe.
In your report (MS Word File), please include your mathematical model, excel output, and list the amount of investments for
each investment opportunity in a table and interpret your findings.
5
QBA-3000 Analytics and Applications -
QBA 3000 Case Study Exam-Linear Optimization
This project directly assesses elements of the following program-level objectives (PLO) and course-level objectives (CLO)
PennWest Program Learning Objectives QBA3000
PLO1. Demonstrate proficiency in foundational concepts of CLO3. Identify the assumptions of the linear programming and
functional business areas integer linear programming models; find solutions to these models
graphically and using Excel; appropriately apply the models to
PLO3. Display cultural competence for ethical, sustainable, various situations (e.g. network flow problems, transportation
inclusive, and collaborative leadership practices including a global problems, optimal flow, shortest route, etc.)
perspective
PLO4. Effectively communicate in oral, written, visual, and digital CLO4. Apply course tools and concepts to produce business
formats insights and actionable information and effectively communicate
to decision makers in writing and oral presentation using
appropriate data visualization tools.
Grading Rubric
Criteria Advanced (100%) Proficient (85%) Basic (75%) Developing (65%) Unsatisfactory (0%)
PLO1; CLO3 The mathematical The mathematical The mathematical The mathematical The mathematical
Mathematical models are models are accurate models are functional but models show limited models fail to meet
Models in comprehensive, and mostly complete. incomplete or understanding of the basic requirements.
MS Word precise, and efficient. inconsistent. problem and
report 40% The objective functions optimization concepts. The objective functions
The objective are correctly The objective functions are missing,
functions are formulated and align are mostly accurate but The objective fundamentally
precisely formulated, with the optimization may have noticeable functions contain incorrect, or unrelated
mathematically problem's goal but may errors or fail to fully significant errors or to the optimization
6
QBA-3000 Analytics and Applications -
QBA 3000 Case Study Exam-Linear Optimization
correct, and contain minor errors or capture the optimization are misaligned with problem's goal.
effectively captures lack refinement. problem's goal. the problem's goal.
the goal of the Constraints are absent,
optimization problem. Most constraints in Some constraints in both Many constraints in entirely incorrect, or
both models are clearly models are one or both models are irrelevant in both
All constraints in both identified, logically misinterpreted, poorly formulated, models, demonstrating
models are clearly structured, and mathematically incorrect, irrelevant, or missing, no meaningful progress
identified, logically mathematically correct, or missing, resulting in a lack of or understanding of
structured, and with only minor demonstrating a basic logical structure and linear optimization
mathematically inaccuracies or understanding of linear significant need for principles.
correct. omissions that do not optimization principles revision.
significantly affect the but requiring significant
solution. improvement.
PLO3; CLO4 All Solver parameters Most Solver Solver parameters are Solver parameters are Solver parameters are
Excel Solver (objective function, parameters (objective partially correct but inadequately defined, missing or entirely
45% decision variables, and function, decision contain noticeable errors with major errors in incorrect, with no
constraints) are variables, and or omissions. the objective function, alignment to the
correctly defined and constraints) are decision variables, or optimization problem.
entered. correctly defined and Solver settings are constraints.
entered, with minor functional but incomplete Solver settings are
The objective function errors that do not or inconsistent, such as Solver settings are absent or completely
is correctly linked to impact the result. using a suboptimal inappropriate or inappropriate, rendering
the target cell. solving method or poorly configured, the problem unsolvable.
Solver settings are missing key constraints. preventing the
Decision variable cells mostly appropriate, software from Solver does not provide
are appropriately with only slight Solver provides a correctly solving the a solution, or no solver
defined, and missteps (e.g., an solution, but results are problem. setup.
constraints are clearly incorrect solving not feasible or optimal.
implemented with method or unnecessary Solver fails to provide
proper references.
7
QBA-3000 Analytics and Applications -
QBA 3000 Case Study Exam-Linear Optimization
Solver settings (e.g., constraints). a feasible solution.
solving method,
constraints, Solver runs
optimization type) are successfully and
appropriately provides a feasible
selected and solution.
demonstrate a clear
understanding of the
problem.
Solver runs
successfully and
provides a feasible and
optimal solution.
PLO4; CLO4 The results from Excel The results are mostly The results are partially The results are The results are missing
Output & Solver are accurately accurate and accurate or incomplete, inaccurate or largely or entirely incorrect,
Results in and fully presented. complete, with minor with noticeable errors or incomplete, failing to with no connection to
MS Word errors or omissions. missing components. include key outputs the Solver output.
Report 15% The optimal solution, (e.g., optimal solution
including all decision The optimal solution Some decision variables or objective function The results are absent
variables and the and objective function or constraints may not be value). or incoherent, with no
objective function value are presented but addressed. effort to present or
value, is clearly stated. may lack minor details. Interpretation of interpret Solver outputs.
Interpretation is results is simplistic or
The results are Interpretation of results superficial or contains partially inaccurate,
presented and is mostly accurate but inaccuracies. showing some effort
discussed in a clear may lack depth or clear but lacking depth.
and concise manner. linkage to the problem.
8
QBA-3000 Analytics and Applications -
QBA 3000 Case Study Exam-Linear Optimization
Criteria Business Non-Business
Student Major
9
QBA-3000 Analytics and Applications -
