Exam #2

The Two-Input Data Table: A. Can have two inputs. B. Can have two formulas. C. Neither of the above

C. Neither of the above

You can change an individual cell in the Data Table matrix of values. A. True. B. False. C. Maybe.

C. Maybe.

The Scenario Summary report: A. Tracks the values cells you identify as critical in your model take on under all your defined scenarios. B. Describes all the scenario items you've designated and the values you've assigned those items. C. Neither of the above.

A. Tracks the values cells you identify as critical in your model take on under all your defined scenarios.

When you save a worksheet, any Scenarios you've defined for that sheet are saved automatically. A. True. B. False. C. Only if you turn on the Automatic Save option in the Scenarios dialog box.

A. True.

Enter Solver constraints in the: A. " Solver Parameters" dialog in the "Subject to the Constraints" text box. B. "Add Constraint" dialog, after clicking the Add button in the "Solver Parameters" dialog. C. Solver "Options" dialog, available by clicking the Options button in the "Solver Parameters" dialog.

B. "Add Constraint" dialog, after clicking the Add button in the "Solver Parameters" dialog.

An Excel Scenario is: A. A worksheet to which Solver analysis can be applied. B. A set of values that Excel saves and can substitute on command in a worksheet model. C. A particular spreadsheet layout that can be saved and recalled.

B. A set of values that Excel saves and can substitute on command in a worksheet model

. Solver is: A. A third-party Excel add-in that must be purchased separately from Excel. B. An Excel add-in that's part of the Excel software package. C. An Excel function, available from the Functions dialog box.

B. An Excel add-in that's part of the Excel software package.

With Goal Seek: A. You specify the result you want in a cell and Excel varies the underlying formula until that result is achieved. B. Excel varies the value in a worksheet cell you specify until the formula that's dependent on that cell returns the result you want. C. Neither of the above.

B. Excel varies the value in a worksheet cell you specify until the formula that's dependent on that cell returns the result

You can extend the input values for a Data Table and the Data Table will extend itself automatically to include those values. A. True. B. False C. You must hit F9 to force calculation.

B. False

The Goal Seek command is available on the: A. Insert menu. B. Tools menu. C. Window menu.

B. Tools menu.

Solver is: A. A third-party add-in from Solver Unlimited, Inc. B. An MS Office application. C. An MS Office Excel add-in that comes with Excel.

C. An MS Office Excel add-in that comes with Excel.

A One-Input Data Table is arranged: A. With the inputs down the left hand column and the formula(s) across the top row. B. With the inputs across the top row and the formula(s) down the left-hand column. C. Either A or B.

C. Either A or B.

After Solver finds a solution, you have the option to: A. Keep Solver's solution. B. Restore the model's original values. C. Either A. or B.

C. Either A. or B.

Excel's Data Table: A. Provide a shortcut for generating multiple views for a model in a single operation. B. Is a powerful sensitivity analysis tool that shows how changing certain values in a model's formulas might affect critical elements of the model. C. Provides a way to view and compare the results of many model variations together on a single worksheet. D. All of the above.

C. Provides a way to view and compare the results of many model variations together on a single worksheet.

You can change the input values for a Data Table and the Data Table will recalculate automatically. A. True. B. False. C. You must hit F9 to force calculation.

C. You must hit F9 to force calculation.

The Data Table can handle: A. A single input. B. Two inputs. C. Any number of inputs. D. A or B. E. A or C.

D. A or B.

You can ask Solver to find: A. The maximum value possible in a cell. B. The minimum value possible in a cell. C. A particular value in a cell. D. All the above.

D. All the above.

Which of the following represents the correct syntax (arguments) for the PMT function? a) =PMT(rate, nper, pv, [fv], [type]) b) =PMT(nper,rate, pv, [fv], [type]) c) =PMT(rate, nper, [fv], [type]) d) All of the options above are correct.

a) =PMT(rate, nper, pv, [fv], [type])

Study the screenshot above. Several Conditional Formatting rules have been applied to this worksheet. How can you ascertain which rules have been applied and where? a) Click on the Conditional Formatting button in the Styles group and select Manage Rules. b) Right click in an area of the worksheet that contains conditional formatting and select Format Cells. c) Click on the Conditional Formatting button in the Styles group and select More Rules. d) None of the options listed above are correct.

a) Click on the Conditional Formatting button in the Styles group and select Manage Rules.

After inserting a Pivot Table, the Pivot Table Field List does not automatically appear. How can you activate this area? a) Click on the Field List button in the Show group, under Options in the PivotTable Tools contextual tab. b) Click on the Insert Pivot Table button and select PivotTable Fields List. c) Go to the Backstage View and in the Options dialogue box, click on the PivotTable tab. Check the box for PivotTable Fields List. d) All of the options listed above are correct.

a) Click on the Field List button in the Show group, under Options in the PivotTable Tools contextual tab.

Adam is searching for the Solver function but cannot find it anywhere, not even under the Data tab on the Ribbon. Where could he find it? a) If it is not on the Ribbon, it has not been activated. He needs to activate it under Add-Ins in the Excel Options dialog box. b) It is not under the Data tab, it is a formula. He needs to look under the Formula tab on the Ribbon in the Add-Ins group. c) It is not a function that is usually displayed on the Ribbon. He can add it to the Ribbon as a macro function. d) Solver is a separate program. He needs to launch it from his operating system's Start menu.

a) If it is not on the Ribbon, it has not been activated. He needs to activate it under Add-Ins in the Excel Options dialog box.

Study the screenshot above. When trying to calculate the amount he can loan. Vladimir keeps getting a negative number for this (see highlighted cell). What do you think is the problem? a) The PMT value has not been entered as a negative amount. It should be -$800. b) The PV formula entered in the formula bar does not reference the correct cells. c) Vladimir cannot afford the loan, this is why it is showing as a negative. d) None of the options listed above are correct.

a) The PMT value has not been entered as a negative amount. It should be -$800.

Study the screenshot above. The tabs circled in yellow are not ordinarily visible. What do you need to do in order to access these tabs and the functions they contain? a) These are called contextual tabs. To display them click anywhere within a Pivot Table. b) These are called contextual tabs. To display them click on the Pivot Table tab on the Ribbon. c) These are called PivotTable Tools tabs. To display them tick the PivotTable Tools tick box in the Tables group. d) These are called PivotTable Tools tabs. To display them hover your mouse above the normal tabs on the Ribbon.

a) These are called contextual tabs. To display them click anywhere within a Pivot Table.

Study the screenshot above. Under which tab and in which function group will you find the option to insert a Pivot Table? a) Under the Insert tab in the Tables group. b) Under the Formulas tab in the Data Analysis group. c) In the Data group in the Pivot Tables group. d) In the Data group in the Tables group.

a) Under the Insert tab in the Tables group.

What is the maximum number of cells you can select for the Set Objective variable in the Solver Parameters dialog box? a) You can select only a single cell for the Set Objective variable. b) You can select up to three cells for the Set Objective variable. c) You can select any number of cells for the Set Objective variable. d) You can select as many active cells as a worksheet contains for the Set Objective variable.

a) You can select only a single cell for the Set Objective variable.

Study the screenshot above. Which of the following is the correct formula for calculating the monthly payments in the highlighted cell B2? a) =PMT(B5,B4,B3) b) =PMT(B3,B4,B5) c) =PV(B3,B4,B5) d) =MPMT(B5,B3,B2)

b) =PMT(B3,B4,B5)

Study the screenshot above. Which option on the Conditional Formatting menu should you click to find the Text That Contains... option? a) Click on the Data Bars option. b) Click on the Highlight Cells Rules option. c) Click on the Top/Bottom Rules option. d) You can click on any of the options in the Conditional Formatting menu to access this option.

b) Click on the Highlight Cells Rules option.

What is the first step for creating a Pivot Table? a) Clicking on the Insert Tab and inserting a Pivot Table. b) Create or select data that needs to be analyzed. c) Deciding on which fields (column and row labels) to use to analyze the data. d) None of the above options are correct.

b) Create or select data that needs to be analyzed.

How do you change the range of cells that a Conditional Formatting rule applies to? a) You cannot change the range, you have to clear the rule first and then reapply it. b) In the Conditional Formatting Rules Manager dialogue box, edit the cell range for the rule in the Applies To section. c) Select the cell range and reapply the rule. This will automatically delete the previous rule and create a new one. d) Right click the cell range and select Update Conditional Formatting Rule from the menu list.

b) In the Conditional Formatting Rules Manager dialogue box, edit the cell range for the rule in the Applies To section.

What does the PMT function in Excel calculate? a) It calculates a monthly salary based on interest rates, years of employment and bonuses. b) It calculates the payment of a loan based on constant payments and a constant interest rate. c) It cleans up data that has been incorrectly entered. d) None of the options listed above are correct.

b) It calculates the payment of a loan based on constant payments and a constant interest rate.

Study the screenshot above then recreate the table in your own version of Excel. Carlos cannot afford to pay more than $500 per month on this loan. If the number of payments is extended to 48 months, would he be able to afford the payments? a) Yes, he will be able to afford it. His monthly payment will be $483.15 based on the variables given. b) No, he will not be able to afford it. His monthly payment will be $683.16 based on the variables given. c) Yes, absolutely. His monthly payment will be exactly $500 based on the variables given. d) It is impossible to calculate based on the variables given in this example.

b) No, he will not be able to afford it. His monthly payment will be $683.16 based on the variables given.

Study the screenshot above question 5. How can you remove conditional formatting, such as the colored font and blue, pink and green shading, from this worksheet? a) Select the worksheet or range of cells, right click and select Format Cells from the menu list. From here you can remove formatting. b) Select the worksheet or range of cells, click on Conditional Formatting and select the Clear Rules option. c) Select the worksheet or range of cells, click on Delete in the Cells group and select Delete Formatting Only. d) All of the options listed above are correct.

b) Select the worksheet or range of cells, click on Conditional Formatting and select the Clear Rules option.

When you use Solver, there are several Solving method options you can choose from, depending on your calculation needs. Which of the following is NOT an available Solving Method? a) GRG Non-Linear b) Sum-Product c) Simplex LP d) Evolutionary

b) Sum-Product

Study the screenshot above. What function does this represent? Choose the most correct option. a) This is a Pivot Table Chart. b) This is a Pivot Table Filter. c) This is a Pivot Table Report. d) This is a Pivot Table.

b) This is a Pivot Table Filter.

Where is the Goal Seek function located? a) Under the Goal tab on the Ribbon. b) Under the Data tab on the Ribbon. c) Under the Formulas tab on the Ribbon. d) Under the Insert tab on the Ribbon.

b) Under the Data tab on the Ribbon.

Which of the following scenarios will yield INACCURATE results long-term when using the PMT function. a) Using an unequal number of months for the NPER. b) When the interest rate changes every month. c) When the monthly payment is the same every month until the loan is repaid. d) When the interest rate is the same every month for the whole period.

b) When the interest rate changes every month.

Study the screenshot above. Continuing from Casey's budget pictured above question 3, what number should be entered in the Value Of: box circled in yellow in the screenshot above? a) 3960 b) 10 c) 2850 d) 1

c) 2850

Study the screenshot above. Which of the following formulas, if entered into the highlighted cell (B4), will calculate the total number of payments or months that the loan will be paid off over? a) =PMT(B2,B3,B5) b) =PV(B2,B3,B5) c) =NPER(B3,B2,B5) d) =PMT(B3, B2,B5)

c) =NPER(B3,B2,B5)

What is a Pivot Table? a) A table containing data that is organized horizontally. b) A table used to calculate financial pivot values. c) A tool used to summarize data. d) A table containing only black, grey and white formatting.

c) A tool used to summarize data.

Study the screenshot above paying careful attention to the PMT formula in the formula bar. In order to create a data table, which cell reference(s) should be inserted in the boxes in the Data Table dialog box (circled in yellow)? a) Cell B3 in the Row input cell box and A3 in the Column input cell box. b) Cell E3 in the Row input cell box and E2 in the Column input cell box. c) Cell E3 in the Column input cell box. d) Cell B3 in the Row input cell box.

c) Cell E3 in the Column input cell box.

Study the screenshot above. Which analytical tool can be used to quickly create this list of values for monthly payments based on the variables provided? a) The Solver Utility. b) The Goal Seek function. c) A two-variable data table. d) A one-variable data table.

d) A one-variable data table.

The Sum function is applied to values in a Pivot Table by default. How can I change this so that values are automatically counted and not summed? a) Insert the COUNT formula (=Count()) into the PivotTable. b) Change the format of the values in the Pivot Table to General Numbers. c) In the Calculations group, change the Summarize Values By to Count. d) All of the options listed above are correct.

c) In the Calculations group, change the Summarize Values By to Count.

Where is a Pivot Table inserted? a) Below the last row of data in your worksheet. b) Next to the last column of data in your worksheet. c) It depends on whether you select to insert the Pivot Table in the current worksheet or a new worksheet and where your cursor is. d) Above the first row of data in your worksheet.

c) It depends on whether you select to insert the Pivot Table in the current worksheet or a new worksheet and where your cursor is.

Study the screenshot above. After working out her budget, Casey realizes that her expenses at $3,960 are far above her income of $2850. She wants to modify her budget by reducing all her non-essential budget items. Which analytical tool could she use to achieve this? a) Goal Seek will allow her to change her total expenses by modifying all the cells she specifies which contain non-essential items. b) Data tables will allow her to change all the amounts to fit in with her income. c) She could use Solver to change her total expenses by modifying all the cells she specifies which contain non-essential items. d) She could use the PMT function to calculate her monthly budget payments and exclude non-essential items.

c) She could use Solver to change her total expenses by modifying all the cells she specifies which contain non-essential items.

Study the screenshot above. After doing her budget, Lucy realizes that she cannot pay more than $3,850 per month (Monthly Payment) towards a new apartment. Which ANALYTICAL function could she utilize to calculate her new loan amount based on the screenshot above? a) She could use the Solver function. b) She could use the What-If Analysis function. c) She could use the Goal Seek function. d) She could use the Data Table function.

c) She could use the Goal Seek function

Where can you find the option to set Conditional Formatting rules? a) Under the Insert tab on the Ribbon in the Formatting group. b) Under the Page Layout tab on the Ribbon in the Styles group. c) Under the Home tab on the Ribbon in the Styles group. d) Under the Formulas tab on the Ribbon in the Formatting group.

c) Under the Home tab on the Ribbon in the Styles group.

Immediately after applying a conditional formatting rule, you realize it is a mistake. How do you fix it? Choose the best answer: a) You have to clear the rule you created by going to the Conditional Formatting Rules Manager dialogue box and deleting the rule. b) You have to clear all rules that have been applied to the worksheet or selected range, by clicking on the Clear Rules button. c) You can simply press the Undo button or use the shortcut Ctrl+Z to undo the last rule created. d) You can right click the area where the conditional formatting rule has been applied and select Clear Rule from the menu list.

c) You can simply press the Undo button or use the shortcut Ctrl+Z to undo the last rule created.

Study the screenshot above. Sanjev wishes to purchase a plot of land. His bank's annual fixed interest rate is 28%. What is the value he needs to enter in cell B3 (the highlighted cell) in order to calculate his monthly payments (PMT)? a) 28% b) 2.8% c) 1.2% d) 2.3%

d) 2.3%

The Data Table can handle: a) single input. b) Two inputs. c) Any number of inputs. d) A or B. e) A or C.

d) A or B.

