Issuu

Business Analytics, 3e (Evans) Chapter 1: Introduction to Business Analytics 1) Descriptive analytics: A) can predict risk and find relationships in data not readily apparent with traditional analyses. B) helps companies classify their customers into segments to develop specific marketing campaigns. C) helps detect hidden patterns in large quantities of data to group data into sets to predict behavior. D) can use mathematical techniques with optimization to make decisions that take into account the uncertainty in the data. Answer: B Diff: 1 Blooms: Remember Topic: Descriptive, Predictive, and Prescriptive Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive analytics. 2) A manager at Gampco Inc. wishes to know the company's revenue and profit in its previous quarter. Which of the following business analytics will help the manager? A) prescriptive analytics B) normative analytics C) descriptive analytics D) predictive analytics Answer: C Diff: 1 Blooms: Apply AACSB: Analytic Skills Topic: Descriptive, Predictive, and Prescriptive Analytics LO1: Explain the difference between descriptive, predictive, and prescriptive analytics. 3) Predictive analytics: A) summarizes data into meaningful charts and reports that can be standardized or customized. B) identifies the best alternatives to minimize or maximize an objective. C) uses data to determine a course of action to be executed in a given situation. D) detects patterns in historical data and extrapolates them forward in time. Answer: D Diff: 2 Blooms: Remember Topic: Descriptive, Predictive, and Prescriptive Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive analytics.

2 Chapter 1 Introduction to Business Analytics

Business Analytics, 3e

4) A trader who wants to predict short-term movements in stock prices is likely to use ________ analytics. A) predictive B) descriptive C) normative D) prescriptive Answer: A Diff: 1 Blooms: Apply AACSB: Analytic Skills Topic: Descriptive, Predictive, and Prescriptive Analytics LO1: Explain the difference between descriptive, predictive, and prescriptive analytics. 5) Which of the following questions will prescriptive analytics help a company address? A) How many and what types of complaints did they resolve? B) What is the best way of shipping goods from their factories to minimize costs? C) What do they expect to pay for fuel over the next several months? D) What will happen if demand falls by 10% or if supplier prices go up 5%? Answer: B Diff: 2 Blooms: Understand AACSB: Analytic Skills Topic: Descriptive, Predictive, and Prescriptive Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive analytics. 6) The demand for coffee beans over a period of three months has been represented in the form of an L-shaped curve. Which form of model was used here? A) mathematical model B) visual model C) kinesthetic (tactile) model D) verbal model Answer: B Diff: 1 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Explain the concept of a model and various ways a model can be characterized. 7) Decision variables: A) cannot be directly controlled by the decision maker. B) are assumed to be constant. C) are always uncertain. D) can be selected at the discretion of the decision maker.

Chapter 1: Introduction to Business Analytics

Business Analytics, 3e 3

Answer: D Diff: 2 Blooms: Understand Topic: Models in Business Analytics LO1: Define and list the elements of a decision model. 8) Identify the uncontrollable variable from the following inputs of a decision model. A) investment returns B) machine capacities C) staffing levels D) intercity distances Answer: A Diff: 1 Blooms: Apply Topic: Models in Business Analytics LO1: Define and list the elements of a decision model. 9) Which of the following inputs of a decision model is an example of data? A) estimated consumer demand B) inflation rates C) costs D) investment allocations Answer: C Diff: 1 Blooms: Remember Topic: Data for Business Analytics LO1: Define and list the elements of a decision model. 10) Descriptive decision models: A) aim to predict what will happen in the future. B) describe relationships but do not tell a manager what to do. C) help analyze the risks associated with various decisions. D) do not facilitate evaluation of different decisions. Answer: B Diff: 2 Blooms: Understand Topic: Models in Business Analytics LO1: Explain the concept of a model and various ways a model can be characterized. 11) Prescriptive decision models help: A) make predictions of how demand is influenced by price. B) make trade-offs between greater rewards and risks of potential losses. C) decision makers identify the best solution to decision problems.

4 Chapter 1 Introduction to Business Analytics

Business Analytics, 3e

D) describe relationships and influence of various elements in the model. Answer: C Diff: 1 Blooms: Remember Topic: Models in Business Analytics LO1: Define the terms optimization, objective function, and optimal solution. 12) The manager at Soul Walk Inc., a shoe manufacturing company, wants to set a new price (P) for a shoe model to maximize total profit. The demand (D) as a function of price is represented as: D = 1,500 - 2.5P The total cost (C) as a function of demand is represented as: C = 3,200 + 3.5D Which of the following is a model for total profit as a function of price? A) (1,508.75× price) - (2.5 × price2) - 8,450 B) (3.5 × price2) + 3,200 - (1925.50 × price) C) (1,250 × price) + (5 × price2) - 8,320 D) [4521 + (4.5 × price)] × price - 9684.25 Answer: A Diff: 3 Blooms: Apply Topic: Models in Business Analytics LO1: Define the terms optimization, objective function, and optimal solution. 13) Which decision model incorporates the process of optimization? A) predictive B) prescriptive C) descriptive D) normative Answer: B Diff: 1 Blooms: Remember Topic: Models in Business Analytics LO1: Define the terms optimization, objective function, and optimal solution. 14) Which of the following is the first phase in problem solving? A) defining the problem B) analyzing the problem C) recognizing the problem D) structuring the problem Answer: C Diff: 1 Blooms: Remember

Chapter 1: Introduction to Business Analytics

Business Analytics, 3e 5

Topic: Problem Solving and Decision Making LO1: List and explain the steps in the problem-solving process. 15) Middle managers in operations: A) develop staffing plans. B) determine product mix. C) develop advertising plans. D) make pricing decisions. Answer: A Diff: 1 Blooms: Remember Topic: Problem Solving and Decision Making LO1: List and explain the steps in the problem-solving process. 16) The product mix is determined by the: A) accounting staff. B) middle managers. C) finance managers. D) top managers. Answer: D Diff: 1 Blooms: Remember Topic: Problem Solving and Decision Making LO1: List and explain the steps in the problem-solving process. 17) During which phase in problem solving is a formal model often developed? A) analyzing the problem B) structuring the problem C) defining the problem D) implementing the solution Answer: B Diff: 1 Blooms: Remember Topic: Problem Solving and Decision Making LO1: List and explain the steps in the problem-solving process. 18) Which of the following is true about problem solving? A) Recognizing problems involves stating goals and objectives. B) Analyzing the problem involves characterizing the possible decisions. C) Decision making involves translating the results of the model in the organization. D) Structuring the problem involves identifying constraints. Answer: D

6 Chapter 1 Introduction to Business Analytics

Business Analytics, 3e

Diff: 2 Blooms: Understand Topic: Problem Solving and Decision Making LO1: List and explain the steps in the problem-solving process.

