Expert answer:Excel assignment
Solved by verified expert:I will provide the files.
excel_lab_two___managing_multiple_worksheets.docx
lab_two_sales.xlsm
Unformatted Attachment Preview
Excel Lab Two – Managing Multiple Spreadsheets – Excel 2013
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.) Change the information in A2 to now show Sales: January.
d.) 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.
e.) Apply Sheet Tab Color Dark Red, Accent 2 by clicking the January Sheet tab and then
selecting the color and color style.
(1) Rename worksheet 2 to February
(2) Rename worksheet 3 to March.
(3) You may add colors and accent styles to each sheet to differentiate the sheets.
f.) Make January the active worksheet.
g.) 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)
(3)
(4)
(5)
Select the Formula tab on the ribbon bar
Select the AutoSum function tool from the Function Library
Typing B5*C5 in cell D5. The formula should look like =SUM(B5*C5).
D5 should contain the numerical product of multiplying Quantity (B5) by Unit Price
(C5).
h.) Copy the formula from cell D5 and paste it into cells D6 through D8.
i.) Calculate a total for D9 adding together cells D5 through D8.
j.) 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.
k.) 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.
Excel Lab Two – Managing Multiple Worksheets – Page 2
Copyright © June, 2015 – Dominic M. Mezzanotte, Sr.
l.) Based on the data provided, your worksheet should look similar to Figure 1 above.
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 copying and pasting 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 D6 through D8 for each of
the three monthly worksheets.
(6) February totals will begin in Summary worksheet cell C5 with March starting in cell
D5.
Excel Lab Two – Managing Multiple Worksheets – Page 3
Copyright © June, 2015 – Dominic M. Mezzanotte, Sr.
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.
Figure 2. Sample Completed Summary Worksheet with Calculated Formulas.
q. In the Summary worksheet, enter formulas for each of the Total cells detailed on the
worksheet.
(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.).
• Workbook, Spreadsheet, and Summary sheet and calculations, etc. – 5 points.
Spelling – 1 point.
Excel Lab Two – Managing Multiple Worksheets – Page 4
Copyright © June, 2015 – Dominic M. Mezzanotte, Sr.
Your Name, Inc.
Sales: January
Item Number
Shirts
Coat
Shoes
Pants
Total
Quantity
310
125
245
195
Unit Price
30.95
125.95
250.95
99.95
Gross Sales
Tax Amount
Total
Including Tax
Your Name, Company
Sales: February
Item Number
Shirts
Coat
Shoes
Pants
Total
Quantity
311
145
75
225
Unit Price
28.95
159.95
225.95
75.95
Gross Sales
Tax Amount
Total
Including Tax
Your Name, Company
Sales: March
Item Number
Shirts
Coat
Shoes
Pants
Total
Quantity
355
25
71
190
Unit Price
30.95
125.95
250.95
99.95
Gross Sales
Tax Amount
Total
Including Tax
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades