AD 100
Test 1
QUESTION 1
The IF function has four entries.
True
False
0.9 points
QUESTION 2
Which of the following is not a valid formulation of the SUM function?
=SUM(3,A2,D5,FALSE)
=SUM(A10:A20, A10,A10)
=SUM(A1:D1,B1:B5,6)
=SUM(6)
each of the above is valid
0.9 points
QUESTION 3
How would Excel evaluate the following formula? =OR(7<5, A2=6,NOT("zebra"<="tree"))? Assume that the formula is in cell D4, the workbook contains no circular references, and outside of cell D4 the workbook contains no errors. TRUE FALSE It would return an error There is insufficient information to answer this question 1.4 points QUESTION 4 Use the brewery.xlsx file to answer the following question: How many total transactions took place at Beerland Amherst? 35274 25844 25854 21943 15939 0.9 points QUESTION 5 One of the benefits of using the Table feature is that you'll be able to see the column titles at every moment. True False 0.9 points QUESTION 6 Use the file Code_Enforcement_-_Building_and_Property_Violations.xlsx for this question. What is the total dollar amount of penalties for closed cases in Charlestown due to residents' improper storage of trash (improper storage trash:res)? ( ) 1.4 points QUESTION 7 Use the Brewery.xlsx file to answer the following question: In which month does the highest order quantity take place? April January March July June 0.9 points QUESTION 8 The PivotTable functionality is located in the Data tab. True False 0.9 points QUESTION 9 Use the Brewery.xlsx file to answer the following question: How many beers product names have order quantities less than or equal to 5000? 20 21 17 19 18 1.4 points QUESTION 10 Which of the following functions returns a range of cells? OFFSET COUNTA MATCH REDEFINE None of the given choices 0.9 points QUESTION 11 A named range can be used when creating charts in formulas in functions none of the given choices all of the given choices 0.9 points QUESTION 12 Which of the following OFFSET statements is correct if we want to refer to the range 1 cell right to the base cell and expand the range area to be a 2X3 range? OFFSET(base,0,1,2,3) OFFSET(base,2,3,1,0) OFFSET(base,1,0,2,3) OFFSET(base,2,3,0,1) OFFSET(base,1,0,3,2) 1.4 points QUESTION 13 The Simplex Method is the default solving method in Solver. True False 0.9 points QUESTION 14 The Solver and Analysis Toolpak Add-ins are not built in to Excel. True False 0.9 points QUESTION 15 Use solver to answer the following question: A corrupt shipping concern wishes to maximize the revenue they make from an analytics-bereft manufacturing concern, which has 4 factories and 3 warehouses. Factory 1 supplies 1000 units per week and is charged $5, $3, and $4 to ship each unit to Warehouses 1, 2, and 3 respectively. Factory 2 supplies 1200 units each week and is charged $4, $3, and $3 to ship to Warehouses 1, 2, and 3. Factory 3 supplies 1500 units and is charged $6, $2, and $5 to ship to the three warehouses. Factory 4 supplies 1800 units and is charged $6, $2, and $4. If Warehouse 1 requires 3000 units per week, Warehouse 2 demands 1000, and Warehouse 3 demands 1500, what is the maximum it would cost them in shipping to fulfill each warehouse's demand? ( ) Test 2 QUESTION 1 You have US census figures for population by year, income bracket, and state. You want to see a 2 dimensional table of just income bracket and state for the calendar year 2000. Which OLAP operation would you use? pivot/Rotate drill Down roll Up slice none of the given choices describe this 0.9 points QUESTION 2 You have US census figures for population by year, income bracket, and state. You want to see a 2 dimensional table of just income bracket and state for the calendar year 2000. Which OLAP operation would you use? pivot/Rotate drill Down roll Up slice none of the given choices describe this 0.9 points QUESTION 3 Which of the following is generally true of MOLAP tables? They require more hard drive capacity than ROLAP tables. They require more processing power than ROLAP tables. Both A and B are true None of the given choices are true 1.4 points QUESTION 4 When creating a Power Pivot-driven PivotTable, you may either create it on a new worksheet or an existing one. True False 0.9 points QUESTION 5 Power Pivot-driven PivotTables will allow you to create Calculated Fields. True False 0.9 points QUESTION 6 In the brewery.xlsx file, which of the following is the primary index key for table2? Product Name Debut Style ProductID ABV% 1.4 points QUESTION 7 Use the Brewery.xlsx file to answer the following question: What's the name of the product with the highest total revenue? Black Manatee Evil Squid Bruin Extended Puppy Rearview 0.9 points QUESTION 8 Use the Brewery.xlsx file to answer the following question: What's the name of the product with the lowest total cost (not unit cost, but cost of all units consumed)? Flyin Squirrel Mud Guard Lotus Former Weasel Subatomic Glove 0.9 points QUESTION 9 Use the Brewery.xlsx file to answer the following question: What's the sum of revenue for those beers whose product name contains the letter "I"? 251698.5 321017.5 147895.21 159823.25 400256.89 1.4 points QUESTION 10 Which button in the Power Pivot Home Ribbon allows you to load data from an external excel file? From Database From Data Service From Other Sources Existing Connections None of the Above 0.9 points QUESTION 11 Which button in the Power Pivot Home Ribbon allows you to load data from a text file? From Database From Data Service From Other Sources Existing Connections None of the Above 0.9 points QUESTION 12 Using the file ImportText.txt, what is the sum of the TransCost column? ( ) Test 3 QUESTION 1 Using the following URL, determine the average yield of a Long Term Treasure Bill (LT Composite>10yrs) in 2010:
https://home.treasury.gov/resource-center/data-chart-center/interest-rates/TextView?type=daily_treasury_real_long_term&field_tdr_date_value=all
Under the Resource Center, select “Daily Treasury Real Long-Term Rate Averages”
Select type of Interest Rate Data: Daily Treasury Real Long-Term Rates
Select Time Period: 2010
1.72
1.73
1.74
1.75
1.76
0.9 points
QUESTION 2
Using the following URL of http://finance.yahoo.com/quote/GOOG/history, determine the average volume for shares of Google in 2020. Use the following parameters:
Time Period: 12/31/2019- 12/30/2020
Show: Historical Prices
Frequency: Weekly
(Round the answer to whole number)
( )
0.9 points
QUESTION 3
Using the following URL, what was the average population of the countries ranked 51-100 in population size in 1900 (round to the nearest 1000): https://www.bu.edu/adminsc/2021/03/18/list-of-countries-by-population-in-1900/
( )
1.4 points
QUESTION 4
Use the Brewery.xlsx file to answer the following question: What percentage of profit does Lager account for among the three types of beer? (rounded to 2 decimals)
26.13%
52.00%
25.68%
14.25%
13.76%
0.9 points
QUESTION 5
Use the Brewery.xlsx file to answer the following question: Which state has the highest total profit?
CT
MA
TX
PA
CA
0.9 points
QUESTION 6
Use the Brewery.xlsx file to answer the following question: Across all four locations, what percentage of total profit is made in MA?
75.69%
81.00%
45.26%
74.56%
84.29%
Test 4
QUESTION 1
The Create Relationships and Edit Relationships windows are the same, except the Create Relationships window has all the fields pre-populated.
True
False
0.9 points
QUESTION 2
Within the Relationship View, the number (1) denotes the table on the One side of the relationship.
True
False
0.9 points
QUESTION 3
Which of the following options for data load is off by default?
Import relationships from data sources
Update relationships when refreshing queries
Autodetect relationships after data is loaded
Allow data preview to download in the background
1.4 points
QUESTION 4
Many-to-One cardinality specifies that there is a single record in one table for many records that map in the table that is joined.
True
False
0.9 points
QUESTION 5
Which is an example of a Parent-Child relationship?
“A list of transactions, each of which takes place at a specific time and place”
“A company with multiple offices, which in turn have multiple branches”
“A list of raw materials, each of which are used to assemble various goods”
“A list of services, each of which are available from various companies”
0.9 points
QUESTION 6
Which of the following are most likely to require a many to many relationship?
A list of parents and a list of their children
A list of dates and a list of historical events
A list of cities and a list of GPS coordinates
A list of companies and a list of brands they produce
1.4 points
QUESTION 7
Edits can be made in Reading View.
True
False
0.9 points
QUESTION 8
Which of the options can NOT be changed in the Visualizations toolbar?
Axis Title
Calculated Column
Graph Color
Data Labels
0.9 points
QUESTION 9
To show a line graph and a bar chart what kind of visualization should you create?
Scatter Chart
Matrix Visualization
Combination Chart
Slicer