SIMNET- PRACTICE TEST 100% CORRECT!!., Exams of Advanced Education

Download SIMNET- PRACTICE TEST 100% CORRECT!!. and more Exams Advanced Education in PDF only on Docsity!

SIMNET-

PRACTICE TEST

100% CORRECT!!

SIMNET

Evatee 10/16/24 SIMNET

SIMNET- PRACTICE TEST 100% CORRECT!! Set the print area Answer - Click the back button. On the Page Layout tab, in the page Setup group, click the Print Area button. Click Set Print Area Create a new scenario to reflect a change in cell B8 to a value of 0.09 Name the scenario New Bonus Answer - 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 Convert the cell range to a table using table style Table Style Light 10 The table should include headers. Answer - 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 Orange, Table Style Medium 3 Quick Style (third style in first row under medium) to this table Answer - 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

the Active Field group, click the Field Settings button. Select Average. Click OK. Create a scenario summary report. Accept the recommended results cells Answer - On the data tab, in the forecast group, click the What-if-analysis button, and click scenario manager. Click the summary button. Click ok 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 Answer - 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. 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 Answer - On the Data tab, in the Outline group, click the Group button. In the Group dialog, Rows Is selected by default. Click OK Apply the Data bars conditional formatting option to the selected cells from the Quick Analysis tool Answer - Click the quick analysis tool button at the lower-right corner of the selection. On the formatting tab, click the Data Bars button 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 Answer - 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 Move the selected chart to a new chart sheet Answer - On the chart tools design tab, in the location group, click the Move Chart button click the New Sheet radio button then click ok Filter the state column so only rows with MD are shown Answer

• 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 Insert a Left Arrow block arrow shape to the right of the Jan column in the chart Answer - 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 colorful pallet 4 color scheme to the chart Answer - 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. 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) Answer - Click any data point in the Items

Font Size Answer - In the PivotTable Fields pane, click the Visits check box to add the field to the PivotTable. Use a slicer to filter the data in this table to show only rows where the State value is MD or DE. Font Size Answer - 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. Add line Sparklines to cells E3:E12 to represent the values in B3:D12. Font Size Answer - Click the Quick Analysis Tool button and click the Sparklines tab. Click Line. 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 Answer - 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. Enter a formula in cell C1 to calculate the mean absolute deviation of the values in cells E4:E12. Font Size Answer - In cell C1, enter this formula: =AVERAGE(E4:E12) Edit the code for the FormatTable macro to change the chart style from TableStyleLight11 to TableStyleMedium4. Save your changes.

Font Size Answer - 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. In cell H12, enter a formula to find the highest percentage of items received in the order (cells H2:H11). Font Size Answer - On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Max. Press Enter. Show markers for just the highest values in this Sparkline group. Font Size Answer - On the Sparkline Tools Design tab, in the Show group, click the High Point check box. Move the Car Loan worksheet so it is positioned before the Personal Loan worksheet. Font Size Answer - 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. Merge the cells so the text appears centered across the merged cells. Font Size Answer - On the Home tab, in the Alignment group, click the Merge & Center button. 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.

Show the tracer arrows from the precedent cells to cell B5. Font Size Answer - On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button. 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 Answer - On the Formulas tab, in the Function Library group, click the Logical button. Select IFS. In the Logical_test argument box, enter B6>=20000. In the Value_if_true 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. Change the print option to print the entire workbook, and then print the workbook. Font Size Answer - Click the File tab to open Backstage. Click Print. Click the Print Active Sheets button. Click Print Entire Workbook. Click the Print button. Using the Scenario Manager, show the New Bonus scenario. Close the Scenario Manager when you are through. Font Size Answer - 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 Insert a column to the left of column F.

