BUSI 201 TEST 2 REVIEW

EXPLANATIONS ON EACH STEPS INCLUDED

 

Connect with a professional writer in 5 simple steps

Please provide as many details about your writing struggle as possible

Academic level of your paper

Type of Paper

When is it due?

How many pages is this assigment?

1. Click any of the data markers to select the entire Items Ordered data series

 

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

 

3. Insert a 3-D Clustered Bar   chart (the first chart type in the 3-D Bar section of the Insert   Column or Bar Chart menu).

4. Switch   the rows and columns in the chart, so the data points are   grouped into data series by PO Month.

5. Insert a 3-D pie chart based on the selected data.

6. Insert a Line with Markers chart based on the selected cells.

7. Move the selected chart to a new   chart sheet.

8. Apply the Style 5 Quick Style to the chart.

9. Apply the Colorful Palette 4 color scheme (the fourth option in the   Colorful section) to the chart.

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

11. Filter the chart so the Misc data series is hidden.

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

13. Convert the cell range to a table   using table style Table Style Light 10   (the third style in the second row under the Light styles). The table should   include headers.

14. Add a total row to this table and   display the maximum value for the Total Spent column.

15. Remove duplicate rows where data   in all the columns are identical.

16. Sort this data by the Date of Last Visit column so the   oldest dates are listed first.

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

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

19. Clear the filter from the State column.

20. Enable filtering.

21. Filter the State column so only rows with MD are shown.

22. Add slicers to filter the data in   this table by City.

23. Use a slicer to filter the data   in this table to show only rows where the State value is MD.

24. Convert the table to a normal range.

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

26. Insert a PivotChart using the   first bar chart type.

27. Complete the one-variable data   table in cells G2:H7 to calculate   the total investor payout for varying percentage rates. The formula has been   entered for you in cell H2. It   references the original investor payout percentage in cell B9. The substitute values have been   entered for you in cells G3:G7.

28. Complete the two-variable data   table in cells H3:K8. The formula   has been entered for you in cell H3.   The substitute values in cells I3:K3   reference the total expenses value in cell E5, and the substitute values in cells H4:H8 reference the original investor percentage in cell B9.

29. Use Goal Seek to find the value for cell B2 that will result in a value of $200 for cell B6.   Accept the solution.

30. Insert a clustered column-line combination chart based on the selected   data. The line chart should represent the Total   Sales data series. Both data series should be on the primary axis. This   is the first chart type in the Combo   Chart gallery.

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

32. 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.

33. 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).

34. The August Purchase Orders data point in the Gross Profit data series   has been selected. Change the outline width to 6 pt.

35. 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).

36. 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.

37. 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.

38. Change the gridlines to use the Long Dash dash style.

39. The chart area is selected. Apply   the Linear Up gradient fill (the   second gradient in the last row of the Light Variations section of the   gallery).(the second style from the left in the second row of the Light   Variations)

40. Create a chart from the selected   cells using the POChart chart   template.

41. Extend the trendline to forecast   forward two periods.

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

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

44. Change the Sparklines from   columns to lines.

45. Change the high point marker   color for the selected Sparkline to Light   Green (the fifth color from the left in the row of standard colors). The   Sparkline has already been ungrouped.

46. Ungroup the Sparkline in cell B8 so you can apply formatting to   just that Sparkline.

47. Add a Picture Organization Chart   SmartArt diagram to the worksheet. It is the second option in the first row   of the Hierarchy category.

48. Insert a Organization Chart SmartArt diagram. It is the first option in   the first row of the Hierarchy category. Enter Ken Dishner as the text for the top shape. When you are finished,   Click outside the diagram.

49. Apply the Cartoon SmartArt style to the diagram. It is the third option in   the 3-D section of the gallery.

50. Create and apply a new   conditional formatting rule for the selected cells D2:D26 to apply italic font formatting to only cells that are equal to or above the average for the   selected range.

51. Create and apply a new   conditional formatting rule for the selected cells E2:E26. Apply the default icon set Three traffic lights (unrimmed)   icon set, but show only the icon, not the cell value. Change the values so   the green circle icon (the first icon) will be applied if the cell value is   >= 80 percent and the yellow   circle icon (the second icon) will be applied if the cell value is <80 and   >=20 percent. Note that the   value type is set to percent by default.

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

53. 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.

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

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

56. Use a custom number filter to   show only rows where the quantity ordered (Ordered column) is greater than or equal to 18.

57. Filter the data in place so that   only rows where the PO # value is PO1104007   and the Received value is less than 25   are shown. The criteria range has been set up for you in cells A1:G2.

58. Collapse the entire outline to show   just the subtotals.

59. Create automatic subtotals to sum the values in the Received column for each change in   the Status column. The data have   been sorted for you.

60. In cell A2, 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.

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

62. The total row for each purchase   order uses a SUM function to total the number ordered and the number   received. Create an automatic outline from the rows in this data range.

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

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

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

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

67. Install the Solver add-in.

68. 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.

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

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

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

72. Add a report filter for the State field and use it to filter the   PivotTable to show only data where the value of the State field is MD.

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

74. Add a timeline to the PivotTable   to filter the data by values in the Date   of Last Visit field. Use the timeline to filter the PivotTable to show   only dates in September.

75. Apply the Style 3 Quick Style to the PivotChart.

76. Import data from the Employees table in the Staff database into a new worksheet.

77. Import data from the text file WageIncreases. Allow Excel to import   the data into a table in a new worksheet.

78. Use Flash Fill to autofill the   cells in this column. Start by entering the pattern $5,000 Merit in the   cell E2.

79. Split the selected text into   columns using the comma character   as the delimiter.

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

81. In cells D2:D13, use data   validation to display an in-cell drop-down list of values from the MedCodes named range. Allow blanks.

82. Add this comment to cell B4: Why did advertising increase?

83. Add a hyperlink from cell C11 in the Summary worksheet to link   to cell A7 in the Downtown worksheet. Don’t forget to   use A7 as the cell reference.

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

85. Protect this worksheet so users   can select but not edit locked cells. Users should be able to select and edit   any cells that are unlocked.

86. Unlock the cells B3:D8 so the user can edit the cells   when the worksheet is protected.

87. 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.

88. Save this workbook as a template.

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

90. Add the Developer tab to the   Ribbon.

91. Begin recording a new macro.

92. 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.