Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

EXCEL 2007—CHAPTER 1 LAB EXERCISES, Slides of Accounting

University of New South Wales (UNSW)Accounting

EXCEL 2007—CHAPTER 1 LAB EXERCISES. Apply Your Knowledge—Changing the Values in a Worksheet. Instructions: Start Excel. Open the workbook Apply 1-1 Bicycle ...

Typology: Slides

2021/2022

Uploaded on 07/05/2022

lee_95
lee_95 🇦🇺

4.6

(59)

999 documents

1 / 8

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
EXCEL 2007—CHAPTER 1 LAB EXERCISES
Apply Your Knowledge—Changing the Values in a Worksheet
Instructions: Start Excel. Open the workbook Apply 1-1 Bicycle Shop 3 rd Quarter Sales (Figure 1-99a). See
the inside back cover of this book for instructions for downloading the Data Files for Students, or see your
instructor for information on accessing the files required in this book.
1. Make the changes to the worksheet
described in Table 1-6 so that the
worksheet appears as shown in Figure 1-
99b. As you edit the values in the cells
containing numeric data, watch the totals
in row 8, the totals in column G, and the
chart change.
2. Change the worksheet title in cell Al to
the Title cell style and then merge and
center it across columns A through G. Use
commands in the Font group on the Home
tab on the Ribbon to change the worksheet
subtitle in cell A2 to 16-point Corbel red,
bold font and then center it across
columns A through G. Use the Accent 1
theme color (column 5, row 1 on the Font
Color palette) for the red font color.
3. Update the document properties with your
name, course number, and name for the
workbook. Save the workbook using the
file name, Apply 1-1 Spoke-Up Bicycle
Shop 3rd Quarter Sales. Submit the
assignment as requested by your
instructor.
Table 1-6 New Worksheet Data
Cell Change Cell Contents To
A1 Spoke-Up Bicycle Shop
B4 11869.2
E4 9157.83
D6 5217.92
F6 6239.46
B7 3437.64
pf3
pf4
pf5
pf8

Partial preview of the text

Download EXCEL 2007—CHAPTER 1 LAB EXERCISES and more Slides Accounting in PDF only on Docsity!

EXCEL 2007—CHAPTER 1 LAB EXERCISES

Apply Your Knowledge—Changing the Values in a Worksheet

Instructions: Start Excel. Open the workbook Apply 1-1 Bicycle Shop 3 rd Quarter Sales (Figure 1-99a). See the inside back cover of this book for instructions for downloading the Data Files for Students, or see your instructor for information on accessing the files required in this book.

  1. Make the changes to the worksheet described in Table 1-6 so that the worksheet appears as shown in Figure 1- 99b. As you edit the values in the cells containing numeric data, watch the totals in row 8, the totals in column G, and the chart change.
  2. Change the worksheet title in cell Al to the Title cell style and then merge and center it across columns A through G. Use commands in the Font group on the Home tab on the Ribbon to change the worksheet subtitle in cell A2 to 16-point Corbel red, bold font and then center it across columns A through G. Use the Accent 1 theme color (column 5, row 1 on the Font Color palette) for the red font color.
  3. Update the document properties with your name, course number, and name for the workbook. Save the workbook using the file name, Apply 1-1 Spoke-Up Bicycle Shop 3rd Quarter Sales. Submit the assignment as requested by your instructor.

Table 1-6 New Worksheet Data Cell Change Cell Contents To A1 Spoke-Up Bicycle Shop B4 11869. E4 9157. D6 5217. F6 6239. B7 3437.

Extend Your Knowledge—Formatting Cells and Inserting Multiple Charts

