MIS775 – Decision Modelling for Business Analytics – Trimester 3 2020 Assessment Task 1 – Technology Project Selection – Group Assignment
DUE DATE AND TIME: PERCENTAGE OF FINAL GRADE: WORD COUNT:
Description
Sunday, 13 December 2020, by 8:00pm (AEST) 20%
1200 words
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 (6 marks)
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 (6 marks)
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 (6 marks)
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. Page 2 of 6
Report (12 marks)
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.
Learning Outcomes
This task allows you to demonstrate your achievement towards the unit learning outcomes. The ULOs are aligned with specific graduate learning outcomes – that is, the skills and knowledge graduates are expected to have upon completion of their studies – and this assessment task is an important tool in determining achievement of those outcomes.
If you do not demonstrate achievement of the unit learning outcomes, you will not be successful in this unit.
It is good practice to familiarise yourself with the ULOs and GLOs as they provide guidance on the knowledge, understanding and skills you’re expected to demonstrate upon completion of the unit. In this way they can be used to guide your study.
Unit Learning Outcomes (ULO)
Graduate Learning Outcomes (GLO)
ULO 1: Conceptualise, formulate and represent a business problem as a decision model.
GLO 1: Discipline-specific knowledge and capabilities: appropriate to the level of study related to a discipline or profession
ULO 2: Develop and solve business problems using advanced decision modelling techniques such as optimisation, stochastic modelling and risk analysis in spreadsheets.
GLO 5: Problem Solving: creating solutions to authentic (real world and ill-defined) problems
ULO 3: Interpret and analyse the results; investigate the sensitivity of the solutions to the assumptions of the decision model.
GLO 4: Critical thinking: evaluating information using critical and analytical thinking and judgment
Page 3 of 6
Submission
You are to submit your assignment in the group Assignment Dropbox in the MIS775 CloudDeakin unit site on or before the due date. Two files should be submitted – an Excel spreadsheet and PowerPoint file.
When uploading your PowerPoint file, name your document using the following syntax: