Please help answer part a to l:
Attached below is the data:
of the Documentation worksheet according to your nors de and then type your name in Firstname lastnom You have been asked to determine whether the grand total yearly sales goals were met The 2017 yenily sales goals were met if the yearly sales were 525,000,000 or more - In cell G36, using a logical function, determine whether the yearly sales goals were met. Set the formula tortura value of Tesif the goal was met of a value of No If the d. 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:16. Insert Line sparklines based . in 3. calculate the percentage of sales from mentoring. Display rere decimal W. Save the workbook, close Excel and then submit your fe as directe Chicken AS, and on the places 02B-Trendz.xlsx You will save your file 028-Trent strict Accounting make predictions about this year's sales data before making any decisions on whether or not to on the data in 85.16. Add High Point markers. Also add Line sparklines for the Men's To determine which clothing lines may sell well online, you decide to rank them on a In cell H21, using a Vlookup function determine the product line ranking of each Problem Solve 1 goal was not met MyITLab Student data file needed: Grader () Homework clothing data in G21:632 Add High Point markers. Sales Data Analysis You are the executive assistant to the director of sales at S-Trendz, Inc. a trendy retail store Scale from A to E. First you want to give the ranking table a range name. that has locations in only ten states. The company is considering branching into the online Select M4:Ne, and give the range the name Rank retail market. Your supervisor, Kayla Zimmerman, wants to review last year's sales data and In cells, using a Vlookup function determine the product line ranking of each Finance & product based on the Yearly Sales. Use the range name assigned to the lookup table. Copy the formula through H16 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 Copy the formula through 32. branch into the online market in the future, next year's total sales must be at least $3,000,000 . You have been told that each product line will be considered for online sales only if the Finally, if B-Trendz, Inc. begins selling products online, you will need help setting up and product line has a ranking of A or B. You need to determine which product lines will be considered for online sales maintaining the online store. Several companies have given you their information, and you In cells, using a logical function determine whether the product will be an online need to determine which company would best fit the needs for the B-Trendz online website, 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 the rank was met and a value of No if the rank was a. Open the Excel file, e02B-Trendz. Save your file as e028-Trendz_LastFirst, using your last and first name, Copy the formula through 116 Repeat the same formula for the men's clothing lines starting in cell 121, and copy the b. The Retail sheet contains 2017 quarterly sales data. You need to complete several formula through 132 calculations on this sheet. . 9. You want to visually compare the women's sales and the men's sales of the product lines. Create two charts. Calculate the quarterly sales totals for the women's and men's clothing lines. Create a 3-D clustered column chart that represents the women's clothing sales based Calculate the yearly sales totals for the women's and men's clothing lines. on each product line and the product line's yearly sales. Enter a chart title of Women's Clothing Sales In B36:F36, calculate the grand totals of women's and men'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 022 In B37:537, calculate the percentage of sales from women's clothing. Display zero Apply Style 3 to the chart decimal places. 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 38. Apply Style 3 to the chart. Adjust the vertical ads Bounds and Units of the men's clothing chart to match the vertical axis of the women's clothing chart. h. You want to compare sales percentages of women's and men's clothing and have this chart as a separate chart sheet for ease of printing, Create a clustered column chart to compare the sales percentages of women's and men's clothing sales for all four quarters Move the cartonewchart thee, and are the new worksheer com Position the Sales Compton worksheet to the right the metal worksheet Apply Style to the cht for the chart title Sales Compare Change the chart witte format in WordArt Pattern Full Blue, Accent 1Light Downward Diagonal, Outline Accents.cat the foot of the chart title to 40 .insert a rounded rectangle shape in the top left corner of the chart. Enter the text Waren products coulently outil en products Cunge the site of the rounded rectangle shape to high and 2:3 Wide Trend management believes that with the growing popularity of its clothing products the company can reach the overall yearly goal of $10,000,000 in sales (the sales goal of 2017 was $25.000.000You have been asked to determine the approximate Increase in les of each product that would be needed to reach this goal. Aswime the same percent increase for all the products . Select the Predictions worksheet tab, in cells, multiply the percent increase in sales in 01 by the 2017 sales for women's Quarter 1 Activeware of the Retail worksheets font To use data from another worksheet in this formula, click the worksheet tab, click the cell you want to multiply by the percentage in cell and then press Enter) Don't forget that you want at least the amount from the Retail worksheet plus the predicted increase Copy this formula to the range 05:16 Total the women's clothing sales as well as the women's quarterly sales Repeat the above steps for the men's clothing sales In 336F36, total the quarterly and yearly sales for both women's and men's clothing In cell 636 using a logical function, you will determine whether the sales goal is met The sales goal is met if the yearly sales total is greater than or equal to 530,000,000 Set the formula tortum a value of Yesil goal was met and a value of No if goal was not met . Using trial and error in cell B1. determine the approximate percentage increase that is needed for all products to meet the total sales goal of 530,000,000 in cell G36. Use only whole number percentages To launch online salet. B-Trend will need to hire a website development company to set up and maintain the website. To do so, B-Trend will need to take out a loan to cover the cost of the development and maintenance of the website. You have solicited and received bids from three different companies. For each bid, you need to determine the monthly payment total loan payment and total interest Select the Online worksheet in cell B11, calculate the monthly payment for the loan given the loan amount term, and interest for the Online Solutions company. The result should be a positive value In cell B12. calculate the total payments over the life of the loan In 313.calculate how much total Interest will be paid over the life of the loan. Copy the range B11:313 to D11:013. You need to recommend which company B Trendz should use to create and maintain the online website. The best option is based on the company maintaining the website for at least two years and a monthly payment of $2,100 or less. Use a logical function in cell B15:015 to return the company name from row 2 if the website will be main tained for at least two years and the monthly payment is $2,100 or less. If the value is false, the cell should remain blank not met . k Complete the Documentation worksheet according to your instructor's directions. Insert the file name in the left footer section on all worksheets in the workbook L Save the workbook, exit Excel and then submit your file as directed by your instructor When calculating the payment for the three potential loans, you were asked to have the function return a positive value. Why is it important to return a positive value of a loan payment identify at least two ways to make a payment function return a positive value Problem Solvo 1 529 Critical Thinking A B D F G H J K M N P 1 E B-Trendz, Inc. 2017 Quarterly Sales 2 Product Line Ranking Online Product Sales at a Glance 251,529 Product Line Rank 0 0 E 500,000 D 1,000,000 1.250.000 B 1.500.000 3 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales 4 Women's Clothing 5 Activeware 734,765 745,777 732,937 740,375 6 Casual shirts 245,198 256,789 255,936 7 Graphic Tees 457,000 478,123 477.229 472.721 8 Jackets 312,789 311,987 312,892 315,837 9 Jeans 423,465 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 18 Product Line Ranking Online Product Sales at a Glance 19 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales 20 Men's Clothing 21 Activeware 445,321 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 Jackets 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 98,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 36 Grand Total 37 Women's 7 of Sales 38 Men's % of Sales 39 40 Sales Goal Met? C D E F G H 1 J K L Quarter 2 Quarter 3 Quarter 4 Year Sales Quarter 2 Quarter 3 Quarter 4 Yearly Sales A B Sales Goal Increase 1 for 2018 5% 2 3 Quarter 1 4 Women's Clothing 5 Activeware 6 Casual shirts 7 Graphic Tees 8 Jackets 9 Jeans 10 Outerwear 71 Pants 12 Shorts 13 Sleepwear 74 Socks 75 Sweaters 16 Swimsuits 17 Total 18 19 Quarter 1 20 Men's Clothing 21 Activeware 22 Casual shirts 23 Graphic Tees 24 Jackets 25 Jeans 26 Outerwear 27 Pants 28 Shorts 29 Sleepwear 30 Socks 31 Sweaters 32 Swimsuits 33 Total 34 35 Quarter 1 36 2018 Predicted Total 37 38 39 40 41 Retail Quarter 2 Quarter 3 Quarter 4 Yearly Sales Sales Goal Met? Predictions Online Documentation Company Options Service/Product Maintains website Cost 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% Loan Options Term in Years Interest Monthly Payment Total Loan Payment Total Interest Best Option Retail Predictions Online Documentation C D E B 1 2 B-Trendz, Inc. 3 4 5 Create Date By Whom 6 2018-12-18 Kayla Zimmerman 7 Mod. Date By Whom 8 Description Workbook Name Sales Trends Last Version Backup Name Mod. Description 9 10 11 12 13 14 15 Purpose 16 17 18 19 Create Date Sheet Name 20 2018-12-18 Retail 21 2018-12-18 Predictions 22 2018-12-18 Online 23 24 Creator Kayla Zimmerman Kayla Zimmerman Kayla Zimmerman 25 26 27 28 29 30 31 32 33 Retail Predictions Online Documentation of the Documentation worksheet according to your nors de and then type your name in Firstname lastnom You have been asked to determine whether the grand total yearly sales goals were met The 2017 yenily sales goals were met if the yearly sales were 525,000,000 or more - In cell G36, using a logical function, determine whether the yearly sales goals were met. Set the formula tortura value of Tesif the goal was met of a value of No If the d. 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:16. Insert Line sparklines based . in 3. calculate the percentage of sales from mentoring. Display rere decimal W. Save the workbook, close Excel and then submit your fe as directe Chicken AS, and on the places 02B-Trendz.xlsx You will save your file 028-Trent strict Accounting make predictions about this year's sales data before making any decisions on whether or not to on the data in 85.16. Add High Point markers. Also add Line sparklines for the Men's To determine which clothing lines may sell well online, you decide to rank them on a In cell H21, using a Vlookup function determine the product line ranking of each Problem Solve 1 goal was not met MyITLab Student data file needed: Grader () Homework clothing data in G21:632 Add High Point markers. Sales Data Analysis You are the executive assistant to the director of sales at S-Trendz, Inc. a trendy retail store Scale from A to E. First you want to give the ranking table a range name. that has locations in only ten states. The company is considering branching into the online Select M4:Ne, and give the range the name Rank retail market. Your supervisor, Kayla Zimmerman, wants to review last year's sales data and In cells, using a Vlookup function determine the product line ranking of each Finance & product based on the Yearly Sales. Use the range name assigned to the lookup table. Copy the formula through H16 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 Copy the formula through 32. branch into the online market in the future, next year's total sales must be at least $3,000,000 . You have been told that each product line will be considered for online sales only if the Finally, if B-Trendz, Inc. begins selling products online, you will need help setting up and product line has a ranking of A or B. You need to determine which product lines will be considered for online sales maintaining the online store. Several companies have given you their information, and you In cells, using a logical function determine whether the product will be an online need to determine which company would best fit the needs for the B-Trendz online website, 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 the rank was met and a value of No if the rank was a. Open the Excel file, e02B-Trendz. Save your file as e028-Trendz_LastFirst, using your last and first name, Copy the formula through 116 Repeat the same formula for the men's clothing lines starting in cell 121, and copy the b. The Retail sheet contains 2017 quarterly sales data. You need to complete several formula through 132 calculations on this sheet. . 9. You want to visually compare the women's sales and the men's sales of the product lines. Create two charts. Calculate the quarterly sales totals for the women's and men's clothing lines. Create a 3-D clustered column chart that represents the women's clothing sales based Calculate the yearly sales totals for the women's and men's clothing lines. on each product line and the product line's yearly sales. Enter a chart title of Women's Clothing Sales In B36:F36, calculate the grand totals of women's and men'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 022 In B37:537, calculate the percentage of sales from women's clothing. Display zero Apply Style 3 to the chart decimal places. 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 38. Apply Style 3 to the chart. Adjust the vertical ads Bounds and Units of the men's clothing chart to match the vertical axis of the women's clothing chart. h. You want to compare sales percentages of women's and men's clothing and have this chart as a separate chart sheet for ease of printing, Create a clustered column chart to compare the sales percentages of women's and men's clothing sales for all four quarters Move the cartonewchart thee, and are the new worksheer com Position the Sales Compton worksheet to the right the metal worksheet Apply Style to the cht for the chart title Sales Compare Change the chart witte format in WordArt Pattern Full Blue, Accent 1Light Downward Diagonal, Outline Accents.cat the foot of the chart title to 40 .insert a rounded rectangle shape in the top left corner of the chart. Enter the text Waren products coulently outil en products Cunge the site of the rounded rectangle shape to high and 2:3 Wide Trend management believes that with the growing popularity of its clothing products the company can reach the overall yearly goal of $10,000,000 in sales (the sales goal of 2017 was $25.000.000You have been asked to determine the approximate Increase in les of each product that would be needed to reach this goal. Aswime the same percent increase for all the products . Select the Predictions worksheet tab, in cells, multiply the percent increase in sales in 01 by the 2017 sales for women's Quarter 1 Activeware of the Retail worksheets font To use data from another worksheet in this formula, click the worksheet tab, click the cell you want to multiply by the percentage in cell and then press Enter) Don't forget that you want at least the amount from the Retail worksheet plus the predicted increase Copy this formula to the range 05:16 Total the women's clothing sales as well as the women's quarterly sales Repeat the above steps for the men's clothing sales In 336F36, total the quarterly and yearly sales for both women's and men's clothing In cell 636 using a logical function, you will determine whether the sales goal is met The sales goal is met if the yearly sales total is greater than or equal to 530,000,000 Set the formula tortum a value of Yesil goal was met and a value of No if goal was not met . Using trial and error in cell B1. determine the approximate percentage increase that is needed for all products to meet the total sales goal of 530,000,000 in cell G36. Use only whole number percentages To launch online salet. B-Trend will need to hire a website development company to set up and maintain the website. To do so, B-Trend will need to take out a loan to cover the cost of the development and maintenance of the website. You have solicited and received bids from three different companies. For each bid, you need to determine the monthly payment total loan payment and total interest Select the Online worksheet in cell B11, calculate the monthly payment for the loan given the loan amount term, and interest for the Online Solutions company. The result should be a positive value In cell B12. calculate the total payments over the life of the loan In 313.calculate how much total Interest will be paid over the life of the loan. Copy the range B11:313 to D11:013. You need to recommend which company B Trendz should use to create and maintain the online website. The best option is based on the company maintaining the website for at least two years and a monthly payment of $2,100 or less. Use a logical function in cell B15:015 to return the company name from row 2 if the website will be main tained for at least two years and the monthly payment is $2,100 or less. If the value is false, the cell should remain blank not met . k Complete the Documentation worksheet according to your instructor's directions. Insert the file name in the left footer section on all worksheets in the workbook L Save the workbook, exit Excel and then submit your file as directed by your instructor When calculating the payment for the three potential loans, you were asked to have the function return a positive value. Why is it important to return a positive value of a loan payment identify at least two ways to make a payment function return a positive value Problem Solvo 1 529 Critical Thinking A B D F G H J K M N P 1 E B-Trendz, Inc. 2017 Quarterly Sales 2 Product Line Ranking Online Product Sales at a Glance 251,529 Product Line Rank 0 0 E 500,000 D 1,000,000 1.250.000 B 1.500.000 3 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales 4 Women's Clothing 5 Activeware 734,765 745,777 732,937 740,375 6 Casual shirts 245,198 256,789 255,936 7 Graphic Tees 457,000 478,123 477.229 472.721 8 Jackets 312,789 311,987 312,892 315,837 9 Jeans 423,465 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 18 Product Line Ranking Online Product Sales at a Glance 19 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Yearly Sales 20 Men's Clothing 21 Activeware 445,321 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 Jackets 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 98,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 36 Grand Total 37 Women's 7 of Sales 38 Men's % of Sales 39 40 Sales Goal Met? C D E F G H 1 J K L Quarter 2 Quarter 3 Quarter 4 Year Sales Quarter 2 Quarter 3 Quarter 4 Yearly Sales A B Sales Goal Increase 1 for 2018 5% 2 3 Quarter 1 4 Women's Clothing 5 Activeware 6 Casual shirts 7 Graphic Tees 8 Jackets 9 Jeans 10 Outerwear 71 Pants 12 Shorts 13 Sleepwear 74 Socks 75 Sweaters 16 Swimsuits 17 Total 18 19 Quarter 1 20 Men's Clothing 21 Activeware 22 Casual shirts 23 Graphic Tees 24 Jackets 25 Jeans 26 Outerwear 27 Pants 28 Shorts 29 Sleepwear 30 Socks 31 Sweaters 32 Swimsuits 33 Total 34 35 Quarter 1 36 2018 Predicted Total 37 38 39 40 41 Retail Quarter 2 Quarter 3 Quarter 4 Yearly Sales Sales Goal Met? Predictions Online Documentation Company Options Service/Product Maintains website Cost 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% Loan Options Term in Years Interest Monthly Payment Total Loan Payment Total Interest Best Option Retail Predictions Online Documentation C D E B 1 2 B-Trendz, Inc. 3 4 5 Create Date By Whom 6 2018-12-18 Kayla Zimmerman 7 Mod. Date By Whom 8 Description Workbook Name Sales Trends Last Version Backup Name Mod. Description 9 10 11 12 13 14 15 Purpose 16 17 18 19 Create Date Sheet Name 20 2018-12-18 Retail 21 2018-12-18 Predictions 22 2018-12-18 Online 23 24 Creator Kayla Zimmerman Kayla Zimmerman Kayla Zimmerman 25 26 27 28 29 30 31 32 33 Retail Predictions Online Documentation
