Question

please answer d and e

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

	COST OF UNAVAILABILITY(CU) AND COST TO RECOVER(CR)
	EXP(14-0.25*H)
	H=time from attack to recovery in hours		CU	CR	TC=CU+CR
		Hour(H)	Cost of Unavailability	Cost of Recovery	Total Cost
		1	$5,000	$936,589	$941,589
		2	$10,000	$729,416	$739,416
		3	$15,000	$568,070	$583,070
		4	$20,000	$442,413	$462,413
		5	$40,000	$344,552	$384,552
		6	$60,000	$268,337	$328,337
		7	$80,000	$208,981	$288,981
		8	$100,000	$162,755	$262,755
		9	$120,000	$126,754	$246,754
		10	$140,000	$98,716	$238,716
		11	$160,000	$76,880	$236,880
		12	$180,000	$59,874	$239,874
		13	$230,000	$46,630	$276,630
		14	$280,000	$36,316	$316,316
		15	$330,000	$28,283	$358,283
		16	$380,000	$22,026	$402,026
		17	$430,000	$17,154	$447,154
		18	$480,000	$13,360	$493,360
		19	$530,000	$10,405	$540,405
		20	$580,000	$8,103	$588,103
		21	$630,000	$6,311	$636,311
		22	$680,000	$4,915	$684,915
		23	$730,000	$3,828	$733,828
		24	$780,000	$2,981	$782,981
d	Optimal time =11 Hours
	Optimal Cost=$236,880
e
Pv	Amount of loan required	$236,880
Rate	Monthly interest=(8/12)%	0.6667%
Nper	Number of months of repayment	12
PMT	Value of each monthly payment	$20,605.81	(Using PMT function of excel with Rate=0.6667%,Nper=12,Pv=-236880)