Quiz

There is a phrase in cell A2, "I like to eat ice cream on the 4th of July". Match the result of each formula with the correct display. Answer options may be used more than once or not at all.

=PROPER I Like To Eat Ice Cream On The 4Th Of July =LEFT I lik =UPPER(A2) I LIKE TO EAT ICE CREAM ON THE 4TH OF JULY =LOWER(A2)i like to eat ice cream on the 4th of july

ff

A company's sales by area are presented below, for the first four months of the year. Management asked an analyst to summarize the amount of sales. Which two pieces of information does this PivotTable provide to management? Choose 2 answers A. Total sales for 2013 B. Total sales for 1 Q C. Total sales D. Total sales by month

The amount of a loan is $85,000, which is 80% of the selling price. The remaining 20% is the down payment. What is the down payment on the house, using the iterative calculation and rounding the answer to the nearest dollar?

A. $17,000 B. $21 ,250 C. $68,000 D. $70,833

The accountant for a company wants to create a spreadsheet to calculate sales markup. The costs for these items are located in cells C4 through C30, and the markup percent is located in cell A 1. The formula for the markup will be in the range D4 through D30. What does the formula in cell D4 have to be in order to copy it into cells D5 through D30?

A. =$C4*A1 B. =C4+$A1 C. =C4*A1 D. =C4*A$1

A cook is planning a lunch for eight people: Entrée Name # of Entrees Chicken Primavera 4 Beef Bourgignon 5 Salmon Filet 3 Veal Parmesan 2 Vegetable Lasagna 3 Which combination of entries will feed at least eight people (i.e., which formula will display a result of TRUE)?

A. =(SUMIF(A2:A6,"vegetable lasagna",B2:B6)+SUMIF(A2:A6,"veal parmesan",B2:B6)>=8) B. =(SUMIF(A2:A6,"salmon filet",B2:B6)+SUMIF(A2:A6,"beef bourgignon",B2:B6)>=8) C. =(SUMIF(A2:A6,"chicken primavera",B2:B6)+SUMIF(A2:A6,"beef bourgignon",B2:B6)<=8) D. =(SUMIF(A2:A6,"veal parmesan",B2:B6)+SUMIF(A2:A6,"beef bourgignon",B2:B6)>=8)

A worker plans to set aside money for retirement and is interested to know the present value of potential savings. The pertinent facts include the following: A B 1 Rate 5.00% 2 Years 30 3 Monthly Pmt $125.00 Which formula reflects the present value of the investment?

A. =-PV(B1/12, 12.B2,B3) B. =PV(B1/12, 12*B2,B3) C. =PV(B1,B2,B3) D. =-PV(B1,B2,B3) E. =-PV(B1/12,B2,B3*12) F. =PV(B1/12,B2,B3. 12)

(A1) Name (B1) Sales (C1)Commission (A2) Rep 1 (B2) $12,500.00 (C2) (A3) Rep 2 (B3) $15,000.00 (C3) (A4) Rep 3 (B4) $17,000.00 (C4) The commission percent of 2% is located in cell A5. What should the formula be in cell C2 in order to calculate commission dollars and enable copying to cells C3 and C4?

A. =A5.82 B. =A5+$82 C. =$A5+82 D. =A$5.82

A company's 4Q 2013 sales are as follows: A B 1 Month Sales ($) 2 October $125,000.00 3 November $142,000.00 4 December $162,500.00 Which two formulas will determine the minimum monthly sales for the three months? Choose 2 answers

A. =MINIMUM(B2,B3,B4) B. =MINIMUM(B2:B4) C.=MIN(B2:B4) D.=MIN(B2,B3,B4)

An owner of a company buys Product A from four different vendors. T he purchases made from each vendor last year are displayed in separate tabs in Excel, one tab per vendor. Each tab's set of purchase order data is saved as a named range 'V1PA," 'V2PA," 'V3PA," 'V4PA." The owner would like the analyst to identify the largest purchase order from last year. Which formula accomplishes this goal?

A. =MlN(V1PA,V2PA,V3PA,V4PA) B. =MAX(V1PA!,V2PA!,V3PA!,V4PA!) C. =MIN(V1PA!,V2PA!,V3PA!,V4PA!) D. =MAX(V1PA!+V2PA!+V3PA!+V4PA!) E. =MAX(V1PA,V2PA,V3PA,V4PA) F. =Ml N(V1PA+V2PA+V3PA+V4PA)

The value in cell A1 is 10. Which NOT formula gives the result of TRUE?

A. =NOT(A1=10) B. =NOT(A1<12) C. =NOT(A1=8) D. =NOT(A1>4)

A client wants to borrow $10,000 and pay $200 per month over five years to make a capital purchase. Which function will tell the client the maximum annual interest rate that will meet this payment budget?

A. =RATE(60, 200, 10000) B. =INT(60,-200, 10000) C.=RATE(60,-200, 10000) D. =INT(60, 200, 10000)

An analyst is presented with a list of account numbers displaying 20 digits, starting in cell A2. The analyst only needs the last 1 0 digits. Which formula will show the information the analyst needs?

A. =RIGHT(A2,20,10) B. =RIGHT(10,A2) C. =RIGHT(A2,10) D. =RIGHT(A2,20-10)

An individual's boss requires him to place the date and time on each of his spreadsheets. Which formula should he use?

A. =TODAY() B. =NOW() C. =NOW(TODAY) D. =TODAY(NOW) E. =TIME()

An owner of a company buys Product A from four different vendors. T he purchases made from each vendor last year are displayed in separate tabs in Excel, one tab per vendor. Each tab's set of purchase order data is saved as a named range 'V1PA," "V2PA," 'V3PA," 'V4PA." The owner would like the analyst to summarize all purchase data for Product A on a new tab, adding up the cost of all purchase orders from last year. Which formula accomplishes this goal?

A. =TOTAL(V1 PA+V2PA,V3PA+V4PA) B. =TOTAL(V1 PA+V2PA+V3PA+V4PA) C. =ADD(V1 PA+V2PA,V3PA+V4PA) D. =ADD(V1PA+V2PA+V3PA+V4PA) E. =SUM(V1 PA+V2PA,V3PA+V4PA) F. =SUM(V1 PA+V2PA+V3PA+V4PA)

Which chart type displays each individual investment compared to the entire investment portfolio at a given point in time?

A. A line chart B. A pie chart C. A stacked column chart D. A column chart

Data for a company is as follows: A B C D 48 Name Department Hire Date Service Yrs 49 Employee 1 Paint 1/11/2012 2 50 Employee 2 Assembly 12/4/2009 4 51 Employee 3 Assembly 7/15/2010 3 52 Employee 4 Paint 6/22/2008 5 53 Employee 5 Manufact. 9/26/2007 6 54 Employee 6 Manufact. 4/21/2012 2 55 Employee 7 Manufact. 1/7/2013 1 What is the PivotTable count of years of service

A. Assembly 7, Manufacturing 7, Paint 7 B. Assembly 2, Manufacturing 2, Paint 2 C. Assembly 2, Manufacturing 3, Paint 2 D. Assembly 7, Manufacturing 9, Paint 7

Cell 83 contains a formula that multiply cells B1 and B2. Which formula is in cell B3?

A. B1:B2 B. B1*B2 C. =B1*B2 D. =B1:B2

An accountant wants a spreadsheet that displays customers' requests as whole numbers (zero or greater) for the quantities ordered. How should the accountant create a message that alerts users after they input invalid data?

A. By choosing the Error Alert tab, checking the box next to "Show error alert after invalid data is entered," and typing the message in "Error message" B. By choosing the Error Alert tab, unchecking the box next to "Show error alert after invalid data is entered," and typing the message in "Error message" C. By choosing the Input Message tab, check in the box next to "Show error alert before invalid data is entered," and typing the message in "Input message" D. By choosing the Input Message tab, unchecking the box next to "Show error alert before invalid data is entered," and typing the message in "Input message"

An accountant wants a spreadsheet that limits user input to a pre-chosen set of numbers that can be shown in a drop-down list. This set of numbers is given in the cell range A1:A7. How should the accountant restrict user inputs using data validation according to this criteria?

A. By choosing the Settings tab, "Custom" for "Allow," "A1:A7'' for "Source" B. By choosing the Settings tab, "Text length" for "Allow," "A1:A7'' for "Source" C. By choosing the Settings tab, "Any value" for "Allow," "A1:A7'' for "Source" D. By choosing the Settings tab, "List" for "Allow," "A1:A7'' for "Source"

A student noted a correlation between hours of studying and test grades. The student decides to track this correlation using the CORREL function in Excel. How should this be set up?

A. By graphing hours and grades in an Excel graph, and then using the CORREL function to find the correlation coefficient of the two lines B. By dividing the grade by the number of hours, and then using the CORREL function C. By setting up hours in array A and grades in array B, and then using the CORREL function D. By dividing the number of hours by grade, and then using the CORREL function

A local golf course held a charity tournament that was open to all ages. The following information was gathered about the golfers and presented in a table: name, age, handicap, and score. Using the score column, which function will yield the number of scores returned?

A. COUNT B. AVERAGE C. TOTAL D. SUMIF

A table includes information about 1,000 adults who will be attending a sporting event. One column indicates the number of children who will be attending with each adult. If no children will be attending, the column contains an "N" for none. Which function returns the total number of children for all adults attending the event?

A. COUNT B. COUNTA C. IFSUM D. SUM

A table with information on a company's employee population includes a column with each employee's annual salary. The president of the company would like to know the total salary paid annually by the company. Which function will provide this information?

A. COUNT B. SUM C. IFSUM D. COUNTA

A table with information from a random survey includes a column indicating the age of the participants or an "N" for "no response." Which function will return the number of people surveyed who responded with their age?

A. COUNTA B. COUNT C. SUM D. IFSUM

The following data is in an Excel spreadsheet: Company Name Industry Sales Figures Company A Manufacturing $22,000,000 Company B Industrial $12,000,000 CompanyC Manufacturing $14,000,000 Which steps will display only the information for the Manufacturing Industry?

A. Click anywhere in the table, click the filter button, click the down arrow next to "Company Name," and then select "Manufacturing" B. Click anywhere in the table, click the filter button, click the down arrow next to Industry, and then select all C. Click anywhere in the table, click the filter button, click the down arrow next to "Industry," and then select "Manufacturing" D. Click anywhere in the table, click the filter button, click the down arrow next to "Company Name," and then select "Company Name" to display

A manager has a spreadsheet table with company name, stock prices, and sales dollars. What should the manager do to sort this table by name and then by sales dollars, from largest to smallest?

A. Click anywhere in the table, click the quick sort ascending icon, click the name in the sort by drop-box, then click "then by," and choose "sales dollars smallest to largest" B. Click anywhere in the table, click the sort icon, click the name in the sort by drop-box, then click "then by," and choose "sales dollars smallest to largest" C. Click anywhere in the table, click the quick sort ascending icon, click the name in the sort by drop-box, then click "then by," and choose "sales dollars largest to smallest" D. Click anywhere in the table, click the sort icon, click the name in the sort by drop-box, then click "then by," and choose "sales dollars largest to smallest"

A doctor wants to know how many patients she has seen this month and their diagnoses. The doctor's assistant puts together a report showing each patient's name in column A and diagnosis in column B. Which steps should the assistant take to obtain a list of patients who have been diagnosed with strep throat?

A. Click in the report, click the filter icon, click the down arrow for the diagnosis column, and then select all options except strep throat B. Click in the report, and then click the quick sort ascending icon C. Click in the report, click the filter icon, click the down arrow for the diagnosis column, and then click on the strep throat option D. Click the sort icon, and then click "sort by name" and then "by diagnosis"

An analyst is assigned the task of creating a PivotTable of data for a manufacturing company as follows: A B C 155 Name Department Gross Pay 156 Employee 1 Paint 85.51 157 Employee 2 Assembly 381.34 158 Employee 3 Assembly 324.26 159 Employee 4 Paint 215.02 160 Employee 5 Manufact. 312.16 161 Employee 6 Manufact. 340.26 162 Employee 7 Manufact. 344.16 Which steps need to be taken to create a PivotTable that shows gross pay by department?

A. Click on a cell in the spreadsheet containing the table of data, click on "Data," click on "PivotTable," drag cell B155 to the column labels area, drag the cell C155 to the values area B. Click on a cell in the spreadsheet containing the table of data, click on "Data," click on "PivotTable," drag the Department field to the Row Labels area, drag the Gross Pay field to the Values area C. Click on a cell in the spreadsheet containing the table of data, click on "Insert," click on "PivotTable," verify or modify selection in table/range box, click "OK," drag the Department field to the Row Labels area, drag the Gross Pay field to the Values area D. Click on a cell in the spreadsheet containing the table of data, click on "Insert," click on "PivotTable," verify or modify selection in table/range box, click "OK," drag cell B155 to the Row Labels area, drag cell C155 to the Values area

