If the preceding data validation rule was applied to cell C7, the spreadsheet would look like this:
And clicking the drop-down arrow would display the following:
10. 10 For each of the following scenarios, determine whether the company’s current
backup procedures enable it to meet its recovery objectives and explain why:
a. Scenario 1:
• Recovery point objective = 24 hours
• Daily backups at 3:00 am, process takes 2 hours
• Copy of backup tapes picked up daily at 8:00 am for storage off-site
b. Scenario 2: Company makes daily incremental backups Monday-Saturday at
7:00 pm each night. Company makes full backup weekly, on Sunday at 1:00 pm.
• Recovery time objective = 2 hours
• Time to do full backup = 3 hours
• Time to restore from full backup = 1 hour
• Time to make incremental daily backup = 1 hour
• Time to restore each incremental daily backup = 30 minutes
c. Scenario 3: Company makes daily differential backups Monday-Friday at 8:00
p.m. each night. Company makes full backup weekly, on Saturdays, at 8:00 am.
• Recovery time objective = 6 hours
• Time to do full backup = 4 hours
• Time to restore from full backup = 3 hours
• Time to do differential daily backups = 1 hour on Monday, increasing by 30
minutes each successive day
• Time to restore differential daily backup = 30 minutes for Monday,
increasing by 15 minutes each successive day
SUGGESTED ANSWERS TO THE CASES
Case 10-1 Ensuring Systems Availability
The Journal of Accountancy (available at www.aicpa.org) has published a series of articles
that address different aspects of disaster recovery and business continuity planning:
1. Gerber, J. A., and Feldman, E. R. 2002. “Is Your Business Prepared for the Worst?”
Journal of Accountancy (April): 61-64.
2. McCarthy, E. 2004. “The Best–Laid Plans,” Journal of Accountancy (May): 46-54.
3. Myers, R. 2006. “Katrina’s Harsh Lessons,” Journal of Accountancy (June): 54-63.
4. Phelan, S., and Hayes, M. 2003. “Before the Deluge – and After,” Journal of
Accountancy (April): 57-66.
5. Drew, J., and Tysiac, K. 2013. “Preparing for Disaster,” Journal of Accountancy (May):
26-31.
Required:
a. Read one or more of these articles that your professor assigns. For each article
assigned by your professor, complete the following table, summarizing what each
article said about a specific COBIT 5 management practice (a particular article may
not address all the listed management practices): b. What point(s) did the article(s)
raise that were surprising to you? Why?
COBIT 5 CONTROL OBJECTIVE
POINTS DISCUSSED IN ARTICLE
1. Define the business continuity policy,
objectives, and scope.
2. Choose a cost-effective continuity strategy
that will ensure timely and effective recovery
from a disaster.
3. Document the procedures for disaster
recovery and resumption of business
operations.
4. Test the DRP and BCP.
5. Periodically review the DRP and BCP.
Update as required.
6. Train employees on DRP and BCP
procedures.
7. Establish and document backup procedures.
8. Conduct a post resumption review and assess
the adequacy of the DRP and BCP.
Solution: Answers will vary, but discussions of part a should include at least the following points
from each article (note that some items in an article may address more than one category and
some categories may not be addressed in an article):
Gerber, J. A., and Feldman, E. R. 2002. “Is Your Business Prepared for the Worst?”
COBIT 5
CONTROL
OBJECTIVE
1. Define
business
continuity
policy,
objectives,
and scope.
2. Choose a
cost effective
strategy.
3. Document
the DRP and
BCP.
4. Test the
DRP and
BCP.
5. Periodic
review and
update of
plans.
6. Training
7. Document
backup
procedures.
8. Conduct
post-
resumption
review.
McCarthy, E. 2004. “The Best–Laid Plans,” Journal of Accountancy (May):
COBIT 5
CONTROL
OBJECTIVE
1. Define
business
continuity
policy,
objectives,
and scope.
2. Choose a
cost effective
strategy.
3. Document
the DRP and
BCP.
4. Test the
DRP and
BCP.
5. Periodic
review and
update of
plans.
6. Training
7. Document
backup
procedures.
8. Conduct
post-
resumption
review.
Myers, R. 2006. “Katrina’s Harsh Lessons,” Journal of Accountancy (June):
COBIT 5
Control
Objective
Points discussed in article
1. Define
business
continuity
policy,
objectives,
and scope.
• Reviews different types of plans and what each contains
• Importance of communications procedures – and specific
recommendations of how to ensure you can do this
2. Choose a
cost effective
strategy.
3. Document
the DRP and
BCP.
• Specific steps for how to recover data after floods, fires, etc.
4. Test the
DRP and
BCP.
• Need to test the plan at least annually
5. Periodic
review and
update of
plans.
• Need to test the plan at least annually
6. Training
• Divide responsibilities across employees and practice
7. Document
backup
procedures.
8. Conduct
post-
resumption
review.
• Lessons learned – problems with only one backup communication
plan (e.g., cell towers going down)
Phelan, S., and Hayes, M. 2003. “Before the Deluge – and After,”
COBIT 5
CONTROL
OBJECTIVE
Points discussed in article
1. Define
business
continuity
policy,
objectives,
and scope.
• Involve senior management in developing the plans
• 40% of firms without a plan go out of business
• sidebar about medical needs
2. Choose a
cost effective
strategy.
• Discusses hot sites and other issues about planning to replace the
infrastructure
• Examples of the benefits of having a plan so can be prepared
3. Document
the DRP and
BCP.
• Communication methods discussed
• Sidebar explains how to recover various information assets that were
damaged/lost
4. Test the
DRP and
BCP.
5. Periodic
review and
update of
plans.
• Communication methods discussed
6. Training
7. Document
backup
procedures.
• Specific examples of the kinds of information assets that need to
backup
• Detailed side-bar on how to actually recover data/information in
various situations
8. Conduct
post-
resumption
review.
• Lessons learned (need for multiple communications channels)
Drew, J. and Tysica, K. 2013. “Preparing for Disaster,”
COBIT 5
Control
Objective
Points discussed in article
1. Define
business
continuity
policy,
objectives,
and scope.
• Stressed importance of communications strategies
• Sidebar about need to include insurance coverage
• Stresses importance of considering human needs of employees
(gives examples of how some CPA firms did this)
• Sidebar on topics to include in DRP
2. Choose a
cost effective
strategy.
• Discussed the role of the cloud
3. Document
the DRP and
BCP.
• Provided a detailed example of a DRP
4. Test the
DRP and
BCP.
• Need to ensure your employees know what to do – conduct drills to
practice.
5. Periodic
review and
update of
plans.
6. Training
7. Document
backup
procedures.
8. Conduct
post-
resumption
review.
Solution to part b: many actual responses are possible. Two of the most likely
Case 10-2 Ensuring Process Integrity in Spreadsheets
Download the payroll spreadsheet from the course website. The spreadsheet contains a
number of errors. Write a report that identifies the following types of problems:
• Error in a formula
• Hardwiring
Use the following format for your report:
Problem
Cell(s) where problem
located
Explanation: why this
is a problem
Solution
Example: error in a
formula
E19
Overtime pay is
miscalculated as 1.5 x
total hours worked,
rather than just
overtime hours.
Correct the formula so
that it only applies
overtime rate to hours
worked in excess of 40.
Optional: If required by your professor, fix all the errors you identified and submit a
corrected copy of the spreadsheet.
Solution to basic problem:
Problem
Cell(s) where
problem located
Explanation: why
this is a problem
Solution
Example: error in a formula
E19
Overtime pay is
Correct the
worked, rather
than just overtime
hours.
overtime rate to
hours worked in
excess of 40.
Formula to calculate column totals in row 23 is wrong, it
only sums rows 13-21.
Cells B23:K23
Omits row 22
(possible that row
was inserted after
the row with the
column total
formulas was
created).
Correct the
formula to sum
from rows 13-22
Overtime formula uses hard-wiring:
=IF(B13>40,ROUND((B13–40)*C13*1.5,2),0)
Cells E13:E22,
except for cell
E19
If overtime pay
ever changes, need
to manually correct
every cell with this
formula
Store the
overtime rate in a
cell (e.g., C2) and
reference that cell
in all formulas
Taxable income formula uses hard-wiring:
=ROUND(F13–(G13*50),2)
Cells H13:H22
If federal deduction
per allowance ever
changes, need to
manually correct
every cell with this
formula
Store the
allowance
deduction in a
cell (e.g., E4) and
reference that cell
in all formulas
Federal tax formula uses hard-wiring:
=IF(H13<200,0.15*H13,IF(H13<400,0.2*H13,0.3*H13))
Cells I13:I22
If withholding rates
ever change, need
to manually correct
every cell with this
formula
Store the
withholding rates
in specific cells
(e.g., C6:C8) and
reference those
cells via a
Lookup formula
State tax formula uses hard-wiring:
to manually correct
in specific cells
Cells J13:J22
If withholding rate
Store the state
every cell with this
formula
(e.g., D9) and
reference that cell
in the formulas
Important cells with data that should be permanent are not
protected
Employee number
and Payrate
through net pay
columns; also all
the reference
section (key
assumptions and
rules)
Cells can be
accidentally
overwritten
Protect the cells;
only the hours
worked field
should be editable
Solution to optional assignment is available as teaching resource.