SIMnet- Practice Test

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 Forecast group, clcik the What if- Analysis button, and then clcik 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

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

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

This worksheet does not include any total rows, and there are blank rows between groups if data. Manually create groups of rows that can be expanded or collapsed as needed. Starter with the group of selected cells

On the Data tab, in the Outline group, click the Group button. In the Group dialog, Rows Is selected by default. Click OK

Use a custom text filter to show only rows where the item name (Item column) begins with the letter G. Font Size

On the Data tab, in the Sort & Filter group, click the Filter button to display the AutoFilter arrows at the top of each column. Click the AutoFilter arrow at the top of the Item column, and point to Text Filters. Click Begins With... Type G in the box next to begins with. Click OK.

Use a custom date filter to show only rows where the order date (Order Date column) is after 6/30/2020. Font Size

On the Data tab, in the Sort & Filter group, click the Filter button to display the AutoFilter arrows at the top of each column. Click the AutoFilter arrow at the top of the Order Date column, and point to Date Filters. Click After... Type 6/30/2020 in the box next to is after. Click OK.

Sort the data so cells with 100% solid fill circle icon in the quantity in stock column appear on top

On the Data tab, in the Sort & Filter group, click the Filter button to display the AutoFilter arrows at the top of each column. Click the AutoFilter arrow at the top of the column. Point to Sort by Color and click the solid fill circle icon

Sort this data alphabetically from A to Z by values in the Last column. Font Size

On the Data tab, in the Sort & Filter group, click the Sort A to Z button.

Sort this data by the Visits column so the smallest numbers are listed first. Font Size

On the Data tab, in the Sort & Filter group, click the Sort A to Z button.

Sort the data first alphabetically by the values in the Item column and then by the dates in the Date Expected column with the oldest dates first. Font Size

On the Data tab, in the Sort & Filter group, click the Sort button. Expand the Sort by list and select Item. Click the Add Level button. Expand the Then by list and select Date Expected. Click OK.

Edit the code for the FormatTable macro in the Visual Basic Editor. Font Size

On the Developer tab, in the Code group, click the Macros button. Select FormatTable and click the Edit button.

Edit the code for the FormatTable macro to change the chart style from TableStyleLight11 to TableStyleMedium4. Save your changes. Font Size

On the Developer tab, in the Code group, click the Macros button. Select FormatTable and click the Edit button. In the line of code Change ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight11"change TableStyleLight11 to TableStyleMedium4. On the Visual Basic Editor toolbar, click the Save button.

Begin recording a new macro. Font Size

On the Developer tab, in the Code group, click the Record Macro button.

Record a new macro with the name ConvertTable. Do not assign a shortcut key or description. The macro should convert the table to a range using the command on the Table Tools Design tab, Tools group. Font Size

On the Developer tab, in the Code group, click the Record Macro button. Type ConvertTablein the Macro name box. Click OK. On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes. On the Developer tab, in the Code group, click the Stop Recording button.

Add a form control button to run the FormatTable macro. The button should be placed at approximately cells G1:I3. Font Size

On the Developer tab, in the Controls group, click the Insert Controls button. Click the Button (Form Control) button. Click and drag over across cells G1:I3 to draw the button. Click FormatTable. Click OK.

Hide all of the dependency tracer arrows at once. Font Size

On the Formulas tab, in the Formula Auditing group, click the Remove Arrows button.

Show the tracer arrows from the precedent cells to cell B5. Font Size

On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.

Enter a formula using DSUM to calculate the total value in the Total Spent column for rows that meet the criteria in the crteria range A1:62. 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 DSUM, and click OK. Enter CustomersDB in the Database arqument box. In the Field argument box, enter "Total Spent". In the Criteria argument box, enter A1:G2. Click OK

In cell H2, enter a formula using COUNTIFS to count the number of rows where values in the range named DeliveryTime have a value greater than 14 and cells in the range named ReorderStatus display "no". Font Size

On the Formulas tab, in the Function Library group, click More Functions. Point to Statistical, and select COUNTIFS. In the Criteria_Range1 argument box, type DeliveryTime. In the Criteria1 argument box, type ">14". In the Criteria_Range2 argument box, type ReorderStatus. In the Criteria2 argument box, type "no". Click OK.

