2023 For this assignment you will work with a workbook full of sales data You | Assignment Collections

For this assignment, you will work with a workbook full of sales data. You will practice many of the skills covered in Unit 3 and the previous units. As you navigate the workbook, note that columns B, C, D, and E are hidden—you won’t need to make them visible to complete the assignment.

Download the File

Download and save the file Excel 2013 Assignment 3 from the BA101 Moodle site. For more instructions on how to download files, review How To Download a File.

Edit the File

Open the file you just saved. (You may have to click Enable Editing in order to start editing the document.) Complete the steps below in order, one at a time. Please make only the changes listed in the instructions. When you’re done, save the file and email to Ruderman at rudermc@linnbenton.edu 

Instructions

Select cell F3 and then click the Subtotal command on the Data tab. Select the following options in the dialog box that appears:

At each change in: Sales Region

Use function: Sum

Add subtotal to: Total Units Sold

HINT: Make sure to uncheck (Column M). Do not uncheck anything else.

HINT: Be sure that there is a check mark in the box that says Summary Below Data and Replace Current Subtotalswhich are both default settings.

HINT: When you’re done, a subtotal will appear in column K for each region (East, North, South, West).

Select cell range A2:L23. Next, format the data as a table.

HINT: Make sure that the My table has headers box is checked when inserting the table.

Select cell L4. From here, we can tell that this salesperson sold 140 units per quarter, on average. If you look in the formula bar, you can see that this value is determined by a function that averages the values in cell range G4:J4.

Imagine that this salesperson wanted to increase his average to 150 units per quarter. We could use Goal Seek (a type of what-if analysis) to find out how many more units he would need to sell in Quarter 4 to increase his average.

Use Goal Seek to set the value of cell L4 to 150 by changing cell J4.

Add a new column to the right of the existing columns in the table. When you’re done, column M should be included in the table. Then, select cell M2 and type “Yearly Trends”.

Select cell M3. Next, insert a Line sparkline in the cell. Use cell range G3:J3 as the source data for the Data Range.

Select cell M3 and drag the fill handle all the way to cell M21. When you’re done, sparklines should appear for each salesperson.

Select cell range F25:J25. Next, insert a column chart that uses the 2-D clustered column chart style.

HINT: Hover the mouse over the charts to see the chart name.

Select the chart and apply any chart style from the Chart Styles group on the Chart Tools Design tab. Do not keep the chart set to the default style.

Move the chart to a new worksheet, then select the Quarterly Sales Data worksheet.

Select cell range G3:J6. Next, apply the following conditional formatting rule to the cell range:

Highlight Cells that are Greater Than the value 150 with Green Fill with Dark Green Text.

HINT: When you’re done, any cell in cell range G3:J6 with a value higher than 150 will have a green fill and dark green text.

Select cell A2 and then click the PivotTable command on the Insert tab. In the dialog box that appears, click OK.

The PivotTable will appear on a new worksheet. Rename the worksheet to PivotTable.

In the PivotTable Fields List, click the checkboxes next to Salesperson and Total Units Sold. When you’re done, the PivotTable should show the total number of units sold by each salesperson.

In the PivotTable, click the Row Labels filter drop-down arrow, scroll to the bottom of the list and uncheck the box next to (blank). This will remove the subtotals we added at the beginning of the assignment from the PivotTable.

With the PivotTable selected, insert a slicer that uses the Sales Region field. When you’re done, you should be able to use the slicer to quickly switch between the total number of units sold in each sales region.