Data Analytics Exam 1

The following function was entered into cell A10 on an Excel worksheet: =EOMONTH(B2,1) If the date 9/16/2020 is entered into cell B2, which of the following will be the output displayed in cell A10 after proper formatting is applied?

10/31/2020

Observe the Excel worksheet below. After the ENTER key is tapped, if cell C2 is copied and pasted into cell E3, which of the following will appear if cell E3 is double clicked?

=$B$2 (Stays the same)

A formula needs to be entered into cell C2 on an Excel worksheet that is being used as a template. The goal of the formula is to divide the value in cell A2 by the value in cell B2. The number entered into cell A2 is 20,000 and the number entered into cell B2 is 100. Which of the following shows how this formula should be entered into cell C2?

=A2/B2

The following loan data is entered into an Excel worksheet: Loan principal, cell B5, $150,000 APR, cell B6, 5% Terms, cell B7, 30 years Assume the loan payments are made at the end of each period. Which of the following PMT functions should be used to calculate the annual payments for this loan?

=PMT(B6,B7,-B5)

Which of the following is true with respect to Treemap charts?

A Treemap chart can be created with a dataset that only contains two columns. When creating a Treemap chart with three or more columns, the dataset must be sorted in a logical sequence beginning with the left most column.

Which of the following is true with respect to the PV function.

A minus sign must precede a cell reference used to define the pmt and [fv] arguments. The minus sign is not required for any of the other arguments.

Consider the following formula: =A9+B7*C9/A4^D10 What math computation will be conducted first in this formula?

A4^D10

Consider the following formula: =(A9+B7)*C9/A4^D10 What math computation will be conducted first in this formula?

A9+B7

Which of the following is true when formatting the labels in the plot area of a Treemap chart. Select all that apply.

After all labels are activated in the plot area, clicking any label will activate only that label and deactivate all other labels. The available formatting commands in the Home tab of the Ribbon can be used. All labels will be activated when one label is clicked the first time.

When entering settings into the Goal Seek dialog box, the cell reference used in a formula or function would be entered into which of the following?

By changing cell

Which of the following items can be accomplished using the Value Field Settings dialog box? Select all that apply.

Change the name of a field. Show values in a field as a percent of total column or row. Count instead of sum numeric data that is added to the VALUES area of the PivotTable. Apply number formats to a field such as percentage or currency formats.

How can you change the height and width of an Excel Chart? Select all that apply.

Click anywhere on the chart to activate it, then click and drag any of the sizing handles around the perimeter of the chart. Click anywhere on the chart to activate it, hold the CTRL key, hold the SHIFT key, and tap the ARROW keys on your keyboard.

After clicking the Sales Dollars field in the PivotTable shown below, which of the following will open the Value Field Settings dialog box?

Click the Field Settings button in the PivotTable Analyze tab of the Ribbon.

Which of the following can be used to launch Excel? Select all that apply.

Click the Start button, scroll down to programs that start with the letter E, and select Excel. Double click the Excel icon on the desktop. Click the Excel icon pinned to the start menu.

Consider the PivotTable shown below. The goal is to sort the data so the month with the highest sales dollars is listed first in column A. Identify the steps listed that would NOT be used to accomplish this goal. Select all steps that would NOT be used.

Click the down arrow in Row 1 Column B for the Year field. Select Sort A to Z from the Row Labels dropdown menu. Click the Sort button in the Data tab of the Ribbon. Click More Options in the Sort dialog box.

If an Excel worksheet contains many columns of both negative and positive values, which data visualization technique would be most effective in identifying the highest and lowest values in each column?

Color Scales

Which of the following are true when preparing a worksheet to be printed? Select all that apply.

Commands to prepare a worksheet for printing are found in the Page Layout tab of the Ribbon. Print settings applied to one worksheet will not be applied to other worksheets in a workbook. A worksheet can be scaled to fit on one piece of paper. A worksheet can be arranged to be printed in either landscape or portrait orientation.

Which of the following can be use to duplicate data on a worksheet? Select all that apply.

Copy and Paste Commands CTRL C and CTRL V ALT H C C and ALT H V P Auto Fill

Which of the following is true with regards to the Accounting Number Format? Select all that apply.

Currency symbols are left justified in the cell. The dollar sign $ appears on the command button. It will format values with two decimal places for cents.

Assume a dataset is being sorted using a column that contains the months of the year. If the data is going to be sorted in calendar year order (January to December), what should be selected in the Order dropdown box in the Sort dialog box.

Custom List

Which of the following are components of the DATA Analytics Framework? Select all that apply.

Decision, Acquisition, Time, Analysis

What function key is used to open the Excel help window?

F1

What function key is used to open the Save As dialog box?

F12

If a worksheet that contains data is deleted, the Undo command can be used to restore the deleted worksheet if necessary.

False

Which of the following will help when managing and analyzing a large dataset that has many columns and/or rows?

Freeze Panes

Consider the data shown in the figure below. A PivotTable will be used to analyze this data so the total sales dollars for each month can be compared for the year 2017 and 2018. Review the steps listed below. Select each step that would be used to accomplish this goal.

Highlight all of the data that will be used to create the PivotTable including the column headings in row 1. Click the Insert tab of the Ribbon Click the PivotTable button. Click and drag the Year field into the COLUMNS area of the PivotTable. Click and drag the Month field into the ROWS area of the PivotTable. Click and drag the Total Sales Dollars field into the VALUES area of the PivotTable.

Which of the following statements is a valid reason for carefully auditing the outputs of PivotTables and PivotCharts?

If new data is added to a worksheet after PivotTables and PivotCharts have already been created, this new data will not be included in the outputs of either analytics tool.

The following formula was entered into an Excel worksheet: =B5*12 Which of the following statements are true with respect to this formula?

If the number in cell B5 is changed, the output of the formula will change. The number entered into cell B5 will be multiplied by 12. B5 is a cell reference.

Which tab of the Ribbon should be used (according to the textbook) when adding a header or footer to an Excel worksheet?

Insert

When entering numeric data into an Excel worksheet, you should omit any symbols such as commas or dollar signs because:

It slows down the data entry process.

The worksheet shown in the figure below contains two years of sales that will be used to create a PivotChart. The goal is to create a column chart that shows the total sales dollars by year for each month. Given this goal, what would be the best chart area to place the Year field?

Legend

Which of the following functions can be used to identify the highest value in a range of cells?

MAX

Which of the following functions can be used to identify the lowest value in a range of cells?

MIN

An Excel worksheet is being used to record employee data for a small company. The mailing address for each employee will be entered into the first five cells of each row beginning in column A. The range A1:E25 has been highlighted. Which of the following is the most appropriate command to use for this project?

Merge Across

Which of the following is true when working with a dataset that was provided by another person? Select all that apply.

Obtain clear definitions and identify the purpose for all columns of data in the dataset. Conduct a Data Audit before working with the data.

The following was entered into an Excel worksheet: =SUM(A2,A10) Based on this entry, which of the following is true?

Only the values entered into cells A2 and A10 will be summed.

Which of the following would allow you to change the title of a chart. Select all that apply.

Place the mouse pointer over the chart title, right click, and select Edit Text from the Right-Click dropdown menu. Click the chart title and begin typing to replace the existing chart title. Click the chart title twice and edit the existing chart title.

Which of the following can be used as a data internal control that prevents a user from changing or deleting formulas and functions while using a worksheet?

Protect Sheet

The data shown in the figure will be used to create a PivotTable to summarize the Total Sales Dollars by month. The project goal is to identify the month and year with the highest sales dollars. The Month field should be dragged into which of the following report areas to accomplish this goal?

Rows

Which of the following commands can be used to save an Excel workbook to the cloud using OneDrive? (Note: this question refers to the computer application (not website) version of Excel.)

Save As

When entering settings into the Goal Seek dialog box, a cell that contains a formula or function would be entered into which of the following?

Set cell

Which of the following can be used to access commonly used Excel commands? Select all that apply.

The Right Click Menu and the Quick Access Toolbar

Which of the following are true if you are typing words into a cell that extend beyond the width of the column? Select all that apply.

The Wrap Text command can be used to stack any truncated words on multiple lines in a cell. The column width can be increased until all of the words in the entry fit into the cell. Words that extend beyond the width of the column will be truncated if there is data in the cell to the right.

The following FV function was entered into an Excel worksheet to calculate the value of an investment: =FV(B3,B4,B5,B5,0) The following data was entered into an Excel worksheet: Rate of Return, cell B3, 5.5% Investment duration in years, cell B4, 15 years Amount invested at the beginning of each year, cell B5, $3,200 Given this information, which of the following are flaws in the FV function that was entered into the worksheet? Select all that apply.

