I need an explanation for this Computer Science question to help me study.

This lab assumes you are using MS Excel 13 or above. You may use a higher version; however, be aware that there may be slight changes in the different versions. If your submission does not work in MS Excel 13 or above, it is considered non-functioning and you will receive a zero.

Grading Rubric

TOTAL: 40 points

• Part A: 39 points
  • (1 pt) Name in cell A1
  • (1 pt) Entered the data as in Figure 1
  • (1 pt) Merged, centered, and bolded B3-H3.
  • (1 pt) Formatted all the items columns with no decimals, and price columns as Currency.
  • (1 pt) Make all column headers and department names bold.
  • (1 pt) Bolded label in cell D4 that says: $sales
  • (1 pt) Copied the correct formula into every appropriate $sales item in all departments for the January $sales column.
  • (1 pt) Inserted formulas at the bottom of each dept. to sum its January $sales column.
  • (1 pt) Has a cell border at the bottom of each department for January
  • (1 pt) Columns B-N have the correct formulas
  • (1 pt) Created a new column to the right of the June $sales column with the header “Avg $”
  • (1 pt) Copied your average formula into each relevant row.
  • (1 pt) Created a new column labeled “Tot. Items.”
  • (1 pt) Added a formula to relevant rows in the Tot. Items column using the SUM function
  • (1 pt) Created a new column to the right of Tot. Items labeled Tot. Sales
  • (1 pt) Copied the total sales formula to all relevant rows.
  • (1 pt) Centered all the column titles in row 4.
  • (1 pt) Summed the Tot Items and Tot Sales for each department.
  • (1 pt) Put an outside border around the Tot Sales values for each department.
  • (1 pt) Put a bottom border after the last item in each department in the Tot. Items and Tot. Sales columns.
  • (1 pt) Put a bottom border after the last item in each department in column A
  • (1 pt) Created a new column to the right of Tot Sales labeled CkTot
  • (1 pt) Created a formula that sums the monthly $sales columns for that department in the Totals for Tool Dept. row
  • (1 pt) Created a new row two rows below the Totals for Small Appliances row labeled Grand Total.
  • (1 pt) Sums all the CkTot values for all departments.
  • (1 pt) Uses a vertical lookup table starting at cell C28.
  • (1 pt) Added a new column between columns A and B labeled Code.
  • (1 pt) Copied the code specified in the department title to the Code column for that item.
  • (1 pt) Added a new column after the CkTot column called Markdown formatted using %.
  • (1 pt) Add a formula that uses the VLOOKUP function
  • (1 pt) Copied the markdown formula for all the rest of the items.
  • (1 pt) Has a column to the right of Markdown labeled Sale Price formatted as currency.
  • (1 pt) Copies the sale price formula for all items.
  • (1 pt) Has a row for Hammer in the Tools Department.
  • (1 pt) Has a column to the right of Sale Price titled 30% more.
  • (1 pt) Has a formula in the screwdriver row that uses the IF function to produce the requested result
  • (1 pt) Calculates the sum of 30% more in the Grand Total row.
  • (1 pt) Hides columns D-O (the monthly sales figures) so that the summary appears.
  • (1 pt) Added the formula =CELL(“filename”) in cell A2
• Part B: 1 point
  • Follows requested project submission format

Guidelines and Policies

Getting Help

If you have a question, please post on Piazza, and we will answer as quickly as possible. We will try to answer questions and provide help within 24 hours of your request. If you do not receive a response in 24 hours, please send the request again.

Although we will answer questions, provide clarification, and give general help where possible up until the deadline, we will not help you with your submission within 24 hours of the deadline. We will not provide any help after the deadline.

Guidelines

This is an individual lab assignment. You must do the vast majority of the work on your own. It is permissible to consult with classmates to ask general questions about the assignment, to help discover and fix specific bugs, and to talk about high level approaches in general terms. It is not permissible to give or receive answers or solution details from fellow students.

You may research online for additional resources; however, you may not use material that was written specifically to solve the problem you have been given, and you may not have anyone else help you write the material or solve the problem. You may use resources found online, providing that they are appropriately and clearly cited, within your submission.

By submitting this assignment, you agree that you have followed the above guidelines regarding collaboration and research.

In this lab, you will learn to:

• Use more advanced features of MS Excel
• Use advanced functions, such as IF, in your spreadsheet

Creating a Spreadsheet

Problem: J’s Department store wants to use a spreadsheet to store the number of units sold of each item, for each month of the year.

1. Getting Started
   1. Create a new blank spreadsheet.
   2. Put your name in cell A1, and format column A with a width of 28.
   3. Enter the data as in Figure 1. Each value in a “month” column is the number of Items Sold that month. Don’t worry about formatting yet… we’ll get to that later.

Figure 1

