CISM Practice Exam 2

Delete comment

Review Tab - Delete Comment

Ungroup the worksheets.

Click the Estimates worksheet tab.

Create a new workbook based on the Home Loan Comparison template.

Click the File tab to open Backstage. Click New. Click the Home Loan Comparison template, and click Create.

Install the Solver add-in.

Click the File tab. Click Options. Click Add-Ins. Click Go... Click the Solver Add-in check box. Click OK.

Save this workbook as a template.

Click the File tab. Click Save As. Click the Browse button. In the Save As dialog, click the Save as type arrow and select Excel Template. Click Save.

Use the Document Inspector to remove all personal information and hidden information from this workbook. Close the Document Inspector when you are through.

Click the File tab. Click the Check for Issues button, and select Inspect Document. Click Yes. Click Inspect. Click the Remove All button next to Document Properties and Personal Information. Click Close.

Open the Compatibility Checker to check if this workbook contains elements that are not compatible with earlier versions of Excel. Close the Compatibility Checker without making any changes.

Click the File tab. Click the Check for Issues button, and then click Check Compatibility. Click OK.

Mark the workbook as final so it opens as read-only to discourage editing.

Click the File tab. Click the Protect Workbook button, and select Mark as Final. Click OK.

Encrypt the workbook with this password: password

Click the File tab. Click the Protect Workbook button. Click Encrypt with Password. Type password. Click OK. Type password in the Confirm Password dialog. Click OK.

Use Consolidate to enter values in the selected cells by summing data from cells B3:D8 in the worksheets Uptown, Downtown, and City Center. Do not include links to the source data.

On the Data tab, in the Data Tools group, click the Consolidate button. Click in the Reference box and then click the Uptown worksheet tab. Click and drag to select B3:D8. Click the Add button. Click the Downtown worksheet tab. Click Add. Click the City Center worksheet tab. Click Add. Click OK.

Add Only use codes from the list as an error alert to the validation rules for the selected cells. Do not include a title.

On the Data tab, in the Data Tools group, click the Data Validation button. Click the Error Alert tab. Type: Only use codes from the list Click OK.

Add comment to selected cell

Review Tab - New Comment

Data that violate the validation rules in this worksheet have been circled. Clear the validation circles.

On the Data tab, in the Data Tools group, click the Data Validation button arrow. Click Clear Validation Circles.

Add Click the arrow as an input message to the validation rules for the selected cells. Do not include a title.

On the Data tab, in the Data Tools group, click the Data Validation button. Click the Input Message tab. Type: Click the arrow Click OK.

Apply data validation rules to the selected cells to allow only decimal numbers less than 5.00. Allow blank cells.

On the Data tab, in the Data Tools group, click the Data Validation button. Expand the Allow list and select Decimal. Expand the Data list and select less than. Type 5.00 in the Maximum box. Click OK.

Use data validation to display an in-cell drop-down list of values from the MedCodes named range. Allow blanks.

On the Data tab, in the Data Tools group, click the Data Validation button. In the Data Validation dialog, expand the Allow list and select List. In the Source box, type: =MedCodes Click OK.

Complete the one-variable data table in cells G12:H17 to calculate the total investor payout for varying percentage rates. The formula has been entered for you in cell H12. It references the original investor payout percentage in cell B17. The substitute values have been entered for you in cells G13:G17.

On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Data Table... Enter B17 in the Column input cell box. Click OK.

Complete the two-variable data table in cells H12:K17. The formula has been entered for you in cell H12. The substitute values in cells H13:H17 reference the original owner withdrawal value in cell B17, and the substitute values in cells I12:K12 reference the total expenses value in cell E11.

On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Data Table... Enter E11 in the Row input cell box. Enter B17 in the Column input cell box. Click OK.

Create a scenario summary report. Accept the recommended results cells.

On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Scenario Manager. Click the Summary button. Click OK.

Using the Scenario Manager, show the New Bonus scenario. Close the Scenario Manager when you are through.

On the Data tab, in the Data Tools group, click the What-If Analysis button, and then click Scenario Manager. Click New Bonus in the Scenarios: box. Click Show. Click Close.

Create a new scenario to reflect a change in cell B8 to a value of 0.09 Name the scenario New Bonus.

On the Data tab, in the Data Tools group, click the What-If Analysis button, and then click Scenario Manager. Click the Add button. Type New Bonus in the Scenario name box. Click OK. Type 0.09 in the text box. Click OK

Open the Watch Window and create a new watch for the selected cell.

On the Formulas tab, in the Formula Auditing group, click the Watch Window button. Click Add Watch. Click Add.

In cell B6, enter a formula to calculate the future value of this savings strategy. Use cell references wherever possible. The annual interest rate is stored in cell B5, the number of payments in cell B4, and the monthly payment amount in cell B3. Remember to divide the annual interest rate by 12 and use a negative value for the Pmt argument.

On the Formulas tab, in the Function Library group, click the Financial button. Click FV. Enter B5/12 in the Rate argument box. Enter B4 in the Nper argument box. Enter -B3 in the Pmt argument box. Click OK.

In cell B14, enter a formula using NPV to calculate the value today (the present value) of the tuition payment option 3. Use cell B7 as the Rate argument and the cell range B10:B13 as the Value1 argument. Use cell references for all values.

On the Formulas tab, in the Function Library group, click the Financial button. Click NPV. Enter B7 in the Rate box. Enter B10:B13 in the Value1 box. Click OK.

In cell B10, enter a formula using PV to calculate the value today (the present value) of the four-year tuition plan. Use cell references wherever possible. The annual interest rate for your investment account is stored in cell B8, the number of monthly payments in cell B7, and the monthly payment amount in cell B6. Payments will be made at the beginning of every period. Pay attention to the time periods for the interest rate and payment schedule. Remember to express the Pmt argument as a negative.

On the Formulas tab, in the Function Library group, click the Financial button. Click PV. Enter B8/12 in the Rate box. Enter B7 in the Nper box. Enter -B6 in the Pmt box. Type 1 in the Type box. Click OK.

Enter a formula using DAVERAGE to calculate the average value in the Total Spent column for rows that meet the criteria in the criteria range A1:G2. The database is defined by the named range CustomersDB.

On the Formulas tab, in the Function Library group, click the Insert Function button to open the Insert Function dialog. Expand the Or select a category list and select Database. Select DAVERAGE, and click OK. Enter CustomersDB in the Database argument box. In the Field argument box, enter "Total Spent". In the Criteria argument box, enter A1:G2. Click OK.

Enter a nested IF function in cell B8 to calculate the bonus amount. If the value in cell B6 is greater than 1500, then the bonus amount is 2500. Use a nested IF formula in the Value_if_false argument to return a draw amount of 500 if the value in cell B6 is greater than 1000 and 50 if it is not.

On the Formulas tab, in the Function Library group, click the Logical button and select IF. In the Logical_test box, type B6>1500. In the Value_if_true argument box, type 2500. Click in the Value_if_false argument box, and then click the arrow in the Name box to the left of the formula bar and select IF. In the Logical_test box, type B6>1000. In the Value_if_true argument box, type 500. In the Value_if_false argument box, type 50. Click OK.

In cell D6, enter a formula using AND to display TRUE if the daily sales (cell C6) is greater than the overall average (cell C3) and the daily sales (cell C6) is greater than the employee's average (cell C4). Use cell references and enter the arguments exactly as described in this question.

On the Formulas tab, in the Function Library group, click the Logical button. Click AND. In the Logical1 argument box, enter C6>C3. In the Logical2 argument box, enter C6>C4. Click OK.

In cell D6, enter a formula using OR to display TRUE if the daily sales (cell C6) is greater than the overall average (cell C3) or the daily sales (cell C6) is greater than the employee's average (cell C4). Use cell references and enter the arguments exactly as described in this question.

On the Formulas tab, in the Function Library group, click the Logical button. Click OR. In the Logical1 argument box, enter C6>C3. In the Logical2 argument box, enter C6>C4. Click OK.

In cell B3, enter a formula using INDEX to look up the expected delivery date for the purchase order at the row position in cell B2. Use the named range JunePOs as the Array argument. The price is found in the fifth column of the array.

On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Click OK to select the array,row_num,column_num option. In the Function Arguments dialog, in the Array box enter JunePOs. In the Row_num box, enter B2. In the Column_num box, enter 5. Click OK.

In the selected cell, enter a formula using MATCH to look up the row position of the item listed in cell B1 in the array named POitems. Require an exact match.

On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select MATCH. In the Function Arguments dialog, in the Lookup_value box, enter B1. In the Lookup_array box, enter POitems. In the Match_Type argument box, enter 0. Click OK.

Enter a formula in cell E2 using SUMIFS to calculate the total price (use the named range JunePrices) where the value in the JunePOs named range is equal to the value in cell D1 and the value in the JuneCompanies named range is equal to "Salon Supplies".

On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMIFS. In the Sum_range argument box, type JunePrices. In the Criteria_range1 argument box, type JunePOs. In the Criteria1 argument box, type D1. In the Criteria_range2 argument box, type JuneCompanies. In the Criteria2 argument box, type "Salon Supplies". Click OK.

Enter a formula in cell D1 using AVERAGEIFS to calculate the average price (use the named range JunePrices) where the value in the JunePOs named range is equal to the value in cell D1 and the value in the JuneCompanies named range is equal to the text string "DermoMagic".

On the Formulas tab, in the Function Library group, click the More Functions button. Under Statistical, click AVERAGEIFS. In the Average_range argument box, type JunePrices. In the Criteria_range1 argument box, type JunePOs. In the Criteria1 argument box, type D1. In the Criteria_range2 argument box, type JuneCompanies. In the Criteria2 argument box, type "DermoMagic". Click OK.

Enter a formula using the EXACT function to display TRUE if the value of cell D2 matches the value of cell E2 exactly.

On the Formulas tab, in the Function Library group, click the Text button. Click EXACT. In the Text1 argument box, enter D2. In the Text2 argument box, enter E2. Click OK.

Enter a formula using the REPLACE function to replace only the first three character in cell A2 with the two characters "HH". Be sure to use 1 as the Start_num argument.

On the Formulas tab, in the Function Library group, click the Text button. Click REPLACE. In the Old_text argument box, enter A2. In the Start_num argument box, enter 1. In the Num_characters argument box, enter 3. In the New_text argument box, enter "HH". Click OK.

In the selected cell, use the CONCATENATE function to combine the values of cells B3 and C3 with a space in between. The result of the formula should look like this: Alexandria VA

On the Formulas tab, in the Function Library group, click the Text button. Select CONCATENATE from the list. Type B3 in the Text1 box. Type " " in the Text2 box. Type C3 in the Text3 box. Click OK.

Insert a screenshot of the open Internet Explorer browser window showing employment statistics into this worksheet. Include a hyperlink with the image, but do not have Excel remember your choice.

On the Insert tab, click the Illustrations button to expand the Illustrations group. Click the Screenshot button. Click the first thumbnail in the gallery. Click Yes.

Add a hyperlink from the selected cell to link to cell A1 in the Inventory List worksheet.

On the Insert tab, in the Links group, click the Hyperlink button. Under Link to: click Place in this document. In the Or select a place in this document box, click Inventory List. Click OK.

Add a slicer to the PivotTable for the State field and use the slicer to show only data where the value is VA.

On the PivotTable Tools Analysis tab, in the Sort & Filter group, click the Insert Slicer button. In the Insert Slicers dialog, click the check box for State. Click OK. In the slicer, click VA.

Add a calculated field to this PivotTable to calculate the average sales per visit by state (=Sales/Visits). Name the new field Average.

On the PivotTable Tools Analyze tab, click the Calculations button, and then click Fields, Items, & Sets. Select Calculated Field In the Insert Calculated Field dialog, type Average in the Name box. In the Formula box, delete the 0 and type Sales/Visits. Do not delete the equals sign. Click OK.

Modify the Visits field to use the AVERAGE function instead of the SUM function.

On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button. Select Average. Click OK.

Apply the Pivot Style Dark 2 Quick Style to the PivotTable.

On the PivotTable Tools Design tab, in the PivotTable Styles group, click the More button to expand the Quick Styles gallery, and select Pivot Style Dark 2.

This workbook has workbook protection applied. Remove it.

On the Review tab, in the Changes group, click the Protect Workbook button.

Apply workbook protection to this workbook.

On the Review tab, in the Changes group, click the Protect Workbook button. Click OK.

Enable sharing so more than one person at a time can work on this workbook.

On the Review tab, in the Changes group, click the Share Workbook button. In the Share Workbook dialog, click the Allow changes by more than one user at a time check box. Click OK. Click OK to save the workbook and add sharing.

This workbook has track changes enabled. Accept all the changes that have not yet been reviewed.

On the Review tab, in the Changes group, click the Track Changes button. Click Accept/Reject Changes. Click OK. Click OK. Click Accept All.

Turn on track changes to highlight all changes made in this workbook since it was last saved.

On the Review tab, in the Changes group, click the Track Changes button. Click Highlight Changes... Click the Track changes while editing check box. Expand the When box and select Since I last saved. Click OK. Click OK to save the workbook.

Remove the hyperlink from the selected cell.

Right-click and select Remove Hyperlink.

Edit the AVERAGEIFS formula in cell E2 using IFERROR so if the formula results in an error, the message not in PO will display instead of the #DIV/0! error. Edit the formula directly in the cell or the formula bar. Remember to use quotation marks around the text string.

Edit the formula in the formula bar or the cell to: =IFERROR(AVERAGEIFS(Prices,POs,D1,Companies,"DermoMagic"),"not in PO")

Group together worksheets Q1-Q2 and Q3-Q4 so you can make changes to both at the same time.

Hold the Shift key and click the sheet tab labeled Q3-Q4.

Use Solver to find the combination of attendees that will result in the maximum possible profit. Add a constraint to the existing Solver parameters to require that the values in the named range Attendees are less than or equal to the values in the named range MaxPerClass. Run Solver and accept the solution.

On the Data tab, in the Analysis group, click the Solver button. Click the Add button. Type Attendees in the Cell Reference box. Type MaxPerClass in the Constraint box. Click OK. Click Solve. Click OK.

Use Consolidate to enter values in the selected cells by summing data from cells B3:D8 in the worksheets Telephone, Brick-n-Mortar, and Online. Include links to the source data to display subtotals.

On the Data tab, in the Data Tools group, click the Consolidate button. Click in the Reference box and then click the Telephone worksheet tab. Click and drag to select B3:D8. Click the Add button. Click the Brick-n-Mortar worksheet tab. Click Add. Click the Online worksheet tab. Click Add. Click the Create links to source data check box. Click OK.

Use Consolidate to create subtotals using the Sum function for values in the named range ItemsOrdered. Include both the top row and left column labels. The named range ItemsOrdered has been defined for you. The consolidation should show the subtotals only and not the details.

On the Data tab, in the Data Tools group, click the Consolidate button. Type ItemsOrdered in the Reference box. Click the Top row check box. Click the Left column check box. Click OK.

This worksheet has validation rules applied. Find and circle cells that violate those rules.

On the Data tab, in the Data Tools group, click the Data Validation button arrow and select Circle Invalid Data.

Change the data source for this PivotTable to use the table named CustomersTable.

On the PivotTable Tools Analyze tab, in the Data group, click the Change Data Source button. Type CustomersTable in the Table/Range box. Click OK.

Changes have been made to the underlying data for this PivotTable. Refresh the PivotTable data.

On the PivotTable Tools Analyze tab, in the Data group, click the Refresh button.