SOE11151 Strategic Project Risk Management Assignment

Answer all questions.

Note that these questions should be completed by using Excel (except for Case Studies 1 and 4), then copy and paste or screenshot the charts/tables/part of the excel to a word document. You may wish to add any relevant statement(s)/comment(s) when responding to the tasks on the word document. The word document should be page numbered, grammar and spell checked, and well-referenced, as well as all the answers clearly numbered.

The submission format should be one excel file and one word processed document, both of which should be uploaded in Moodle via Turnitin by the due date.

Note that this is an individual work and will be checked using Turnitin plagiarism detection software.

Case Study 1: Decision Tree Analysis

As a project manager working with diverse projects in Edinburgh, you must determine whether your organisation is to build a new £120m plant to enable it to produce a new range of remotely controlled air conditioning systems or whether you should upgrade the existing plant at a cost of £50m. Your financial people have calculated that if you build a new plant, you have a 60% chance of earning £200m if there is a strong demand as opposed to earning £90m if there is a weak demand. They have also told you that if you upgrade the existing plant, you have a 40% chance of earning £60m on weak demand and you expect to earn £120m on strong demand.

Use decision tree analysis to calculate the expected monetary values for each case and recommend as to which option you would endorse.

Case Study 2: Quantitative Risk Analysis

National Library of Scotland is considering publishing an electronic textbook about spreadsheet applications for organisations. The fixed cost of manuscript preparation, textbook design, and web site construction is estimated to be £160,000. Variable processing costs are estimated to be £6 per book. The publisher plans to sell single-user access to the book for £46.

a. Build a spreadsheet model to calculate the profit/loss for a given demand. What profit can be anticipated with a demand of 3,500 copies?

b. Use a data table to vary demand from 1,000 to 6,000 in increments of 200 to assess the sensitivity of profit to demand.

c. Use Goal Seek to determine the access price per copy that the publisher must charge to breakeven with a demand of 3,500 copies.

 d. Consider the following scenarios:

	Scenario 1	Scenario 2	Scenario 3	Scenario 4	Scenario 5
Variable Cost/Book	£6	£8	£12	£10	£11
Access Price	£46	£50	£40	£50	£60
Demand	2,500	1,000	6,000	5,000	2,000

For each of these scenarios, the fixed cost remains $160,000. Use Scenario Manager to generate a summary report that gives the profit for each of these scenarios. Which scenario yields the highest profit? Which scenario yields the lowest profit?

Case Study 3: Regression

A small business firm examined buyer characteristics to forecast the amount charged by credit card clients. The firm collected yearly income, household size, yearly credit card charges for a sample of 50 customers. The data collected are presented in the table below.

Income (£1000s)	Household Size	Amount Charged (£)
104	4	4516
80	4	3659
82	4	5600
100	6	5242
81	4	2364
105	2	4570
87	2	3231
90	4	3848
116	4	5264
101	4	4610
75	5	4708
98	4	4719
77	2	2977
83	4	3014
115	3	4714
113	5	5465
92	8	4912
71	2	2948
94	2	3495
87	7	4671
112	6	6178
71	4	4123
105	9	5801
92	2	3520
91	8	5328
104	8	6073
80	1	3083
98	3	4366
84	7	4086
117	4	5537
100	3	4105
117	7	5845
105	6	5870
102	3	4390
112	5	5205
114	2	4657
72	4	4079
79	6	4390
89	2	3472
85	2	3621
89	6	4683
104	3	4230
73	7	4627
77	4	3421
76	7	5103
111	3	4773
80	4	3567
72	4	3574
96	6	5320
116	6	5649

Your Task:

1. Develop two estimated regression equations:
   1. First using annual income as the independent variable
   1. Second using household size as the independent variable.
   1. Which variable is the better predictor of annual credit card charges? Discuss your findings.
2. Develop an estimated regression equation with annual income and household size as the independent variables. Discuss your findings.
3. What is the predicted annual credit card charge for a five-person household with an annual income of $70,000?
4. Discuss the need for other independent variables that could be added to the model. What additional variables might be helpful?

Case Study 4: Risks, Governance, Ethics, and Sustainability

Smith is a hard-working individual who works in a medium size financial company in UK. His organisation is mainly specialised in providing support facilities such as soft loans to people encountering financial problems. One of the activities of their business is to maintain a large database of information on the customers using their services. Besides financial information, Smith company’s data files contain personal details such as names, phone numbers and current addresses of the customers. Smith is currently working on a project and has been assigned the task of analysing data and writing a report that contains number of customers who have completed payment for their loans, customers who are behind on their payments, customers who have defaulted but are yet to be issued a formal notification, the default rate, and the probability of default for customers whose circumstances have changed. The report must be ready in time for a meeting with senior administrators be held in 4 days’ time. However, Smith is under a lot of pressure from his other commitments and could not find time in his already busy schedule during office hours. Consequently, Smith decided to copy the data required to prepare a report, from the office data centre into his large portable hard-drive and work on the report over the weekend at home to finish on time. However, his home computer malfunctioned on Saturday morning. In response, to avoid failing to meet the deadline, Smith, decided to use a computer at a local library not far from his residence. He was able to complete the project on time for the meeting.

Your Tasks:

Discuss the following questions in relation to project risks, governance, ethics, and sustainability.

1. What are the ethical issues raised by Smith’s conduct?
2. What are the risks associated with Smith’s behaviour?
3. What should be done to prevent Smith’s conduct from happening again in the future? You might wish to develop a risk register containing at least 5 risks.
4. Reflect and document your opinion about sustainability in the financial sector