EXCEL 2007—CHAPTER 1 LAB EXERCISES, Slides of Accounting

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.2 E4 9157.83 D6 5217.92 F6 6239.46 B7 3437.64 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. 6. 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. 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-1 Konas Expresso Coffee Annual Cost of Goods. 9. Print the worksheet. 10. 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 G8 should equal $462,135.04. 11. 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% - Accent3 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 14. Step 7. Double-click cell E6 and use in-cell editing to change the Santa Fe annual Large Business sales (cell E6) to $154,108.49. Write the company total in cell F9 at the top of the first printout. Click the Undo button. 15. Click cell Al and then click the Merge & Center button to split cell A1 into cells A1, B1, 16. C1, D1, E1, and F1. To merge the cells into one again, select the range A1:F1 and then click the Merge & Center button on the Home tab on the Ribbon. 17. Close the workbook without saving the changes. Submit the assignment as requested by your instructor. Lab 3: College Cost and Financial Support Worksheet Problem: Attending college is an expensive proposition and your resources are limited. To plan for your four- year college career, you have decided to organize your anticipated resources and costs in a worksheet. The data required to prepare your worksheet is shown in Table 1-9. 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.