Office 2016 – myitlab:grader – Instructions
Excel Project
YO16_XL_BU02_GRADER_PS1_HW - B-Trendz 1.3
Project Description:
You are the executive assistant to the director of sales at B-Trendz, Inc., a trendy retail store that has locations in
only ten states. The company is considering branching into the online retail market. Your supervisor, Kayla
Zimmerman, wants to review last year’s sales data and make predictions about this year’s sales data before
making any decisions on whether or not to begin selling online and, if so, which products may sell well online.
You have been asked to determine the best-selling women’s and men’s clothing line by ranking them on a scale
from A to E. Only products that have a ranking of A or B will be considered for online sales. To branch into the
online market in the future, next year’s total sales must be at least $3,000,000. Finally, if B-Trendz, Inc. begins
selling products online, you will need help setting up and maintaining the online store. Several companies have
given you their information, and you need to determine which company would best fit the needs for the B-Trendz
online website.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
1
Points
Possible
Instructions
Start Excel. Open the file named e02_grader_hw_B-Trendz.xlsx. Save the file with the name
e02_grader_hw_B-Trendz_LastFirst, replacing LastFirst with your last and first name.
0.000
2
On the Retail worksheet, in cells B17:E17 calculate the quarterly sales totals for the women’s
clothing line. In cells B33:E33 calculate the quarterly sales totals for the men’s clothing line.
4.000
3
In cells F5:F17, calculate the yearly sales totals for the women’s clothing line. In cells
F21:F33, calculate the yearly sales totals for the men’s clothing line. In B36:F36, calculate the
grand totals of women’s and men’s clothing sales.
5.000
4
In B37:E37, calculate the percentage of sales from women’s clothing. Format as Percentage
and display zero decimal places. In B38:E38, calculate the percentage of sales from men’s
clothing. Format as Percentage and display zero decimal places.
4.000
5
6
7
8
You have been asked to determine whether the grand total yearly sales goals were met.
The 2018 yearly sales goals were met if the yearly sales were $25,000,000 or more. In cell
G36, using a logical function, determine whether the yearly sales goals were met. Set the
formula to return a value of Yes if the goal was met or a value of No if the goal was not met.
You want to create quick visuals of the quarterly sales of each product line, so you decide to
use Sparklines to view the sales at a glance. In G5:G16, insert Line Sparklines based on the
data in B5:E16. Add High Point markers. Also add Line Sparklines for the Men’s clothing data
in G21:G32 based on data in B21:E32. Add High Point markers.
Assign the named range of Rank to the cells M4:N8.
1
3.000
2.000
To determine which clothing lines may sell well online, you decide to rank them on a scale
from A to E. In cell H5, using a Vlookup function, determine the product line ranking of each
product based on the Yearly Sales. Use the range name assigned to the lookup table. Copy
the formula through H16. In cell H21, using a Vlookup function, determine the product line
ranking of each product based on the Yearly Sales. Use the range name assigned to the
Updated: 02/27/2017
5.000
5.000
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
lookup table. Copy the formula through H32.
9
10
11
12
In cell I5, using logical functions, determine whether the product will be an online product.
The product will be an online product if it has a ranking of A or B. Set the formula to return a
value of Yes if rank was met and a value of No if rank was not met. Copy the formula
through I16. Repeat the same formula for the men’s clothing lines starting in cell I21, and
copy the formula through I32.
Create a 3-D clustered column chart that represents the women’s clothing sales based on
each product line and the product line’s yearly sales. Enter a chart title of Women’s
Clothing Sales. Move the chart so the top left corner of the chart is in cell K10 and the
bottom right corner of the chart is in cell Q22. Apply Style 3 to the chart.
Create a second 3-D clustered column chart that represents the men’s clothing sales based on
each product line and the product line’s yearly sales. Enter a chart title of Men’s Clothing
Sales. Move the chart so the top left corner of the chart is in cell K23 and the bottom right
corner of the chart is in cell Q38. Apply Style 3 to the chart. Adjust the vertical axis Bounds
and Units of the men’s clothing chart to match the vertical axis of the women’s clothing chart.
Create a clustered column chart to compare the sales percentages of women’s and men’s
clothing sales for all four quarters. Move the chart to a new chart sheet, and name the new
worksheet SalesComparison. Position the SalesComparison worksheet to the right the Retail
worksheet. Apply Style 4 to the chart. Enter the chart title Sales Comparison. Change the
chart title format using WordArt, Pattern Fill-Blue, Accent 1, Light Downward Diagonal, Outline
- Accent 1. Note, depending on the version of Office used, the WordArt style name may be
Pattern Fill: Blue, Accent color 5, Light Downward Diagonal Stripe; Outline: Blue, Accent color
5. Increase the font of the chart title to 40. Insert a rounded rectangle shape in the top left
corner of the chart. Note, depending on the version of Office used, the shape name may be
Rectangle: Rounded Corners. Enter the text Women’s products consistently outsold
men’s products. Change the size of the rounded rectangle shape to 0.6" high and 2.25"
wide.
5.000
6.000
6.000
8.000
13
On the Predictions worksheet, in cell B5, calculate the 2019 sales goal amount for women’s
activeware based on the 2018 amount on the Retail worksheet and the percent in cell B1 on
the Predictions worksheet. Copy this formula to the range B5:E16.
4.000
14
In cell B17, calculate the total quarter 1 sales goal amount and copy the formula over to E17.
In cell F5, calculate the total yearly sales goal amount for women’s activeware. Copy the
formula down to F17.
4.000
15
In cell B21 calculate the 2019 sales goal amount for men’s activeware based on the 2018
amount on the Retail worksheet and the percent in cell B1 on the Predictions worksheet. Copy
this formula to the range B21:E32.
6.000
16
In cell B33, calculate the total quarter 1 sales goal amount and copy the formula over to E33.
In cell F21, calculate the total yearly sales goal amount for men’s activeware. Copy the
formula down to F33.
4.000
Updated: 02/27/2017
2
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
17
Excel Project
Points
Possible
Instructions
In B36:F36, total the quarterly and yearly sales for both women’s and men’s clothing.
3.000
18
In cell G36, using a logical function, determine whether the sales goal is met. The sales goal is
met if the yearly sales total is greater than or equal to $30,000,000. Set the formula to return
a value of Yes if goal was met and a value of No if goal was not met.
5.000
19
Using trial and error in cell B1, determine the approximate lowest percentage increase that is
needed for all products to meet the total sales goal of $30,000,000 in cell G36. Use only whole
number percentages.
2.000
20
21
22
23
On the Online worksheet, in cell B11, calculate the end of month payment amount for the loan
given the loan amount, term, and interest for the Online Solutions company. Use cell
references where applicable. The result should be a positive value. Copy the formula over to
D11.
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula
over to D12.
In B13, calculate how much total interest will be paid over the life of the loan. Copy the
formula over to D13.
In cells B15:D15, use a logical function to recommend which company B-Trendz should use to
create and maintain the online website. The function should return the company name from
row 2 if the website will be maintained for at least two years and the monthly payment is
$2,100 or less. If the value is false, the cell should remain blank.
6.000
3.000
2.000
5.000
24
Insert the File Name field in the left custom footer section of the Header/Footer tab in the
Page Setup dialog box on all worksheets in the workbook.
3.000
25
Ensure that the worksheets are correctly named and placed in the following order in the
workbook: Retail, SalesComparison, Predictions, and Online. Close the workbook and then
close Excel. Submit the workbook as directed.
0.000
Total Points
Updated: 02/27/2017
3
100.000
Current_Instruction.docx
B-Trendz, Inc.
2018 Quarterly Sales
Quarter 1
Women's Clothing
Activeware
Casual shirts
Graphic Tees
Jackets
Jeans
Outerwear
Pants
Shorts
Sleepwear
Socks
Sweaters
Swimsuits
Total
734,765 745,777 732,937 740,375 2,953,854
245,198 256,789 255,936 251,529 1,009,452
457,000 478,123 477,229 472,721 1,885,073
312,789 311,987 312,892 315,837 1,253,505
423,465 413,726 414,756 419,202 1,671,149
182,746 201,725 200,273 194,768
779,512
293,476 291,234 293,745 297,312 1,175,767
192,782 197,315 198,295 193,800
782,192
283,927 281,334 280,687 285,111 1,131,059
124,587 125,079 126,213 121,510
497,389
239,847 242,703 245,666 247,897
976,113
234,987 310,719 306,827 245,321 1,097,854
3,725,569 3,856,511 3,845,456 3,785,383 15,212,919
Quarter 1
Men's Clothing
Activeware
Casual shirts
Graphic Tees
Jackets
Jeans
Outerwear
Pants
Shorts
Sleepwear
Socks
Sweaters
Swimsuits
Total
Quarter 2 Quarter 3 Quarter 4 Yearly Sales Sales at a Glance
Quarter 2 Quarter 3 Quarter 4 Yearly Sales Sales at a Glance
445,321 446,123 449,154 451,876 1,792,474
324,987 325,968 331,825 337,345 1,320,125
267,210 269,432 272,045 274,123 1,082,810
392,876 394,836 395,185 395,987 1,578,884
283,746 284,998 293,199 300,154 1,162,097
182,649 184,316 186,978 185,285
739,228
294,517 301,512 310,936 312,123 1,219,088
222,036 226,897 229,465 232,000
910,398
99,254
97,312
98,251
97,945
392,762
122,475 122,978 126,253 127,423
499,129
101,345
94,968
90,156
98,127
384,596
75,978
79,196
83,926
76,293
315,393
2,812,394 2,828,536 2,867,373 2,888,681 11,396,984
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales Sales Goal Met?
Grand Total
6,537,963 6,685,047 6,712,829 6,674,064 26,609,903
YES
Women's % of Sales
57%
58%
57%
57%
57%
Men's % of Sales
43%
42%
43%
43%
43%
Product
Line
Ranking
Online
Product
Product
Line
Ranking
Online
Product
Product Line Rank
0
E
500,000
D
1,000,000
C
1,250,000
B
1,500,000
A
Sales Goal Increase
for 2019
5%
Quarter 1
Quarter 2
Quarter 3
Quarter 4
Year Sales
Quarter 1
Quarter 2
Quarter 3
Quarter 4
Yearly Sales
Quarter 1
Quarter 2
Quarter 3
Quarter 4
Yearly Sales Sales Goal Met?
Women's Clothing
Activeware
Casual shirts
Graphic Tees
Jackets
Jeans
Outerwear
Pants
Shorts
Sleepwear
Socks
Sweaters
Swimsuits
Total
Men's Clothing
Activeware
Casual shirts
Graphic Tees
Jackets
Jeans
Outerwear
Pants
Shorts
Sleepwear
Socks
Sweaters
Swimsuits
Total
2018 Predicted Total
Company Options
Service/Product
Maintains website
Cost
Loan Options
Term in Years
Interest
Monthly Payment
Total Loan Payment
Total Interest
Best Option
Online Solutions
Online For You
Creative Development
Designs website for
Designs website for
Designs website for
online product sales
online product sales
online product sales
1
2
3
$
100,000.00 $
110,000.00 $
120,000.00
5
5.05%
5
5.05%
5
5.05%
Purchase answer to see full
attachment