Download SIMNET PROGRAM EXAM QUESTIONS & ANSWERS and more Exams Advanced Education in PDF only on Docsity!
SIMNET PROGRAM EXAM
QUESTIONS & ANSWERS
Hide the column showing 2016 data and the bonus rate. - Correct Answers - You clicked cell B1, right clicked the column B header. In the Col Header Right-Click menu, you clicked the Hide menu item. In cell D15, enter a formula using a counting function to count the number of cells in the Billable? column (cells D2:D14) that are not blank. - Correct Answers - In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the More Functions button, clicked the Math & Trig button, clicked the Lookup & Reference button, clicked the Date & Time button, clicked the Text button, clicked the Logical button, clicked the Financial button, clicked the Recently Used button, and clicked the More Functions button. You clicked cell D15, clicked cell D15, typed =counta in cell D15, clicked the =counta(D2:D14) view, clicked the =counta(D2:D14) view, clicked cell D15, clicked the D15 Cell Input, clicked cell D15, clicked the D15 Cell Input, typed =counta(D2:D14) in cell D15, and pressed Enter. Set row 1 to print on every page. - Correct Answers - In the Page Layout Ribbon Tab in the Page Setup Ribbon Group, you clicked the Print Titles button. Inside the Page Setup dialog, you clicked the Rows to repeat at top: item. Inside the Page Setup dialog, you clicked the Expand Dialog item. Inside the Page Setup dialog, you clicked the Rows to repeat at top: item. You clicked on the row 1 header. Inside the Page Setup dialog, you clicked the Expand Dialog item. Inside the Page Setup dialog, you pressed the Enter key. Set the print area so only the selected cells will print. - Correct Answers - You clicked the back arrow button. In the Page Layout Ribbon Tab in the Page Setup Ribbon Group, you clicked the Print Area button. In the Print Area menu, you clicked the Set Print Area menu item. Change the color of the sheet tab for the Dec 18 worksheet to Green, Accent 6, Lighter 80% (the last option in the second row of theme colors). - Correct Answers - You right clicked the Dec 18 tab. In the Tab Right Click menu in the Tab Color menu, you selected the Green, Accent 6, Lighter 80% color option. Center the content in the selected cells horizontally. - Correct Answers - In the Home Ribbon Tab in the Alignment Ribbon Group, you clicked the Align Center button.
Show the tracer arrows from the precedent cells to cell C7. - Correct Answers - You clicked and dragged the cell C7 fill handle, clicked the Smart Tag Button, and clicked cell E12. In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Trace Precedents button. Merge the cells so the text appears centered across the merged cells. - Correct Answers - In the Home Ribbon Tab in the Alignment Ribbon Group, you clicked the Merge & Center button. Apply the Accounting Number Format to the selected cells. - Correct Answers - In the Home Ribbon Tab in the Number Ribbon Group, you clicked the Number Format drop- down arrow. In the Number Format menu, you selected the Accounting option. Change the width of the selected columns to 14. - Correct Answers - In the Home Ribbon Tab in the Cells Ribbon Group, you clicked the Format button. In the Format menu, you clicked the Column Width... menu item. Inside the Column Width dialog, you typed 14 in the Column Width: input, clicked the OK button. Modify this worksheet so you can see four different areas of the worksheet at the same time and scroll each area independently of the others. - Correct Answers - In the View Ribbon Tab in the Window Ribbon Group, you clicked the Split button. Insert the current date in cell A1. Do not include the current time. - Correct Answers - In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Date & Time button. In the Date & Time menu, you clicked the TODAY menu item. Inside the Function Arguments dialog, you clicked the OK button. Insert a column to the left of the selected cell. - Correct Answers - You right clicked the column D header. In the Col Header Right-Click menu, you clicked the Insert menu item. Without adjusting the column widths, guarantee that all columns will print on one page. - Correct Answers - You opened the backstage view, clicked the Print navigation button, clicked the Print Orientation button, clicked the Print Orientation button, clicked the Print Orientation button, clicked the Print Selection button, clicked the Print Selection button, clicked the Print Orientation button, and clicked the Scaling button. In the Scaling menu, you clicked the Fit All Columns on One Page menu item. Enter a formula in cell B1 using the SUMPRODUCT function to calculate the total value of the current leases by multiplying the current monthly rents by the remaining months on each lease. Use the range names Rents and Leases. - Correct Answers - In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the SUMPRODUCT menu item. Inside the Function Arguments dialog, you typed Rents in the Array1 input, typed Leases in the Array2 input, and clicked the OK button.
typed A3 in the Lookup_value input, typed Abbreviation in the Table_array input, typed 2 in the Col_index_num input, typed False in the Range_lookup input, and clicked the OK button. Change the font for the selected cell to Cambria. - Correct Answers - In the Home Ribbon Tab in the Font Ribbon Group, you clicked the Font drop-down arrow. In the Font menu, you clicked the Cambria font. Insert the current date and time in cell A1. - Correct Answers - In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Date & Time button. In the Date & Time menu, you clicked the NOW menu item. Inside the Function Arguments dialog, you clicked the OK button. Add a footer that displays the page number in the center section. - Correct Answers - In the Design Ribbon Tab in the Header & Footer Elements Ribbon Group, you clicked the Page Number button. Wrap the text in the selected cell. - Correct Answers - In the Home Ribbon Tab in the Alignment Ribbon Group, you clicked the Wrap Text button. Add an element to the center section of the header to display the current date (not Dec 18, which is the worksheet name). - Correct Answers - In the Mini Toolbar, you clicked the Header & Footer button. In the Design Ribbon Tab in the Header & Footer Elements Ribbon Group, you clicked the Current Date button. Modify the number format so no decimal places are visible after the decimal point. - Correct Answers - In the Home Ribbon Tab in the Number Ribbon Group, you clicked the Accounting Number Format button arrow. In the Accounting Number Format menu, you clicked the More Accounting Formats... menu item. Inside the Format Cells dialog, you changed the Decimal places input to 3, changed the Decimal places input to 2, changed the Decimal places input to 1, changed the Decimal places input to 0, and clicked the OK button. Enter a formula in cell B10 to return a value of 35000 if the Net Profit After Tax (cell B9) is greater than or equal to 470000 or 1000 if it is not. - Correct Answers - In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button, clicked the Logical button. In the Logical menu, you clicked the IF menu item. Inside the Function Arguments dialog, you clicked the Cancel button. You clicked cell B10, clicked cell B10, clicked cell B10, typed =If in cell B10, clicked the =IF(B9>=350000,"35000","1000") view, clicked cell B10, clicked the B10 Cell Input, typed =IF(B9>=350000,"35000","1000") in cell B10, and pressed Enter. Use a single command to clear everything (content and formatting) from the selected cell. - Correct Answers - In the Home Ribbon Tab in the Editing Ribbon Group, you clicked the Clear button. In the Clear menu, you clicked the Clear All menu item.
Use a custom text filter to show only rows where the expense category (Category column) contains the word server. - Correct Answers - In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Filter button. You clicked cell B1, clicked the Quick Sort dropdown. In the Table Filter menu in the Text Filters menu, you clicked the Contains... menu item. Inside the Custom AutoFilter dialog, you typed server in the second filter input, clicked the OK button. The total row for each expense category uses a SUM function to total the costs. Create an automatic outline from the rows in this data range. - Correct Answers - In the Data Ribbon Tab in the Outline Ribbon Group, you clicked the Group button arrow. In the Group menu, you clicked the Auto Outline menu item. Add a total row to this table and display the average for the Cost column instead of the sum of all the values. - Correct Answers - In the Design Ribbon Tab in the Table Style Options Ribbon Group, you checked the Total Row check box. You clicked cell C21, clicked the Total Row arrow. In the Total Row menu, you clicked the Average menu item. Apply the Drop Shadow, Rectangle Quick Style. - Correct Answers - In the Format Ribbon Tab in the Picture Styles Ribbon Group, you clicked the Picture Effects button, clicked the Picture Effects button, and expanded the Quick Styles Gallery. In the Format Ribbon Tab in the Picture Styles Ribbon Group in the Quick Styles menu, you clicked the Drop Shadow Rectangle gallery option. Remove the hyperlink from cell B3. - Correct Answers - You clicked cell B3, right clicked cell B3. In the Right-Click menu, you clicked the Remove Hyperlinks menu item. Insert a recommended PivotTable, using the Sum of Bonus by Employee option. - Correct Answers - In the Insert Ribbon Tab in the Tables Ribbon Group, you clicked the PivotTable button. You clicked cell D1. Inside the Create PivotTable dialog, you pressed Backspace. You clicked cell A1. Inside the Create PivotTable dialog, you clicked the OK button. In cell A10, enter a formula using OR to display TRUE if net profit before tax in 2016 (cell B5) are greater than 750000 (seven hundred, fifty thousand) or net profit before tax in 2016 (cell C5) are greater than 750000 (seven hundred, fifty thousand). Use cell references where appropriate and enter the arguments exactly as described in this question. - Correct Answers - You clicked the Insert Function launcher button. Inside the Insert Function dialog in the Or select a category drop-down, you selected All. Inside the Insert Function dialog from the Select a function list, you selected OR. Inside the Insert Function dialog, you clicked the OK button. Inside the Function Arguments dialog, you typed B5>750000 in the Logical1 input, typed C5>750000 in the Logical2 input, and clicked the OK button.
Apply the Offset Diagonal Bottom Right shadow effect to the shape. - Correct Answers - In the Format Ribbon Tab in the Shape Styles Ribbon Group, you clicked the Shape Effects button. In the Shape Effects menu in the Shadow menu, you selected the Offset: Bottom Right option. Apply conditional formatting to the selected cells so cells with a value greater than 400 are formatted using a light red fill. - Correct Answers - In the Home Ribbon Tab in the Styles Ribbon Group, you clicked the Conditional Formatting button. In the Conditional Formatting menu in the Highlight Cells Rules menu, you clicked the Greater Than... menu item. Inside the Greater Than dialog, you typed 400 in the Format cells that are GREATER THAN: input. Inside the Greater Than dialog in the with drop-down, you selected Light Red Fill. Inside the Greater Than dialog, you clicked the OK button. In cell D2, use CONCAT to combine the text from cell B2 with the text from cell C2, with a colon in between. The result of the formula should look like this: Rent:Parking - Correct Answers - You clicked the Insert Function launcher button. Inside the Insert Function dialog in the Or select a category drop-down, you selected All. Inside the Insert Function dialog from the Select a function list, you selected CONCAT. Inside the Insert Function dialog, you clicked the OK button. You clicked cell B2. Inside the Function Arguments dialog, you clicked the DialogHeader view, clicked the dialog undefined button. You clicked cell C2. Inside the Function Arguments dialog, you clicked the OK button. Sort the data first alphabetically by the values in the Category column and then by the dates in the Date column with the oldest dates first. - Correct Answers - [...] In the Home Ribbon Tab in the Editing Ribbon Group, you clicked the Sort & Filter button. You clicked cell B1, double-clicked in cell B1, double-clicked in cell B1, clicked cell B1, double-clicked in cell B1, clicked cell B2, clicked cell B1, double-clicked in cell B1, clicked cell B1, double-clicked in cell B1, clicked cell B2, double-clicked in cell B2, clicked cell D2, clicked cell B1, clicked cell B1, double-clicked in cell B1, double-clicked in cell B1, double-clicked in cell B1, clicked cell B1, double-clicked in cell B1, double- clicked in cell B1, double-clicked in cell B1, clicked cell B1, double-clicked in cell B1, double-clicked in cell B1, double-clicked in cell B1, clicked cell B1, double-clicked in cell B1, and clicked cell B1. In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Sort button. Inside the Sort dialog in the Sort By drop-down, you selected Category. Inside the Sort dialog, you clicked the Add level button. Inside the Sort dialog in the Sort By2 drop-down, you selected Date. Inside the Sort dialog, you clicked the OK button. Add column Sparklines to cells F2:F11 to represent the values in B2:E11. - Correct Answers - In the Insert Ribbon Tab in the Sparklines Ribbon Group, you clicked the Column button. Inside the Create Sparklines dialog, you typed F2:F11 in the Location Range input, clicked the OK button. Display the data table, including the legend keys. - Correct Answers - Click the Chart Elements button and click the Data Table check box.
Filter the data in place so that only rows where the Category value is Meals and the Cost value is greater than 20 are shown. The criteria range has been set up for you in cells A1:C2. - Correct Answers - In the Data Ribbon Tab in the Sort & Filter Ribbon Group, you clicked the Advanced button. You clicked cell A1. Inside the Advanced Filter dialog, you clicked the OK button. Insert a Radial Cycle SmartArt diagram. It is the second option in the third row of the Cycle category. Enter Net Profit as the text for the circle in the middle. Add Returns as the text for the top circle. Add Expenses as the text for the circle on the right. Add Salaries as the text for the bottom circle. Add Draws as the text for the circle on the left. When you are finished, Click outside the diagram. - Correct Answers - In the Insert Ribbon Tab in the Illustrations Ribbon Group, you clicked the SmartArt button. Inside the Choose a SmartArt Graphic dialog from the SmartArt Category list, you clicked the Cycle item. Inside the Choose a SmartArt Graphic dialog, you clicked the Radial Cycle image, clicked the OK button. You clicked the [Text] text, typed Net Profit, clicked the [Text] text, clicked the [Text] text, typed Returns, clicked the [Text] text, clicked the [Text] text, typed Expenses, clicked the [Text] text, clicked the [Text] text, typed Salaries, clicked the [Text] text, clicked the [Text] text, typed Draws, and clicked cell J13. Ungroup the grouped sheets. - Correct Answers - You right clicked the Dec 18 tab. In the Tab Right Click menu, you clicked the Ungroup Sheets menu item. Change the gridlines to use the Dash dash style. - Correct Answers - In the Format Ribbon Tab in the Shape Styles Ribbon Group, you clicked the Shape Outline button arrow. In the Shape Outline menu in the Dashes menu, you selected the Dash option. Create a new watch in the Watch Window for the selected cells. - Correct Answers - In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Watch Window button. Inside the Watch Window dialog, you clicked the Add Watch... button. Inside the Add Watch dialog, you clicked the Add button. In cell B9, enter a formula using NPV to calculate the present value of a payment plan with variable annual payments as shown in cells B11:B14. The interest rate is stored in cell B2. Use a cell range as a single Value argument. - Correct Answers - You clicked the Insert Function launcher button. Inside the Insert Function dialog in the Or select a category drop-down, you selected All. Inside the Insert Function dialog from the Select a function list, you selected NPV. Inside the Insert Function dialog, you clicked the OK button. You clicked cell B2. Inside the Function Arguments dialog, you typed B11:B14 in the Value1 input, clicked the OK button. Apply data validation rules to the selected cells to allow only decimal numbers between 2.5 and 4.0. Do not allow blanks. - Correct Answers - In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button arrow. In the Data Validation menu, you clicked the Data Validation... menu item. Inside the Data
name: list, you selected FormatPieChart. Inside the Macro dialog, you clicked the Edit button. You edited the macro code, clicked the Save button. Change the security setting to disable all macros and display a warning in the Message Bar so you can choose to enable them on a case-by-case basis. - Correct Answers - In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button, clicked the What-If Analysis button. In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button arrow. You opened the backstage view, clicked the Options navigation button, and clicked the Trust Center tab. Inside the Excel Options dialog, you clicked the Trust Center Settings... button. You clicked the Macro Settings tab. Inside the Trust Center dialog, you selected the Disable all macros with notification Radio Button, clicked the OK button. Inside the Excel Options dialog, you clicked the OK button. Add the Developer tab to the Ribbon. - Correct Answers - You opened the backstage view, clicked the Options navigation button, and clicked the Add-Ins tab. Inside the Excel Options dialog, you clicked the Go... button. Inside the Add-ins dialog, you clicked the Cancel button. You opened the backstage view, clicked the Options navigation button, and clicked the Customize Ribbon tab. Inside the Excel Options dialog, you checked the Developer check box, unchecked the Developer check box, checked the Developer check box, and clicked the OK button. Create a Forecast Sheet based on the selected data. Use a line chart and forecast values through 2018. - Correct Answers - In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the Forecast Sheet button. Inside the Create Forecast Worksheet dialog, you clicked the Create button. Run the FormatPieChart macro. - Correct Answers - In the Developer Ribbon Tab in the Code Ribbon Group, you clicked the Macros button. Inside the Macro dialog, you clicked the Run button. Add a calculated field to this PivotTable to calculate the total pay for each employee (=Salary+Bonus). Name the new field Total Pay. - Correct Answers - In the Analyze Ribbon Tab in the Calculations Ribbon Group, you clicked the Fields, Items, & Sets button. In the Fields, Items, & Sets menu, you clicked the Calculated Field... menu item. Inside the Insert Calculated Field dialog, you typed Total Pay in the Name input, typed = Salary+Bonus in the Formula input, and clicked the OK button. Add a form control button to run the CreateQ1Chart macro. The button should be placed at approximately cell A1. - Correct Answers - In the Developer Ribbon Tab in the Controls Ribbon Group, you clicked the Insert button. In the Insert menu, you clicked the Button (Form Control) button. You clicked the undefined view. Inside the Assign Macro dialog, you clicked the OK button. Check if this workbook contains elements that are not compatible with earlier versions of Excel. Close the Compatibility Checker without making any changes. - Correct
Answers - You opened the backstage view, clicked the Check for issues button. In the Protect Workbook menu, you clicked the Check Compatibility menu item. Inside the Microsoft Excel - Compatibility Checker dialog, you clicked the OK button. In cells D2:D20, use data validation to display an in-cell drop-down list of values from the Days named range. Do not allow blanks. - Correct Answers - In the Data Ribbon Tab in the Data Tools Ribbon Group, you clicked the Data Validation button arrow. In the Data Validation menu, you clicked the Data Validation... menu item. Inside the Data Validation dialog in the Allow drop-down, you selected List. Inside the Data Validation dialog, you typed =Days in the Source: input, unchecked the Ignore blank check box, and clicked the OK button. Use Solver to find the combination of procedures that will result in the maximum possible profit per week in cell B7. Use the range name Optimal as the variable cells. Add these constraints in order: values in the named range Optimal are less than or equal to the values in the named range Max; values in the named range Optimal are whole numbers; values in the named range Procedures are less than or equal to values in the named range MaxAllowed. Run Solver and accept the solution. - Correct Answers
- In the Data Ribbon Tab in the Analyze Ribbon Group, you clicked the Solver button. Inside the Solver Parameters dialog, you typed B7 in the Set Objective input, typed Optimal in the By Changing Variable Cells input, and clicked the Add button. Inside the Add Constraint dialog, you typed Optimal in the Cell Reference input, typed Max in the Constraint input, clicked the Add button, and typed Optimal in the Cell Reference input. Inside the Add Constraint dialog in the Operator drop-down, you selected int. Inside the Add Constraint dialog, you clicked the Add button, typed Procedures in the Cell Reference input, typed MaxAllowed in the Constraint input, and clicked the OK button. Inside the Solver Parameters dialog, you clicked the Solve button. Inside the Solver Constraints dialog, you clicked the OK button. Using the Scenario Manager, show the Low COLA scenario. Close the Scenario Manager when you are through. - Correct Answers - In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the Scenarios: list. Inside the Scenario Manager dialog from the Scenarios: list, you selected Low COLA. Inside the Scenario Manager dialog, you clicked the Show button, clicked the Show button, and clicked the Close button. Create a new scenario to reflect a change in cell B9 to a value of 0.01 Name the scenario Low COLA. - Correct Answers - In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the What-If Analysis button. In the What-If Analysis menu, you clicked the Scenario Manager... menu item. Inside the Scenario Manager dialog, you clicked the Add... button. Inside the Add Scenario dialog, you typed Low COLA in the Scenario name: input, clicked the OK button. Inside the Scenario Values dialog, you typed 0.01 in the text box input, clicked the OK button.
