ACIS 1504 - Introduction to Data Analytics & Business Intelligence

Business IntelligenceLogical Functions

Concept Map Business Intelligence

Design Implementation

IF, VLOOKUP

3-D Ref

Accuracy

Efficiency

Range Name

Group

Objectives• Define Business Intelligence.

• Define the Efficiency design goal.

• Demonstrate Excel’s logical functions that help produce business intelligence.

Segment A:Business Intelligence

Business Intelligence (BI)Set of software and methodologies that present visualizations depicting insights gathered from analytics and traditional IS for the purpose of improved decision making.

Pandora Internet Radio

Recommender Software Music Genome Project

• Arrangement• Beat• Harmony• Lyrics• Melody• Rhythm• Tempo• Voice

Content.time.com/time/video/player/0,32068,88490113001_1992632,00.html

Efficiency

All calculations, including comparisons, should be done by the spreadsheet.

Segment B:IF Functions

• IF function displays one of two possible values depending on the outcome of a logical test

• Logical Test compares two things

• If the Logical Test equates to TRUE, the cell is filled with the True Value.

• If the Logical Test equates to FALSE, the cell is filled with the False Value.

IF Function

MAC: Logical Test is broken into 3 separate boxes

Use the IF function to assign the label of easy or hard based on the hours. If a course requires more than 3 hours of study time per week, we will label it HARD. Three or less hours of study is labeled EASY.

Course Difficulty Example

•What cell is used for the logical test?•What is the logical test?•What is the true value? False value?

Course Difficulty Example

3

Bonus Points Example

1. Open One Semester.xlsx2. Extra Credit

A. Add a new column to display the extra credit points. B. Give all students in the 8:00 Section 10 points of extra

credit. Students in the 9:00 section receive zero extra credit. (Display either 0 or 10 in this new column.)

3. Curve A. Add a new column to display the curved Exam 1 grades.B. Curve Exam 1 grades by increasing all Freshmen grades

by 10% and leaving all other grades the same. Show both the original and curved exam grades in separate columns. Do not show the curve points separately.

Shipping Cost Example• Open SmartPen Sales.xlsx.

• Delete the data in the Shipping Cost column.

• Use an IF statement to display 4.95 when the shipping method is standard and 14.95 if it is not standard.

SUMIF, COUNTIF & AVERAGEIF• SUMIF function includes a value in the

calculation only when the criteria is true

=sumif(range, criteria, sum_range)

• Each cell in range is compared to criteria.

• If that comparison equates to TRUE, the corresponding cell in sum_range is included in the calculation.

8:00 Average Example

Calculate the average for Exam 1 of just those students in the 8:00 section.

SUMIFS, COUNTIFS & AVERAGEIFS• SUMIFS function includes a value in the calculation

only when two or more criteria are true

=sumif(sum_range, criteria_range1, criteria1criteria_range2, criteria2)

• Each cell in rangex is compared to criteriax.

• If all comparisons equate to TRUE for a single row, the corresponding cell in sum_range is included in the calculation.

8:00 Average Example

Calculate the average of Exam 1 for Freshmen in the 8:00 section.

Segment D:Grouping Worksheets

Grouping Worksheets• Hold SHIFT or CONTROL as you select sheet

tabs. Right-click and select Group.

• Allows you to make changes to multiple worksheets at once.

• All groups sheets must have same layout.

• Right-click, Ungroup

Income Statement ExampleOpen Income Statement.xlsx

1. Group all 12 months and apply:• Formatting for Clarity• Add Total Expenses

Calculation• Add Net Income Calculation

2. Ungroup Worksheets

Quarterly SmartPen Example

Open SmartPen Sales Qtr.xlsx.

This workbook has two more months of data added.

Quarterly SmartPen Sales Example

Add a totals row to July, August & September

Quarterly SmartPen Sales Example

1. Group July, August and September worksheets:• Insert a new row just below Row 1.• Format this row as a calculation row.• Add SUM functions to the second row for

