Your assignment: Complete the spreadsheet posted on Canvas to determine the economically optimal investment in additional security in an organization. Deliverable: Spreadsheet that uses formulas to answer the assignment question: Read the supporting \"Example Calculations\" commentary at the end of this document that provides additional insight into the contents of the spreadsheet & its intended use PRIOR to attempting to complete this assignment. Populate the cells in columns C-F using formulas. Write a formula using the XLOOKUP function in cell B27 that returns the amount of the optimal additional investment in security. Your submission should: 1. Use formulas rather hard coding answers. 2. Have a correct & functioning XLOOKUP. 3. Be arithmetically correct based on the \"Example Calculations\" provided. Assignment Submission REMINDER: Use of Artificial Intelligence is PROHIBITED in this assignment. 1. Provide your Name, ASURITE #, Course Number & Title, & Class Section # at the top of page, left justified as noted in the spreadsheet. 2. Complete the assignment as outlined above. 3. Save the spreadsheet using the file name format: CIS401_A01_yourlastname_firstinitial.xlsx. 4. Submit the spreadsheet on Canvas. Example Calculations For this assignment, 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. Annualized Loss Expectancy (ALE) The formula for ALE consists of the following: The probability a breach will occur * The estimated loss associated with a breach Using the example, if our company invests an amount of $250,000 in Cybersecurity, 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 ALE of $3,000,000. 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 in a business area (in this case Cybersecurity). For 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. Marginal Benefit The formula for Marginal Benefit consists of the following: The Previous ALE The Current ALE The Marginal Benefit refers to the total amount an additional investment reduces the ALE. In simpler terms, if our company invests more money, we expect that the ALE will decrease. For example, if we are investing $250,000, our current ALE is $3,000,000. Meanwhile, the previous ALE 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 in Cybersecurity, we were able to reduce our ALE by $1,000,000. The formula for Net Benefit consists of the following: Marginal Benefit Marginal Investment When our company invests more and more in Cybersecurity, 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 of each investment. Net Benefit will compare the additional amount of money we have invested in Cybersecurity to the amount that our ALE decreased. The goal should be that the ALE 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 ALE decrease is lower than the additional amount invested). 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, let's 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. XLOOKUP The best way to find the optimal investment through a formula is by using XLOOKUP. For this part of the assignment, perform a web search on how to use this function in Excel. However, there are two important points to consider: 1. The column Additional Investment in Cybersecurity 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 XLOOKUP function requires that the column being searched through appears at the end. Therefore, when using the XLOOKUP function, use the Additional Investment in Cybersecurity column at the end of the spreadsheet instead of at the beginning. 2. The XLOOKUP function requires a value to be searched with the objective being to find the investment amount that has the highest cumulative net benefit. Therefore, use cell G26 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 0.4 Annualized Loss Additional Investment in Security Probability of Breach Expectancy Marginal Investment Marginal Benefit Net Benefit Cumulative Net Benefit _Additional Investment in Cybersecurity $0 0.4 $4,000,000 $0 $0 $0 $0 S0 $250,000 0.3 $3,000,000 $250,000 $500,000 0.25 $500,000 $750,000 0.2 $750,000 $1,000,000 0.12 $1,000,000 $1,250,000 0.05 $1,250,000 $1,500,000 0.02 $1,500,000 $1,750,000 0.01 $1,750,000 $2,000,000 0.007 $2,000,000 $2,250,000 0.005 $2,250,000 $2,500,000 0.004 $2,500,000 $2,750,000 0.003 $2,750,000 $3,000,000 0.003 $3,000,000 $3,250,000 0.003 $3,250,000 $3,500,000 0.003 $3,500,000 $3,750,000 0.003 $3,750,000 $4,000,000 0.003 $4,000,000 Max Cumulative Net Benefit: 30 Optimal investment (create formula in cell B27) =