The manager at Goody Woods Inc., a manufacturer of wooden utensil sets, has observed that when the company sells its sets at $240, 1,540 units are sold, and when the price is raised to $320, demand falls to 1,220 units. Use this information to answer the following two questions. 19) Develop a linear model relating the demand for Goody Woods' units to the price. Answer: Economic theory tells us that demand for a product is negatively related to its price. The linear model to predict demand as a function of price is: D = a - bP where D is the quantity demanded, P is the unit price, a is a constant that estimates the demand when the price is zero, and b is the slope of the demand function. Substituting the values given in the data: 1,540 = a - (b × 240) 1,220 = a - (b × 320) By solving the two equations, values of a and b can be found. a = 2,500, which indicates the demand for wood units when price is zero, b = 4, which is the slope of the demand function. Substituting values of a and b in the linear demand prediction model: D = 2,500 - 4P Diff: 3 Blooms: Apply AACSB: Analytic Skills Topic: Predictive Spreadsheet Models. LO1: Build spreadsheet models for descriptive, predictive, and prescriptive applications. 20) Develop a prescriptive model that will help Goody Woods identify the price that maximizes the total revenue. Answer: Having found out the model for demand as a function of price, sales (S) can be expressed as: S = 2,500 - 4P Revenue (R) = Sales x Price = (2,500 - 4P) × P = 2,500P - 4P2 Thus, Goody Woods Inc. can identify the price that maximizes the total revenue using: R = 2,500P - 4P2 Diff: 3

Chapter 1: Introduction to Business Analytics

Business Analytics, 3e 7

Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Define the terms optimization, objective function, and optimal solution. 21) Decision support systems evolved from efforts to improve military operations prior to and during World War II. Answer: FALSE Diff: 1 Blooms: Remember Topic: Evolution of Business Analytics LO1: Summarize the evolution of business analytics and explain the concepts of business intelligence, operations research and management science, and decision support systems. 22) A deterministic model is one in which all model input information is either known or assumed to be known with certainty. Answer: TRUE Diff: 1 Blooms: Remember Topic: Models in Business Analytics LO1: Explain the difference between a deterministic and stochastic decision model. 23) The complexity of a problem increases when the problem belongs to an individual rather than a group. Answer: FALSE Diff: 1 Blooms: Remember Topic: Problem Solving and Decision Making LO1: List and explain the steps in the problem-solving process. 24) A goal of Korel & Marke, a dot-com company, is to gain strategic advantage over its rival firms. How can Korel & Marke use analytics and exploit social media to accomplish this goal? Answer: Analytics is helping businesses learn from social media and exploit social media data for strategic advantage. Using analytics, Korel & Marke can integrate social media data with traditional data sources such as customer surveys, focus groups, and sales data. It can also understand trends and customer perceptions of its products; and create informative reports to assist its marketing managers and product designers. Diff: 2 Blooms: Understand Topic: What is Business Analytics? LO1: State some typical examples of business applications in which analytics would be beneficial.

8 Chapter 1 Introduction to Business Analytics

Business Analytics, 3e

25) What are the three components of decision support systems (DSS)? Answer: DSSs include three components: Data management - The data management component includes databases for storing data and allows the user to input, retrieve, update, and manipulate data. Model management - The model management component consists of various statistical tools and management science models and allows the user to easily build, manipulate, analyze, and Communication system - The communication system component provides the interface necessary for the user to interact with the data and model management components. Diff: 2 Blooms: Remember Topic: Evolution of Business Analytics LO1: Summarize the evolution of business analytics and explain the concepts of business intelligence, operations research and management science, and decision support systems. 26) Explain how data are used by accountants, economists, and operations managers. Answer: Following are the ways data are used: Accountants conduct audits to determine whether figures reported on a firm's balance sheet fairly represent the actual data by examining samples (that is, subsets) of accounting data, such Economists use data to help companies understand and predict population trends, interest rates, industry performance, consumer spending, and international trade. Operations managers use data on production performance, manufacturing quality, delivery times, order accuracy, supplier performance, productivity, costs, and environmental compliance to manage their operations. Diff: 2 Blooms: Remember Topic: Data for Business Analytics LO1: State examples of how data are used in business. 27) Data used in business analytics need to be reliable and valid. Explain. Answer: Sample data do not always reflect reality. People do not always behave the same when observed, nor do they always act as they say they act. Poor data can result in poor decisions. Hence, care must be taken when working with data, and every effort should be made to ensure that data are sufficiently accurate. Diff: 2 Blooms: Remember Topic: Data for Business Analytics LO1: State examples of how data are used in business. 28) Why do predictive decision models incorporate uncertainty? Answer: The future is always uncertain. Uncertainty is imperfect knowledge of what will happen; risk is associated with the consequences of what actually happens. Even though uncertainty may exist, there may be no risk. However, risk is an outcome of uncertainty, though not always. Thus, many predictive models incorporate uncertainty and help decision makers

Chapter 1: Introduction to Business Analytics

Business Analytics, 3e 9

analyze the risks associated with their decisions. Diff: 2 Blooms: Understand Topic: Models in Business Analytics LO1: Explain the difference between uncertainty and risk. 29) Which of the following is not a challenge faced by organizations that want to develop analytics capabilities? A) a lack of understanding of how to use analytics. B) competing business priorities. C) understanding benefits versus perceived costs of analytics studies. D) difficulty in getting good data and sharing information. Answer: C Diff: 1 Blooms: Remember Topic: What is Business Analytics? LO1: Explain why analytics is important in today’s business environment. 30) Prescriptive analytics: A) summarizes data into meaningful charts and reports that can be standardized or customized. B) identifies patterns and relationships existing in large data sets. C) uses data to determine a course of action to be executed in a given situation. D) detects patterns in historical data and extrapolates them forward in time. Answer: C Diff: 2 Blooms: Remember Topic: Descriptive, Predictive, and Prescriptive Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive analytics.

10 Chapter 1 Introduction to Business Analytics

Business Analytics, 3e

Use the table below to answer the following question(s). Fiberia Accessories, a clothing retailer, is planning to introduce a new line of sweaters as part of the winter collection for $65 with an inventory of 1500. The main selling season is 60 days between November and December. The store then sells the remaining units in a clearance sale at 65 percent discount. Out of the 60 main retail days, Fiberia sells the sweaters at full retail price for only 45 days, while giving a discount of 25 percent for the remaining 15 days. The demand functions a, and b are given as 79.5 and 1.1 respectively. Marked Down Pricing Model for Fiberia Accessories's new sweater Data Retail Price Inventory Selling Season (days) Days at Full Retail Intermediate Markdown Clearance Markdown Demand Function A B

$65 1500 60 45 25 percent 65 percent 79.5 1.1

31) What is the average daily sale during the full retail sales period? A) 15 B) 33.33 C) 8 D) 24.55 Answer: C Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 32) Calculate the total number of units sold during the full retail sales period. A) 33.33 B) 520 C) 187.5 D) 360 Answer: D Diff: 2 Blooms: Apply AACSB: Analytic Skills Copyright © 2020 Pearson Education, Inc.

Chapter 1: Introduction to Business Analytics

Business Analytics, 3e 11

Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 33) Calculate the total revenue during the full retail sales period. A) $23,400 B) $16,200 C) $2,880 D) $17,550 Answer: A Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 34) Calculate the daily sales during the discount sales period. A) 39.28 B) 133.3 C) 388.13 D) 25.88 Answer: D Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 35) Calculate the total units sold during the discount sales period. A) 388.13 B) 25.88 C) 133.3 D) 39.28 Answer: A Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 36) Calculate the total revenue during the discount sales period. A) $4,478.91 B) $18,921.09

12 Chapter 1 Introduction to Business Analytics

Business Analytics, 3e

C) $10,042.73 D) $43,321.09 Answer: B Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 37) Calculate the revenue for the clearance sales period. A) $18,921.09 B) $23,400 C) $48,871.88 D) $17,105.16 Answer: D Diff: 3 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 38) Calculate the total revenue for the new line of sweaters. A) $59,426.25 B) $48,871.88 C) $23,400 D) $43,231.09 Answer: A Diff: 1 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models.