In cell H12, enter a formula to find the highest percentage of items received in the order (cells H2:H11). Font Size

On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Max. Press Enter.

In cell H12 enter a formula to find the lowest percentage of items received in the order (cells H2:H11)

On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Min. Press Enter.

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. Font Size

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 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. Font Size

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

Enter formula in cell B8 using the IFS function to calculate a bonus. If the value in cell B6 is greater than or equal to 20000, the bonus is 1000. If the value in cell B6 is greater than or equal to 10000, the bonus is 250. If the value in cell B6 is less than 10000, the bonus is 0. Font Size

On the Formulas tab, in the Function Library group, click the Logical button. Select IFS. In the Logical_test1 argument box, enter B6>=20000. In the Value_if_true1 argument box, enter 1000. In the Logical_test2 argument box, enter B6>=10000. In the Value_if_true2 argument box, enter 250. In the Logical_test3 argument box, enter B6<10000. In the Value_if_true3 argument box, enter 0. Click OK.

Enter a nested function in cell B2 using INDEX and MATCH to find the expected delivery date for the item listed in cell B1. Use the named range JunePOs to reference the cell range INDEX Array argument. The expected due date is in column 5. In the INDEX Row_num function argument, use MATCH to look up the row number for the item listed in B1. Use the named range POitems as the MATCH Lookup_array argument. Require an exact match. Font Size

On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Select the array,row_num,column_num option. Click OK. In the Function Arguments dialog, in the Array box enter JunePOs. In the Column_num box, enter 5. Click in the Row_num box, and then click the arrow in the Name box to the left of the formula bar and 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.

In cell B2, 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. Font Size

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 E1 using SUMIF to calculate the total quantity in stock for items from the company "ColorFab". Use the range name Company for the Range argument, the text string "ColorFab" for the Criteria argument, and InStock for the Sum_range argument. Font Size

On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMIF. In the Range argument box, type Company. In the Criteria box, type "ColorFab". In the Sum_range argument box, type InStock. Click OK.

In cell F12, enter a formula using a counting function to count numbers in the Ordered column (cells F2:F11). Font Size

On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNT from the list. Click and drag to select cells F2:F11 in the Value1 box. Click OK.

In cell G12, enter a formula using a counting function to count the number of blank cells in the Received column (cells G2:G11). Font Size

On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNTBLANK from the list. Click and drag to select cells G2:G11. Click OK.

Enter a formula in cell E2 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". Font Size

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.

In cell D2, use the CONCAT function to combine the values of cells B2 and C2 with a space in between. The result of the formula should look like this: Davidsonville MD Font Size

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

Add a footer that displays the page number in the center section. Font Size

On the Header & Footer Tools Design tab, in the Header & Footer Elements group, click the Page Number button. OK

Apply bold and italic formatting to the selected cell. Font Size

On the Home tab in the Font group, click the Bold button and the Italic button.

Center the content in the selected cells horizontally. Font Size

On the Home tab, in the Alignment group, click the Center button.

Merge the cells so the text appears centered across the merged cells. Font Size

On the Home tab, in the Alignment group, click the Merge & Center button.

Wrap the text in the selected cell. Font Size

On the Home tab, in the Alignment group, click the Wrap Text button.

Insert a column to the left of column F. Font Size

On the Home tab, in the Cells group, click the Insert button arrow and select Insert Sheet Columns.

Add a new worksheet to the left of the November sheet Font Size

On the Home tab, in the Cells group, click the Insert button arrow, and select Insert Sheet.

Use Format Painter to copy the formatting from cell E1 and apply it to cell F1. Font Size

On the Home tab, in the Clipboard group, click the Format Painter button. Click cell F1.

Enter a formula in cell E4 to calculate the average value of cells B4:D4. Font Size

On the Home tab, in the Editing group, click the AutoSum button arrow and select Average. Press Enter.

Add a Blue-Gray, Text 2 (in the top row of theme colors, the fourth color from the left) top border to the selected cells. Use the thickest single line style available. Font Size

