PART B

Refer to the “CapBudget” and “Gamma” worksheets when you are attempting this question. Clearly label your answers.

A delivery company is looking to invest in 5 new trucks that costs $100 000 each to replace older models of their current truck fleet. The finance team has received data from the logistics team regarding the potential cost savings regarding this purchase. Based on their analysis, they believe the new trucks will save on running costs (wages, driver meals, supplies, tolls, etc.), fuel costs for each trip as well as parts and labour in maintenance and repairs. As a senior risk analyst, you have been given the task of estimating the parameters and distributions for the input variables as well as generating the results in the model that measures how much this decision will save the company on costs on an annual basis.

You will be developing a model and using a Monte Carlo simulation approach to provide a range of outcomes to allow the finance team to be well-informed regarding whether to proceed with this decision.

The variables and the parameters of the model are as follows:

a.) Enter the correct parameters for your model in the CapBudget and Gamma worksheets.

For b.) and c.), refer to the “Gamma” worksheet. This worksheet provides you with the density function of the gamma distribution that you will use to model the number of trips made by each truck on a monthly basis.

b.) Generate the descriptive statistics and note the shape of this gamma distribution.

c.) By letting the alpha and beta parameters vary (±5), note how the density function change. (Hint: You may also want to see how the descriptive statistics change. Providing the figures of the density function may help)

d.) Using appropriate references to the random variables and the parameters, fill out the simulations table to generate results for the model.

e.) Fill out the frequency histogram table. You will need to make an adjustment for the number of observations where the savings exceed $2 000 000.

f.) Fill out the simulation results table.

g.) How many simulations are enough such that the margin of error will be less than 1%? Round it to the nearest 10.

h.) Confirm that your answer in

g.) indeed results in the margin of error being less than 1%. If not, increase it until it does.

Reflection

This model evaluates an investment project based on one year of results and determines whether it should proceed or not based on this. How likely will projects breakeven within a year and is this a realistic criterion in the case of long-term investments? What are the assumptions relating to the input variables? To what extent does it reflect reality in context of this project? Finally, how large should the savings be and the likelihood of success be to deem this investment as being worth pursuing?

Question 2

Refer to the “ExotOp”, “ExotOpSims” and “Options” worksheets when you are attempting this question.

Note that when you are generating the option results, you will be unable to work on MS Excel for a few minutes.

For safeguard to ensure you do not simulate too many results and thus potentially prevent you from returning to work on your spreadsheet, start off with generating 5 trials of 10 000 simulations each. After you have confirmed that your model is showing the correct output, then you can run more trials and simulations.

As a consultant working in a leading advisory firm, you have recently been engaged by the Board of Directors of a mid-tier resources company to estimate the value of an options grant to be issued to the incoming CEO as part of the salary package. However, since you have been too busy with other tasks at hand, you have handed this project to a trusted analyst who has prepared the valuation model for you. Your task is to review the model and its assumptions and then to prepare a recommendation to your client regarding how many options should be granted.

Your analyst has done a very good job with the model design, as you will find in the spreadsheet. Use this model to firstly get a clearer understanding of the instruments being granted.

a.) Fill out the table labelled “Option Conditions”.

You will now be describing briefly the way the valuation model works for the purposes of estimating a fair value of the instrument. Answer all these in the table labelled “Model Specifications”.

b.) What are the index and stock measures being modelled for the purpose of estimating the value of the option?

c.) What model is used for the index and stock measures?

d.) What are the key output variables in this model?

e.) What modelling approach is used to estimate the value of the option?

f.) Explain how the correlation between the stock and index is modelled.

You are now going to enter model parameters depending on the following conditions:

g.) Amend the model for these parameters.

h.) Generate 5 trials of 10 000 simulations to estimate the option price.

i.) Fill out the table to compute the statistics for the trials.

Generating the results may take as much as 10 minutes. During this time, you will not be able to do anything on MS Excel so you can work on the following questions:

In this section, you will develop your recommendations in light of the results.

j.) What is the 95% confidence interval for the estimated option price given by your 5 trials? Give your answer correct to the nearest cent.

k.) Calculate the margin of error for the estimated option price from 10 000 simulations, giving your answer as a % correct to 2 decimal places.

l.) Calculate how many simulations and trials of 10 000 simulations would be required to get the estimated option price to have a margin of error of less than 1%. Round your answer up to the nearest thousand simulations and the nearest number of trials.

m.) Now, using your answer in l.), generate more trials and see whether your margin of error is reduced. Do so until you have a margin of error of less than 1 %.

n.) Estimate how many options should be granted to the executive, based on the results you have provided. Give your answer to the nearest cent.

Reflection

Executives are meant to serve the interest of shareholders. To what extent does this option serve this purpose? How time-efficient is the way the prices and exercise conditions structured to perform the task? In setting model assumptions, how much do your assumptions agree with trends in the market conditions? This project requires you to act as an independent advisor to a client, though the client has multiple stakeholders. How do you act in a professional manner?