Chapter 1: Introduction to Business Analytics

Business Analytics, 3e 13

Use the table below to answer the following question(s). In the spreadsheet below, there is data on the price, cost, demand, and quantity produced for an item. There are also different "what if" values that can help a manager to calculate costs and revenue with variability in demand. 1 2 3 4 5 6 7 8 9 10

A Profit Model

Data Unit Price ($) Unit Cost ($) Fixed Cost ($) Demand Quantity Produced

50 25 550,000 60,000 55,000

C What-If Demand Values 20,000 40,000 55,000 60,000 65,000

39) Which of the following is the Excel formula to determine the number of units sold? A) =B8 B) =MIN(0,B8,B9) C) =MIN(B8,B9) D) =MAX(0,B8,B9) Answer: C Diff: 1 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 40) If D is demand, P is the unit price, and c and d are constants (where d > 0 is the price elasticity), which of the following is a nonlinear demand prediction model? A) D = d + (c × P) B) D = (d × P)c C) D = cd × P -d D) D = cP Answer: D Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models.

14 Chapter 1 Introduction to Business Analytics

Business Analytics, 3e

Use the chart below to answer the following two questions:

Gallons

Sales Function 920 900 880 860 840 820 800 780 760 $0

$5 Price

$10

41) What is the slope of the sales function D = a - bP? A) 4 B) 9 C) 12 D) 17 Answer: C Diff: 3 Blooms: Apply Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 42) How many gallons will be sold if the price is increased to $4.75? A) 833.26 B) 843 C) 798.72 D) 818 Answer: B Diff: 3 Blooms: Apply AACSB: Analytic Skills Topic: Models in Business Analytics LO1: Illustrate examples of descriptive, predictive, and prescriptive models. 43) What does business analytics use to help managers make better decisions? A) data and information technology. B) statistical analysis and quantitative methods. Copyright © 2020 Pearson Education, Inc.

Chapter 1: Introduction to Business Analytics

C) mathematical or computer-based models. D) all of the above. Answer: D Diff: 1 Blooms: Remember Topic: What is Business Analytics? LO1: Define business analytics.

Business Analytics, 3e 15

Business Analytics (Evans) Chapter 1 Appendix A1 Basic Excel Skills 1) Which of the following symbols is used to represent exponents in Excel? A) ^ B) * C) # D) ! Answer: A Diff: 1 Blooms: Remember Topic: Excel Formulas and Addressing LO1: Find buttons and menus in the Excel 2010 ribbon. 2) Which of the following ways would 102 × 53 / 100 - 73 be represented in an Excel spreadsheet? A) 10(2) * 5(3) / 100 ^ 73 B) 10(2) ^ 5(3) / 100 - 73 C) 10^2 * 5^3 / 100 - 73 D) 10*2 ^ 5*3 / 100 - 73 Answer: C Diff: 1 Blooms: Understand Topic: Excel Formulas and Addressing LO1: Write correct formulas in an Excel worksheet. 3) Which of the following is a difference between relative addressing and absolute addressing when using cell formulas in Excel? A) A relative address uses a dollar sign before either the row or column label; an absolute address uses the ampersand symbol before either the row or column label. B) A relative address uses a dollar sign before either the row or column label; an absolute address uses just the row and column label in the cell reference. C) A relative address uses just the row and column label in the cell reference; an absolute address uses a dollar sign before either the row or column label. D) A relative address uses only the column label in the cell reference; an absolute address uses the row. Answer: C Diff: 2 Blooms: Remember Topic: Excel Formulas and Addressing LO1: Apply relative and absolute addressing in Excel formulas.

Chapter 1 Appendix A1 Basic Excel Skills

Business Analytics, 3e 17

Use the data given below to answer the following question(s) Below is the spreadsheet for demand prediction of a company that sells chocolates.

1 2 3 4 5 6 7 8 9 10 11

A Demand Prediction Models

Linear Model A B

10,000 10

Price $50 $55 $45

Demand 9,500 9,450 9,550

4) Given that D = a-bP, where D, is demand, "a" and "b," are linear constants, and P, is price, from the below spreadsheet, how will the formula in B9 be represented in Excel using relative addressing? A) B4-B5*A9 B) C5-C6*A10 C) B4-B5*A10 D) B5-B6*A10 Answer: A Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Excel Formulas and Addressing LO1: Copy formulas from one cell to another or to a range of cells. 5) If a dollar sign is used after the column in B5 (B$5), how will the formula at B8 be represented in C9 using absolute addressing? A) C3-B$5*C9 B) C5-C$6*B9 C) C5-C$6*C9 D) C5-C$5*B9 Answer: D Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Excel Formulas and Addressing LO1: Apply relative and absolute addressing in Excel formulas. Copyright © 2020 Pearson Education, Inc.

18 Chapter 1 Appendix A1 Basic Excel Skills

Business Analytics, 3e

6) If a dollar sign is used before the column label B4 ($B4), how will the formula at B10 be represented in C11 using absolute addressing? A) $B5-C6*B11 B) $C5-C6*A10 C) $B4-C5*B11 D) $A5-C6*B11 Answer: A Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Excel Formulas and Addressing LO1: Apply relative and absolute addressing in Excel formulas. 7) If, in the spreadsheet, cells B9 and B10 were empty, which of the following formulas should be entered in B8 so that the formula can be dragged to B9 and B10 to obtain their correct values? A) B4-B5*A8 B) B4-B5*$A8 C) $B4-B5*$A8 D) $B$4-$B$5*$A8 Answer: D Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Excel Formulas and Addressing LO1: Copy formulas from one cell to another or to a range of cells. 8) Using a $ sign before a column label ________. A) keeps the reference to both the row and column fixed B) keeps the reference to the row fixed, but allows the column reference to change C) keeps the reference to column fixed, but allows the row reference to change D) allows both the row and column references to change Answer: C Diff: 1 Blooms: Remember AACSB: Analytic Skills Topic: Excel Formulas and Addressing LO1: Copy formulas from one cell to another or to a range of cells.

Chapter 1 Appendix A1 Basic Excel Skills

Business Analytics, 3e 19

9) To copy a formula from a single cell or range of cells down a column or across a row, first ________, click and hold the mouse on the small square in the lower right-hand corner of the cell, and drag the formula to the "target" cells which you wish to copy. A) press Ctrl-C B) select the cell or range C) press Ctrl-Enter D) select the whole spreadsheet Answer: B Diff: 1 Blooms: Remember AACSB: Analytic Skills Topic: Excel Formulas and Addressing LO1: Copy formulas from one cell to another or to a range of cells. 10) Trace the process of copying and pasting a cell, which has a formula in it, such that the formula is not retained in the pasted cell. A) Home - Paste - Paste Special - Paste Values B) Home - Paste - Paste Special - Paste Validation C) Home - Paste - Paste Special - Paste Formats D) Home - Paste - Paste Special - Paste Formulas Answer: A Diff: 1 Blooms: Remember Topic: Miscellaneous Excel Functions and Tools LO1: Copy formulas from one cell to another or to a range of cells.

20 Chapter 1 Appendix A1 Basic Excel Skills

Business Analytics, 3e

Use the data given below to answer the following question(s). Below is a spreadsheet of purchase orders for a computer hardware retailer.

1 2 3

A Purchase Orders

Supplier Rex 4 Technologies Rex 5 Technologies Rex 6 Technologies Rex 7 Technologies Max's 8 Wavetech Max's 9 Wavetech Max's 10 Wavetech 11 12

