Lab 6: Advanced Excel Features

There are a lot of details which you'll need to look, in this assignment. I will be providing hints for the most important ones. There are 4 parts in this assignment. (You can revise the basics of spreadsheet here - Spreadsheet basics) 

#1. Download Lab-6-start.txt from ICON. This will be your starting point.
#2. Convert your data as shown below into your excel file.

#3. PART - 1

• Format Cells: Follow this link to learn about formatting cells. Format Cells.
• Merge Cells:

           If we want to merge the cells L3,M3,N3 and rows L4,M4,N4, select the cells - click Home on the ribbon and click "Merge & Center".

• Now, you need to modify the excel sheet according to the following rules: (Assuming the data is positioned beginning in Cell A1, we need to move down to make room for some title headings.)

1. Add three rows at the top of worksheet. Merge and center a title "Estimated Total Quarterly Sales, Retail and E-Commerce" that spans columns A through I of row 1. Change the font to be larger and bold.
2. Merge cells C3 and D3 and enter the title Retail Sales.
3. Merge E3 and E4, with the title E-Commerce % Total Sales. 
4. Merge F3 and G3. Enter the title Quarter to Quarter % Change.
5. Place Total Sales in F4 and E-Commerce in G4.
6. Merge H3 and I3. Enter the title Year to Year % Change.
7. Place Total Sales in H4 and E-Commerce in I4.
8. Modify all of the column widths as necessary so these headings fit. 

 In the end, you should have something like this

#4. PART - 2 (Formula)

• Now, we calculate values of the columns by using excel formula. For all the formula, once you have finished it for one cell, you can use the formula for the entire column by dragging the 'small plus sign' (that appears when you hover over small box on the right bottom of the cell) to the cell, until which you want to calculate. 

1. E-Commerce % Total Sales = Total E-Commerce Sales/Total Sales. The corresponding excel formula for row 5 will be =E5/D5. Now, drag it all the way down to fill the entire column.
2. Calculate quarter to quarter changes in cells F5 and G5. The formula is the difference between the current quarter (i.e the corresponding retail sales of the current cell you are in) and previous quarter’s sales divided by previous quarter’s sales.
3. Starting with 2013 1st quarter, enter the formulas to calculate year to year changes for both total sales and e-commerce in cells H5 and I5. This formula is similar to the formulas for part 2 above, except that to get a yearly difference for the same quarter. Check for Q1 in 2013 and Q1 in 2012.

#5. PART - 3 (Formatting) 

• It's time to format the values we have and make it more presentable.

1. Format the values in columns C and D as money, with a dollar sign, commas and no decimal places.
2. Format the remaining values as percentages with 2 decimal places.
3. Add appropriate borders and colors to separate the columns whose values you calculated.
4. Add conditional formatting to display all negative percentage values in columns F and G s in red. Drag the formatting to the entire column using the 'small plus sign' as mentioned above. You can use conditional formatting as shown below: 
5. Conditional formatting for column H and I: when a current value decreases from the corresponding value in the previous quarter, you display those values in bold. Place the conditional format in cells H5 and I5, and then copy it down. Hint: make a new rule. The formula for H5 is = H5 < H6.

#5. PART - 4 (Charts)

1. Create a new worksheet named Graphs on which to draw the chart. Use the string function Concatenate (Use this link to learn about this function concatenate function) to create label values in cell C5 (in a new column). Then copy the formula down the rest of the column using the ‘small plus sign’. After this, your excel should look like this: 
   
2. Create a bar graph based on the new column (C5) and Quarterly Percentage Changes for both Total sales and E-Commerce Sales. Format the value axis numbers as percentages and show the corresponding quarter beneath each pair of sales values for that quarter.

          

#6. Almost done!!

1. Rename the tab of each worksheet Like: ‘Data’ for the sales details and ‘Chart’ for the bar graph. The footer on the left, the date in the middle, and the page number on the right hand side. Hint: Insert, Custom Footer. 
2. Format each page so that when printed, each page shows Prepared by: Your Name in
3. Set the page to Landscape mode. 
4. Use Wrap Text on the Home menu to make all text in a column heading visible.
5. Experiment with Print Preview to determine which pages are better formatted as portrait or landscape when printed. Fit each worksheet into one page!

#7. When you are done, turn in your excel sheet to the dropbox.
#8. Grading (Total-20 points)

      Based on Formula

• Concatenation: 1 point
• % Total: 2 points
• Quarter to Quarter % change: 2.5 points
• Year to year % change: 2.5 points

      Based on Formatting

• Negative values - red, Descending values - bold: 2.5 points each (5 points)
• Rest of formatting (colors, cells): 2 points

      Based on charts and others

• Chart: 3 points
• Fit into one page, landscape, footer: 2 points
• Extra rules (conditional formatting): -1 point