Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 13, 2024

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

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.

I Can't attach a file to Chegg, so I attached the picture to it.
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 +

Step by Step Solution

There are 3 Steps involved in it

Step: 1

Get Instant Access with AI-Powered Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

Step: 3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial accounting

Authors: Walter T. Harrison, Charles T. Horngren, William Bill Thomas

8th Edition

978-0136108863

Students also viewed these Accounting questions

Question

6. A tourist agency wants to know what proportion of vistors to the Eiffel Tower are from the Far East. To find out they survey 100 people in the line to purchase tickets to the top of the tower one...

Answered: 1 week ago

Question

★★★★★

In your community, some employers maintain stable workforces at all costs, and others furlough and recall workers seemingly at the drop of a hat. What are the differences in markets, management,...

Answered: 1 week ago

Question

★★★★★

Dorothy Koehl recently leased space in the Southside Mall and opened a new business, Koehl's Doll Shop. Business has been good, but Koehl frequently run out of cash. This has necessitated late...

Answered: 1 week ago

Question

★★★★★

#1. Vista Corp. bought a piece of new equipment at the beginning of the year at a cost of $32,500. The equipment's estimated useful life is 4 years and the residual value is $2,500. The estimated...

Answered: 1 week ago

Question

★★★★★

How is the NDAA used to shape defense policies indirectly?

Answered: 1 week ago

Question

★★★★★

Flu cases per school this past flu season in the Remulak school system *(n = 500) were 15 per week. For the entire state, the weekly average was 16, and the standard deviation was 15.1. Are the kids...

Answered: 1 week ago

Question

★★★★★

John and Susan, both age 35, are married, and each own 35% of an S corporation for which they perform services as employees. The company had $30,000 in ordinary business income (John and Susan both...

Answered: 1 week ago

Question

★★★★★

What are the requirements outlined in the Deloitte Policy Manual, Section 1553 intended to accomplish? Protect the general public, as well as investors, creditors, and Deloitte employees Ensure even...

Answered: 1 week ago

Question

★★★★★

Using your knowledge of navigating the global environment and your analysis of Walmart's experience as described in the Management at Work article, answer the questions posed. You begin by reminding...

Answered: 1 week ago

Previous Question Next Question

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.