Item Item Description Cost Graphics Card $ 89

Cost per A/P Terms Order Quantity Order (Months) No.

Order Size

$3115

AL123

Large

Monitor

$150

$2250

AL234

Small

Keyboard

$ 15

$600

AL345

Large

Speakers

$ 15

$300

AL456

Small

HD Cables

$ 5

$50

KO876

Small

Processor

$278

$6950

KO765

Large

Hard disk

$120

$2160

KO654

Small

11) To find the total order cost, what Excel formula should be used in A12? A) =COUNT(C4:C10) B) =COUNT(C4:C7) C) =MAX(C4:C10) D) =SUM(E4:E10) Answer: D Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Excel Functions LO1: Use basic and advanced Excel functions. 12) For which of the following columns can the COUNT function be performed? A) column G B) column E C) column B D) column A Copyright © 2020 Pearson Education, Inc.

Chapter 1 Appendix A1 Basic Excel Skills

Business Analytics, 3e 21

Answer: B Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Excel Functions LO1: Use basic and advanced Excel functions. 13) To find the average of the total cost of orders from Rex Technologies, what Excel formula should be used in A12? A) =AVERAGE(C4:C10) B) =AVERAGE(C4:C7) C) =AVERAGE(E4:E7) D) =AVERAGE(E4:E10) Answer: C Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Excel Functions LO1: Use basic and advanced Excel functions. 14) The easiest way to locate a particular function is to select a cell and click on the Insert function button represented by ________ on the Excel ribbon. A) fx B) Σ C) $ D) % Answer: A Diff: 1 Blooms: Remember Topic: Excel Functions LO1: Find buttons and menus in the Excel 2010 ribbon. 15) What is the Insert function in Excel? Answer: The easiest way to locate a particular function is to select a cell and click on the Insert function button fx, which can be found under the ribbon next to the formula bar and also in the Function Library group in the Formulas tab. You may either type in a description in the search field, such as "net present value," or select a category, such as "Financial," from the drop-down box. This feature is particularly useful if you know what function to use but are not sure of what arguments to enter because it will guide you in entering the appropriate data for the function arguments. Diff: 1 Blooms: Remember Topic: Excel Functions LO1: Use basic and advanced Excel functions.

22 Chapter 1 Appendix A1 Basic Excel Skills

Business Analytics, 3e

16) The Excel function of ________ is used to find the largest value in a range of cells. A) SUM(range) B) COUNT(range) C) MAX(range) D) COUNTIF(range, criteria) Answer: C Diff: 1 Blooms: Remember Topic: Excel Functions LO1: Apply logical functions in Excel formulas. 17) ________ measures the worth of a stream of cash flows, taking into account the time value of money. A) Accounting rate of return B) Net present value C) Internal rate of return D) Adjusted present value Answer: B Diff: 1 Blooms: Remember Topic: Excel Functions LO1: Design simple Excel templates for descriptive analytics. 18) The ________ reflects the opportunity costs of spending funds now versus achieving a return through another investment, as well as the risks associated with not receiving returns until a later time. A) modified internal rate of return B) payback period C) accounting rate of return D) discount rate Answer: D Diff: 1 Blooms: Remember Topic: Excel Functions LO1: Design simple Excel templates for descriptive analytics. 19) Identify the equation for calculating the net present value for a stated period of time, where Ft = cash flow in period t, and i is the discount rate.

Ft  i 2 t 0 n n

A) NPV =



Chapter 1 Appendix A1 Basic Excel Skills

Business Analytics, 3e 23

B) NPV =  Ft (1  i )t t 0 n

Ft t t  0 (1  i ) n F D) NPV =  t (1  i )t t 0 i

C) NPV = 

Answer: C Diff: 1 Blooms: Remember Topic: Excel Functions LO1: Design simple Excel templates for descriptive analytics. 20) A positive NPV means that the investment will provide added value because the projected return exceeds the ________. A) modified internal rate of return B) discount rate C) accounting rate of return D) adjusted present value Answer: B Diff: 1 Blooms: Remember Topic: Excel Functions LO1: Design simple Excel templates for descriptive analytics. 21) Describe the method of calculating the net present value (NPV) in Excel. Answer: The Excel function NPV (rate, value1, value2,…) calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). Rate is the rate of discount over the length of one period (i), and value1, value2,… are 1 to 29 arguments representing the payments and income. The values must be equally spaced in time and are assumed to occur at the end of each period. The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If the first cash flow (such as an initial investment or fixed cost) occurs at the beginning of the first period, then it must be added to the NPV result and not included in the function arguments. Diff: 1 Blooms: Remember Topic: Excel Template Design LO1: Design simple Excel templates for descriptive analytics.

Business Analytics, 3e (Evans) Chapter 2: Database Analytics 1) What is a database? A) a structured collection of related files and data B) simply a collection of data C) a data file holding a single file D) flat files used to store data Answer: A Diff: 1 Blooms: Remember Topic: Data Sets and Databases LO1: Explain the difference between a data set and a database and apply Excel range names in data files. 2) In a database, information is stored and maintained in ________. A) fields B) measurements C) entities D) attributes Answer: C Diff: 1 Blooms: Remember Topic: Data Sets and Databases LO1: Explain the difference between a data set and a database and apply Excel range names in data files. 3) In a database file, which is organized in a two-dimensional table, the rows represent records of related data elements. Answer: TRUE Diff: 1 Blooms: Remember Topic: Data Sets and Databases LO1: Explain the difference between a data set and a database and apply Excel range names in data files. 4) The sort buttons in Excel can be found under: A) the Data tab in the Sort & Filter group. B) the Home tab in the Styles group. C) the Insert tab in the Sort group. D) the Sort tab in the Filter group. Answer: A Diff: 1 Copyright © 2020 Pearson Education, Inc. 24

Chapter 2: Database Analytics

Business Analytics, 3e 25

Blooms: Remember Topic: Data Queries: Tables, Sorting, and Filtering LO1: Construct Excel tables and be able to sort and filter data. Use the data given below to answer the following question. Following is the purchase order database of 'The Chef Says So', a restaurant in New York, over the last quarter (April-June). Order Date 5/6/2012 4/7/2012 5/13/2012 6/10/2012 6/22/2012 5/17/2012 4/25/2012 6/1/2012 4/2/2012 5/27/2012 6/13/2012 4/30/2012 5/11/2012 6/18/2012 5/9/2012 5/30/2012 6/6/2012 4/3/2012 6/26/2012 4/23/2012 4/29/2012 4/4/2012 6/15/2012 6/25/2012 5/23/2012 6/28/2012 5/25/2012 5/1/2012

Item

Region

Supplier

Unit Cost

Steel Fork Ceramic Plate Steel Fork Silver Spoon Steel Fork Ceramic Plate Steel Fork Steel Fork Steel Fork Ceramic Plate Steel Fork Ceramic Plate Ceramic Plate Steel Fork Glass Bottle Ceramic Bowl Ceramic Plate Silver Spoon Silver Spoon Ceramic Bowl Steel Fork Ceramic Bowl Ceramic Plate Ceramic Plate Ceramic Plate Ceramic Plate Ceramic Bowl Steel Fork

Antasia Puitoria Puitoria Puitoria Almeco Antasia Puitoria Puitoria Almeco Antasia Puitoria Antasia Antasia Antasia Puitoria Antasia Puitoria Antasia Antasia Puitoria Puitoria Antasia Puitoria Puitoria Antasia Almeco Puitoria Puitoria

