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.
Why Work with Us
Top Quality and Well-Researched Papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
Professional and Experienced Academic Writers
We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.
Free Unlimited Revisions
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.
Prompt Delivery and 100% Money-Back-Guarantee
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.
Original & Confidential
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.