What is possible in freeze panes?

A. Freezing the column above where a person is working B. Freezing the column below where a person is working C. Freezing the row above where a person is working D.Freezing the row on both sides of where a person is working

What is a characteristic of a changeable input in a spreadsheet model?

A. It adds inputs that will remain unchanged. B. It includes any element of the process that may change. C. It appears sporadically across the spreadsheet. D. It uses industry presumptions that are hard-coded.

What is a benefit of using PivotTables?

A. It can perform two additional calculations simultaneously on the PivotTable data. B. It can perform three additional calculations simultaneously on the PivotTable data. C. It can perform without focusing on calculation options on the PivotTable data. D. It can perform additional calculation options one at a time on the PivotTable data.

What is a benefit of using the PivotTables values?

A. It defines which data will be displayed. B. It creates fewer categories by grouping together unique values. C. It defines how data will be summarized and arranged. D. It selects the item of a data field that will display.

What is a benefit of using the Values element in PivotTables?

A. It re-formats using Sum and other calculations. B. It sorts using Sum and other calculations. C. It summarizes using Sum and other calculations. D. It rearranges values using Sum and other calculations.

What is a benefit of using a slicer in a PivotTable?

A. It screens the data source for consistency. B. It calculates a grand total for all table values. C. It sorts using product and other calculations. D. It applies additional filtering of a selected data field.

Which two options are available in the Solver parameters dialog window for optimization analysis? Choose 2 answers

A. Max B. Value Of C. Less Than D. Greater Than

Which output can be used to build a model for an automobile loan approval?

A. Monthly payment B. Financial decision C. Interest rate D. Requested loan amount

An individual wants to buy a boat. To obtain the money, the individual decides to save $100 per month at a 5% interest rate. Which function will determine how much money the individual will have at the end of two years?

A. NPER B. FV C. NPS D. PMT

An individual agrees to pay a friend $250 per month for 36 months at an interest rate of 4.99%. Which function will provide the present value of these payments?

A. NPV B. PMT C. EFFECT D. VALUE

What is a possible outcome using Boolean logic?

A. Only the numbers 0 and 1 B. Only the numbers 0 and -1 C. Only numbers between 0 and 1 D. Only numbers between 0 and -1

An individual wants to purchase a golf cart from a neighbor for $3,500. The neighbor agrees to accept monthly payments of $100 at an interest rate of 3%. The buyer would like to determine how many $100 monthly payments will be needed to complete the purchase. Which function will determine how many $100 monthly payments are needed to complete the purchase?

A. PAY B. NPMT C. PMT D. NPER

An administrator wishes to display the number $3.10 in cell A4. How can this goal be met?

A. Select cell A4, select the currency category, then enter the number 3.1 B. Select the currency category, select cell A4, then enter the number 3.1 C. Click on the accounting number format icon, select cell A4, then enter the number 3.1 D. Select cell A4, click on the number format icon, then enter the number 3.1

An owner would like to purchase a building, and the bank has presented several interest rate options. A spreadsheet exists showing the amount of money available per month and displays it as an income statement complete with formulas. The owner would like to use Excel to present the interest rate options with their respective monthly payments. Which function in Excel will complete this task?

A. The Scenario Manager B. A two input data table C. Goal Seek D. A one input data table

A manager would like to see a compilation of all the different financing options available for their upcoming machine purchase and would like them presented in a concise report that displays all of the options at the same time. Which what-if tool should the analyst use to achieve this goal?

A. The Scenario Summary B. The Scenario Manager C. The Option Manager D. The Option Summary

A student has a student loan with a balance of $20,000, and payments are $333.33 each month. Another student can afford a $250 payment and would like to know the maximum that can be borrowed. Which what-if analysis tool should the student use?

A. The Scenario manager B. Goal seek C. A one input data table D. A two input data table

What is true about correlation coefficients when using the CORREL function in Excel?

A. They must be a positive number. B. They can produce an answer in percentage format. C. They must be a whole number. D. They can be a negative number.

What is a valid use of a circular reference in a spreadsheet model?

A. Two different calculations that must be completed simultaneously and depend on the other calculation's solution B. A single calculation whose formula argument directly refers to itself as part of the calculation C. A single calculation whose formula argument indirectly refers to itself as part of the calculation D. Two different calculations that must be completed simultaneously and do not depend on the other calculation's solution

An auto mechanic is interested in knowing if there is a correlation between the number of teenage drivers and car repairs. The mechanic uses the =CORREL function to determine the correlation coefficient of .234. The mechanic then uses the =FORECAST function to predict the number of repairs based on the number of teenage drivers. How reliable is the auto mechanic's forecast?

A. Unreliable, due to a weak correlation B. Unreliable, due to a strong correlation C. Reliable, due to a strong correlation D. Reliable, due to a moderate correlation E. Unreliable, due to a moderate correlation F. Reliable, due to a weak correlation

A student wants to have text appear in Excel as it would in a business document, with all of the text starting on the left-hand side. Which group on the homepage should be used to accomplish this goal?

A. View B. Page layout C. Text justification D. Alignment

A person is interested in knowing if there is a correlation between the sky color and their desire to mow the lawn. This person uses the =CORREL function in Excel to determine the correlation. The correlation coefficient is .274. What type of relationship exists between the sky color and desire to mow the lawn?

A. weak B. Moderate C.Strong D. No relationship

A list of monthly sales is displayed in multiple worksheets, one tab for each month. Each range of data is named with the three-character month abbreviation. Management wants to identify the largest sale for the year. Which formula will display this information?

A.=MAX(JAN+FEB+MAR+APR+MAY +JUN+JUL +AUG+SEP+OCT +NOV+DEC) B.=MIN(JAN+FEB+MAR+APR+MAY+JUN+JUL+AUG+SEP+OCT+NOV+DEC) C.=MAX(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC) D.=MIN(JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC)

Match the category with its type of function. Answer options may be used more than once or not at all.

Auto sum COUNT, NUMBER, MAX Logical AND, NOT, OR Date and Time WEEKNUM, WEEKDAY Financial DURATION, COUPDAYS, EFFECT

Which two chart types can be used to present a president with the total sales of a company over the last four years?

Choose 2 answers A. A column chart B. A stacked column chart C. A line chart D. A pie chart

Which two options are possible under "Review" on the Excel ribbon?

Choose 2 answers A. Format B. Graphs and charts C. Track changes D. Protect sheet

A client would like to change the title of the chart from "Electronic Store Sales 2013" to "Accessories Sales for 2013."

How can this change be made? A. By clicking on the chart, then "Chart Tools," " Design," "Quick Layout," "Chart Labels" B. By double-clicking on the title to select it, then choosing border styles to edit the text C. By clicking on the chart, then "Chart Tools," " Design," "Add Chart Element," "Axis Titles" D. By clicking on the title to select it, then clicking again on the title to edit the text

A company's overall financial picture for 2013 is shown below. The analyst has added bullet points on the right shewing what each color reflects.

What is the name or this graphing feature? A. Data labels B. An X Axis title C. A Y Axis title D. A legend

A company's overall picture for 2013 is shown below. Management asked the analyst to add the values of each data element into the chart.

Which charting feature accomplishes this request? A. One input data B. The legend C. Data labels D. The axis titles

A company's sales for 2013 are shown below. Management would like the analyst to label each color to display what the color means.

Which charting feature did the analyst use to complete this request? A. A legend B. A pie chart C. A data label D. A chart title E. An axis title

A company's sales for 2013 are displayed below. Management would like the analyst to show the data in the chart.

Which charting feature should the analyst use to complete this request? A. The horizontal axis B. The vertical axis C. A legend D. A data label E. A data table

A client would like to include the percentage data points on the pie chart, as shown below.

Which feature should be used? A. A legend series B. A series values C. A data series D. A series name

A student created a summary of several scenarios in Excel showing tuition costs at different universities. The student wants to take 10 credit hours, but her income level varies because of her job.

Which scenario maximizes the student's surplus spending money for school? A. Scenario 1 B. Scenario 2 C. Scenario 3 D. Scenario 4 E. Scenario 5

The data series for mobile phones incorrectly pulls from BS:MS.

Which series will reflect the correct mobile phones data? A. B6:M6 B. B8:M8 C. B7:M7 D. B6:M8

The monthly financial information of a person interested in buying a house is reflected in this template used by a banker to determine loan eligibility:

Which what-if analysis tool did the banker use? A. A two input data table B. A one input data table C. The Scenario Manager D. Goal Seek