Peter Kane Jones Gerry Sarah Peter Audrey Jones Thomas Peter Mary Henry Philip Peter Simson Peter Mary Peter Philip Kane Simson Philip Gerry Simson Peter Sarah Jones Audrey

5.44 23.44 8.44 23.44 6.44 8.44 5.44 8.44 5.44 12.44 8.44 5.44 23.44 8.44 128.45 19.44 12.44 12.44 23.44 8.44 4.74 19.44 12.44 18.45 8.44 23.44 8.44 5.44

Units 98 53 39 30 59 63 78 93 35 63 93 32 84 38 5 19 31 67 18 99 70 77 49 90 7 10 53 69

26 Chapter 2: Database Analytics

Order Date 4/12/2012 4/18/2012 6/30/2012 5/19/2012 4/16/2012 6/4/2012 5/2/2012 4/19/2012 6/11/2012 5/31/2012 6/2/2012 4/13/2012 5/3/2012 6/3/2012 4/17/2012

Business Analytics, 3e

Item

Region

Supplier

Unit Cost

Silver Spoon Steel Fork Ceramic Plate Glass Bottle Ceramic Bowl Ceramic Bowl Ceramic Bowl Glass Bottle Steel Fork Silver Spoon Ceramic Plate Steel Fork Ceramic Plate Ceramic Plate Ceramic Plate

Antasia Puitoria Puitoria Puitoria Antasia Puitoria Puitoria Almeco Puitoria Almeco Almeco Puitoria Puitoria Puitoria Puitoria

Henry Gerry Gerry Kane Peter Mary Kane Sarah Gerry Sarah Thomas Audrey Jones Jones Audrey

8.44 4.74 12.44 128.45 8.44 15.94 27.4 278.45 4.74 5.44 23.44 4.74 8.44 23.44 8.44

Units 99 56 83 8 65 58 45 6 10 79 60 17 14 97 31

5) Describe how to sort the data by inventory value to compute cumulative percentage of total inventory value to help the restaurateur conduct a Pareto analysis. (Assume that no damages were caused to the inventory purchased over the three months) Answer: In order to calculate inventory value of items, only Item, Unit Cost, and Units have to be retained in the table. Inventory value can be calculated by multiplying the unit cost by the number of units. Percentage and the cumulative percentage may be calculated based on the inventory values. Then, sort by Item, calculate subtotals for each Item, calculate percentages, sort the percentage in descending order and then calculate the cumulative percentages. Item Steel Fork Ceramic Plate Steel Fork Silver Spoon Steel Fork Ceramic Plate Steel Fork Steel Fork Steel Fork

Unit Cost 5.44 23.44 8.44 23.44 6.44 8.44 5.44 8.44 5.44

Units 98 53 39 30 59 63 78 93 35

Inventory Cumulative Percentage Value Percentage 533.12 1.77646 1.77646 1242.32 4.13966 5.91612 329.16 1.09683 7.01295 703.2 2.3432 9.35616 379.96 1.2661 10.6223 531.72 1.7718 12.3941 424.32 1.41392 13.808 784.92 2.61551 16.4235 190.4 0.63445 17.0579

Chapter 2: Database Analytics

Item Ceramic Plate Steel Fork Ceramic Plate Ceramic Plate Steel Fork Glass Bottle Ceramic Bowl Ceramic Plate Silver Spoon Silver Spoon Ceramic Bowl Steel Fork Ceramic Bowl Ceramic Plate Ceramic Plate Ceramic Plate Ceramic Plate Ceramic Bowl Steel Fork Silver Spoon Steel Fork Ceramic Plate Glass Bottle Ceramic Bowl Ceramic Bowl Ceramic Bowl Glass Bottle Steel Fork Silver Spoon Ceramic Plate Steel Fork Ceramic Plate Ceramic Plate Ceramic Plate Total

Unit Cost 12.44 8.44 5.44 23.44 8.44 128.45 19.44 12.44 12.44 23.44 8.44 4.74 19.44 12.44 18.45 8.44 23.44 8.44 5.44 8.44 4.74 12.44 128.45 8.44 15.94 27.4 278.45 4.74 5.44 23.44 4.74 8.44 23.44 8.44

Business Analytics, 3e 27

Units 63 93 32 84 38 5 19 31 67 18 99 70 77 49 90 7 10 53 69 99 56 83 8 65 58 45 6 10 79 60 17 14 97 31

Inventory Cumulative Percentage Value Percentage 783.72 2.61151 19.6695 784.92 2.61551 22.285 174.08 0.58007 22.865 1968.96 6.56097 29.426 320.72 1.0687 30.4947 642.25 2.14011 32.6348 369.36 1.23078 33.8656 385.64 1.28503 35.1506 833.48 2.77732 37.928 421.92 1.40592 39.3339 835.56 2.78425 42.1181 331.8 1.10562 43.2238 1496.88 4.98791 48.2117 609.56 2.03118 50.2428 1660.5 5.53312 55.776 59.08 0.19687 55.9728 234.4 0.78107 56.7539 447.32 1.49056 58.2444 375.36 1.25078 59.4952 835.56 2.78425 62.2795 265.44 0.8845 63.164 1032.52 3.44057 66.6045 1027.6 3.42417 70.0287 548.6 1.82805 71.8568 924.52 3.08069 74.9374 1233 4.1086 79.0461 1670.7 5.56711 84.6132 47.4 0.15795 84.7711 429.76 1.43205 86.2032 1406.4 4.68641 90.8896 80.58 0.26851 91.1581 118.16 0.39373 91.5518 2273.68 7.57636 99.1282 261.64 0.87184 100 30010.2

28 Chapter 2: Database Analytics

Business Analytics, 3e

Diff: 3 Blooms: Apply AACSB: Analytic Skills Topic: Data Queries: Tables, Sorting, and Filtering LO1: Construct Excel tables and be able to sort and filter data. 6) Which of the following relies on sorting data and calculating the cumulative percentage of the characteristic of interest? A) Randolph diagram B) Anscombe's quartet C) Bland-Altman plot D) Pareto analysis Answer: D Diff: 1 Blooms: Remember Topic: Data Queries: Tables, Sorting, and Filtering LO1: Apply the Pareto Principle to analyze data. Use the data given below to answer the following question(s). Below is a spreadsheet of purchase orders for a computer hardware retailer. 1 2

A B Purchase Orders

Item Item Description Cost Graphics 4 Rex Technologies Card $ 89 5 Rex Technologies Monitor $150 6 Rex Technologies Keyboard $ 15 7 Rex Technologies Speakers $ 15 8 Max's Wavetech HD Cables $ 5 9 Max's Wavetech Processor $278 10 Max's Wavetech Hard disk $120 11 Item 12 Supplier Order Size Cost 13 Rex Technologies Large >15 14 Max's Wavetech 3

Supplier

Cost per A/P Terms Order Quantity Order (Months) No.

Order Size

35 15 40 20 10 27 18

Large Small Large Small Small Large Small

$3115 $2250 $600 $300 $50 $6950 $2160

20 25 15 25 25 30 20

AL123 AL234 AL345 AL456 KO876 KO765 KO654

Chapter 2: Database Analytics

Business Analytics, 3e 29

