SIMNET Exam 1 Practice
Apply the Accounting Number Format to the selected cells.
$ under "General"
Edit the formula in cell D2 so the references to cell C2 will update when the formula is copied, and the reference to cell B9 will remain constant. Use AutoFill to copy the formula to cells D3:D6.
=C2+(C2+$B$9)
Apply the Short Date format to the selected cells.
Arrow next to general, short date
Set the print area so only the selected cells will print.
Back, page layout, page setup group, print area, set print area
Add the Profit-Sharing field to the PivotTable.
Check profit sharing from table on right
Click any of the data markers to select the entire Gross Profit data series.
Click a column
Remove the file from Protected View so you can edit it.
Click enable editing
Insert a 3-D Clustered Column chart (the first chart type in the 3-D Column section of the Insert Column or Bar Chart menu).
Click the Insert tab. In the Charts group, click the Insert Column or Bar Chart button. Click the 3-D Clustered Bar chart type.
Create a 3-D pie chart from the selected data.
Click the Insert tab. In the Charts group, click the Insert Pie Chart button. Select the 3-D Pie chart type.
Use the Quick Analysis tool to calculate totals for the selected cells. Insert the totals in the empty row below the cell range.
Click the Quick Analysis tool button and then click the Totals tab. Click the first Sum button.
Use Format Painter to copy the formatting from cell D1 and apply it to cell E1.
Click the paintbrush, click E1
Apply the Double Accounting underline format to the selected cells.
Click the small arrow in the bottom right corner of the section with underline. In the underline section, expand the Underline list, select double accounting.
Remove duplicate rows where data in all the columns are identical.
Data tab, data tools group, remove duplicates
Sort this data alphabetically from A to Z by values in the Category column.
Data tab, sort and filter group, a to z
Clear the filter from the Category column.
Data tab, sort and filter group, clear
Use a slicer to filter the data in this table to show only rows where the Category value is Coffee or Meals.
Design > Tools > Insert Slicer > Category checkbox > Click multiselect > select everything EXCEPT Coffee/Meals
Use AutoFill to copy the formula and formatting in cell E2 to cells E3:E6.
Drag bottom right corner down
Replace all instances of the word Coffee in this worksheet with Office Supplies. Do not replace them one at a time. Close the dialog when you are finished.
Find and replace, find coffee, replace with office supplies, replace all
Enter a formula in cell D2 to find the four digit year by extracting the last four digits of the sale date in cell A2. Use cell references where appropriate.
Formula, text, right, A2 in text, 4 in num_chars
In cell E15, enter a formula to find the highest line item cost this month (cells E2:E14).
Formulas tab, function library group, autosum arrow, max, enter
Insert the current date in cell A1. Do not include the current time.
Formulas, date and time, today
Open the Evaluate Formula dialog to analyze the formula in this cell.
Formulas, formula auditing group, evaluate formula
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 100 if it is not.
Formulas, function library group, logical, if, enter B9>470000 in logical_test box. Enter 35000 in Value_if_true box. Enter 100 in Value_if_false box.
In cell D7, enter a formula using the function for the Declining balance depreciation method. Use the cell names Cost and Salvage for the Cost and Salvage function arguments. Use the appropriate cell reference for the Life argument. Use the appropriate cell reference for the Period argument. Be sure to use the appropriate cell reference types to allow you to copy the formula to the rest of the column. The asset will be in use in year 1 for 5 months.
Formulas, function library, financial, DB. Cost is cost, Salvage is salvage, $B$3 is life, A7 is period, 5 is month
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.
Formulas, function library, financial, NPV, B2 in rate box, B11:B14 in Value1
In cell B7, enter a formula using the function for the Straight-line depreciation method. Use the cell names Costand Salvage for the Cost and Salvage function arguments. Use the appropriate cell reference for the Life argument. Be sure to use the appropriate cell reference type to allow you to copy the formula to the rest of the column.
Formulas, function library, financial, SLN, Cost is cost, Salvage is salvage, $B$3 is life, ok
Enter a nested function in cell F9 using INDEX and MATCH to find the ending balance for the date listed in cell C8. Use the named range Schedule to reference the cell range for the schedule of loan payments. The Ending Balancecolumn is column 6. In the INDEX Row_num function argument, use MATCH to look up the row number for the date listed in C8. Use the named range PayDates to reference the cell range for the Payment Date column. Require an exact match.
Formulas, function library, lookup and reference, index, first option, ok. Array box is Schedule. Column_num is 6. Click Row_num, click arrow for the name box, click match. Lookup_value is C8, Lookup_array is PayDates, Match_type is 0, ok.
Enter a formula in cell F2 to find the averaged rank of the value in cell C4 compared to the values in cells C2:C13.
Formulas, function library, more functions, statistical, RANK.AVG. Number argument box is C4. Ref is C2:C13, ok
Enter a formula in cell B3 using the VLOOKUP function to find the meaning for the medical abbreviation listed in cell A3. Use the name Abbreviation for the lookup table. The item names are located in column 2 of the lookup table. Be sure to require an exact match.
Formulas, lookup, lookup. Lookup_value is A3. Table_array is Abbreviation. Col_index_num is 2. Range_lookup is False
Enter a formula in cell D5 to calculate B5/B4 rounded to 4 decimal places.
Formulas, math and trig, round, b5/b4 in top, 4 in num_chars
Use the Function Arguments dialog to enter a SUM function in cell B7 to calculate the total of cells B2:B6.
Formulas, math and trig, sum, B2:B6
Edit the CurrentSalaries named range so it refers to cells B2:B6 on the Salaries worksheet. Close the Name Manager when you are finished.
Formulas, name manager, current salaries, change last number to 6
Preview how this worksheet would print with formulas showing instead of calculated values
Formulas, show formulas
Enter a formula in cell B7 to display the text from cell A7 in all lower case letters.
Formulas, text, lower, A7
Add a footer that displays just the page number in the center section. Do not include the word Page.
Header and footer, page number
Insert a column to the left of column E.
Home tab, cells group, insert arrow, insert sheet columns
Enter a formula in cell B7 to calculate the average value of cells B2:B6.
Home tab, editing group, autosum button arrow, average, enter
Clear the conditional formatting rules from the worksheet.
Home, conditional formatting, clear rules, clear rules from entire sheet
Apply conditional formatting to the selected cells using the Three Traffic Lights (Unrimmed) icon set (the first icon set in the Shapes section).
Home, conditional formatting, icon sets, first set in shapes
Hide the TimeSheets worksheet.
Home, format, (under delete?) hide and unhide, hide sheet
Delete the selected cell range, shifting up the cells below the range.
Home, styles section, delete, delete sheet rows
Complete the one-variable data table in cells E3:F8 to calculate the breakeven sales point for varying owner withdrawal amounts. The formula has been entered for you in cell E3. It references the original owner withdrawal value in cell B12. The substitute values have been entered for you in cells E4:E8.
In the Data Ribbon Tab in the Forecast Ribbon Group, you clicked the Data Table.... menu item. Inside the Data Table dialog, you typed B12 in the Column input cell input, you clicked the OK button.
Complete the two-variable data table in cells A7:E12. The formula has been entered for you in cell A7. The substitute values in cells B7:E7 reference the original cost of goods percentage in cell B3, and the substitute values in cells A8:A12 reference the original owner withdrawal percentage in cell B4.
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 Data Table... menu item. Inside the Data Table dialog, you typed B3 in the Row input cell input, typed B4 in the Column input cell input, and 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
In the Formulas Ribbon Tab in the FunctionLibrary Ribbon Group, you clicked the Textbutton. In the Text menu, you clicked the CONCAT menu item. Inside the FunctionArguments dialog, B2 in Text 1, : in Text 2, C2 in Text 3
Use AutoSum to enter a formula in the selected cell to calculate the total of all the contiguous cells above.
In the Home Ribbon Tab in the Editing Ribbon Group, you clicked the AutoSum button arrow. In the AutoSum menu, you clicked the Sum menu item. You pressed Enter.
Convert the cell range to a table using table style Table Style Light 8 (the first style in the second row under the Light styles). The table should include headers.
In the Home Ribbon Tab in the Styles Ribbon Group, you clicked the Format as Table button. In the Format as Table menu, you selected the Table Style Light 8 option. Inside the Format As Table dialog, you clicked the OK button.
Add slicers to filter the data in this table by Date.
In the design ribbon tab in the tools ribbon group, you clicked the insert slicer button. inside the insert slicer dialog you checked the data check box then clicked ok
Switch the rows and columns in the chart, so the data points are grouped into data series by year.
In the design ribbon tab you clicked the switch row/column button
Apply conditional formatting to the selected cells so cells with a value greater than 400 are formatted using a light red fill.
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 mine 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 dialoging in the with drop down you selected light red fill. inside the greater than dialog you clicked the ok button
Use a single command to clear everything (content and formatting) from the selected cell.
On the Home tab, in the Editing group, click the Clear button. Select Clear All.
Insert a recommended PivotTable, using the Sum of Bonus by Employee option.
On the Insert tab, in the Tables group, click the Recommended PivotTables button. Click the Sum of Bonus by Employee option. Click OK.
Modify the Bonus field to use the MAX function instead of the SUM function.
On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button. Select Max. Click OK.
Convert the table to a normal range.
On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes.
Enter a formula in the selected cell using SUMIF to calculate the total expenses for the category Office Expense. Use the range name Category for the Range argument, the text string "Office Expense" for the Criteria argument, and Cost for the Sum_range argument.
On the formula tab, in the function library group, click the Math & Trig button. click SUMIF. in the range argument box, type category. in the criteria box, type "office expense". in the sum range argument box, type cost. click ok
From Page Break Preview view, insert a page break immediately above the row 12.
Page layout, page setup group, breaks, insert page break
Clear the print area.
Page layout, print area, clear print area
Without adjusting the column widths, guarantee that all columns will print on one page.
Page layout, scale group, next to width click the arrow in automatic, 1 page
Add column Sparklines to cells F2:F11 to represent the values in B2:E11.
Quick Analysis button, Sparklines, Column
Copy the Salaries worksheet to a new workbook.
Right-click the Salaries sheet tab, and select Move or Copy... Expand the To book list and select (new book). Click the Create a copy check box. Click OK.
Navigate to the second sheet in the workbook.
Second sheet on the bottom
Click the Name Box.
Small box next to large formula line
Add a total row to this table.
Table design tab, table style options group, click total row
Enter a formula in cell H1 to calculate the mean of the differences in cells E2:E13.
Type in =AVERAGE(E2:E13)
Modify the worksheet so the first row is always visible when you scroll the worksheet down.
View, freeze panes, freeze top row
Modify this worksheet so the gridlines do not show.
View, show group, uncheck gridlines
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.
View, split
This worksheet has been split into four panes. Return the worksheet to a single view.
View, split
Save the file to a new folder inside the Documents folder on the computer. Name the new folder Marketing. Name the file BusinessPlanUpdated.
With your document open, click File > Save As.Under Save As, select where you want to create your new folder. ...In the Save As dialog box that opens, click New Folder.Type the name of your new folder, and press Enter. ...Click Save.
Insert a Line chart based on the first recommended chart type.
You clicked the Quick Analysis Tool button, clicked the Charts tab header, and clicked the Line button.
In cell E2, enter a formula using TEXTJOIN to combine the text from cells B2:D2 into a single text string. Use the #character as the delimiter. Ignore blank cells. Use a cell range as the Text1 argument.
click on formulas tab--text-TEXTJOIN--enter # in delimiter--enter B2:D2 in text1--click ok
