Excel problem

CMPU Hospitals, Inc. is increasingly worried about ransomware attacks. Malware infects machines through emails or portable devices, and once installed in a computer, the malware encrypts the data and makes it inaccessible to the user. Then the attackers demand payment to decrypt the data and avoid the files to be destroyed or permanently locked. It has been reported that those attacks target organizations of several sizes, with ransom demands varying from a few thousand to several million dollars per attack, depending on the size of the organization and the number of computers infected.

The response to ransomware attacks vary. Some companies have no choice but pay the ransom, while others with good business continuity plans isolate the infected machines and retrieve the lost data from backups or redundant systems. However, there are always costs related to the unavailability of information and technology, and costs to recover from an attack.

CMPU is asking you to estimate those costs as a function of the time elapsed from attack to recovery. Build an Excel model with references and formulas such that CMPU can change cost and period assumptions to see their effect on the estimates.

The cost of unavailability is estimated as $5,000 per hour during the first four hours following an attack but then increase to $20,000 for each of the next eight hours, and to $50 thousand for each hour afterward.

The cost to recover from an attack was estimated from statistical methods and is believed to follow the formula:

Cost to recover from attack = EXP( 14 0.25 * (time from attack to recovery in hours) )

a) Make a list with time from attack to recovery varying from 1 to 24 hours, in steps of 1 hour. Calculate the total cost of unavailability for each value of time from attack to recovery.

b) Calculate the cost to recover from an attack for each value of time from attack to recovery.

c) Use the results a) and b) to plot a Disaster Recovery Cost Curve Chart. This Chart is explained in section 5.1

d) What is the optimal cost and time of a disaster recovery plan? (An approximate answer is fine, which you can obtain looking at the graph. You dont need to find the exact cost and time.)

e) The optimal cost found in d) represents the amount of money that CMPU should invest to recover from ransomware attacks. CMPU should have that amount immediately available because we never know when an attack will occur. However, CMPU doesnt have the money to spare. It will obtain a short-term loan from a bank. The bank offers an 8% APR when the loan is paid in 12 monthly payments. What is the value of each monthly payment?

Excel functions you may want to use: IF(); EXP() function to calculate the power of an expression; one of Excels financial functions PMT(), PV(), RATE(), NPER().

ABC company is asking you to estimate those costs as a function of the time elapsed from attack to recovery. Build an Excel model with references and formulas such that CMPU can change cost and period assumptions to see their effect on the estimates.

The cost of unavailability is estimated as $5,000 per hour during the first four hours following an attack but then increase to $20,000 for each of the next eight hours, and to $50 thousand for each hour afterward.

The cost to recover from an attack was estimated from statistical methods and is believed to follow the formula:

Cost to recover from attack = EXP( 14 0.25 * (time from attack to recovery in hours) )

a) Make a list with time from attack to recovery varying from 1 to 24 hours, in steps of 1 hour. Calculate the total cost of unavailability for each value of time from attack to recovery.

b) Calculate the cost to recover from an attack for each value of time from attack to recovery.

c) Use the results a) and b) to plot a Disaster Recovery Cost Curve chart

d) What is the optimal cost and time of a disaster recovery plan? (An approximate answer is fine, which you can obtain looking at the graph. You dont need to find the exact cost and time.)

e) The optimal cost found in d) represents the amount of money that CMPU should invest to recover from ransomware attacks. CMPU should have that amount immediately available because we never know when an attack will occur. However, CMPU doesnt have the money to spare. It will obtain a short-term loan from a bank. The bank offers an 8% APR when the loan is paid in 12 monthly payments. What is the value of each monthly payment?

Excel functions you may want to use: IF(); EXP() function to calculate the power of an expression; one of Excels financial functions PMT(), PV(), RATE(), NPER().