7) To find the largest quantity of items ordered from Rex Technologies, what Excel formula should be used in A12? A) =COUNTIF(D4:D10) B) =SUM(D4:D7) C) =MAX(D4:D7) D) =COUNT(D4:D7) Answer: C Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Logical Functions LO1: Apply logical functions in Excel formulas. 8) To find the number of orders with A/P terms less than 25 months, what Excel formula should be used in A12? A) =COUNTIF(F4:F10,"<25") B) =COUNT(F4:F10,25) C) =AVERAGE(F4:F10,"<25") D) =COUNTIF(F4:F10,F5) Answer: A Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Logical Functions LO1: Apply logical functions in Excel formulas. 9) If purchase quantities of 25 units or higher are found to be large orders, and orders less than 25 are considered to be small, what IF function should be entered in H4 to be copied to H5:H10 to calculate each order's size? A) =IF(D4=AND=OR=25,"Large","Small") B) =IF(D4<>25,"Small")=AND(D4=25,"Large") C) =IF(D4=25,"Large")=OR("Small") D) =IF(D4>=25,"Large","Small") Answer: D Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Logical Functions LO1: Apply logical functions in Excel formulas. 10) Which of the following Database functions calculates the count of purchase orders made by Rex Technologies? A) =DCOUNT(A3:H10,C3,A12:A13)

30 Chapter 2: Database Analytics

Business Analytics, 3e

B) =DCOUNT(A4:H10,C3,A12:A13) C) =DCOUNT(A3:H10,C3,A13) D) =DCOUNT(A3:H10,A3,A12:A13) Answer: A Diff: 3 Blooms: Apply Topic: Lookup Functions for Database Queries LO1: Use database functions to extract records. 11) Which of the following Database functions calculates the count of purchase orders made by Rex Technologies for large size orders? A) =DCOUNT(A3:H10,C3,A12:B13) B) =DCOUNT(A3:H10,”Rex Technologies”,B12:B13) C) =DCOUNT(A3:H10,C3,B12:B13) D) =DCOUNT(A4:H10,D3,A12:B13) Answer: A Diff: 3 Blooms: Apply Topic: Lookup Functions for Database Queries LO1: Use database functions to extract records. 12) Which of the following Database functions calculates the total quantity ordered in large size? A) =DSUM(A3:H10,”Quantity”,B12:B13) B) =DSUM(A3:H10,D3,B12:B13) C) =DSUM(A3:H10,D3,A12:B13) D) =DSUM(A4:H10,D3,B12:B13) Answer: B Diff: 3 Blooms: Apply Topic: Lookup Functions for Database Queries LO1: Use database functions to extract records. 13) Which of the following Database functions calculates the total cost charged to Rex Technologies for large size orders? A) =DSUM(A3:H10,C3,A12:B14) B) =DSUM(A4:H10,”Cost per Order”,A12:B13) C) =DSUM(A3:H10,C3,A12:B13) D) =DSUM(A3:H10,E3,A12:B13) Answer: D Diff: 3 Blooms: Apply Topic: Lookup Functions for Database Queries LO1: Use database functions to extract records.

Chapter 2: Database Analytics

Business Analytics, 3e 31

14) Which of the following Database functions calculates the average item cost ordered by Rex Technologies? A) =DAVERAGE(A3:H10,C3,A12:A13) B) =DAVERAGE(A3:H10,C3,A12:B13) C) =DAVERAGE(A3:H10,”Item Cost”,A12:A14) D) =DSUM(A3:H10,C3,A12:A13)/COUNTA(A4:A10) Answer: A Diff: 3 Blooms: Apply Topic: Lookup Functions for Database Queries LO1: Use database functions to extract records. 15) Which of the following is a differentiation between calculating using the functions COUNT and COUNTIF? A) COUNT does not require a range of cells; COUNTIF requires a range of cells. B) COUNT only requires a range of cell and can be obtained without special criteria, COUNTIF requires range and special criteria to be calculated. C) COUNT requires a range of cells; COUNTIF does not require a range of cells, only special criteria. D) COUNT calculates the sum of values for a range of cells; COUNTIF finds the largest value in a range of cells. Answer: B Diff: 2 Blooms: Remember Topic: Logical Functions LO1: Apply logical functions in Excel formulas. 16) ________ is a logical function that returns one value if the condition is true and another if the condition is false. A) OR(condition 1, condition 2…) B) AND(condition 1, condition 2…) C) TO(value if true, value if false) D) IF(condition, value if true, value if false) Answer: D Diff: 1 Blooms: Remember Topic: Logical Functions LO1: Apply logical functions in Excel formulas. 17) Which of the following functions is a logical function that returns TRUE if any condition is true and FALSE if not? A) TO(value if true, value if false) B) AND(condition 1, condition 2…)

32 Chapter 2: Database Analytics

Business Analytics, 3e

C) OR(condition 1, condition 2…) D) IF(condition, value if true, value if false) Answer: C Diff: 1 Blooms: Remember Topic: Logical Functions LO1: Apply logical functions in Excel formulas. 18) Give the logical function for the following: If cell B7 equals 12, check contents of cell B10. If cell B10 is 10, then the value of the function in the string is YES; if not, it is a blank space. If cell B7 does not equal 12, then the value of the function is 7. A) =IF(B7=12,(AND(B10=10, "")(YES)),7) B) =IF(B10=10,(OR(B7=12,"")"YES")7) C) =IF(B7=12,(IF(B10=10,"YES", "")),7) D) =IF(B7=12,(AND(B10=10,"YES","")(B10="NO"),7) Answer: C Diff: 3 Blooms: Understand AACSB: Analytic Skills Topic: Logical Functions LO1: Apply logical functions in Excel formulas. 19) If cell G7 contains the function ________, it states that if the value in cell C3 is 9, the number 7 will be assigned to cell G7; if the value in cell C3 is not 9, the number 4 will be assigned to cell G7. A) =IF(G7=9)(G7=7)=OR(G7=4) B) =IF(G7=7)=THEN(C3=9)=OR(C3=4) C) =IF(C3=9)(C3=7)=OR(C3=4) D) =IF(C3=9,7,4) Answer: D Diff: 2 Blooms: Understand AACSB: Analytic Skills Topic: Logical Functions LO1: Apply logical functions in Excel formulas. 20) The function ________ returns a value or reference of the cell at the intersection of a particular row and column in a given range. A) VLOOKUP(lookup_value, table_array, col_index_num) B) INDEX(array, row_num, col_num) C) MATCH(lookup_value, lookup_array, match_type) D) HLOOKUP(lookup_value, table_array, row_index_num) Answer: B

Chapter 2: Database Analytics

Business Analytics, 3e 33

Diff: 1 Blooms: Remember Topic: Lookup Functions for Database Queries LO1: Use Excel lookup functions to make database queries. 21) Which of the following Lookup functions returns the relative position of an item in an array that equals a specified value in a specified order? A) HLOOKUP(lookup_value, table_array, row_index_num) B) MATCH(lookup_value, lookup_array, match_type) C) INDEX(array, row_num, col_num) D) VLOOKUP(lookup_value, table_array, col_index_num) Answer: B Diff: 1 Blooms: Remember Topic: Lookup Functions for Database Queries LO1: Use Excel lookup functions to make database queries. 22) In a MATCH function, if the match_type = 0, then ________. A) the function finds the largest value that is less than or equal to lookup_value B) the function finds the smallest value that is greater than or equal to lookup_value C) MATCH finds the first value that is exactly equal to lookup_value D) the values in the lookup_array must be in a particular order Answer: C Diff: 1 Blooms: Remember Topic: Lookup Functions for Database Queries LO1: Use Excel lookup functions to make database queries. 23) For which of the following MATCH functions must the values in the lookup_array be ordered in a descending order? A) When match_type = -1 B) When match_type >1 C) When match_type = 0 D) When match_type = 1 Answer: A Diff: 1 Blooms: Remember Topic: Lookup Functions for Database Queries LO1: Use Excel lookup functions to make database queries.

