Unit 6 Assignment -CIS 222

Sep 21, 2021 | students

ood morning,

This is the Unit 6 Assignment -CIS 222

  • Due Sunday by 11:59pm Points 100 Submitting a file upload Available Sep 20 at 12am – Sep 26 at 11:59pm 7 days

Tables for a Retail Company

A.png

 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.
B.png
  1.  Figure 5.32 VLOOKUP window
  2. 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.
  3. In K4, calculate Profit (Product Price – Product Cost). Copy this formula down column K.
  4. Format columns I, J, and K as currency with two decimal places.
  5. 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.
  6. 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).
  7. 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.
  8. 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.
  9. 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.
  10. 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.
  11. 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)
  12. 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.
  13. Save the SC5 Dynamite Customer Sales workbook.
  14. Submit the SC5 Dynamite Customer Sales workbook as directed by your instructor.

Attribution

“5.5 Scored Assessment” (Links to an external site.)

 by Diane Shingledecker, Portland Community College (Links to an external site.)

 is licensed under CC BY 4.0 (Links to an external site.)

Previous

Next

THE DATA FILE IS BELOW,