CMPU Hospitals, Inc. is increasingly worried about ransomware attacks.

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.

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

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().