Question

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 don’t 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 doesn’t 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 Excel’s financial functions PMT(), PV(), RATE(), NPER().

Answer 1

Answers.

a) & b). The total cost of unavailability for each value of time from attack to recovery varying from 1 to 24 hours, in steps of 1 hour is listed below Assumed that time starts from 12.00.00 am

sl no	Time hours	Cost of unavailablity	Cumulative Cost.
1	12.00 am to 12.59 am	$                           5,000.00	$                  5,000.00
2	01.00 am to 01.59 am	$                           5,000.00	$                10,000.00
3	02.00 am to 02.59 am	$                           5,000.00	$                15,000.00
4	03.00 am to 03.59 am	$                           5,000.00	$                20,000.00
5	04.00 am to 04.59 am	$                         20,000.00	$                40,000.00
6	05.00 am to 05.59 am	$                         20,000.00	$                60,000.00
7	06.00 am to .06.59 am	$                         20,000.00	$                80,000.00
8	07.00 am to .07.59 am	$                         20,000.00	$            1,00,000.00
9	08.00 am to .08.59 am	$                         20,000.00	$            1,20,000.00
10	09.00 am to .09.59 am	$                         20,000.00	$            1,40,000.00
11	10.00 am to .10.59 am	$                         20,000.00	$            1,60,000.00
12	11.00 am to .11.59 am	$                         20,000.00	$            1,80,000.00
13	12.00 pm to .12.59 pm	$                         50,000.00	$            2,30,000.00
14	01.00 pm to .01.59 pm	$                         50,000.00	$            2,80,000.00
15	02.00 pm to .02.59 pm	$                         50,000.00	$            3,30,000.00
16	03.00 pm to .03.59 pm	$                         50,000.00	$            3,80,000.00
17	04.00 pm to .04.59 pm	$                         50,000.00	$            4,30,000.00
18	05.00 pm to .05.59 pm	$                         50,000.00	$            4,80,000.00
19	06.00 pm to .06.59 pm	$                         50,000.00	$            5,30,000.00
20	07.00 pm to .07.59 pm	$                         50,000.00	$            5,80,000.00
21	08.00 pm to .08.59 pm	$                         50,000.00	$            6,30,000.00
22	09.00 pm to 09.59 pm	$                         50,000.00	$            6,80,000.00
23	10.00 pm to 10.59 pm	$                         50,000.00	$            7,30,000.00
24	11.00 pm to 11.59 pm	$                         50,000.00	$            7,80,000.00

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

Hours	Cost of unavailablity	Cost to recover from attack
1	$                           5,000.00	$                                     9,36,589.16
2	$                           5,000.00	$                                     7,29,416.37
3	$                           5,000.00	$                                     5,68,070.04
4	$                           5,000.00	$                                     4,42,413.39
5	$                         20,000.00	$                                     3,44,551.90
6	$                         20,000.00	$                                     2,68,337.29
7	$                         20,000.00	$                                     2,08,981.29
8	$                         20,000.00	$                                     1,62,754.79
9	$                         20,000.00	$                                     1,26,753.56
10	$                         20,000.00	$                                         98,715.77
11	$                         20,000.00	$                                         76,879.92
12	$                         20,000.00	$                                         59,874.14
13	$                         50,000.00	$                                         46,630.03
14	$                         50,000.00	$                                         36,315.50
15	$                         50,000.00	$                                         28,282.54
16	$                         50,000.00	$                                         22,026.47
17	$                         50,000.00	$                                         17,154.23
18	$                         50,000.00	$                                         13,359.73
19	$                         50,000.00	$                                         10,404.57
20	$                         50,000.00	$                                           8,103.08
21	$                         50,000.00	$                                           6,310.69
22	$                         50,000.00	$                                           4,914.77
23	$                         50,000.00	$                                           3,827.63
24	$                         50,000.00	$                                           2,980.96

c).

Cost to recover from attack 1000000 900000 800000 700000 600000 500000 400000 300000 200000 100000 - Cost of unavailablity - Hours OTTTTTTTTTTTTTTTT 1 3 5 7 9 11 13 15 17 19 21 23