Instructions: Start Excel. Open the workbook Extend 1-1 Pack-n-Away Shipping. See the inside back cover of this book for instructions for downloading the Data Files for Students, or see your instructor for information on accessing the files required in this book. Perform the following tasks to format cells in the worksheet and to add two charts to the worksheet.

  1. Use the commands in the Font group on the Home tab on the Ribbon to change the font of the title in cell Al to 24-point Arial, red, bold and subtitle of the worksheet to 16-point Arial Narrow, blue, bold.
  2. Select the range A3:E8, click the Insert tab on the Ribbon and then click the Dialog Box Launcher in the Charts group on the Ribbon to open the Insert Chart dialog box (Figure 1-100).
  3. Insert a Stacked Line chart by clicking the Stacked Line chart in the gallery and then clicking the OK button. Move the chart either below or to the right of the data in the worksheet. Click the Design tab and apply a chart style to the chart.
  4. If necessary, reselect the range A3:E8 and follow Step 3 above to insert a 3-D Area chart in the worksheet. You may need to use the scroll box on the right side of the Insert Chart dialog box to view the Area charts in the gallery. Move the chart either below or to the right of the data so that each chart does not overlap the Stacked Line chart. Choose a different chart style for this chart than the one you selected for the Stacked Line chart.
  5. Resize each chart so that each snaps to the worksheet gridlines. Make certain that both charts are visible with the worksheet data without the need to scroll the worksheet.
  6. Update the document properties with your name, course number, and name for the workbook.
  7. Save the workbook using the file name, Extend 1-1 Pack-n-Away Shipping Charts. Submit the assignment as requested by your instructor.

Lab 1: Annual Cost of Goods Worksheet

Problem: You work part-time as a spreadsheet specialist for Kona's Expresso Coffee, one of the up-and-coming coffee franchises in the United States. Your manager has asked you to develop an annual cost of goods analysis worksheet similar to the one shown in Figure 1-102.

Instructions: Perform the following tasks.

  1. Start Excel. Enter the worksheet title, Kona's Expresso Coffee, in cell A1 and the worksheet subtitle, Annual Cost of Goods, in cell A2. Beginning in row 3, enter the store locations, costs of goods, and supplies categories shown in Table 1-7.
  2. Use the SUM function to determine the totals for each store location, type of supply, and company grand total.
  3. Use Cell Styles in the Styles group on the Home tab on the Ribbon to format the worksheet title with the Title cell style. Center the title across columns A through G. Do not be concerned if the edges of the worksheet title are not displayed.
  4. Use buttons in the Font group on the Home tab on the Ribbon to format the worksheet subtitle to 14-point Calibri dark blue, bold font and center it across columns A through G.
  5. Use Cell Styles in the Styles group on the Home tab on the Ribbon to format the range A3:G3 with the Heading 2 cell style, the range A4:G7 with the 20% - Accent1 cell style, and the range A8:G8 with the Total cell style. Use the buttons in the Number group on the Home tab on the Ribbon to apply the Accounting Number format to the range B4:G4 and the range B8:G8. Use the buttons in the Number group on the Home tab on the Ribbon to apply the Comma Style to the range B5:G7. Adjust any column widths to the widest text entry in each column.
  1. Select the range A3:F7 and then insert a 3-D Clustered Column chart. Apply the Style 5 chart style to the chart. Move and resize the chart so that it appears in the range A10:G22. If the labels along the horizontal axis (x-axis) do not appear as shown in Figure 1-102, then drag the right side of the chart so that it is displayed in the range A10:G22.
  2. Update the document properties with your name, course number, and name for the workbook.
  3. Save the workbook using the file name Lab 1-1 Konas Expresso Coffee Annual Cost of Goods.
  4. Print the worksheet.
  5. Make the following two corrections to the sales amounts: $9,648.12 for Seattle Condiments (cell E6), $12,844.79 for Chicago Pastries (cell C7). After you enter the corrections, the company totals in cell G should equal $462,135.04.
  6. Print the revised worksheet. Close the workbook without saving the changes. Submit the assignment as requested by your instructor.

Lab 2: Annual Sales Analysis Worksheet

Problem: As the chief accountant for Scissors Office Supply, Inc., you have been asked by the sales manager to create a worksheet to analyze the annual sales for the company by location and customer type category (Figure 1-103). The office locations and corresponding sales by customer type for the year are shown in Table 1-8.

