Transcript Verifiablity lecture slides

Verifiability
ACIS 1504 - Introduction to Data Analytics & Business Intelligence
Business
Intelligence
Quality
Information
Functions
5-Component
SDLC
Info Literacy
Ethics
Design
• Spreadsheet*
• Database
Data
Analytics
Big Data*
Methods
•
•
•
•
•
Data Mining*
Text Mining*
OLAP*
Visualization*
Query
Database
Benefits 
Components 
Implement with:
* Excel
 Access
Concept Map
Data
Analytics
Design
Implementation
Documentation
Verifiability
Protection
Data
Validation
Objectives
• Explain the Verifiability Design Goal.
• Demonstrate Excel features often used to
support verifiability.
Segment A:
Verifiability
Answers From Big Data
• Data Agents
• Unstructured
• New Talent
• New Way of
Thinking
• Opportunity
https://www.youtube.com/watch?v=LrNlZ7-SMPk
Spreadsheet Design Goals
1. Simplicity
2. Clarity
3. Verifiability
4. Accuracy
5. Efficiency
Verifiability
• Easy to understand
• Minimize erroneous data entry
Segment B:
Understandability
Titles
Rename Worksheet
Double-click sheet
tab to change text
Headers & Footers
Comments
Range Names
Documentation Worksheet
Document Nashville Hotels Example
Open the Nashville Hotels spreadsheet we
worked with last class.
Add appropriate documentation including:
• Sheet Name
• Headers and Footers
• Comments
Segment C:
Data Validation
Data Validation
• Define rules to limit data entry to reasonable
values.
Data Validation Decisions
1. Which data cells may require data
validation?
2. What rules can I establish to identify
incorrect data?
3. Is this rule accurate 100% of the time?
4. What should my error message be?
Data Validation Error Message
Data Validation
First, select data range.
Enrollment Data Validation Example
• ACIS 1504 is usually capped at 2 sections of 500 per
semester. Sometimes a few extra students are
added to a section but that is rare. Apply data
validation to the Enrollment worksheet to limit the
entry a erroneous enrollment figures.
• Apply data validation to the Grades worksheet to
ensure that each student is entered as a Freshman,
Sophomore, Junior or Senior.
Hotel Data Validation Example
• Add Data Validation to the Nashville Hotels
worksheet to ensure that a reasonable value
is entered for the ROOMS field.
• Limit the PRICE field to the following entries:
Budget, Economy, Midprice, Upscale, Luxury.
Segment D:
Worksheet Protection
Worksheet Protection
Restricts entry into cells containing headings
and calculations.
Worksheet Protection Decisions
1. Which cells require protection?
2. Which cells need to be unlocked?
Worksheet Protection Error Message
Worksheet Protection (Step 1)
Worksheet Protection (Step 2)
Enrollment Protection Example
Setup the Enrollment worksheet so that
only enrollment figures can be modified.
Hotel Protection Example
Select Sheet 2 of the Nashville Hotels
workbook.
Assume that you may want to add new hotel
information in the future. You may want to
add another property to an existing hotel
entry or you may need to add a completely
new chain.
Setup worksheet protection.