1. Merge and center B3-H3 and make the contents bold.
2. Format all the items columns with no decimals, and all price columns as Currency.
3. Make all column headers bold and make department names bold and underlined.
1. Summarizing the Data
   1. For all departments: to the right of each month column, create a new column.
   2. Put a bold label in cell D4 that says: $sales
   3. Create a formula for the 1st item in January (cell D5) that calculates the sales (currency format) for that item for that month, (multiply the value in the Price column by number of units for that month).
      1. Use both mixed and relative addressing techniques where needed, so the formulas can be copied and filled, instead of being re-typed.
   4. Copy the formula into every appropriate $sales item in all departments for the January $sales column.
   5. Insert formulas at the bottom of each dept. to sum its January $sales column.
   6. Put a cell border at the bottom of each department for January, above the total.
   7. Copy & paste the entire Jan. $sales column (all departments) into all the $sales columns for the rest of the sheet. Check your formulas to be sure they are multiplying the correct data (current month’s items * price).
   8. Make the width of columns B-N 10 so you can see your results.
2. Calculating Statistics
   1. Create a new column to the right of the June $sales column with the header “Avg $” (in bold) with a column width of 10
   2. Add a formula using the built-in “AVERAGE” function to calculate the average of all the $sales columns in the first row for the Tool department.
      1. Remember, the average function takes a range, but the $sales columns are not contiguous, so you can’t use a “:” to define that range. Check the class notes for other ways to specify a range.
   3. Copy your average formula into each relevant row below.
   4. Create a new column to the right of with a column width of 10 labeled “Tot. Items.”
   5. In the screwdriver row, add a formula in the Tot. Items column using the SUM function to calculate the total number of items sold.
   6. Copy your sum formula to each relevant row below.
   7. Create a new column to the right of Tot. Items labeled Tot. Sales. with a column width of 10.
   8. In the screwdriver row, add a formula in the Tot. Sales column by multiplying tTot. Items by the price.
   9. Copy the total sales formula to all relevant rows below.
   10. Center all the column titles in row 4.
   11. For each department, sum the Tot Items and Tot Sales for that department on the line below the last item.
   12. Put an outside border around the Tot Sales values for each department.
   13. Put a bottom border after the last item in each department in the Tot. Items and Tot. Sales columns.
   14. Put a bottom border after the last item in each department in column A. Below the bottom border, and the label Totals for xyz, where xyz is the name of the department (e.g. Tools Dept.)
3. Double Entry Bookkeeping – Checking your results
   1. Create a new column to the right of Tot Sales labeled CkTot. with a width of 10.
   2. In the Totals for Tool Dept. row, in the CkTot column, create a formula that sums the monthly $sales columns for that department.
      1. If you have done everything correctly, the value in CkTot should match the value in TotSales for that department.
   3. Create a new row two rows below the Totals for Small Appliances row labeled (in bold) Grand Total.
   4. In the CkTot column of the Grand Total row, sum all the CkTot values for all departments.
   5. DJ’s Department Store is having a sale and many items will be marked down (discounted). The discount will depend on two things – the price of the item, and the department that the item is sold in. Figure 2 summarizes the discounts for the sale.
4. Adding a Lookup Table
   
   1. Create a vertical lookup table from the data in Figure 2 (which represents the data horizontally) starting at cell C28.
      1. Your lookup table does not have to have labels but should be formatted appropriately so that it is legible.
   2. Add a new column between columns A and B with a column width of 4, and label it Code.
   3. For every item, copy the code specified in the department title to the Code column for that item.
      1. This code is needed to select the correct column of the discount lookup table you created.
   4. Add a new column after the CkTot column called (in bold) Markdown. Format this column using %.
   5. In the screwdriver row, add a formula that uses the VLOOKUP function to determine the markdown percentage for screwdrivers.
      1. Use absolute and relative addressing so that you can copy this formula to the rest of the items.
   6. 37. Copy the markdown formula for all the rest of the items.
      1. Spot check to make sure your discounts are correct.
   7. Create another column to the right of Markdown labeled Sale Price. Format this column as currency.
   8. In the screwdriver row, calculate the sale price as price times (1-markdown) for this item.
   9. Copy the sale price formula for all items.
5. Modifying your spreadsheet
   1. Add a new row for Hammer in the Tools Department. Use a price of $8.98, and sales values as follows:
      1. Jan – 105
      2. Feb – 109
      3. Mar – 108
      4. Apr – 112
      5. May – 95
      6. Jun – 115
   2. Format the hammer row and update any formulas as needed.
6. Sorting within your spreadsheet
   1. Use the Sort command to arrange items alphabetically (A-Z) within each department.
   2. What happens to our Grand Total sales, if all items with more than $3,200 in sales per year were to sell 30% more sales per year? Add a column to the right of Sale Price titled 30% more.
   3. Create a formula in the screwdriver row that uses the IF function to compute 1.3 x Tot Sales if Tot Sales > $3,200, or just Tot Sales if not.
   4. Copy the formula for each item. Format this column using Currency.
   5. Calculate the sum of 30% more in the Grand Total row.
7. Getting back to the Summary
   1. Hide columns D-O (the monthly sales figures) so that the summary appears.
   2. Add the formula =CELL(“filename”) in cell A2
8. Save the workbook as Lab5-lastname.xlsx

Submission

• Required code naming and organization:
  • Lab5-lastname.xlsx

Every lab will have required submission guidelines. Please read submission requirements carefully. Any deviations from specifications on future labs or projects will result in significant point deductions or incomplete grades.

Submit this file on MyCourses. To do so, go to: