Test Questions
Part 1 - Formatting:
1. Insert a column to the right of Product Name and Size and use text to columns to split the size from the Product Name. Rename the column headers
2. Use the "Find and Replace" function within Excel to replace anything that says "Gender Neutral" with "Neutral".
3. Please sort the the entire table in alphabetical order by "Vendor"
4. Change the format of Wholesale Cost to be in $ format.
5. Change "% off Wholesale" to be rounded to 0 decimal points.
6. Change the row height to be 20 for each row
7. Use conditional formating to highlight duplicate values in the Product Name column red with red text.
8. Put the table columns in this order: Vendor, Product Name, Country of Origin, Size, Gender, wholesale cost, % off wholesale, zulily cost, retail price
9. Hide the Country or Origin column
10. Use conditional formatting to highlight "% off Wholesale" values that are less then 20% off.
Part 2 - Data Management/Formulas: (please note - cells highlighted in yellow
NOTE: All sums should be at the bottom of the column in the provided highlighted cells.
1. Calculate "zulily cost" column based on information in the"Wholesale Cost" & "% off wholesale cost" columns.
2. Calculate "zulily % off retail price" based on "retail price (MSRP)" & "zulily sale price".
3. Sum the "Starting Inventory" at the bottom.
4. Based on the sell through percentage, calculate the total units sold for each vendor. Format the cells to round the units sold to the nearest solid
5. Calculate the total sell-through based on Starting Inventory and Units Sold.
6. Using zulily Sale Price and Units Sold, calculate the zulily Total Demand.
7. Using Unit sold and zulily cost, calculate Total zulily COGS.
8. Calculate zulily Gross Profit $
9. Calculate the Gross Margin Rate.
10. At the bottom of each column, calculate the total Units Sold, Total zulily Demand, Total zulily COGS and zulily Profit.
11. Calculate the total zulily Gross Margin %
Vendor
Absorba
AdoraBelle Pettiskirts
Anne Geddes
Bella Bling
Bella Bling
Bella Bling
Chick Lingo
Dollie & Me
Dr. Bloom
Joyous & Free
Kanu Surf Boys
Kanu Surf Girls
Kanu Surf Women
Melissa & Doug
My Blankee
My First Organics
Raj Imports
Sneak A' Roos
Teva
Twin Sisters
Product Name
Orange Ruffle Romper
Pretty Pink Tutu
Motherhood Journal
Pink Girls' Tights
White Glitter Hat
White Glitter Hat
Frozen Tee
Cozy Pajama Set
Gray Necklace
Women's Shift Dress
Green Corduroy Pants
Purple Cotton Halter Dress
Flouncy Tunic Top
Wooden Puzzle Set
Green Receiving Blanket
Organic Seed Planting Kit
Women's Chevron Poncho
Pink Striped Mary Jane Shoes
Women's Turquoise Sandals
Bedtime Stories Set
Totals
Size
12 months
2T
one size
2T
6/12 months
6/12 months
3T
5
one size
6
7
6x
8
one size
one size
no size
4
Toddler 2
Size 6.5
n/a
gender
Neutral
Girl
Women
Girl
Girl
Girl
Neutral
Girl
Women
Women
Boy
Girl
Women
Neutral
Girl
Neutral
Women
Girl
Women
Neutral
wholesale
cost
$10,49
$11,91
$14,16
$30,44
$13,13
$13,13
$14,23
$16,28
$14,70
$15,75
$15,75
$14,96
$17,31
$26,25
$20,99
$27,29
$34,64
$33,08
$26,25
$47,25
$417,97
% off
wholesale
25%
50%
35%
15%
20%
20%
25%
30%
60%
25%
30%
40%
15%
25%
30%
15%
30%
25%
15%
45%
zulily cost
$7,87
$5,95
$9,21
$25,87
$10,50
$10,50
$10,67
$11,39
$5,88
$11,81
$11,03
$8,98
$14,72
$19,69
$14,69
$23,20
$24,25
$24,81
$22,31
$25,99
retail price zulily % off
Starting
(MSRP)
retail price zulily price Inventory
$21,98
32%
$
14,99
12
$24,95
31%
$
17,22
16
$29,68
37%
$
18,57
20
$63,78
45%
$
34,77
48
$27,50
28%
$
19,92
24
$27,50
28%
$
19,92
24
$29,81
29%
$
21,27
660
$34,10
34%
$
22,62
10
$30,80
22%
$
23,97
28
$33,00
23%
$
25,32
32
$33,00
19%
$
26,67
51
$31,35
43%
$
18,00
533
$36,28
19%
$
29,37
36
$55,00
44%
$
30,72
40
$43,98
27%
$
32,07
31
$57,18
42%
$
33,42
44
$72,58
50%
$
36,12
50
$69,30
46%
$
37,47
52
$55,00
29%
$
38,82
112
$99,00
59%
$
40,17
56
1.879
Sell through %
50%
25%
15%
23%
20%
80%
10%
25%
36%
45%
12%
18%
45%
55%
60%
65%
7%
44%
21%
50%
Units Sold
6
4
3
11
5
19
66
3
10
14
6
96
16
22
19
29
4
23
24
28
406
total zulily
demand
$90
$69
$56
$384
$96
$382
$1.404
$57
$242
$365
$163
$1.727
$476
$676
$597
$956
$126
$857
$913
$1.125
$10.759
total zulily cost
$47
$24
$28
$286
$50
$202
$704
$28
$59
$170
$67
$861
$238
$433
$273
$663
$85
$568
$525
$728
$6.040
Zulily gross
zulily gross
profit
margin %
$43
48%
$45
65%
$28
50%
$98
26%
$45
47%
$181
47%
$700
50%
$28
50%
$182
75%
$195
53%
$96
59%
$866
50%
$237
50%
$243
36%
$323
54%
$292
31%
$42
33%
$290
34%
$388
43%
$397
35%
$4.718
47%
Purchase answer to see full
attachment