Capstone assignment( Admin2390- Capstone Experience for APT

Excel Independent Challenge 2

Create the worksheet (name it DATA) shown below. Wait to create the Pivot Table until

instructed to do so.

You work for Brain Trust, a home-based business that creates and manufactures 3-D

printed toys for all age groups. The Excel spreadsheet just created shows the category,

related age-group, inventory quantity, and sales price for each item.

1. Apply Data Bars (Hint: Conditional Formatting) to the Total column. (Your choice

of color).

2. Apply Conditional Formatting of your choice to the Age Group Column so that it

is easy to see each Age Group.

3. Insert a new worksheet into your workbook. Name it REPORT..

4. Create the illustrated Pivot Table sorting on Category and Total.

5. Format the Totals in the Pivot Chart as Accounting.

6. Change the Quantity of STEM Card Sets to 500 and Foam and Foam and Clay

Kit to 100.

7. Apply Data Bars (Hint: Conditional Formatting) to the Total column. (Your choice

of color).

8. Apply Conditional Formatting of your choice to the Age Group Column so that it

is easy to see each Age Group.

9. Create each of the graphs described below and put on a separate worksheet

labeled using the “letter” shown beside the description. In other words, you will

have a worksheet labeled “a”, “b”, etc. You will add a descriptive title to each

chart and a legend of your choosing.

a. Create a Pie Chart that shows each Category as a Percentage of Total

Sales.

b. Create a Pie Chart that shows Quantity of each item as a percentage of

total quantity.

c. Create a Line Chart that shows the total in dollars of each item in the

Toddler category.

d. Extra-Credit: Create a 2-D column chart that shows Quantity (of units by

Category).