Font Size Answer - On the Home tab, in the Cells group, click the Insert button arrow and select Insert Sheet Columns. Collapse the entire outline to show just the subtotals. Font Size Answer - Click the 2 outline symbol at the left of the column headings. Center the content in the selected cells horizontally. Font Size Answer - On the Home tab, in the Alignment group, click the Center button. Enter a formula in cell E4 to calculate the average value of cells B4:D4. Font Size Answer - On the Home tab, in the Editing group, click the AutoSum button arrow and select Average. Press Enter. Apply bold and italic formatting to the selected cell. Font Size Answer - On the Home tab in the Font group, click the Bold button and the Italic button. Cell F6 has been copied. Paste just the formula without the cell formatting into the selected cell (cell F7). Font Size Answer - Click the Paste button arrow, and then click the Formulas option. Insert a Waterfall chart based on cells A1:B10. Font Size Answer - On the Insert tab, in the Charts group, click the Insert Waterfall or Stock Chart button. Select Waterfall. Wrap the text in the selected cell.

Filter the data to show only cells with the solid green background in the Quantity in Stock column. Font Size Answer - 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. Use Format Painter to copy the formatting from cell E1 and apply it to cell F1. Font Size Answer - On the Home tab, in the Clipboard group, click the Format Painter button. Click cell F1. Convert the table to a normal range. Font Size Answer - On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes. OK In cell F12, enter a formula using a counting function to count numbers in the Ordered column (cells F2:F11). Font Size Answer - 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:F in the Value1 box. Click OK. On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet. Font Size Answer - Type = and then click the ByMonth sheet tab. Click cell B3. Press Enter. 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 Answer - 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. Add a form control button to run the FormatTable macro. The button should be placed at approximately cells G1:I3. Font Size Answer - 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. Resize the selected chart so it is approximately 12 rows tall. Font Size Answer - Click and drag a resize handle up or down toward the center of the chart to make it smaller. Use AutoFill to copy the formula and formatting in cell B9 to cells C9:D9. Font Size Answer - Click the Fill Handle tool in the selected cell and drag across to cell D9. Release the mouse button. Edit the code for the FormatTable macro in the Visual Basic Editor. Font Size Answer - On the Developer tab, in the Code group, click the Macros button. Select FormatTable and click the Edit button. Begin recording a new macro. Font Size Answer - On the Developer tab, in the Code group, click the Record Macro button.

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 Answer - 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). 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 Answer - 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. Clear the print area. Font Size Answer - On the Page Layout tab, in the Page Setup group, click the Print Area button. Click Clear Print Area. Autofit column F to best fit the data. Font Size Answer - Double-click the right column boundary for column F. Set the last data point (March Net Income) as a total. Font Size Answer - 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

Insert a recommended PivotTable, using the Sum of Total Spent by State option. Font Size Answer - On the Insert tab, in the Tables group, click the Recommended PivotTables button. Click the Sum of Total Spent by State option. Click OK. Display the details for just the order closed group. Font Size Answer - Click the + symbol next to row 9. Apply conditional formatting so cells with a value greater than the average are formatted using a yellow fill with dark yellow text. Font Size Answer - 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. 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 Answer - 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

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 Answer - 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). 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 Answer - 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. Change the zoom level for the worksheet to be 110%. Font Size Answer - Click the Zoom In button on the zoom slider. Save this worksheet as a tab-delimited text file. Font Size Answer - 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. 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 Answer - 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 new worksheet to the left of the November sheet Font Size Answer - On the Home tab, in the Cells group, click the Insert button arrow, and select Insert Sheet. Change the scaling option so all columns will print on one page. Font Size Answer - On the Page Layout tab, in the Scale to Fit group, click the Width arrow. Click 1 page. Hide the chart title. Answer - Click the Chart Elements button and click the Chart Title check box. 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 Answer - 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. Add a linear trendline to this chart. Use the default trendline formatting. Font Size Answer - Click the Chart Elements button that appears near the upper right corner of the chart. Click the Trendline check box to add a checkmark. Hide all of the dependency tracer arrows at once.