The [pv] argument should be zero. The [type] argument should be 1. A minus sign must precede the cell location used to define the pmt argument.

The following PMT function was entered into an Excel worksheet: =PMT(B2/12,B3,B4,-B5,1) The purpose of this function is to calculate the monthly payments for a loan, where you have annualized percentage rate, the repayment years, total asset cost, down payment, and loan principal in appropriate cells in your spreadsheet. Payments are required at the end of each month. Which of the following identifies a flaw with this function. Select all that apply.

The [type] argument should be zero. The nper argument must be multiplied by 12. The minus sign should precede cell B4 in the pv argument. The [fv] argument should be zero.

The following function was entered into a cell on an Excel worksheet: =TEXT(B2,mmmm) Which of the following explains why the output of this function would be the #NAME? error code?

The code entered into the format_text argument is not enclosed in quotations.

Which of the following is true with respect to column headings and creating PivotTables?

The column headings will become the field names which are used to create and arrange a PivotTable.

A home buyer is purchasing a home that costs $250,000. Cash in the amount of $50,000 was paid. A loan will be used to pay for the remaining cost of the house. Based on this information, which of the following is true?

The down payment is $50,000

If the [Type] argument of the PMT function is defined with the number 1, which of the following is true?

The function will assume that payments will be made at the beginning of each period.

The following was entered into cell A11 on an Excel worksheet: =AVERAGE(A2:A10) Which of the following is true if cell A11 is copied and pasted into cell C11?

The function will be modified to: =AVERAGE(C2:C10).

When the mouse pointer changes to cross arrows when dragged to the edge of a highlighted range of cells, it indicates that:

The highlighted range can be moved by clicking and dragging.

Select all items that are true with respect to formatting worksheet tabs.

The label on a worksheet tab can be changed by double clicking it. The color of a worksheet tab can be changed by selecting Tab Color after clicking the Format button in the Home tab of the Ribbon and then selecting a color from the color palette. The label on a worksheet tab can be changed by double clicking it. The color of a worksheet tab can be changed by selecting Tab Color after clicking the Format button in the Home tab of the Ribbon and then selecting a color from the color palette.

In the image below, why has the mouse pointer changed to a black plus sign?

The mouse pointer has been placed over the Fill Handle.

Consider the following formula that was entered into an Excel worksheet: =A4/12 The use of the number 12 in this formula is valid for which of the following reasons?

The purpose of the formula is to calculate the value entered into cell A4 in terms of months or per month.

Which of the following are considered limitations of data analytics? Select all that apply.

The results of a data analytics project and corresponding decisions will depend on the quality of the data. It can be difficult to predict what data will become in the future.

An Excel workbook contains three worksheets: Data, Report, and Reference. The following was entered into cell A5 in the Report worksheet: =Data!C2 Which of the following is true with regards to this entry.

The value entered into cell C2 on the Data worksheet will appear in cell A5 on the Report worksheet.

Which of the following are true with respect to the outputs shown in column A of the image below? ####

The width of column A is too narrow for the data that is entered into the range A1:A4. There are numbers entered into the cells in the range A1:A4.

Which of the following is considered a hierarchy chart?

Treemap

A Treemap chart will be distorted if the dataset is not sorted beginning with the left most column.

True

Which of the following is a valid reason for using the Paste Formulas command?

When it is necessary to only paste a formula and not any formats that were applied to the cell that was copied.

Which of the following is true with regards to the Protect Sheet command? Select all that apply.

Worksheet protection can be configured such that a user can change a target range of cells on a worksheet. A password can be assigned in order to turn off worksheet protection. To protect a worksheet, click the Protect Sheet button in the Review tab of the Ribbon.

Which of the following will help make the most efficient use of horizontal space on a worksheet during the design process by maximizing the number of visible columns on one screen?

Wrap Text

The residual value of a lease would be used to define which of the following arguments of the PMT function to calculate annual lease payments?

[fv]

Which of the following can be associated with a cell range? Select all that apply.

a contiguous block of cells highlighted on a worksheet and A5:E25

The FV function is being used to determine the value of a $45,000 investment that earns a rate of return of 3.5% per year for 30 years. The 30 year time frame for this investment will be used to define which of the following arguments?

nper

The terms of a lease would be used to define which of the following arguments of the PMT function to calculate annual lease payments?

nper