Steps to Perform:

Step	Instructions	Points Possible
1	Start Excel. Download and open the file named Excel_BU02_Assessment1_Trendz.xlsx. Grader will automatically add your last name to the beginning of the filename. Save the file to the location where you are storing your files.	0
2	The Retail sheet contains 2025 quarterly sales data. You need to complete several calculations on this sheet. In cell range B17:E17, calculate the quarterly sales totals for the womens clothing line. In cell range B33:E33, calculate the quarterly sales totals for the mens clothing line.	3.2
3	You also want to calculate the yearly totals. In cell range F5:F17, enter a formula to calculate the yearly totals. In cell range F21:F33, enter a formula to calculate the yearly totals.	2
4	In B36:F36, total the quarterly and yearly sales for both womens and mens clothing.	2
5	In cell range B37:E37, calculate the percentage of sales from womens clothing based on womens total and the grand total. Display zero decimal places. In cell range B38:E38, calculate the percentage of sales from mens clothing based on mens total and the grand total. Display zero decimal places.	4
6	You have been asked to determine whether the grand total yearly sales goals were met. The 2025 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. Return a value of Yes if the goal was met or a value of No if the goal was not met (use quotation marks where necessary).	2
7	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 cell range G5:G16, insert Line Sparklines based on the data in cell range B5:E16. Add High Point markers. Add Line Sparklines for the Mens clothing data in cell range G21:G32 based on data in cell range B21:E32. Add High Point markers.	1.6
8	To determine which clothing lines may sell well online, you decide to rank them on a scale from A to E. First, you want to give the ranking table a range name. Assign the range name Rank to the cell range K4:L8.	2
9	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 lookup function, determine the product line ranking of each product based on the Yearly Sales. Use the range name Rank assigned to the lookup table. Copy the formula through cell range H5:H16. In cell H21, using a lookup function, determine the product line ranking of each product based on the Yearly Sales. Use the range name Rank assigned to the lookup table. Copy the formula through cell range H21:H32.	3.2
10	You have been told that each product line will be considered for online sales only if the product line has a ranking of A. You need to determine which product lines will be considered for online sales. In cell I5, using a logical function, determine whether the product will be an online product. The product will be an online product if it has a ranking of A. Set the formula to return a value of Yes if the rank was met and a value of No if the rank was not met. Copy the formula through cell range I5:I16.	1.6
11	In cell I21, using a logical function, determine whether the product will be an online product. The product will be an online product if it has a ranking of A. Set the formula to return a value of Yes if the rank was met and a value of No if the rank was not met. Copy the formula through cell range I21:I32.	2
12	Next, you want to count the Rank scores for women's clothing. In cell O4, enter a formula to count the rank of the product line for women in cell range H5:H16 based on the value in cell N4. Ensure your formulas will be copyable to the cells below. Copy the formula but not the formatting through cell range O4:O8.	2
13	You also want to count the Rank scores for men's clothing. In cell R4, enter a formula to count the rank of the product line for men in cell range H21:H32 based on the value in cell Q4. Ensure your formulas will be copyable to the cells below. Copy the formula but not the formatting through cell range R4:R8.	2
14	You want to visually compare the womens sales and the mens sales of the product lines. Create two charts. Create a 3-D clustered column chart that represents the womens clothing sales based on each product line in cell range A5:A16 and the product lines yearly sales in F5:F16. Enter a chart title of Womens 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 R19. Apply Style 3 to the chart.	0
15	Create a second 3-D clustered column chart that represents the mens clothing sales based on each product line and the product lines yearly sales. Enter a chart title of Mens Clothing Sales Move the chart so the top left corner of the chart is in cell K21 and the bottom right corner of the chart is in cell R33. Apply Style 3 to the chart. Adjust the vertical axis Bounds of the mens clothing chart to a maximum of 3000000 to match the vertical axis of the womens clothing chart.	0
16	Next, compare sales percentages of womens and mens 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 womens and mens clothing sales for all four quarters. Move the chart to a chart sheet, and then 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 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 Womens products consistently outsold mens products Change the size of the rounded rectangle shape to .6 high and 2.25 wide.	5.2
17	B-Trendz management believes that with the growing popularity of its clothing products, the company can reach the overall yearly goal of $30,000,000 in sales for 2026. The sales goal of 2025 was $25,000,000. You have been asked to determine if the approximate increase in sales of each product would allow the sales goal to be met. On the Predictions worksheet, in column F, total the womens clothing quarterly and yearly sales, and then total the mens clothing quarterly and yearly sales. Format the results to look like the numbers in the table. Widen column F so that the results are visible.	3.2
18	In cell G36, using a logical function, determine whether the sales goal is met. The sales goal is met if the 2026 predicted sales total is greater than or equal to $30,000,000. If the result of the logical test is true, the value of Yes is returned but if the result is false, then a value of No is returned.	2
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 F36. Use only whole number percentages.	0
20	To launch online sales, B-Trendz will need to hire a website development company to set up and maintain the website. To do so, B-Trendz 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. On 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. Change the result of the payment function to be a positive value. Copy the formula through cell D11.	2
21	Save and close Excel_BU02_Assessment1_Trendz.xlsx. Exit Excel. Submit the file as directed.	0