YO16_XL_BU02_GRADER_PS1_HW – BTrendz 1.3
Project Description:
You are the executive assistant to the director of sales at BTrendz, 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 bestselling 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 BTrendz, 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 BTrendz online website.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step 
Instructions 
Points Possible 
1 
Start Excel. Open the file named e02_grader_hw_BTrendz.xlsx. Save the file with the name e02_grader_hw_BTrendz_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 
You have been asked to determine whether the grand total yearly sales goals were met. 
5.000 
6 
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. 
3.000 
7 
Assign the named range of Rank to the cells M4:N8. 
2.000 
8 
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 lookup table. Copy the formula through H32. 
5.000 
9 
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. 
5.000 
10 
Create a 3D 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. 
6.000 
11 
Create a second 3D 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. 
6.000 
12 
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 FillBlue, 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. 
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 
17 
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 
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. 
6.000 
21 
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula over to D12.

3.000 
22 
In B13, calculate how much total interest will be paid over the life of the loan. Copy the formula over to D13. 
2.000 
23 
In cells B15:D15, use a logical function to recommend which company BTrendz 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. 
5.000 
24 
Insert the File Namefield 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 
100.000 
YO16_XL_BU02_GRADER_PS1_HW – BTrendz 1.3
Project Description:
You are the executive assistant to the director of sales at BTrendz, 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 bestselling 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 BTrendz, 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 BTrendz online website.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step 
Instructions 
Points Possible 
1 
Start Excel. Open the file named e02_grader_hw_BTrendz.xlsx. Save the file with the name e02_grader_hw_BTrendz_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 
You have been asked to determine whether the grand total yearly sales goals were met. 
5.000 
6 
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. 
3.000 
7 
Assign the named range of Rank to the cells M4:N8. 
2.000 
8 
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 lookup table. Copy the formula through H32. 
5.000 
9 
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. 
5.000 
10 
Create a 3D 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. 
6.000 
11 
Create a second 3D 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. 
6.000 
12 
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 FillBlue, 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. 
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 
17 
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 
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. 
6.000 
21 
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula over to D12.

3.000 
22 
In B13, calculate how much total interest will be paid over the life of the loan. Copy the formula over to D13. 
2.000 
23 
In cells B15:D15, use a logical function to recommend which company BTrendz 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. 
5.000 
24 
Insert the File Namefield 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 
100.000 
YO16_XL_BU02_GRADER_PS1_HW – BTrendz 1.3
Project Description:
You are the executive assistant to the director of sales at BTrendz, 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 bestselling 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 BTrendz, 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 BTrendz online website.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step 
Instructions 
Points Possible 
1 
Start Excel. Open the file named e02_grader_hw_BTrendz.xlsx. Save the file with the name e02_grader_hw_BTrendz_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 
You have been asked to determine whether the grand total yearly sales goals were met. 
5.000 
6 
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. 
3.000 
7 
Assign the named range of Rank to the cells M4:N8. 
2.000 
8 
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 lookup table. Copy the formula through H32. 
5.000 
9 
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. 
5.000 
10 
Create a 3D 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. 
6.000 
11 
Create a second 3D 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. 
6.000 
12 
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 FillBlue, 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. 
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 
17 
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 
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. 
6.000 
21 
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula over to D12.

3.000 
22 
In B13, calculate how much total interest will be paid over the life of the loan. Copy the formula over to D13. 
2.000 
23 
In cells B15:D15, use a logical function to recommend which company BTrendz 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. 
5.000 
24 
Insert the File Namefield 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 
100.000 
YO16_XL_BU02_GRADER_PS1_HW – BTrendz 1.3
YO16_XL_BU02_GRADER_PS1_HW – BTrendz 1.3YO16_XL_BU02_GRADER_PS1_HW – BTrendz 1.3YO16_XL_BU02_GRADER_PS1_HW – BTrendz 1.3
Project Description:
Project Description: Project Description: Project Description:
You are the executive assistant to the director of sales at BTrendz, 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 bestselling 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 BTrendz, 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 BTrendz online website.
You are the executive assistant to the director of sales at BTrendz, 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 bestselling 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 BTrendz, 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 BTrendz online website.You are the executive assistant to the director of sales at BTrendz, 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 bestselling 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 BTrendz, 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 BTrendz online website.You are the executive assistant to the director of sales at BTrendz, 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 bestselling 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 BTrendz, 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 BTrendz online website.
Instructions:
Instructions: Instructions: Instructions:
For the purpose of grading the project you are required to perform the following tasks:
For the purpose of grading the project you are required to perform the following tasks:
Step 
Instructions 
Points Possible 
1 
Start Excel. Open the file named e02_grader_hw_BTrendz.xlsx. Save the file with the name e02_grader_hw_BTrendz_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 
You have been asked to determine whether the grand total yearly sales goals were met. 
5.000 
6 
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. 
3.000 
7 
Assign the named range of Rank to the cells M4:N8. 
2.000 
8 
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 lookup table. Copy the formula through H32. 
5.000 
9 
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. 
5.000 
10 
Create a 3D 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. 
6.000 
11 
Create a second 3D 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. 
6.000 
12 
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 FillBlue, 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. 
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 
17 
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 
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. 
6.000 
21 
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula over to D12.

3.000 
22 
In B13, calculate how much total interest will be paid over the life of the loan. Copy the formula over to D13. 
2.000 
23 
In cells B15:D15, use a logical function to recommend which company BTrendz 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. 
5.000 
24 
Insert the File Namefield 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 
100.000 
Step
Instructions
Points Possible
Step
Instructions
Points Possible
Step
Step
StepStepStep
Instructions
Instructions
InstructionsInstructionsInstructions
Points Possible
Points Possible
Points PossiblePoints PossiblePoints Possible
1
Start Excel. Open the file named e02_grader_hw_BTrendz.xlsx. Save the file with the name e02_grader_hw_BTrendz_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
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.
5.000
6
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.
3.000
7
Assign the named range of Rank to the cells M4:N8.
2.000
8
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 lookup table. Copy the formula through H32.
5.000
9
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.
5.000
10
Create a 3D 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.
6.000
11
Create a second 3D 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.
6.000
12
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 FillBlue, 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.
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
17
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
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.
6.000
21
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula over to D12.
3.000
22
In B13, calculate how much total interest will be paid over the life of the loan. Copy the formula over to D13.
2.000
23
In cells B15:D15, use a logical function to recommend which company BTrendz 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.
5.000
24
Insert the File Namefield 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
100.000
1
Start Excel. Open the file named e02_grader_hw_BTrendz.xlsx. Save the file with the name e02_grader_hw_BTrendz_LastFirst, replacing LastFirst with your last and first name.
0.000
1
1
111
Start Excel. Open the file named e02_grader_hw_BTrendz.xlsx. Save the file with the name e02_grader_hw_BTrendz_LastFirst, replacing LastFirst with your last and first name.
Start Excel. Open the file named e02_grader_hw_BTrendz.xlsx. Save the file with the name e02_grader_hw_BTrendz_LastFirst, replacing LastFirst with your last and first name.
Start Excel. Open the file named e02_grader_hw_BTrendz.xlsx. Save the file with the name e02_grader_hw_BTrendz_LastFirst, replacing LastFirst with your last and first name.e02_grader_hw_BTrendz.xlsxe02_grader_hw_BTrendz_LastFirstLastFirst
0.000
0.000
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
2
2
222
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.
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.
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.
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
4.000
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
3
3
333
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.
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.
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.
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
5.000
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
4
4
444
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.
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.
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.
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
4.000
4.000
5
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.
5.000
5
5
555
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 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 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.
YesNo
5.000
5.000
5.000
6
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.
3.000
6
6
666
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.
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.
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.
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.
3.000
3.000
3.000
7
Assign the named range of Rank to the cells M4:N8.
2.000
7
7
777
Assign the named range of Rank to the cells M4:N8.
Assign the named range of Rank to the cells M4:N8.
Assign the named range of Rank to the cells M4:N8.
Rank
2.000
2.000
2.000
8
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 lookup table. Copy the formula through H32.
5.000
8
8
888
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 lookup table. Copy the formula through H32.
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 lookup table. Copy the formula through H32.
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 lookup table. Copy the formula through H32.
5.000
5.000
5.000
9
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.
5.000
9
9
999
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.
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.
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.
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 menYes No ’s clothing lines starting in cell I21, and copy the formula through I32.
5.000
5.000
5.000
10
Create a 3D 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.
6.000
10
10
101010
Create a 3D 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 3D 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 3D 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 3D 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 WomenWomen’’s Clothing Saless 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.
6.000
6.000
6.000
11
Create a second 3D 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.
6.000
11
11
111111
Create a second 3D 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 second 3D 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 second 3D 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 second 3D 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 MenMen’’s Clothing Saless 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.
6.000
6.000
6.000
12
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 FillBlue, 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.
8.000
12
12
121212
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 FillBlue, 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.
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 FillBlue, 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.
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 FillBlue, 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.
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 FillBlue, 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 WomenSalesComparisonSales ComparisonWomen’’s products consistently outsold mens products consistently outsold men’’s productss products. Change the size of the rounded rectangle shape to 0.6″ high and 2.25″ wide.
0.6″ 2.25″
8.000
8.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
13
13
131313
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.
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.
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.
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
4.000
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
14
14
141414
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.
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.
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.
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
4.000
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
15
15
151515
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.
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.
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.
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
6.000
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
16
16
161616
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.
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.
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.
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
4.000
4.000
17
In B36:F36, total the quarterly and yearly sales for both women’s and men’s clothing.
3.000
17
17
171717
In B36:F36, total the quarterly and yearly sales for both women’s and men’s clothing.
In B36:F36, total the quarterly and yearly sales for both women’s and men’s clothing.
In B36:F36, total the quarterly and yearly sales for both women’s and men’s clothing.
In B36:F36, total the quarterly and yearly sales for both women’s and men’s clothing.
3.000
3.000
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
18
18
181818
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.
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.
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.
YesNo
5.000
5.000
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
19
19
191919
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.
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.
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
2.000
2.000
20
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.
6.000
20
20
202020
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.
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.
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.
6.000
6.000
6.000
21
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula over to D12.
3.000
21
21
212121
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula over to D12.
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula over to D12.
In cell B12, calculate the total payment amounts over the life of the loan. Copy the formula over to D12.
3.000
3.000
3.000
22
In B13, calculate how much total interest will be paid over the life of the loan. Copy the formula over to D13.
2.000
22
22
222222
In B13, calculate how much total interest will be paid over the life of the loan. Copy the formula over to D13.
In B13, calculate how much total interest will be paid over the life of the loan. Copy the formula over to D13.
In B13, calculate how much total interest will be paid over the life of the loan. Copy the formula over to D13.
2.000
2.000
2.000
23
In cells B15:D15, use a logical function to recommend which company BTrendz 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.
5.000
23
23
232323
In cells B15:D15, use a logical function to recommend which company BTrendz 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.
In cells B15:D15, use a logical function to recommend which company BTrendz 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.
In cells B15:D15, use a logical function to recommend which company BTrendz 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.
5.000
5.000
5.000
24
Insert the File Namefield 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
24
24
242424
Insert the File Namefield in the left custom footer section of the Header/Footer tab in the Page Setup dialog box on all worksheets in the workbook.
Insert the File Namefield in the left custom footer section of the Header/Footer tab in the Page Setup dialog box on all worksheets in the workbook.
Insert the File Namefield 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
3.000
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
25
25
252525
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.
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.
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.RetailSalesComparisonPredictionsOnline
0.000
0.000
0.000
Total Points
100.000
Total Points
Total Points
Total PointsTotal PointsTotal Points
100.000
100.000
100.000100.000100.000