34 Chapter 2: Database Analytics

Business Analytics, 3e

24) Using the spreadsheet below, provide the steps in using Excel formulas in finding the cost of the first order for Item number 1345, and the total cost of all Item numbers 1345, using the Match and Index functions in Excel. Column B is sorted by item number in ascending order.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

A Purchase Orders

Supplier Rex Technologies Rex Technologies Rex Technologies Rex Technologies Max's Wavetech Max's Wavetech Max's Wavetech Rex Technologies Rex Technologies Rex Technologies Rex Technologies Max's Wavetech

Item No. Item Cost Quantity 1123 $ 89 35 1234 $150 15 1345 $ 15 40 1345 $ 15 20 1345 $ 5 10 1765 $278 27 1654 $120 18 1765 $ 54 56 1100 $ 71 33 1100 $ 10 14 1683 $ 7 25 1683 $100 31

E Cost per Order $3115 $2250 $ 600 $ 300 $ 50 $6950 $2160 $3024 $2343 $ 140 $ 175 $3100

Answer: To find the order cost associated with the first order for 1345, which is in column E, we have to first use the Match function, =MATCH(1345,$B$4:$B$15,0). Accordingly, the result will be, =MATCH(1345,$B$4:$B$15,0) = 3. In order to find the cost associated with this result, we add this function in an Index function. Therefore, the formula for finding cost is, =INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,0),5). …. (1) The result for this formula is, =INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,5) = $600. To find the total cost associated with all items under Item number: 1345, we find the last cost associated with 1345 in column E, which is given by the formula =INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,1),5). …. (2) The result for this formula is, =INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,1),5) = $50. We then substitute both (1) and (2) into Excel's SUM function. Therefore we get, =SUM(INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,0),5):INDEX($A$4:$E$15,MATCH(1 345,$B$4:$B$15,1),5)) Therefore the total cost for all items under 1345, =SUM(INDEX($A$4:$E$15,MATCH(1345,$B$4:$B$15,0),5):INDEX($A$4:$E$15,MATCH(1 345,$B$4:$B$15,1),5)) = $950. Diff: 3 Blooms: Apply AACSB: Analytic Skills Copyright © 2020 Pearson Education, Inc.

Chapter 2: Database Analytics

Business Analytics, 3e 35

Topic: Lookup Functions for Database Queries LO1: Use Excel lookup functions to make database queries. 25) AND(condition 1, condition 2…) is a logical function that returns TRUE if all conditions are true and FALSE if not. Answer: TRUE Diff: 1 Blooms: Remember AACSB: Analytic Skills Topic: Logical Functions LO1: Apply logical functions in Excel formulas. 26) To use the VLOOKUP(lookup_value, table_array, col_index_num), the table must be sorted in descending order. Answer: FALSE Diff: 1 Blooms: Remember AACSB: Analytic Skills Topic: Lookup Functions for Database Queries LO1: Use Excel lookup functions to make database queries. 27) In a MATCH function, the default value for match_type = 0. Answer: FALSE Diff: 1 Blooms: Remember AACSB: Analytic Skills Topic: Lookup Functions for Database Queries LO1: Use Excel lookup functions to make database queries. 28) In a MATCH function, if match_type = 1, then the function finds the smallest value that is greater than or equal to lookup_value. Answer: FALSE Diff: 1 Blooms: Remember AACSB: Analytic Skills Topic: Lookup Functions for Database Queries LO1: Use Excel lookup functions to make database queries. 29) Explain the different Lookup functions in Excel. Answer: Excel provides some useful functions for finding specific data in a spreadsheet. These functions are useful in many applications:

36 Chapter 2: Database Analytics

Business Analytics, 3e

VLOOKUP(lookup_value, table_array, col_index_num) looks up a value in the leftmost column of a table and returns a value in the same row from a column you specify. The table must be sorted in an ascending order. HLOOKUP(lookup_value, table_array, row_index_num) looks up a value in the top row of a table and returns a value in the same column from a row you specify. The table must be sorted in an ascending order from left to right. INDEX(array, row_num, col_num) Returns a value or reference of the cell at the intersection of a particular row and column in a given range. MATCH(lookup_value, lookup_array, match_type) Returns the relative position of an item in an array that matches a specified value in a specified order. Diff: 1 Blooms: Remember Topic: Lookup Functions for Database Queries LO1: Use Excel lookup functions to make database queries. 30) Which of the following is true about constructing PivotTables? A) It is not possible to construct the PivotTable in the same worksheet. B) Dragging a field into the Report Filter area allows addition of a third dimension to the analysis. C) Placing a field each in the row and column labels will automatically sum the variable values in the table. D) PivotTables cannot be duplicated by copying and pasting an existing table. Answer: B Diff: 2 Blooms: Remember Topic: PivotTables LO1: Use PivotTables to analyze and gain insight from data.

Business Analytics, 3e (Evans) Chapter 3: Data Visualization 1) To select a chart type in Excel from the Charts group, which tab has to be accessed? A) Design tab B) Layout tab C) Insert tab D) Format tab Answer: C Diff: 1 Blooms: Remember Topic: Creating Charts in Microsoft Excel LO1: Create Microsoft Excel charts. Use the data given below to answer the following question(s). Following is the purchase order database of 'The Chef Says So', a restaurant in New York, over the last quarter (April-June).

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

A Order Date 5/6/2012 4/7/2012 5/13/2012 6/10/2012 6/22/2012 5/17/2012 4/25/2012 6/1/2012 4/2/2012 5/27/2012 6/13/2012 4/30/2012 5/11/2012 6/18/2012 5/9/2012 5/30/2012 6/6/2012 4/3/2012 6/26/2012 4/23/2012 4/29/2012 4/4/2012 6/15/2012

B Item Steel Fork Ceramic Plate Steel Fork Silver Spoon Steel Fork Ceramic Plate Steel Fork Steel Fork Steel Fork Ceramic Plate Steel Fork Ceramic Plate Ceramic Plate Steel Fork Glass Bottle Ceramic Bowl Ceramic Plate Silver Spoon Silver Spoon Ceramic Bowl Steel Fork Ceramic Bowl Ceramic Plate

C Region Antasia Puitoria Puitoria Puitoria Almeco Antasia Puitoria Puitoria Almeco Antasia Puitoria Antasia Antasia Antasia Puitoria Antasia Puitoria Antasia Antasia Puitoria Puitoria Antasia Puitoria

D E F Supplier Unit Cost Units Peter 5.44 98 Kane 23.44 53 Jones 8.44 39 Gerry 23.44 30 Sarah 6.44 59 Peter 8.44 63 Audrey 5.44 78 Jones 8.44 93 Thomas 5.44 35 Peter 12.44 63 Mary 8.44 93 Henry 5.44 32 Philip 23.44 84 Peter 8.44 38 Simson 128.45 5 Peter 19.44 19 Mary 12.44 31 Peter 12.44 67 Philip 23.44 18 Kane 8.44 99 Simson 4.74 70 Philip 19.44 77 Gerry 12.44 49