On the Home tab, in the Font group, click the Borders button arrow, and select More Borders... In the Format Cells dialog, Border tab, click the thickest line available in the Style section. Expand the Color palette and select Blue-Gray, Text 2. In the Border section, click the button representing a top border.

Apply the Top and Double Bottom Border to the selected cells with a single command. Font Size

On the Home tab, in the Font group, click the Borders button arrow, and select Top and Double Bottom Border.

Apply conditional formatting so cells with a value greater than the average are formatted using a yellow fill with dark yellow text. Font Size

On the Home tab, in the Styles group, click the Conditional Formatting button. Point to Top/Bottom Rules, and click Above Average. Click the drop-down arrow and select Yellow Fill with Dark Yellow Text. Click OK.

Filter the state column so only rows with MD are shown

On the Home tab, in the editing group, clcik the sort and filter button and select filtter click the arrows at the top of State column; click the (select all) check box to remove all checkmarks. Click the check box in front of MD then click OK

Convert the cell range to a table using table style Table Style Light 10 The table should include headers.

On the Home tab, in the styles group, click the Format as table button click Table Style Light 10 (Third style in second row under light styles) Verify My table has headers checkbox is checked click ok

Apply the conditioning formatting to the selected cells so cells with a value greater than 10 are formatting using a yellow fill with dark yellow text

On the Home tab, in the styles group, click the conditional formatting button. Point to Highlight Cells Rules, and click Greater Than.. type 10 in the format cells that are greater than box. Expand the format selector box and select yellow fill with dark yellow text. Click ok

Insert a Waterfall chart based on cells A1:B10. Font Size

On the Insert tab, in the Charts group, click the Insert Waterfall or Stock Chart button. Select Waterfall.

Insert a recommended PivotTable, using the Sum of Total Spent by State option. Font Size

On the Insert tab, in the Tables group, click the Recommended PivotTables button. Click the Sum of Total Spent by State option. Click OK.

From Page Break Preview view, insert a page break between rows 4 and 5. Font Size

On the Page Layout tab, in the Page Setup group, click the Breaks button. Click Insert Page Break.

Clear the print area. Font Size

On the Page Layout tab, in the Page Setup group, click the Print Area button. Click Clear Print Area.

Change the scaling option so all columns will print on one page. Font Size

On the Page Layout tab, in the Scale to Fit group, click the Width arrow. Click 1 page.

Modify this worksheet so gridlines will print. Font Size

On the Page Layout tab, in the Sheet Options group, click the Print check box under Gridlines.

Apply the Gallery theme to the workbook. Font Size

On the Page Layout tab, in the Themes group, click the Themes button. Click the Gallery theme.

Modify the visits field to use the average function instead of Sum function

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

Show markers for just the highest values in this Sparkline group. Font Size

On the Sparkline Tools Design tab, in the Show group, click the High Point check box.

Apply the Sparkline Style Colorful #2 Quick Style to the selected Sparkline group. It is the second option in the last row of the gallery. Font Sizeuick Style to the selected Spark

On the Sparkline Tools Design tab, in the Style group, click the More button. Click the second option in the last row of the gallery.

Convert the table to a normal range. Font Size

On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes. OK

Use a slicer to filter the data in this table to show only rows where the State value is MD or DE. Font Size

On the Table Tools Design tab, in the Tools group, click the Insert Slicer button. Click the State check box. Click OK. In the State slicer, click the Multi-Select button. Click the VA button to turn it off.

Freeze the top row of the worksheet. Font Size

On the View tab, in the Window group, click the Freeze Panes button. Click Freeze Top Row.

Move the selected chart to a new chart sheet

On the chart tools design tab, in the location group, click the Move Chart button click the New Sheet radio button then click ok

Create a scenario summary report. Accept the recommended results cells

On the data tab, in the forecast group, click the What-if-analysis button, and click scenario manager. Click the summary button. Click ok

Insert the current date in the selected cell. Do not include the current time

On the formulas tab, in the Function Library group, click the Date and Time button. Click TODAY. Click ok

Insert a Left Arrow block arrow shape to the right of the Jan column in the chart

On the insert tab, click the illustrations button to open the Illustrations group click the shapes button and select left arrow shape from the Blocks arrow section. the cursor changes to a cross hair. click on the chart to the right of the Jan column

Apply the Orange, Table Style Medium 3 Quick Style (third style in first row under medium) to this table

On the table design tab, in the table styles group, clcik the quick styles button or clcik the more button to expand the Table Styles gallery. Click Orange, Table Style Medium 3. It's the third style in the first row under Medium styles

Change the color of the sheet tab for the November worksheet to Purple (the last option at the right in the row of standard colors). Font Size

Right-click the worksheet tab and point to Tab Color. Click Purple (the last option at the right in the row of standard colors).

On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet. Font Size

Type = and then click the ByMonth sheet tab. Click cell B3. Press Enter.

Enable filtering

on the home tab, in the editing group, click the sort and filter button and select filter

Add the DEVELOPER tab to the ribbon

Click File tab. Click Options to open the Excel Options dialog. Click Customize Ribbon. At the right side of the Customize the Ribbon window, the Customize the Ribbon list shows all the tab and groups available. Click the Developer check box to add the Developer tab to the Ribbon. Click OKk

Resize the selected chart so it is approximately 12 rows tall. Font Size

Click and drag a resize handle up or down toward the center of the chart to make it smaller.

Select the Items Received data series and apply the Intense Effect - Gold, Accent 4 shape style (the third style from the right in the last row)

Click any data point in the Items Received series to select the series. On the Chart Tools Format tab, Shape Styles group, click the More button to expand the Shape Styles gallery. Select Intense Effect - Gold, Accent 4 shape style (the third style from the right in the last row).

Display the details for just the order closed group. Font Size

Click the + symbol next to row 9.

Collapse the entire outline to show just the subtotals. Font Size

Click the 2 outline symbol at the left of the column headings.

The Items Received data series is already selected. Select just the August Purchase Orders data point in the series and change the outline color to Orange, Accent 2 (the fifth color from the right in top row of theme colors). Font Size

Click the August Purchase Orders column in the Items Received data series once to select it. On the Chart Tools Format tab, Shape Styles group, click the Shape Outline button, and click Orange, Accent 2 (the fifth color from the right in top row of theme colors).

Filter the data to show only cells with the solid green background in the Quantity in Stock column. Font Size

Click the AutoFilter arrow at the top of the column. Point to Filter by Color and click the green color under Filter by Cell Color.

Move the Car Loan worksheet so it is positioned before the Personal Loan worksheet. Font Size

Click the Car Loan worksheet tab and hold down the left mouse button. Drag the mouse pointer to a point just in front of the Personal Loan worksheet tab, and release the mouse button.

Hide the chart title.

Click the Chart Elements button and click the Chart Title check box.

Display the data table, including the legend keys. Font Size

Click the Chart Elements button and click the Data Table check box.

Add a linear trendline to this chart. Use the default trendline formatting. Font Size

Click the Chart Elements button that appears near the upper right corner of the chart. Click the Trendline check box to add a checkmark.

Change the print option to print the entire workbook, and then print the workbook. Font Size

Click the File tab to open Backstage. Click Print. Click the Print Active Sheets button. Click Print Entire Workbook. Click the Print button.

Save this worksheet as a tab-delimited text file. Font Size

Click the File tab. Click Save As. On the Save As page, click the Save as type arrow and select Text (Tab delimited) (*.txt). Click Save. Click OK.

Use AutoFill to copy the formula and formatting in cell B9 to cells C9:D9. Font Size

Click the Fill Handle tool in the selected cell and drag across to cell D9. Release the mouse button.

Cell F6 has been copied. Paste just the formula without the cell formatting into the selected cell (cell F7). Font Size

Click the Paste button arrow, and then click the Formulas option.

Add line Sparklines to cells E3:E12 to represent the values in B3:D12. Font Size

Click the Quick Analysis Tool button and click the Sparklines tab. Click Line.

Insert a Clustered Column chart based on the first recommended chart type

Click the Quick Analysis Tool button, and then click the Charts tab. Click Clustered Column

Change the zoom level for the worksheet to be 110%. Font Size

Click the Zoom In button on the zoom slider.