columns E and I – L. Assume we may be adding more invoices to all three worksheets so sum the current rows plus another hundred empty rows.

2. Ungroup the worksheets.• Change the quantity of the first July invoice to 27

and the first August invoice to 38.

Segment E:3-D Cell References

3-D Cell References• Range on another sheet

Sheet1!A1:A10

• Range across multiple sheetsSheet1:Sheet3!A1

Quarterly Income Statement

Combine the Jan, Feb and Mar Income Statements into one quarterly worksheet.

Quarterly Income Statement1. Create 1st Quarter worksheet:• Copy a monthly worksheet• Rename sheet 1st Qtr• Delete amounts• Add January, February, March columns

2. Enter function to display amounts from monthly sheets • Start function with equal sign• Point to January Revenue cell• Press ENTER• Copy to expense amount cells• Repeat for each month

Annual Income StatementCreate a new worksheet that displays the total, January through December, of each figure.

Annual Income Statement1. Create Annual worksheet:• Copy a monthly worksheet• Rename sheet Annual• Delete amounts

2. Enter a function to total revenue• Start function with =SUM(• Point to January cell• Enter colon• Point to December cell• Press ENTER• Repeat for each amount

Quarterly SmartPen Sales ExampleUse a simple 3-D formula to display appropriate values in cells B2:F4 from the July, August and September worksheets.

Quarterly SmartPen Sales Example

You could use a simple SUM function to create the totals for Row 5. Instead, practice 3-D cell references by creating a 3-D SUM function in cells B5:F5 to total appropriate values from the July, August and September worksheets.

Segment F:Range Names

Range Names•More user-friendly name than column and

row reference.

•Makes calculations more understandable.

• Locate with Find, Go To

• Range Names are absolute

Income Statement

On the Annual worksheet:• Name cell B2 TotRev• Name cell B7 TotExp

Re-enter the Net Income formula to use these range names

Quarterly SmartPen Quantity Example• Assign three range names, one to each total

quantity cell on the July, August and September worksheets.

• Use these range names to calculate the quarterly total quantity on the Quarter worksheet.

Segment G:VLOOKUP Function

• LOOKUP functions display one of MANY possible values depending on the outcome of a logical test.• Often used to translate one value into

another value.

• Requires a lookup table.• Lookup Value is compared to Lookup Table• Can find Exact Match (false) or next closest

value (true)

VLOOKUP Function

1. First column must be used for logical test.

2. First column must contain a value, not a range of values.

3. First column should be in ascending order.

Lookup Table Rules

Lookup Directory Example

Find the extension for a given name in the directory.

Lookup Table for Directory Example

Lookup Directory Example Solution

1. F1 is compared to first column of lookup table.

2. Extension from Column 2 is displayed for match.

3. False means an exact match must be found.

Assign Colleges Example

In the One Semester file, assign students to colleges depending on their major.

Assign Colleges Example

Setup the list on the Colleges worksheet so that it can be used as a lookup table.

1. No blank rows in a lookup table2. Both college and major must appear on

each row of the lookup table3. The value you want to compare with the

dataset must be the first column4. The first column must be in ascending

order

Assign Colleges Lookup Table

Assign Colleges Lookup Function

Add a new column to the Grades worksheet labelled College.

Use a VLOOKUP function to assign colleges based on majors.

Assign Grades Example

Use the VLOOKUP function to assign letter grades on a standard 10-point scale based on the average of all four exams.

Assign Grades Lookup TableThe first column must:

• Contain the value for comparison

• Contain only the low end of the range

• Be in ascending order

Assign Grades VLOOKUPFunction

=VLOOKUP(K2, LetterGrade, 3, TRUE)

Quantity Discount ExampleOpen the SmartPen Sales.xlsx file.

Customers get discounts when they order larger quantities of SmartPens.

Use the VLOOKUP function to assign the appropriate unit price based on the quantity ordered.

Start by making sure the lookup table complies with the lookup table rules.