How do you remove or delete a Pivot Table? a) Select the Pivot Table and press Clear All in the Actions group under the Options tab on the PivotTable Tools contextual tabs. b) Delete the worksheet that contains the Pivot Table. c) Select the columns and/or rows containing the Pivot Table and press Delete on your keyboard. d) All of the options listed above are correct.

d) All of the options listed above are correct.

The Conditional Formatting function in Excel allows you to: a) Automatically apply formatting based on specific rules or conditions being met. b) Automatically apply highlighting to selected cell ranges based on specific rules or conditions being met. c) Automatically change the font of selected cell ranges based on specific rules or conditions being met. d) All of the options listed above are correct.

d) All of the options listed above are correct.

Top/Bottom Rules allow you to apply conditional formatting to cells that fall within the top or bottom numbers or percentile. How many items will it allow you to include in the rule? a) The top or bottom 10 items or top or bottom 10% only. b) The top 10 items and top 10% only. c) The top or bottom 100 items only. d) Any logical number of items or percentages in top or bottom value frames.

d) Any logical number of items or percentages in top or bottom value frames.

Study the screenshot above. Which of the formulas entered below will provide the same results as that of the PMT function above when entered into single cell on a separate worksheet (i.e. not in the table above)? a) =PMT(0.24/12,5*12,5500) b) =PMT(0.02,60,5500) c) Neither of the above options is correct. d) Both of the above options are correct.

d) Both of the above options are correct.

Which of the following is NOT a box in the PivotTable Fields List pane? a) Filters. b) Columns c) Values. d) Formulas

d) Formulas

Which of the following is NOT a box in the PivotTable Fields List? a) Column Labels b) Report Filter c) Values d) Formulas

d) Formulas

Sally wants to buy a car but is unsure how much she afford to make a loan for. The maximum monthly payment she can afford is $780. Her bank's interest rate is 14% annually (fixed) if she pays off the loan over 5 years. What is the maximum amount she can afford? Use Excel to calculate this amount. a) It is impossible to calculate based on the variables given in this question. b) She can afford to make a loan for $53,352.05. c) She can afford to make a loan for $46,800.03. d) She can afford to make a loan for $33,522.07.

d) She can afford to make a loan for $33,522.07.

Where will you find the Data Table function? a) Under the Insert tab, in the Tables group, select Table. b) Under the Insert tab, in the Tables group, select Pivot Table. c) Under the Data tab, in the Analysis group, select Data Table. d) Under the Data tab, in the Data Tools group, select What-If-Analysis and Data Table.

d) Under the Data tab, in the Data Tools group, select What-If-Analysis and Data Table.

Which of the following functions can you NOT perform using the Conditional Formatting option? a) Insert icons that visually display the ranking or rating of values in a selected range. b) Apply conditional formatting to dates that fall within specified date ranges. c) Use conditional formatting to highlight any duplicate values or data in a worksheet. d) Use conditional formatting to dynamically underline in red and green any language or spelling errors on a worksheet

d) Use conditional formatting to dynamically underline in red and green any language or spelling errors on a worksheet

Which of the following functions can you NOT perform using the Conditional Formatting option? a) Insert icons that visually display the ranking or rating of values in a selected range. b) Apply conditional formatting to dates that fall within specified date ranges. c) Use conditional formatting to highlight any duplicate values or data in a worksheet. d) Use conditional formatting to dynamically underline in red and green any language or spelling errors on a worksheet.

d) Use conditional formatting to dynamically underline in red and green any language or spelling errors on a worksheet.

Which of the following formatting options can you set for Conditional Formatting rules? a) Light Red Fill With Dark Red Text, Yellow Fill With Dark Yellow Text and Green Fill With Dark Green Text. b) Light Red Fill With Dark Red Text. c) Light Red Fill, Light Yellow Fill and Light Green Fill. d) You can apply any of the formatting detailed in the options above as well as number, border, shading and font formatting.

d) You can apply any of the formatting detailed in the options above as well as number, border, shading and font formatting.