Spreedsheet 1
WEEK # 6 – EXERCISES CHAPTER # 10 (LEVEL 1 2)
Exercise Ch10-1: Level 1 Helmet Template You have a similar workbook to Kiolas that helps analysts in the helmet product group understand projected income for their project line. You want to update this workbook to include data validation rules for guiding data entry, protect the worksheets and workbook, and document the contents of the workbook so users know what its purpose is and how to enter the data accurately.
As in the Income Template workbook, columns A and B contain the assumptions, whereas columns D and E contain the projections. To prepare this workbook so that it is error free and TheZones product managers can easily work with it, use the data validation and protection tools to help prevent data-entry errors and unauthorized changes to the worksheets. Also provide adequate documentation. Complete the following: 1. Open the workbook named Helmets.xlsx located in the Chapter 10 folder, and then save the file
as 10-1-Helmet-Template-YourName.xlsx. Make sure the Projected Income worksheet is displayed. 2. Apply a validation rule to cell B4 that displays a drop-down list of all helmets provided in the Data worksheet as well as the total. Once the validation rule is in place, select the Adult Bicycle Helmet Blue. 3. Apply a validation rule to cell B8 and add an error alert warning that explains that this value is expected to be between 0% and 100% and that the cell has been formatted as a percent. Allow other values to be entered. Enter 330%. 4. Apply a validation rule to cell B12 that limits the tax rate to 0%, 15%, or 35%. Include an input message listing these three values and alerting the user that the cell is formatted as a percent. 5. Apply a validation rule to cell B14 and include an input message that explains this value must be a positive integer. 6. Use the Circle Invalid Data feature to identify existing data entry errors. Correct circled errors. Set the COGS value to 33%.
2
WEEK # 6 – EXERCISES CHAPTER # 10 (LEVEL 1 2)
7. Include a comment for cell B9 that this value is prorated based on percent of sales. 8. Hide the Salesperson column on the SalesHistory worksheet. 9. Remove the Locked property from cells B4, B8, B12, and B14 on the Projected Income worksheet. 10. Insert a new worksheet named Documentation and enter your name and the current date in the appropriate cells. Include text that explains the purpose of the workbook and how to use it. 11. Turn on worksheet protection for each of the worksheets. Do not specify a protection password. 12. Turn on workbook protection. Do not specify a protection password. 13. Add your name and Current Date at the end of the workbook.
14. Save and close the 10-1-Helmet-Template-YourName.xlsx workbook.
3
WEEK # 6 – EXERCISES CHAPTER # 10 (LEVEL 1 2)
Exercise Ch10-2: Level 1 Troubleshooting Formulas and Data Entry in a Payroll Data Workbook for Irenes Scrapbooking World Similar to other small businesses, Irenes Scrapbooking World outsources the processing of its payroll to its accounting firm. Twice per month, Irene Watson, the owner of Irenes Scrapbooking World, creates a workbook that contains her employees payroll information to send to the accounting firm of Wipson & Lynn, LLP. Wipson & Lynn then uses that information to prepare the paychecks for the employees and calculate Irenes Scrapbooking Worlds payroll liabilities to the government and other entities, and to prepare its payroll-related reports, such as the Federal 941 forms. Wipson & Lynn charges Irene based on the number of paychecks it processes for her company. A number of Irenes payroll-related workbooks have contained data-entry errors that the firms personnel had to fix before it could process the payroll. Wipson & Lynn also charges Irene for the time it takes to track down and fix those errors. Irene has asked you to use the Excel data validation and protection tools to troubleshoot the current payroll workbook and set up the workbook to help prevent errors from occurring in the future. Complete the following steps: 1. Open the workbook named Payroll.xlsx located in the Chapter 10 folder, and then save the file
as 10-2-Scrapbooking-Payroll-YourName.xlsx. 2. On the Documentation worksheet, enter your name and the current date in the appropriate cells. Review the documentation information. 3. Switch to the Payroll Register worksheet. Set up an appropriate validation rule, input message, and error alert for the Tax Status data in cells C7:C15. 4. Set up an appropriate validation rule, input message, and error alert for the Allowances data in cells D7:D15. (Hint: Employees can take 0 or more withholding allowances.) 5. Set up an appropriate validation rule, input message, and error alert for the Insurance Plan data in cells E7:E15. (Hint: The valid health plan options are stored in cells L22:L24.) 6. Identify and correct all data that does not meet the validation settings you created in Steps 3 through 5. 7. Correct any other flagged or unflagged errors. 8. Change the properties of the cells so that users can edit only the hours worked information for each employee when the worksheet is protected. 9. Hide the column containing the SSN numbers. 10. Insert a comment in cell D6, stating that Allowances are the number of dependents claimed for income tax withholding purposes. 11. Enable worksheet protection. Do not specify a password. 12. Enable the workbook structure protection.
13. Save and close the 10-2-Scrapbooking-Payroll-YourName.xlsx workbook.
4
WEEK # 6 – EXERCISES CHAPTER # 10 (LEVEL 1 2)
Exercise Ch10-3: Level 2 Cost of Goods Sold. Kiola creates a worksheet detailing the Cost of Goods Sold for use in the workbook, but notices that the worksheet contains a few error messages and error indicators. Figure 10.34 shows the Cost of Goods Sold worksheet.
This worksheet calculates the monthly total manufacturing cost of producing sunglasses by summing labor, materials, and overhead. It also records the costs of the beginning and ending sunglass inventory for the month. You have been directed to estimate the Cost of Goods Sold by subtracting the ending inventory cost from the beginning inventory cost and adding that to the total manufacturing cost. Your task is to audit the worksheet and correct the problems. Complete the following: 1. Open the workbook named COGS.xlsx located in the Chapter 10 folder, and then save the file as
10-3-COGS-Template-YourName.xlsx. 2. Use the Error Checking dialog box to diagnose and fix the #NAME? Error message in cell B14. 3. Use the Trace Error command on the Error Checking menu to trace the #VALUE! Errors in cells C11, D11, and E11. Diagnose and fix the errors. 4. Use the Error Alert button to view Excel Help information about the error in cell E5. Read the Help information, and then close the Excel Help window. Correct the error as needed. 5. Use the Trace Precedents tool in the Formula Auditing group to trace the unflagged error in cell B15. Correct the formula so that it correctly identifies the maximum manufacturing cost among the three months. 6. View and evaluate the worksheet formulas. Correct any errors you find. 7. Add your name and date at the end of the exercise
8. Save and close the 10-3-COGS-Template-YourName.xlsx workbook.
5
WEEK # 6 – EXERCISES CHAPTER # 10 (LEVEL 1 2)
Exercise Ch10-4: Level 2- Troubleshooting Formulas in a Job Invoicing Workbook for Davids Computer Repair. As extra income, David Green has been operating a small computer repair business for a number of years. He has decided to use an Excel workbook to create a template for a sales invoice. Davids idea is that he will enter the data for each job in a few cells on a worksheet. This data will be inserted in an invoice in another portion of the worksheet, which he can then print and send to the customer as a bill. As a new user of Excel, David has asked for your help in identifying and correcting errors in the invoice area of his worksheet. In addition, he asks for your help in creating some basic documentation for the workbook so that others can use it. 10 Complete the following steps: 1. Open the workbook named Repair.xlsx located in the Chapter 10 folder, and then save the file
as 10-4-Repair-Invoicing-YourName.xlsx. 2. On the Documentation worksheet, insert your name and the current date in the appropriate cells. 3. On the Job Invoicing worksheet, use the Formula Auditing tools to identify and fix errors in the formulas of the Customer Invoice section. Evaluate whether all flagged errors indicate true errors or false positives. Fix the flagged errors that are truly errors. Also, look for any false negative errors in the invoices formulas. Use the following explanations for the calculations as you troubleshoot the worksheet:
The Invoice Date should be a formula that displays the current date. The total labor charge is computed by multiplying the number of hours spent on the job by
the labor charge per hour. Sales tax is computed by multiplying a sales amount by the sales tax rates. Sales tax is
computed separately for labor and parts as they have different sales tax rates. The total invoice amount due from the customer is the sum of the total charges for parts,
labor, and the sales tax items. 4. Enter the following job data and view the resulting invoice:
Job date: 2/17/16 Customer name: Mason McDonald Total hours spent on job: 1.3 Charge per hour: $55 Total parts for the job: $119.95 Sales tax on labor is 5% and Sales tax on Parts is 7.5%
5. On the Documentation worksheet, explain the purpose of the workbook, assumptions in the workbook, and instructions for using the workbook. 6. Display the Job Invoicing worksheet formulas, and adjust column widths so they are only as wide as necessary. Set the page layout orientation to Landscape. Show Trace Precedent lines for cells F18 and F14. 7. Print the worksheet to a Microsoft XPS Document Writer file named Job Invoicing. Be sure to execute the print command directly after displaying the Trace Precedent lines or they will not be visible.
6
WEEK # 6 – EXERCISES CHAPTER # 10 (LEVEL 1 2)
8. Reset the workbook to display values, reset to portrait orientation, readjust column widths, and turn off Trace Precedent lines.
9. Save and close the 10-4-Repair-Invoicing-YourName.xlsx workbook.