Set the print area

Click the back button. On the Page Layout tab, in the page Setup group, click the Print Area button. Click Set Print Area

Hide column E

Click the column selector for Column E. On the Home tab, in the Cells group, click the Format button. Point to Hide & Unhide, and click Hide Columns.

Apply the Data bars conditional formatting option to the selected cells from the Quick Analysis tool

Click the quick analysis tool button at the lower-right corner of the selection. On the formatting tab, click the Data Bars button

Set the last data point (March Net Income) as a total. Font Size

Double-click the March Net Income data point to open the Format Data Point task pane. In the task pane, click the Set as total check box

Autofit column F to best fit the data. Font Size

Double-click the right column boundary for column F.

Enter a formula in cell C1 to calculate the mean absolute deviation of the values in cells E4:E12. Font Size

In cell C1, enter this formula: =AVERAGE(E4:E12)

Enter a formula in cell E4 to calculate the absolute value of C4-D4. Font Size

In cell E4, enter this formula: =ABS(C4-D4)

Add the Visits field to the PivotTable. Font Size

In the PivotTable Fields pane, click the Visits check box to add the field to the PivotTable.

apply the colorful pallet 4 color scheme to the chart

On the Chart Tools Design tab, in the Chart Styles group, click the Change Colors button. Click Colorful Palette 4, it is the fourth option in the Colorful section.

Add another data series to the chart to represent the number of items ordered. Use the value of cell B1 as the series name and Cells b2:b4 as the series values. Do not rebuild the chart from scratch

On the Chart Tools Design tab, in the Data group, click the Select Data button. In the Select Data Source dialog, click the Add button. Click cell B1 to enter it in the Series name box. Click in the Series values box and delete the default entry. Click and drag cells B2:B4. Click OK to add the new series. Click OK to accept the change to the chart data source.

Change the chart type to the first 100 % stacked column option (the third option along the top of the right pane). Font Size

On the Chart Tools Design tab, in the Type group, click the Change Chart Type button. Click 100% Stacked Column (the third option along the top of the right pane). Click OK.

Modify the chart so the Total data series is plotted along the secondary axis. Font Size

On the Chart Tools Design tab, in the Type group, click the Change Chart Type button. In the Change Chart Type dialog, click the Secondary Axis check box next to the Total series.

The chart title is selected. Apply the Gradient Fill, Gray WordArt quick style (the first style in the second row of the WordArt Quick Styles gallery). Font Size

On the Chart Tools Format tab, in WordArt Styles group, click the WordArt Quick Styles button to expand the gallery. Click the first style in the second row (Gradient Fill, Gray).

The August Purchase Orders data point has been selected for you. Rotate the pie chart exactly 130° so this data point appears at the right side of the chart near the legend. Font Size

On the Chart Tools Format tab, in the Current Selection group, click the Format Selection button to open the Format Data Point task pane. In the Angle of first slice box, type the 130 and press Enter.

The August Purchase Orders data point has been selected for you. Explode the data point by exactly 45% to move it slightly away from the rest of the pie chart. Font Size

On the Chart Tools Format tab, in the Current Selection group, click the Format Selection button to open the Format Data Point task pane. In the Point Explosion box, type 45 and press Enter.

Use Solver to find the combination of attendees that will result in the maximum possible profit in cell G1. Use the range name Attendees as the variable cells. Add these constraints in order: values in the named range Attendees are less than or equal to the values in the named range MaxPerClass; values in the named range Attendees are whole numbers; values in the named range TotalAttendees are less than or equal to the values in the named range MaxAttendees. Run Solver and accept the solution. Font Size

On the Data tab, in the Analyze group, click the Solver button. Type G1 in the Set Objective box. (The Max radio button is selected by default.) Type Attendees in the By Changing Variable Cells box, and then click the Add button. Type Attendees in the Cell Reference box. Type MaxPerClass in the Constraint box. Click Add. Type Attendees in the Cell Reference box. Expand the drop-down list in the middle box and select Int. Click Add. Type TotalAttendees in the Cell Reference box. Type MaxAttendees in the Constraint box. Click OK. Click Solve. Click OK.