Need help Completing the spreadsheet to determine the economically optimal investment in additional security.

Deliverable:

Spreadsheet that uses formulas to answer the assignment question:

1. Populate the cells in columns C-F using formulas

2. Write a formula in cell B23 that returns the amount of the optimal additional investment in security.

HINT: One way to do this is to use a VLOOKUP function in cell B23.

Homework 4 Example Calculations

For Homework 4, several calculations are required to find the optimal investment. For our purposes,

we will be using the investment amount of $250,000 for all our example calculations.

1. Expected Loss

The formula for Expected Loss consists of the following:

The probability a breach will occur x The estimated loss associated with a breach.

Using the homework example, if our company invests an amount of $250,000 into our

securities, the probability of breach decreases from a 40% chance to a 30% chance.

However, the total amount of money at risk stays constant at $10,000,000. Therefore, if we

multiply our new probability of breach, .3, by our total potential loss, $10,000,000, we get

an expected loss of $3,000,000.

2. Marginal Investment

The formula for Marginal Investment consists of the following:

Current Investment Amount Previous Investment Amount

Marginal Investment is the extra amount of money our company is investing into a business

area (in this case securities). For our example, if we are considering investing an amount of

$250,000, our marginal investment would be our current investment amount, $250,000,

minus our previous investment amount, $0. This would give us a marginal investment equal

to $250,000. In addition, if you instead considered investing $500,000, the marginal

investment would be our current investment, $500,000, minus our previous investment,

$250,000, giving us a marginal investment once again equal to $250,000.

3. Marginal Benefit

The formula for Marginal Benefit consists of the following:

The Previous Expected Loss The Current Expected Loss

The Marginal Benefit refers to the total amount an additional investment reduces the

Expected Loss. In simpler terms, if our company invests more money, we expect that the

expected loss will decrease. For example, if we are investing $250,000, our current expected

loss is $3,000,000. Meanwhile, the previous expected loss is $4,000,000 for investing $0. If

we take $4,000,000 and subtract $3,000,000, we get a marginal benefit of $1,000,000. This

means that by investing an additional $250,000 into securities, we were able to reduce our

expected loss by $1,000,000.

4. Net Benefit

The formula for Net Benefit consists of the following:

Marginal Benefit Marginal Investment

When our company invests more and more into securities, we want to make sure that we are

getting a positive return on our investment. To see if our investments are giving us positive

results, we calculate the net benefit on each investment. Net Benefit will compare the

additional amount of money we have invested into securities to the amount that our expected

loss decreased. The goal should be that the expected loss decreases by more than the amount

of money we invest. For example, if our investment of $250,000, we have a marginal

investment of $250,000 and a marginal Benefit of $1,000,000. This would mean that our

total net benefit will equal $1,000,000 - $250,000, or $750,000. (*Note: Net Benefits can be

negative if the expected loss decrease is lower than the additional amount invested).

5. Cumulative Net Benefit

The formula for Cumulative Net Benefit consists of the following:

Current Net Benefit + All Previous Net Benefits

To find the optimal investment, we need to find the point at which investing more money

will not produce positive results. To find this point, we calculate the cumulative Net Benefit.

For our investment of $250,000, this would equal the current net benefit of $750,000 plus all

other previous net benefits (since we only have one prior investment, we would use that net

benefit of $0.) This would equal $750,000 + $0 or $750,000. For a further demonstration,

lets say our investment of $500,000 gives us a net benefit of $250,000. If we were to

calculate its cumulative net benefit, it would be $250,000 + ($750,000 + $0) = $1,000,000.

6. VLOOKUP

The best way to find the optimal investment through a formula is by using a VLOOKUP.

For this part of the assignment, students are expected to google how to use this function in

Excel. However, there are two important points students should consider:

1. The column Additional Investment in Securities appears twice, both at the start of the

excel document and at the end. The reason this column is repeated at the end of the

document is since the VLOOKUP function requires that the column being searched

through appear at the end. Therefore, when using the VLOOKUP function, use the

Additional Investment in Securities column at the end of the excel instead of at the

beginning.

2. The VLOOKUP function requires a value to be searched. What we want to find is the

investment amount that has the highest cumulative net benefit. Therefore, use cell G23

for this value, as it holds a formula to calculate the max value of the cumulative net

benefit column.

Estimated loss associated with a breach Probability of breach given current security 10,000,000 Probability of breach Expected LOSs Marginal Investment M Marginal benefit Net Benefit 54,000,000 $3,000,000 0.004 Max Cumulative Net B Optimal investment (create formula in cell 823)