Tables for a Retail Company

Tables for a Retail Company

Tables for a Retail Company

Figure 5.31 Completed Exercise Figure 5.31 Completed Exercise

Retail companies with today’s online, as well as, in-store sales have a lot of data to keep track of! Keeping track of sales, costs, and profits on a daily basis is essential to making the most of a business. This exercise illustrates how to use the skills presented in this chapter to generate the data needed on a daily basis by a retail company. See Figure 5.31 above.

When saving files for submission, include your last name in the file name.

  1. Open the data file SC5 Data from the Data Files and save the file to your computer as SC5 Dynamite Customer Sales.
  2. Click on the Sales sheet. In I4, enter a VLOOKUP function that will find the Product Price for the Product in E4 in the table in the Product Table sheet and return it to I4. In your VLOOKUP function, fill in the required parameters using Figure 5.32 below. Copy the VLOOKUP function down column I.
    Figure 5.32 VLOOKUP window Figure 5.32 VLOOKUP window
  3. In J4, enter a VLOOKUP function that will find the Product Cost for the Product in E4 in the table in the Product Table sheet and return it to J4. This VLOOKUP function will be the same as the VLOOKUP function in I4 – Except the Col_index_num will be 3 instead of 4. Copy the function down column J.
  4. In K4, calculate Profit (Product Price – Product Cost). Copy this formula down column K.
  5. Format columns I, J, and K as currency with two decimal places.
  6. Click in cell A3. Insert a table with headers for the range A3:K52. Be careful here: Excel will try to insert a table starting with A2. You want to make sure your range starts with A3 here.
  7. Make a copy of the Sales sheet and rename it Online Sales by Date. Place this sheet to the right of the Sales sheet. Filter out Retail in Sales Type, so that only Online Sales are displayed. Sort the filtered data by Date Sold (oldest to newest).
  8. Make a copy of the Sales sheet and rename it June Sales by Country. Place this new sheet to the right of the Online Sales by Date sheet. Filter this sheet to only show June dates by using the Date Filter Between. Sort this sheet alphabetically (A to Z) by Country and then alphabetically by Name.
  9. Make a copy of the Sales sheet and rename it Subtotals by Date. Subtotal the sheet by Date (Oldest to Newest), summing the Profit column. Click the 2 Outline button to show just the subtotals by date and the grand total.
  10. Make one final copy of the Sales sheet and rename it Subtotals by Type. Place this new sheet to the right of the Subtotals by Date sheet. Subtotal the sheet by Sales Type, summing the Profit column.
  11. Add a 2nd subtotal to the Subtotals by Type sheet that subtotals by Type and averages the Profit column. (Hint: uncheck Replace Current Subtotals in the Subtotal dialog box.) Notice that 4 Outline buttons appear with the 2nd subtotal. Figure out which Outline button to click to display both subtotals for Online and Retail and two Grand Totals.
  12. Preview each worksheet in Print Preview and make any necessary changes for professional printing. (Hint: Orientation, page scaling, and print titles might need to be used)
  13. Double-check that your sheets are in the following order from left to right: Sales, Online Sales by Date, June Sales by Country, Subtotals by Date, Subtotals by Sales Type, and Product Table.
  14. Save the SC5 Dynamite Customer Sales workbook.
  15. Submit the SC5 Dynamite Customer Sales workbook as directed by your instructor.

Are you overwhelmed by your class schedule and need help completing this assignment? You deserve the best professional and plagiarism-free writing services. Allow us to take the weight off your shoulders by clicking this button.

Get help

Who Will Write your Paper

Writer avatar
Tutor_Charles

Rating: 4.9/5    Reviews 377

Success rate 96%

Completed projects: 545

I am an experienced writer offering professional services across diverse fields. I provide quality work with strict adherence to the deadline.

Writer avatar
Tutor_Dr. Edith

Rating: 4.8/5    Reviews 381

Success rate 96%

Completed projects: 490

I am an experienced writer with great research and analytical skills. Original quality paper and on-time delivery are guaranteed. Let's do this!

Writer avatar
Tutor_Dr.Judy

Rating: 4.7/5    Reviews 316

Success rate 99%

Completed projects: 398

I am a professional writer with more than 8 years of experience. My mission is to provide you with high-quality paper in a timely fashion.

Writer avatar
Tutor_Ian

Rating: 4.8/5    Reviews 371

Success rate 99%

Completed projects: 432

Hi, I deliver quality papers on time following all the instructions provided. Client's satisfaction is my priority. Welcome for plagiarism-free papers.

Writer avatar
Tutor_Prof. Allan

Rating: 4.8/5    Reviews 400

Success rate 98%

Completed projects: 487

With over half a decade of academic experience, I'm committed both to efficiency and punctuality. If it's quality you're looking for, then I'm here to provide!

Writer avatar
Tutor_Prof. Gabie

Rating: 4.9/5    Reviews 447

Success rate 98%

Completed projects: 546

A quality-driven, skilled writer with over 5 years of experience in various disciplines. I deliver plagiarism-free papers and content of impeccable quality, always original and always before the deadline!

Writer avatar
Tutor_Sarah

Rating: 4.9/5    Reviews 427

Success rate 100%

Completed projects: 567

I have been a full-time freelance researcher and writer for a period of over 6 years. I have successfully completed over 1000 projects and assignments. I have had a good professional relationship with my clients. Try me today!

Writer avatar
Tutor_Stella

Rating: 4.9/5    Reviews 305

Success rate 96%

Completed projects: 404

I am a proficient writer with knowledge across a wide range of academic disciplines. I guarantee quality, timely and original content.

Writer avatar
Tutor_SylviaYoung

Rating: /5    Reviews 419

Success rate 96%

Completed projects: 506

Hi, count on me to deliver quality and exemplary papers. I will pay attention to all your instructions, and meet all your expectations.