Excel Lab Two - Managing Multiple Worksheets PDF

Preview

Summary

Lab assignments using Microsoft Excel to create Pivot tables and datasets. The assignment also required the use of MS Excel formulas....

Description

Excel Lab Two – Managing Multiple Spreadsheets – Excel 2013 _108476_1

Purpose: In many situations, a single workbook may contain several worksheets. You may have to both maintain and perhaps summarize these worksheets into additional worksheets. Excel allows you to group, edit, and format multiple worksheets and reflect changes made to the “active” worksheet to be passed through to other related worksheets. Grouping worksheets into logical elements such as time periods, etc., allows large amounts of data to be entered and processed in several worksheets and then tied together. Multiple worksheets can be color-coded to facilitate easier identification with formulas in one worksheet tied to cells in other worksheets. Files Needed: 

Lab_Two_Sales

Save Your Files as: 

LastName_FirstInitial_LabTwo_Sales.

ASSIGNMENT: Where Your Name is shown below enter your name. In this assignment, you will add worksheets to your workbook, construct formulas, and group worksheets together. Use the “?” Help features of Excel if you have any difficulty in solving this lab assignment. a.) Save your file as named above. b.) Change the information in A1 to now show the company name – The Latest Fashion Trends, Inc. – Your Name. c.) Rename Sheet 1 to January by right clicking the sheet tab named Sheet 1, selecting Rename from the drop down menu and then typing January. d.) Apply Sheet Tab Color Dark Red by right clicking the January Sheet tab, selecting Tab Color and then selecting the color. (1) Rename worksheet 2 to February (2) Rename worksheet 3 to March. (3) You must add different colors to each sheet to differentiate the sheets. e.) Make January the active worksheet. f.) In cell D5, construct a formula to compute Gross Sales = (Quantity * Unit Price). This can be handled by: (1) Making the active cell D5 Excel Lab Two – Managing Multiple Worksheets - Page 1 Copyright © June, 2015 – Dominic M. Mezzanotte, Sr.

(2) Typing the following formula in cell D5. The formula should look like =SUM(B5*C5) (3) D5 should contain the numerical product of multiplying Quantity (B5) by Unit Price (C5). g.) Copy the formula from cell D5 and paste it into cells D6 through D8. h.) Calculate a total for D9 adding together cells D5 through D8. i.) In cell E5, Use the Formula tab to construct a formula to compute a rounded to 2 decimal places TaxAmount = (Gross Sales * Tax Rate (6 percent)). This can be calculated by: (1) The formula should look like =SUM((D5*0.06)+0.005) (2) From the Home tab on the ribbon bar, select the Number group and change the currency to show only 2 decimals and no dollar sign.

Figure 1. Sample Worksheet with Calculated Formulas. j.) Copy the formula in cell E5 and past it into cells E6 through E8. (1) (2) (3) (4)

Calculate a total for cell E9 adding together cells E6 through E8. Calculate a total for F5 adding together cells D5 and E5. Copy the formula in cell F5 and paste it into cells F6 through F8. Calculate a total for F9 adding together cells F5 through F8.

k.) Based on the data provided, your worksheet should look similar to Figure 1 above.

Excel Lab Two – Managing Multiple Worksheets - Page 2 Copyright © June, 2015 – Dominic M. Mezzanotte, Sr.

m. Make the February and March worksheets function and look similar to the January worksheet. NOTE: This can be done in various ways by analyzing what you did for the January worksheet and applying the same formulas from it to both February and March. n. Insert a new (4th) worksheet by: (1) Clicking the Insert worksheet tool ( + ) immediately to the right of the last worksheet shown at the bottom left of the Excel workbook adds another worksheet to the workbook. (2) Rename the worksheet Summary. (3) Beginning in cell A4 and using the Tab key through to cell F4 type. Month, January, February, March, Total for Quarter, and Monthly Average. (4) Beginning in cell A5 and using the Enter key through A9 type Shirts, Coats, Shoes, Pants, and Total. o. Change the Heading beginning in cell A2 to Sales: Summary – 1st Quarter. Make the font 15. (1) Adjust all currency column widths to accommodate the dollar values to be displayed. The columns should all be the same size. (2) Save your workbook. (3) Increase the sizes of cells A1 through F1. This is necessary in order that the value fields can accommodate the values from the three monthly worksheets. p. Tie the totals for each month’s worksheet to the Summary worksheet as follows using the Gross Sales column for each type of product listed:: (1) (2) (3) (4)

On the Summary worksheet, click the cell B5 and then type = Click the January worksheet Click cell D5 and press the Enter key The system will display in the Summary worksheet cell B5 the numeric value from cell D5 in the January worksheet. (5) Continue steps 1 through 4 for the entire Gross Sales column D5 through D8 for each of the three monthly worksheets. NOTE: You may want to change any Quantity and/or Unit Price on any of the Monthly worksheets and observe the effect the change has on the Summary worksheet.

Excel Lab Two – Managing Multiple Worksheets - Page 3 Copyright © June, 2015 – Dominic M. Mezzanotte, Sr.

Figure 2. Sample Completed Summary Worksheet with Calculated Formulas.

(6) In the Summary worksheet, enter formulas for each of the Total cells detailed on the worksheet. February totals will begin in Summary worksheet cell C5 and ends in cell C8 with March starting in cell D5. ( Sum of B5 to B8 for January total in Summary tab, C5 to C8 for February total, D5 to D8 for March total) (1) Your Summary worksheet should look similar to Figure 2 above. Upload your completed assignment in Blackboard. Grading Matrix:

   

Organization – 5 points (includes all forms of punctuation such as dollar signs, decimal values if applicable, and periods, positive and/or negative values identified, etc.) Format – 5 points (includes cell width, professionalism, etc.). Column Headings – 2 points (cells properly labeled, etc.). Calculations – 7 points (includes formulas, column totals, cross foot totals, etc.). Excel Lab Two – Managing Multiple Worksheets - Page 4 Copyright © June, 2015 – Dominic M. Mezzanotte, Sr.



 Workbook, Spreadsheet, and Summary sheet and calculations, etc. – 5 points. Spelling – 1 point.

Excel Lab Two – Managing Multiple Worksheets - Page 5 Copyright © June, 2015 – Dominic M. Mezzanotte, Sr....