Reference no: EM132730755
MIS775 Decision Modelling for Business Analytics - Deakin Business School
LO 1: Conceptualise, formulate and represent a business problem as a decision model.
LO 2: Develop and solve business problems using advanced decision modelling techniques such as optimisation, stochastic modelling and risk analysis in spreadsheets.
LO 3: Interpret and analyse the results; investigate the sensitivity of the solutions to the assumptions of the decision model.
Assessment Task - Technology Project Selection
Description
This assignment is designed to let you explore and evaluate three optimisation approaches to choosing a set of projects to invest in, and then write up your findings in a technical report.
Acme Group Inc. is a multinational strategy consultancy with global headquarters in Melbourne, Australia. The company offers strategic solutions to its clients and has an annual revenue of $500M. In order to maintain their competitive position in the marketplace Acme Group needs to continually look for opportunities to invest in new technologies. The Chief Information Officer, Jane Porter, is currently considering twelve technology proposals put forth by three divisions within the Group - Marketing, R&D, and HR. Each division has provided Jane with the capital expenditures required for each project over the next three years and, based on the expected revenue to be generated by each project, the NPV of each project as shown below.
|
Project
no.
|
Division
|
Capital expenditure* ($M)
|
NPV* ($M)
|
|
Year 1
|
Year 2
|
Year 3
|
|
1
|
Marketing
|
2.5
|
1.0
|
1.0
|
0.6
|
|
2
|
Marketing
|
5.0
|
3.0
|
3.0
|
1.8
|
|
3
|
Marketing
|
1.0
|
2.0
|
4.0
|
0.8
|
|
4
|
Marketing
|
7.5
|
5.0
|
3.0
|
3.1
|
|
5
|
Marketing
|
2.0
|
4.0
|
8.0
|
2.2
|
|
6
|
R&D
|
10.0
|
3.0
|
3.0
|
1.8
|
|
7
|
R&D
|
7.5
|
7.5
|
3.0
|
4.1
|
|
8
|
R&D
|
8.0
|
7.0
|
6.0
|
2.8
|
|
9
|
R&D
|
4.0
|
6.0
|
8.0
|
3.8
|
|
10
|
HR
|
1.0
|
2.0
|
4.0
|
1.0
|
|
11
|
HR
|
7.0
|
5.0
|
3.0
|
2.6
|
|
12
|
HR
|
15.0
|
4.0
|
4.0
|
3.4
|
After consulting with the Chief Financial Officer, John Clayton, the CEO, Edgar Burroughs, has given Jane an overall three-year budget of $100 million for the set of projects she approves, with a maximum of $40 million available in any one year.
Jane has now tasked you with developing spreadsheet-based decision models that can be used to explore the twelve investment alternatives. The information that Jane is particularly interested in is the following:
1. Number of projects undertaken in each division
2. Capital expenditures ($M) - company wide and by division.
3. NPV and ROI by project, as well as the overall NPV and ROI across all selected investments.
Your analysis will be conducted in MS Excel using the Solver optimisation routine and your report will be written in MS Powerpoint. Only one member of each group is required to submit the group's Spreadsheet and PowerPoint report.
Specific Requirements
The assignment requires you to consider three different approaches to project set selection, as described below, and to write a report in PowerPoint.
Optimisation Approach 1
For this approach, Jane wants to regard each project as being scalable. In other words, a percentage of each project can be invested in, anywhere from 0% to 100%. For example, if she were to invest in 75% of a project then the annual CXs and NPV for the project would be correspondingly reduced to 75% of the quoted amounts.
She wants you to find the values of these project percentages that maximise the following expression:
(1 - r) × (Overall NPV) - r × (Overall CX)
such that for each division the sum of the percentages of the selected projects is at least 100%.
The parameter r is a measure of risk aversion. For example, someone who chooses r = 0 is unconcerned with cost, and is instead completely focused on maximising the overall NPV. At the other extreme, someone who chooses r = 1 is focused on minimising the overall cost. Values of r between 0 and 1 indicate varying degrees of risk aversion.
Your tasks here are:
• Construct a spreadsheet model for each of (i) r = 0, (ii) r = 1, and (iii) your choice of r. Each model should be on a separate worksheet.
• Use Solver to determine project weights that optimise the objective for each spreadsheet model.
• Produce sensitivity reports for each optimal solution.
Optimisation Approach 2
In this approach, Jane wants you to assume that each project is either fully funded or is not invested in (i.e. projects are no longer scalable). She wants to a set of exactly 8 projects, with at least one project selected from each division. She wants no more than 3 of the projects to be chosen from Marketing. Finally, she wants the selection of project 12 to be contingent on both projects 10 and 11 being selected.
The goal is to achieve the maximum overall NPV, subject to the necessary requirements. Your tasks here are:
• Construct a spreadsheet model.
• Use Solver to determine the optimal set of projects .
Optimisation Approach 3
In this approach, Jane wants to again regard each project as being scalable, with at least one project to be selected from each division. This time her goal is to achieve the maximum overall ROI, subject to the necessary requirements.
Your tasks here are:
• Construct a spreadsheet model.
• Use Solver to determine the optimal set of projects.
Report
The PowerPoint document should present all your results in a coherent and compelling manner. Each model should be accompanied by the following:
• A conceptual diagram of the model
• An algebraic formulation of the model
• The optimal solution
• Interpretation of sensitivity analyses output for Optimisation Approach 1. (Use Solver's sensitivity analysis reports to comment on how changes to constraints might affect the optimum set of projects.)
Then, based on your assessment of the various approaches, briefly explain which approach you would prefer to use for selecting the optimal set of projects, and why. Include a summary table listing the details of each optimal set of projects.
Attachment:- Decision Modelling for Business Analytics.rar