Step Points Instructions Possible 1 Start Excel. Open the file named e02_grader_hw_B-Trendz.x/sx. Save the file with the name 0 202_grader_hw_B-Trendz_LastFirst, replacing Lastfirst with your last and first name 2 On the Retail worksheet, in cells B17:E17 calculate the quarterly sales totals for the women's 4 clothing line. In cells B33:E33 calculate the quarterly sales totals for the men's clothing line. 3 In cells F5:F17, calculate the yearly sales totals for the women's clothing line. In cells F21:F33, 5 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. 4 In B37:E37, calculate the percentage of sales from women's clothing. Format as Percentage 4 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. 5 You have been asked to determine whether the grand total yearly sales goals were met. 5 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. 6 You want to create quick visuals of the quarterly sales of each product line, so you decide to 3 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. 7 Assign the named range of Rank to the cells M4:NS. 2 To determine which clothing lines may sell well online, you decide to rank them on a scale 5 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 H32Step Instructions Points Possible 9 In cell 15, using logical functions, determine whether the product will be an online product. The 5 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 116 Repeat the same formula for the men's clothing lines starting in cell 121, and copy the formula through 132 10 Create a 3-D clustered column chart that represents the women's clothing sales based on 6 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 comer of the chart is in cell Q22. Apply Style 3 to the chart. 11 Create a second 3-D clustered column chart that represents the men's clothing sales based 6 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 comer 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. 12 Create a clustered column chart to compare the sales percentages of women's and men's 8 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 comer 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. 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 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. 15 In cell B21 calculate the 2019 sales goal amount for men's activeware based on the 2018 6 amount on the Retail worksheet and the percent in cell B1 on the Predictions worksheet. Copy this formula to the range B21:E32. 16 In cell B33, calculate the total quarter 1 sales goal amount and copy the formula over to E33. 4 In cell F21, calculate the total yearly sales goal amount for men's activeware, Copy the formula down to F33 17 In B36:F36, total the quarterly and yearly sales for both women's and men's clothing. 3_ 18 19 2D 21 23 24 25 In cell (336, using a logical hrnction, determine whether the sales goal is met. The sales goal is met ifttre verity sales total is greater than or emal to $30,DD,DD. Set the formula to retrrm a value of'l'es if goal was met and a value of No if goal was not met Using trial and enor in cell I31= determine the approximate lowest percentage increase that is needed for al products to meet the total sales goal of $30,tiDD,DD in cell 636. Use only whole number percentages. {in the Dnine worksheet, in cell B11, calculate the end of month payment amount for the loan given the tom amount, term, and interest for the Dnline Solutions company. Use oel referenoes wtrere applicable. The result should be a positive value. Copy the formula over to D11. In cell I312, calculate the total payment amounts over the life of the loan. Copy the fomrula over to D12. In B13, cdculate how much total interest will be paid over the life of the loan. Copy the formula over to D13. In cells I315:D15, use a logical function to recommend which company B-mshoukt use to create and mainta'n the online website. The function should return the companyr name from row 2 if the website wil be maintained for at least two years and the monthly payment is $2,1D or less. If the value is false, the cell should remain blank. Insert the File Name field in the left custom footer section of the HearlerfFooter tab it the Page Setup dialog box on all worksheets in the workbook. Ensure that the worksheets are conevctlyr named and placed in the following order it the workbook Retail, W Predictions, and Unline. IIDlose the workbook and then close Excel. Srbmit the workbook as directed. Total Puilts Al X Company Options G H K M N Company Options 2 Online Solutions Online For You Creative Development Designs website for Designs website for Designs website for 3 Service/Product online product sales online product sales online product sales 4 Maintains website 2 5 Cost 100,000.00 $ 110,000.00 $ 120,000.00 6 7 8 Loan Options 9 Term in Years 5 5 5 0 Interest 5.05% 5.05% 5.05% 11 Monthly Payment 12 Total Loan Payment 13 Total Interest 14 5 Best Option 16 17 18 19 Retail Predictions OnlineG H K L M N 0 Q Sales Goal Increase for 2019 5% Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Sales Women's Clothing Activeware Casual shirts Graphic Tees Jackets Jeans 10 Outerwear Pants 2 Shorts 3 Sleepwear 14 Socks 5 Sweaters 6 Swimsuits 7 Total 18 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales 20 Men's Clothing Activeware Casual shirts Graphic Tees 4 Jackets Jeans 26 Outerwear 7 Pants 28 Shorts Sleepwear Socks Sweaters 32 Swimsuits 3 Total Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales Sales Goal Met? 2018 Predicted Total Retail Predictions Online +C D G H M N O Q B-Trendz, Inc. 2 2018 Quarterly Sales Product Line Online Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales Sales at a Glance Ranking Product Product Line Rank Women's Clothing 0 E 5 Activeware 734,765 745,777 732,937 740,375 500,000 Casual shirts 245,198 256,789 255,936 251,529 1,000,000 7 Graphic Tees 457,000 478,123 477,229 472,721 1,250,000 8 Jackets 312,789 311,987 312,892 315,837 1,500,000 A 9 Jeans 423,465 5 413,726 414,756 419,202 10 Outerwear 182,746 201,725 200,273 194,768 11 Pants 293,476 291,234 293,745 297,312 12 Shorts 192,782 197,315 198,295 193,800 13 Sleepwear 283,927 281,334 280,687 285,111 14 Socks 124,587 125,079 126,213 121,510 15 Sweaters 239,847 242,703 245,666 247,897 16 Swimsuits 234,987 310,719 306,827 245,321 17 Total 181 Product Line Online 19 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales Sales at a Glance Ranking Product 20 Men's Clothing 21 Activeware 445,321 1 446,123 449,154 451,876 22 Casual shirts 324,987 325,968 331,825 337,345 23 Graphic Tees 267,210 269,432 272,045 274,123 24 Jacket 392,876 394,836 395,185 395,987 25 Jeans 283,746 284,998 293,199 300,154 26 Outerwear 182,649 184,316 186,978 185,285 27 Pants 294,517 301,512 310,936 312,123 28 Shorts 222,036 226,897 229,465 232,000 29 Sleepwear 99,254 97,312 98,251 97,945 30 Socks 122,475 122,978 126,253 127,423 31 Sweaters 101,345 94,968 90,156 18,127 32 Swimsuits 75,978 79,196 83,926 76,293 33 Total 34 35 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales Sales Goal Met? 36 Grand Total 37 Women's % of Sales 38 Men's % of Sales Retail Predictions Online