Instructions: Perform the following tasks.

  1. Create the worksheet shown in Figure 1-103 using the data in Table 1-8.
  2. Use the SUM function to determine totals sales for the four offices, the totals for each customer type, and the company total. Add column and row headings for the totals row and totals column, as appropriate.
  3. Format the worksheet title with the Title cell style and center it across columns A through F. Use the Font group 0It the Ribbon to format the worksheet subtitle to 16-point Cambria green, and bold font. Center the title across columns A through F.
  4. Format the range A3:F3 with the Heading 2 cell style, the range A4:F8 with the 20% - Accent
  5. cell style, and the range A9:F9 with the Total cell style. Use the Number group on the Ribbon to format cells B4:F4 and B9:F9 with the Accounting Number Format and cells B5:F8 with the Comma Style numeric format. Adjust the width of column A in order to fit contents of the column.
  6. Chart the range A3:E8. Insert a 100% Stacked Column chart for the range A3:E8, as shown in Figure 1-103, by using the Column button on the Insert tab on the Ribbon. Use the chart location A11:F22.
  7. Update the document properties 'with your name, course number, and name for the workbook.
  8. Save the workbook using the file name, Lab 1-2 Scissors Office Supply Annual Sales. Print the worksheet.
  9. Two corrections to the figures were sent in from the accounting department. The correct sales are $98,342.16 for Miami's annual Small Business sales (cell C5) and $48,933.75 for
  10. St. Louis's annual Nonprofit sales (cell D8). After you enter the two corrections, the company total in cell F9 should equal $2,809,167.57. Print the revised worksheet.
  11. Use the Undo button to change the worksheet back to the original numbers in Table 1-8. Use
  12. the Redo button to change the worksheet back to the revised state.
  13. Close Excel without saving the latest changes. Start Excel and open the workbook saved in

Instructions Part 1:

  1. Using the numbers in Table 1-9, create the worksheet shown in columns A through F in Figure 1-104.
  2. Format the worksheet title as Calibri 24-point bold red.
  3. Merge and center the worksheet title in cell Al across columns A through F.
  4. Format the worksheet subtitles in cells A2 and All as Calibri 16-point bold green.
  5. Format the ranges A3:F3 and A12:F12 with the Heading 2 cell style, the ranges A4:F9 and A13:F17 with the 20% - Accent1 cell" style, and the ranges Al 0:F10 and A18:F18 with the Total cell style.
  6. Update the document properties, including the addition of at least one keyword to the properties, and save the workbook using the file name, Lab 1-3 Part 1 College Cost and Financial Support.
  7. Print the worksheet. Submit the assignment as requested by your instructor. After reviewing the numbers, you realize you need to increase manually each of the Junior-year expenses in column D by $600. Change the Junior-year expenses to reflect this change. Manually change the financial aid for the Junior year in cell D 16 by the amount required to cover the increase in costs. The totals in cells FI0 and F18 should equal $87,373.90. Print the worksheet. Close the workbook without saving changes.

Instructions Part 2:

  1. Open the workbook Lab 1-3 Part 1 College Cost and Financial Support and then save the workbook using the file name, Lab 1-3 Part 2 College Cost and Financial Support.
  2. Insert an Exploded pie in 3-D chart in the range G3:K10 to show the contribution of each category of cost for the Freshman year. Chart the range A4:B9 and apply the Style 8 chart style to the chart.
  3. Add the Pie chart title as shown in cell G2 in Figure 1-104.
  4. Insert an Exploded pie in 3-D chart in the range G12:K18 to show the contribution of each category of financial support for the Freshman year.
  5. Chart the range A13:B17 and apply the Style 8 chart style to the chart.
  6. Add the Pie chart title shown in cell G11 in Figure 1-104.
  7. Update the identification area with the exercise part number and save the workbook.
  8. Print the worksheet. Submit the assignment as requested by your instructor.

Instructions Part 3:

  1. Open the workbook Lab 1-3 Part 2 College Cost and Financial Support. Do not save the workbook in this part. A close inspection of Table 1-9 shows that both cost and financial support figures increase 6% each year. Use Excel Help to learn how to enter the data for the last three years using a formula and the Copy and Paste buttons on the Home tab on the Ribbon. For example, the formula to enter in cell C4 is =B4*1.06.
  2. Enter formulas to replace all the numbers in the range C4:E9 and C13:EI7. If necessary, reformat the tables, as described in Part 1. The worksheet should appear as shown in Figure 1-104, except that some of the totals will be off by 0.01 due to rounding errors.
  3. Save the worksheet using the file name, Lab 1-3 Part 3 College Cost and Financial Support.
  4. Print the worksheet. Press CTRL+ACCENT MARK C) to display the formulas.
  5. Print the formulas version. Submit the assignment as requested by your instructor.
  6. Close the workbook without saving changes.