She has asked you to finish creating a worksheet
Anna Barstow, the sales director at Plaxco-Perez Windows and Doors, Inc., has just received the raw sales data for the first week of October. She has asked you to finish creating a worksheet that she started. Anna wants you to calculate the commission amount for each rep, which is 5% of the order amount, and then show the order amount less the commission for each order. She also wants you to sort the orders, in alphabetical order by rep and then from highest to lowest within each rep grouping. You also need to highlight orders greater than $10,000 using conditional formatting, and use functions to make key calculations to determine a few statistics for the week, including the highest and lowest individual orders, the averages for each rep who got the highest and lowest orders, and the number of total orders for the week.
a. Open the file H-4.xlsx from where you store your Data Files, then save it as H-October Sales Rep Report.
b. Enter a formula in cell E7 that calculates the commission owed to the rep. (Hint: Multiply cell D7 by the absolute reference B4.) Use the fill handle to copy the formula to cells E8:E39.
c. Create a formula in cell F7 that subtracts the rep’s commission in cell E7 from the order amount in cell D7.
d. Copy the formula in cell F7 to the range F8:F39.
e. Enter the label Average: in cell C40. Select the range D7:D39, then use the Quick Analysis gallery to enter a formula in cell D40 that calculates the average of the selected range. Use the fill handle to copy the formula in D40 to E40:F40.
f. Create a table from the range A6:F39. Apply the table style Table Style Light 14 to the table. Include a Total row. Use ‘ the fill handle to copy the formula in cell F40 to cells E40:D40.
g. Sort the table first by Sales Rep in alphabetical order and then by Order Amount (Largest to Smallest). Enter a formula in cell C43 that calculates the number of orders for this sales period. (Hint: Use the COUNT function and the range D7:D39.)
h. Select cell C44, then enter a formula that uses the MAX function to identify the highest order amount. Apply the Accounting number format to cell C44 with no decimal places. Locate this order amount in the Order Amount column. What is the name of the rep who got this order? Enter his or her name in cell C4S.
i. Enter a formula in cell C46 that calculates the average order amounts for the rep who had the highest order (see the name of the rep you entered in cell C4S). (Hint: The range in this formula should be the range containing the order amounts for this rep.) Apply the Accounting Number format, and remove all decimals.
j. Select cell C47, then enter a formula that uses the MIN function to identify the lowest order amount. Apply the Accounting number format to cell C47 with no decimal places. Locate this order amount in the Order Amount column. What is the name of the rep who got this order? Enter his or her name in cell C48.
k. Enter a formula in cell C49 that calculates the average order amounts for the rep who had the lowest order (see the name of the rep you entered in cell C48. (Hint: The range in this formula should be the range containing the order amounts for this rep.) Apply the Accounting Number format, and remove all decimals.
I. Use conditional formatting to highlight in green shading with dark green text all the orders that are above $10,000 in the range D7:D39.
m. Apply light orange shading (Orange, Accent 6, Lighter 80%) to cells C41:F41. Enter your name in cell Fl.
n. Preview the worksheet in Backstage view. Adjust the scaling so the whole worksheet fits on one sheet in portrait orientation.
o. Save your changes, close the workbook, then exit Excel. Submit your completed worksheet to your instructor.
The post She has asked you to finish creating a worksheet appeared first on My Assignment Online.
