1	Open the downloaded CAP2_Costumes.xlsx workbook. Save it as CAP_Costumes_LastFirst using your last and first name.
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.
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.
4	In cell D4, use the SUBTOTAL function to calculate the average number of Halloween costumes sold. Format with 0 decimals.
5	In cell D5, use the SUBTOTAL function to calculate the total number of Christmas costumes sold. Format with 0 decimals.
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.
7	Using the data table, create a PivotTable on a new worksheet. Rename the worksheet PivotTable Analysis.
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.
9	Rename the column labels as Halloween Costumes and Christmas Costumes
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.
11	In cell A1, type November Costume Sales. Merge and center A1:C1. Format as Cell Styles Title.
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.
13	In cell B12 of the Pricing worksheet, insert a function that calculates the average price Carly charges for her costumes. Format as Accounting.
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.
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.
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.
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.
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.
19	Apply Green-Yellow-Red color scale conditional formatting to the result values in the data table.
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.

L N B6 fx A B D E F G H 1 K 1 Date Stars Stripes Rainbow Concert Football Baseball Hockey Basketball Halloween Christmas 2 3 4. Halloween Avg| 5 Christmas Total 6 7 8 Carly's Costume Shoppe 9 4th Quarter Sales 10 Date Stars Stripes Rainbow Concert Football Baseball Hockey Basketball Halloween Christmas 11 9/1/2015 12 4 22 8 1 0 2 12 9/2/2015 3 3 3 3 3 3 13 9/3/2015 1 1 1 1 1 1 1 14 9/4/2015 7 2 2 2 2 2 2 15 9/5/2015 5 8 5 5 5 5 5 16 9/6/2015 1 1 1 1 1 1 17 9/7/2015 8 1 9 8 1 0 1 18 9/8/2015 3 3 13 3 3 3 2 9/9/2015 1 Sales Data Pricing | Break-Even Analysis Documentation + 3 O O O C 1 19 1 1 1 1 1 4 # 2 2 2 2 2 4 4 4 0 22 1 1 1 1 1 1 1 1 0 33 51 2 2 2 2 2 2 2 2 0 3 3 3 3 3 3 0 46 3 1 3 1 1 1 1 1 1 1 0 4 4 4 4 4 4 4 4 0 21 51 33 5 5 5 5 5 5 5 0 118 12/17/2015 119 12/18/2015 120 12/19/2015 121 12/20/2015 122 12/21/2015 123 12/22/2015 124 12/23/2015 125 12/24/2015 126 12/25/2015 127 12/26/2015 128 12/27/2015 129 12/28/2015 130 12/29/2015 131 12/30/2015 132 12/31/2015 5 1 7 1 1 1 1 1 1 0 1 1 7 7 7 7 7 7 7 7 2 2 2 2 2 2 2 2 3 OOO 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 4 0 2 3 3 3 3 3 3 3 0 5 17 3 1 1 1 1 7 9 11 1 ood 4 6 8 N 2 4 7 9 2 9 133 D A B Holiday Average 1 Season Retail Price Stars $ 39.95 3 Stripes $ 39.95 1 Rainbow $ 19.95 5 Concert $ 29.95 5 Football $ 49.95 7 Baseball $ 29.95 B Hockey $ 59.95 Basketball $ 34.95 0 Halloween $ 307.00 1 Christmas $ 102.99 2 Average 3 4 B C D E F G H 1 Carly's Costume Shoppe Monthly Break-Even Analysis 2 3 # of Costumes 4 Price 50 $ $ 25 $ 75 $ 100 5 Average Price 10 15 20 25 30 5 Revenue 6 Average Number of Costumes (per day) 5 7 Days of Operations per Year 300 8 $30.00 9 Gross Revenue 10 Expenses 11 Fixed Costs 12 Equipment $27,812.00 13 Insurance $37,500.00 14 Total Fixed Costs 15 Variable Costs 16 Labor expense (per day) $300.00 17 Total Variable Costs 18 Net Income 19 20 Sales Data Pricing Break-Even Analysis Documentation C15 1 X fx Creator Description A B 1 Create Date By Whom 2 7/31/2021 Firstname Lastname 3 Mod. Date By Whom 4 mm/dd/yyyy 5 D Workbook Name yo_e_capstone_Costumes.xlsx Last Version Backup Name VERSION BACKUP NAME : Before modifying any worksheet, save the original workbook with the following name format: Original name_yyyymmdd 6 7 8 9 10 11 12 13 14 15 Create Date Sheet Name 16 Creator Purpose 17 18 19 20 21 22 Sales Data Pricing Break-Even Analysis Documentation + L N B6 fx A B D E F G H 1 K 1 Date Stars Stripes Rainbow Concert Football Baseball Hockey Basketball Halloween Christmas 2 3 4. Halloween Avg| 5 Christmas Total 6 7 8 Carly's Costume Shoppe 9 4th Quarter Sales 10 Date Stars Stripes Rainbow Concert Football Baseball Hockey Basketball Halloween Christmas 11 9/1/2015 12 4 22 8 1 0 2 12 9/2/2015 3 3 3 3 3 3 13 9/3/2015 1 1 1 1 1 1 1 14 9/4/2015 7 2 2 2 2 2 2 15 9/5/2015 5 8 5 5 5 5 5 16 9/6/2015 1 1 1 1 1 1 17 9/7/2015 8 1 9 8 1 0 1 18 9/8/2015 3 3 13 3 3 3 2 9/9/2015 1 Sales Data Pricing | Break-Even Analysis Documentation + 3 O O O C 1 19 1 1 1 1 1 4 # 2 2 2 2 2 4 4 4 0 22 1 1 1 1 1 1 1 1 0 33 51 2 2 2 2 2 2 2 2 0 3 3 3 3 3 3 0 46 3 1 3 1 1 1 1 1 1 1 0 4 4 4 4 4 4 4 4 0 21 51 33 5 5 5 5 5 5 5 0 118 12/17/2015 119 12/18/2015 120 12/19/2015 121 12/20/2015 122 12/21/2015 123 12/22/2015 124 12/23/2015 125 12/24/2015 126 12/25/2015 127 12/26/2015 128 12/27/2015 129 12/28/2015 130 12/29/2015 131 12/30/2015 132 12/31/2015 5 1 7 1 1 1 1 1 1 0 1 1 7 7 7 7 7 7 7 7 2 2 2 2 2 2 2 2 3 OOO 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 4 0 2 3 3 3 3 3 3 3 0 5 17 3 1 1 1 1 7 9 11 1 ood 4 6 8 N 2 4 7 9 2 9 133 D A B Holiday Average 1 Season Retail Price Stars $ 39.95 3 Stripes $ 39.95 1 Rainbow $ 19.95 5 Concert $ 29.95 5 Football $ 49.95 7 Baseball $ 29.95 B Hockey $ 59.95 Basketball $ 34.95 0 Halloween $ 307.00 1 Christmas $ 102.99 2 Average 3 4 B C D E F G H 1 Carly's Costume Shoppe Monthly Break-Even Analysis 2 3 # of Costumes 4 Price 50 $ $ 25 $ 75 $ 100 5 Average Price 10 15 20 25 30 5 Revenue 6 Average Number of Costumes (per day) 5 7 Days of Operations per Year 300 8 $30.00 9 Gross Revenue 10 Expenses 11 Fixed Costs 12 Equipment $27,812.00 13 Insurance $37,500.00 14 Total Fixed Costs 15 Variable Costs 16 Labor expense (per day) $300.00 17 Total Variable Costs 18 Net Income 19 20 Sales Data Pricing Break-Even Analysis Documentation C15 1 X fx Creator Description A B 1 Create Date By Whom 2 7/31/2021 Firstname Lastname 3 Mod. Date By Whom 4 mm/dd/yyyy 5 D Workbook Name yo_e_capstone_Costumes.xlsx Last Version Backup Name VERSION BACKUP NAME : Before modifying any worksheet, save the original workbook with the following name format: Original name_yyyymmdd 6 7 8 9 10 11 12 13 14 15 Create Date Sheet Name 16 Creator Purpose 17 18 19 20 21 22 Sales Data Pricing Break-Even Analysis Documentation +