38 Chapter 3: Data Visualization

25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

A 6/25/2012 5/23/2012 6/28/2012 5/25/2012 5/1/2012 4/12/2012 4/18/2012 6/30/2012 5/19/2012 4/16/2012 6/4/2012 5/2/2012 4/19/2012 6/11/2012 5/31/2012 6/2/2012 4/13/2012 5/3/2012 6/3/2012 4/17/2012

B Ceramic Plate Ceramic Plate Ceramic Plate Ceramic Bowl Steel Fork Silver Spoon Steel Fork Ceramic Plate Glass Bottle Ceramic Bowl Ceramic Bowl Ceramic Bowl Glass Bottle Steel Fork Silver Spoon Ceramic Plate Steel Fork Ceramic Plate Ceramic Plate Ceramic Plate

Business Analytics, 3e C Puitoria Antasia Almeco Puitoria Puitoria Antasia Puitoria Puitoria Puitoria Antasia Puitoria Puitoria Almeco Puitoria Almeco Almeco Puitoria Puitoria Puitoria Puitoria

D Simson Peter Sarah Jones Audrey Henry Gerry Gerry Kane Peter Mary Kane Sarah Gerry Sarah Thomas Audrey Jones Jones Audrey

E 18.45 8.44 23.44 8.44 5.44 8.44 4.74 12.44 128.45 8.44 15.94 27.4 278.45 4.74 5.44 23.44 4.74 8.44 23.44 8.44

F 90 7 10 53 69 99 56 83 8 65 58 45 6 10 79 60 17 14 97 31

2) Describe how to and construct a line chart exhibiting the purchase order of ceramic plates over the three months. Answer: Filter the data set by Item (Ceramic Plate). Sort the Order Date by Oldest to Newest. Select columns A and F and choose the type of chart (Line Chart) from the Charts group under the Insert tab. Change the title and make data and formatting changes where necessary.

Diff: 3

Chapter 3: Data Visualization

Business Analytics, 3e 39

Blooms: Apply AACSB: Analytic Skills Topic: Creating Charts in Microsoft Excel LO1: Create Microsoft Excel charts. 3) Changes to the type of chart, data included in the chart, and chart layout and styles can be made from the Layout tab. Answer: FALSE Diff: 1 Blooms: Remember Topic: Creating Charts in Microsoft Excel LO1: Create Microsoft Excel charts. 4) Describe how to and construct a pie chart exhibiting the count of purchase orders by item. Answer: Identify all the outcomes for the variable Item. Create a frequency table of the counts of purchase orders by each outcome of the variable Item using the Excel formula COUNTIF($B$2:$B$44, criteria), where criteria represents each outcome of the variable Item. Select the entire table and choose the type of chart (Pie Chart) from the Charts group under the Insert tab. Change the title and make data and formatting changes where necessary.

Diff: 3 Blooms: Apply AACSB: Analytic Skills Topic: Creating Charts in Microsoft Excel LO1: Create Microsoft Excel charts.

40 Chapter 3: Data Visualization

Business Analytics, 3e

5) Describe how to and construct a column chart exhibiting the total units purchased by region. Answer: Identify all the outcomes for the variable Region. Create a frequency table of the sum of units for each outcome of the variable Region using the Excel formula SUMIF($C$2:$C$44,criteria,$F$2:$F$44), where criteria represents each outcome of the variable Region. Select the entire table and choose the type of chart (Column Chart) from the Charts group under the Insert tab. Change the title and make data and formatting changes where necessary.

Diff: 3 Blooms: Apply AACSB: Analytic Skills Topic: Creating Charts in Microsoft Excel LO1: Create Microsoft Excel charts.

Chapter 3: Data Visualization

Business Analytics, 3e 41

6) Describe how to and construct an area chart exhibiting the units ordered by the region of Puitoria over the three months. Answer: Filter the data set by Region (Puitoria). Sort the Order Date by Oldest to Newest. Select the columns A and F of the data set and choose the type of chart (Area Chart) from the Charts group under the Insert tab. Change the title and make data and formatting changes where necessary.

Diff: 3 Blooms: Apply AACSB: Analytic Skills Topic: Creating Charts in Microsoft Excel LO1: Create Microsoft Excel charts.

42 Chapter 3: Data Visualization

Business Analytics, 3e

7) Describe how to and construct a scatter chart exhibiting the relationship between the unit cost and the number of units for the purchase of steel forks. Answer: Filter the data set by Item (Steel Fork). Select the columns for Unit Cost and Units (columns E and F), and choose the type of chart (Scatter Chart) from the Charts group under the Insert tab. Change the title and make data and formatting changes where necessary.

Diff: 3 Blooms: Apply AACSB: Analytic Skills Topic: Creating Charts in Microsoft Excel LO1: Create Microsoft Excel charts.

Chapter 3: Data Visualization

Business Analytics, 3e 43

8) Describe how to and construct a combination chart exhibiting the purchase order of ceramic plates as a line, and the unit cost as columns on the secondary axis over the three months. Answer: Filter the data set by Item (Ceramic Plate). Sort the Order Date by Oldest to Newest. Select columns A, E and F and choose the type of chart (Line Chart) from the Charts group under the Insert tab. Right click the unit cost line and select Change Series Chart Type. Change the Unit Cost line to column, and check the Secondary Axis box. Change the title and make data and formatting changes where necessary.

Diff: 3 Blooms: Apply AACSB: Analytic Skills Topic: Creating Charts in Microsoft Excel LO1: Create Microsoft Excel charts. 9) Elaborate on the use of geographic data mapping in business analytics. Answer: Many applications of business analytics involve geographic data. For example, problems such as finding the best location for production and distribution facilities, analyzing regional sales performance, transporting raw materials and finished goods, and routing vehicles such as delivery trucks involve geographic data. In such problems, data mapping can help in a variety of ways. Visualizing geographic data can highlight key data relationships, identify trends, and uncover business opportunities. In addition, it can often help to spot data errors and help end users understand solutions, thus increasing the likelihood of acceptance of decision models. MapPoint is a geographic data-mapping tool that allows you to visualize data imported from Excel and other database sources and integrate them into other Microsoft Office applications. Diff: 1 Blooms: Remember Topic: Creating Charts in Microsoft Excel LO1: Create Microsoft Excel charts.

44 Chapter 3: Data Visualization

Business Analytics, 3e

10) Roger wants to compare values across categories using vertical rectangles. Which of the following charts must Roger use? A) Line chart B) Clustered column chart C) Pie chart D) Stacked column chart Answer: B Diff: 2 Blooms: Apply AACSB: Analytic Skills Topic: Creating Charts in Microsoft Excel LO1: Determine the appropriate chart to visualize different types of data. 11) Which of the following charts provides a useful means for displaying data over time? A) Scatter chart B) A doughnut chart C) Pie chart D) Line chart Answer: D Diff: 1 Blooms: Remember Topic: Creating Charts in Microsoft Excel LO1: Determine the appropriate chart to visualize different types of data. 12) Philip wishes to understand the relative proportion of each data source to the total. Which of the following charts must Philip use? A) Pie chart B) Bar chart C) Scatter chart D) Column chart Answer: A Diff: 1 Blooms: Remember Topic: Creating Charts in Microsoft Excel LO1: Determine the appropriate chart to visualize different types of data. 13) Observations consisting of pairs of variable data are required to construct a ________ chart. A) doughnut B) scatter C) radar D) line Answer: B Diff: 1