YO16_XL_COMP_GRADER_AC_AS - Costumes 1.3

1

Open the downloaded yo_e_capstone_Costumes.xlsx workbook. Save it as yo_e_capstone_Costumes_LastFirst using your last and first name.

0

2

On the Sales Data worksheet, format the data in A10:K132 as a data table with headers. Apply Table Style Medium 4. Note, depending on the version of Office used, the style name may be White: Table Style Medium 4. Create named ranges from the table data, using the headings in A10:K10. 

5

3

Create an advanced filter to find all purchases in the table that take place after September 30, 2015 (>9/30/2015). Enter the appropriate criteria in cell A2 and filter the data in-place.

6

4

In cell D4, use the SUBTOTAL function to calculate the average number of Halloween costumes sold. Format with 0 decimals.

5

5

In cell D5, use the SUBTOTAL function to calculate the total number of Christmas costumes sold. Format with 0 decimals.

5

6

Insert a slicer for the Date field. Apply Slicer Style Other 1, format the slicer with 3 columns, and then resize and move the slicer so it covers range L1:P15 (set the slicer within the borders of these cells). Note, depending on the version of Office used, the style name may be White, Slicer Style Other 1. In the slicer, select all dates from 10/1/2015 through 10/31/2015.

Note, Mac users, on the Data tab, click the Filter button. In cell A10, click the Filter icon to select all October dates.

3

7

Using the data table, create a PivotTable on a new worksheet. Rename the worksheet PivotTable Analysis.

4

8

The Date field should be in the Rows area. Remove the automatic grouping for Months. Rename the label in cell A3 as Date. The quantity of costumes sold should be summed in the Values area as the Sum of Halloween and Sum of Christmas.

5

9

Rename the column labels as Halloween Costumes and Christmas Costumes

2

10

Grand totals for columns should be included. Filter the data so only the month of November displays. Apply Pivot Style Light 1 and Banded Rows formatting. Note, depending on the version of Office used, the style name may be White, Pivot Style Light 1.

4

11

In cell A1, type November Costume Sales. Merge and center A1:C1. Format as Cell Styles Title.

3

12

Click cell D3, and then type Chart for a new column heading. Apply the formatting from cell C3 to D3. Create line sparklines in cell range D4:D33 to chart the corresponding data in columns B:C. Include the high point and low point.

8

13

In cell B12 of the Pricing worksheet, insert a function that calculates the average price Carly charges for her costumes. Format as Accounting.

5

14

Create a scatter chart using the average retail price data (A1:B11). Apply Style 9, Color 3, and apply Data Labels to the right, displaying the X value. Note, depending on the version of Office used, the color name may be Colorful Palette 3.

9

15

Position the chart so that the top-left corner is set inside cell D1 and bottom-right corner is set inside cell N21. Add a Vertical (Y) axis label of Price and remove the Horizontal (X) axis if necessary.

8

16

On the Break-Even Analysis worksheet, in cell D9, enter a formula that calculates the gross revenue. In cell D14, enter a formula that calculates the total fixed costs. In cell D17, enter a formula that calculates the total variable costs. In cell D18, enter a formula that calculates the net income. Resize the column as needed.

5

17

Insert a scroll bar in E6:E18, use 5 as the minimum value, 100 as the maximum value, and the average price as the cell link. Use the scroll bar to find the break-even point for the average price when the average number of costumes sold per day is 10.

9

18

Use the data in the break-even analysis to complete the two-variable data table in range G4:K11. Format cell G5 so the cell reference to D18 will be hidden. Format the values as Accounting.

9

19

Apply Green-Yellow-Red color scale conditional formatting to the result values in the data table.

5

20

Ensure that the worksheets appear in this order: PivotTable Analysis, Sales Data, Pricing, Break-Even Analysis, and Documentation. Save the workbook and close Excel. Submit the file as directed.

0