cs assignment 8

Display the details for just the 1/3/2017 group.

HINT: Click the + symbol next to row 14.

Collapse the first group so the details for 3/1/2017 are hidden.

HINT: Click the - symbol next to row 5.

Collapse the entire outline to show just the subtotals.

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

The previous filter has been cleared for you. Use a custom text filter to show only rows where the customer last name (Last Name column) begins with the letter C.

HINT: Click the AutoFilter arrow at the top of the Last Name column, and point to Text Filters. Click Begins With... Type C in the box next to begins with. Click OK.

Now filter the data to show only cells with the light red background in column C (the Salon Services column).

HINT: Click the AutoFilter arrow at the top of the column. Point to Filter by Color and click the light red color under Filter by Cell Color.

Edit the Data Bar rule and change the data bar fill to a gradient. Save the conditional formatting changes

HINT: Click the Data Bar rule. Click the Edit Rules... button. Click the Fill arrow and select Gradient Fill. Click OK. Click OK.

Delete the Equal to or above average rule.

HINT: Click the Equal to or below average rule. Click the Delete Rule button.

Install the Solver add-in.

HINT: Click the File tab. Click Options. Click Add-Ins. Click Go... Click the Solver Add-in check box. Click OK.

Add a report filter for the Item field and use it to filter the PivotTable to show only data where the Item value is Bronzing gel.

HINT: Click the Item field in the PivotTable Fields pane and drag it to the Filters box in the bottom part of the pane. Click the filter arrow in cell B1. Click Bronzing Gel. Click OK.

Use Solver to find the combination of units sold for each product that will result in the maximum profit in cell B1. Use the range name UnitsSold as the variable cells, and then open the Add Constraints dialog.

HINT: On the Data tab, in the Analysis group, click the Solver button. Type B1 in the Set Objective box. (The Max radio button is selected by default.) Type UnitsSold in the By Changing Variable Cells box, and then click the Add button.

Open the Consolidate dialog and use the Sum function to consolidate prices for the named range ServicesSold. Include both the top row and left column labels. The named range ServicesSold has been defined for you for cells C17:D49.

HINT: On the Data tab, in the Data Tools group, click the Consolidate button. In the Consolidate dialog, the Sum function is already selected. Type ServicesSold in the Reference box. Click the Top row check box. Click the Left column check box. Click OK.

Create a Forecast Sheet based on the selected data. Use a line chart and forecast values through July 1, 2017.

HINT: On the Data tab, in the Forecast group, click the Forecast Sheet button. Click the Create button.

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

HINT: On the Data tab, in the Forecast group, click the What-If Analysis button, and click Scenario Manager. Click the Summary button. Click OK.

Create a new scenario named 5% Tax to change the value of cell B1 to 0.05. Note that in the Scenario Values dialog, the cell name Tax is displayed instead of the cell address B1.

HINT: On the Data tab, in the Forecast group, click the What-If Analysis button, and then click Scenario Manager. Click the Add button. Type 5% Tax in the Scenario name box. Click OK. Type 0.05 in the text box. Click OK.

Create an automatic outline from the rows in this data range.

HINT: On the Data tab, in the Outline group, click the Group button arrow, and click Auto Outline.

The data have been sorted by appointment date in the Appt Date column. Create automatic subtotals to sum the price for each date change in the Appt Date column.

HINT: On the Data tab, in the Outline group, click the Subtotal button. Expand the At each change in list and select Appt Date. Expand the Use function list and select Sum. Click the Price check box to add a checkmark. Click the Location check box to remove the checkmark. Click OK.

Filter the data in place using the list range A4:H36. The criteria range is A1:H2.

HINT: On the Data tab, in the Sort & Filter group, click the Advanced button. Enter A1:H2 in the Criteria range box. Click OK.

Use a custom date filter to show only rows where the appointment date (Appt Date column) is after 1/10/2017.

HINT: 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 Date column, and point to Date Filters. Click After... Type 1/10/2017 in the box next to is after. Click OK.

The previous filter has been cleared for you. Use a custom number filter to show only rows where the service price (Price column) is greater than or equal to 90.

HINT: 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 Price column, and point to Number Filters. Click Greater Than Or Equal To... Type 90 in the box next to is greater than or equal to. Click OK.

Sort the data so cells with the yellow circle icon (the last icon in the list) in column F (the second Daily Total column) appear on top.

HINT: 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 yellow circle icon.

Sort the data first alphabetically by the values in the Top Seller column and then by the dates in the Date column with the oldest dates first.

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

Create a new watch in the Watch Window for the selected cells.

HINT: On the Formulas tab, in the Formula Auditing group, click the Watch Window button. Click Add Watch. Click Add.

Open the Conditional Formatting Rules Manager. Show the all the formatting rules in this worksheet. Apply the Stop If True option to the Bottom 25% rule so cells with a value in the bottom 25% will not have the Data Bar rule applied to them.

HINT: On the Home tab, in the Styles group, click the Conditional Formatting button. Click Manage Rules... Expand the Show formatting rule for list, and select This Worksheet. Click the Stop If True check box next to the Bottom 25% rule.

Create and apply a new conditional formatting rule for the selected cells F3:F33. 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 >= 75 percent and the yellow circle icon (the second icon) will be applied if the cell value is <75 and >=25 percent.

HINT: On the Home tab, in the Styles group, click the Conditional Formatting button. Click New Rule. Expand the Format Style list and select Icon Sets. Click the Show Icon Only check box. Type 75 in the first value box. Type 25 in the second value box. Click OK.

Create and apply a new conditional formatting rule for the selected cells E3:E33 to apply bold font formatting to only cells that are equal to or below the average for the selected range.

HINT: On the Home tab, in the Styles group, click the Conditional Formatting button. Click New Rule. In the Select a Rule Type box, click Format only values that are above or below average. Expand the Format values that are list and select equal or below. Click the Format button. In the Format Cells dialog, on the Font tab, click Bold. Click OK. Click OK.

Add a slicer to this PivotChart for the PO # field and show only data where the PO # value is PO1104001 (the first button in the slicer).

HINT: On the PivotChart Tools Analyze tab, in the Data group, click the Insert Slicer button. Click the PO # check box. Click OK. In the PO # slicer, click the PO1104001 button.

Apply the Style 5 Quick Style to the PivotChart.

HINT: On the PivotChart Tools Design tab, in the Chart Styles group, click Style 5. It is the fifth option in the Quick Styles gallery.

Change the PivotChart type to the first line chart option.

HINT: On the PivotChart Tools Design tab, in the Type group, click the Change Chart Type button. Click Line. Click OK.

Add a calculated field to this PivotTable to summarize the difference between values in the Ordered field and the Received field (=Ordered-Received). Name the calculated field Difference.

HINT: On the PivotTable Tools Analyze tab, click the Calculations button, and then click Fields, Items, & Sets. Select Calculated Field... In the Insert Calculated Field dialog, type Difference in the Name box. In the Formula box, delete the 0 and type Ordered-Received. Do not delete the equals sign. Click OK.

Update the data source for the PivotTable to use the table named JulyTable.

HINT: On the PivotTable Tools Analyze tab, in the Data group, click the Change Data Source button. Type JulyTable in the Table/Range box. Click OK.

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

HINT: On the PivotTable Tools Analyze tab, in the Data group, click the Refresh button.

Add a slicer to the PivotTable for the Item and Date Expected fields. Use the slicer to filter the data further to show only data where the Date Expected value is 8/12/2017.

HINT: On the PivotTable Tools Analyze tab, in the Filter group, click the Insert Slicer button. In the Insert Slicers dialog, click the check boxes for the Item and Date Expected, and then click OK. Click 8/12/2017 in the Date Expected slicer.

Add a timeline to the PivotTable to filter the data by values in the DateExpected field. Use the timeline to filter the PivotTable to show only dates in August.

HINT: On the PivotTable Tools Analyze tab, in the Filter group, click the Insert Timeline button. In the Insert Timeline dialog, click the check box for the DateExpected field. Click OK. Click the August segment on the timeline.

Add grouping by month and quarter to this PivotTable.

HINT: On the PivotTable Tools Analyze tab, in the Group group, click the Group Field button. In the By box, click Quarters. (Months is already selected.) Click OK.

Apply the Pivot Style Light 14 Quick Style to the PivotTable.

HINT: On the PivotTable Tools Design tab, in the PivotTable Styles group, click the More button to expand the Quick Styles gallery, and select Pivot Style Light 14.

Add a constraint to limit values in the OrderWeight named range to be less than or equal to the maximum weight for the order as defined in the MaxWeight named range.

HINT: Type OrderWeight in the Cell Reference box. Type MaxWeight in the Constraint box. Click Add.

A criteria range has been set up for you in cells A1:H2. Enter criteria in the criteria range to show only rows where the Last Name is Smith and the Price is greater than 75 and then click any cell in the list range.

HINT: Type Smith in cell C2. Type >75 in cell F2. Click anywhere in cells A4:H36.

Add a constraint to limit values in the UnitsSold named range to be whole numbers only.

HINT: Type UnitsSold in the Cell Reference box. Expand the drop-down list in the middle box and select Int. Click OK.

Add a constraint to limit values in the UnitsSold named range to be less than or equal to the values in the Stock named range.

HINT: Type UnitsSold in the Cell Reference box. Type Stock in the Constraint box. Click Add.