Microsoft Office Tutorials and References
In Depth Information
In the Lab
The template used to create the annual sales analysis workbook is part of the Data Files for Students.
Rebecca Smart, the company’s accountant, has asked you to use the template to create the year-end
sales analysis workbook.
Instructions Part 1: Perform the following tasks.
1. Open the template Lab 6-1 Rings and Things Annual Sales Analysis Template from the Data
Files for Students. See the inside back cover of this topic for instructions for downloading the
Data Files for Students or see your instructor for information on accessing the fi les required in
this topic. Save the template as a workbook using the fi le name, Lab 6-1 Part 1 Rings and Things
Annual Sales Analysis. Make sure Excel Workbook is selected in the ‘Save as type’ list when you
save the workbook.
2. Add a worksheet to the workbook between Sheet1 and Sheet2 and then paste the contents of
Sheet1 to the three empty sheets.
3. From left to right, rename the sheet tabs Consolidated, Direct Mail, Telesales, and Walk Ins.
Color the tabs as shown in Figure 6–93. On each of the three sales channel sheets, change the sub-
title in cell A2 to match the tab name. Use the title, Consolidated, in cell A1 of the Consolidated
worksheet. Change the title style for each title area in the range A1:F1 to match its tab color. Enter
the data in Table 6–11 into the three sales channel sheets.
Table 6–11 Rings and Things Annual Sales Data by Store and Sales Channel
Direct Mail
Telesales
Walk Ins
Biloxi
Sales
4873275
3291010
1950125
Returns
275375
289500
45102
Hartford
Sales
5239100
2152675
2101054
Returns
463201
500250
62198
Peoria
Sales
3925750
4235100
3100500
Returns
225198
324519
156340
Seattle
Sales
3278109
1975200
2984290
Returns
352679
125500
367109
4. On the Consolidated worksheet, use the SUM function, 3-D references, and copy and paste
capabilities of Excel to total the corresponding cells on the three sales channel sheets. First,
compute the sum in cell B4 and then compute the sum in cell B5. Copy the range B4:B5 to the
range C4:E5. The Consolidated sheet should resemble Figure 6–93.
5. Change the document properties as specifi ed by your instructor. Select all four sheets. Add a
worksheet header with your name, course number, and other information requested by your
instructor. Add the page number and total number of pages to the footer. Change the left and right
margins to .5.
6. With the four sheets selected, preview and then print the workbook in landscape orientation
and use the Black and white option.
7. Save the workbook with the new page setup characteristics. Close the workbook.
8. Submit the assignment as specifi ed by your instructor.
Instructions Part 2: Complete the following tasks.
1. Start Excel. Open the workbook Lab 6-1 Part 1 Rings and Things Annual Sales Analysis and then
save the workbook using the fi le name, Lab 6-1 Part 2 Rings and Things Annual Sales Analysis.
2. Create an embedded Clustered Horizontal Cylinder chart in the range A8:H25 on the Consolidated
worksheet by charting the range A3:E5.
Continued >
 
Search JabSto ::




Custom Search