Integrated Business Projects Part 2 Accounting Project 8 Managing Payroll Records
Project 8 Objectives • Understand basic payroll terminology • Use nested formulas and functions in a worksheet • Modify the layout and format of a worksheet • Freeze panes, lock cells, and protect sheets in a workbook Continued
Objectives (continued) • Integrate word processing and spreadsheet applications • Capture screens and edit pictures • Sort data in a worksheet • Summarize data from multiple sheets in a workbook • Use search engines, iPhones®, and mobile phones for Internet searches and research
Project 8 Jobs • Job 8-1: Complete Worksheet for Salaried Employees • Job 8-2: Update Payroll Worksheet • Job 8-3: Create Payroll Worksheet for Hourly Employees • Job 8-4: Prepare June Salaried Employee Worksheet • Job 8-5: Add and Sort Records • Job 8-6: Complete Quarterly Summary Report
Job 8-1: Complete Worksheet for Salaried Employees Jobs List • Formatting, naming, and navigating worksheets • Using the VLOOKUP function • Creating and editing nested functions • Using absolute and relative references • Freezing panes • Searching the Internet using a computer, iPhone®, or mobile phone
File Management Jobs List Create a folder for each project in this course and be sure to save all project files together in that folder.
Adjust Column Width Jobs List To adjust column width to fit the largest entry in the column, double-click between column headings. Double-click between column headings
Job 8-2: Update Payroll Worksheet Jobs List • Inserting and moving a worksheet • Adding columns to a worksheet
Move a Worksheet Jobs List To move a worksheet in a workbook, click the sheet tab, hold down the mouse button, and drag and drop to the new location.
Select an Entire Worksheet Jobs List To select an entire worksheet, click the Select All button, ( ), or press CTRL-A.
Job 8-3: Create Payroll Worksheet for Hourly Employees Jobs List • Creating a worksheet to calculate gross pay, deductions, and net pay • Copying data from a source document (Word) and pasting it in a destination document (Excel) • Using an online calculator to determine income tax and net pay
Share Data Among Applications Jobs List • Use the Paste Special option to select the format you will use to paste the data. • Use Paste Options, which allow you to keep the formatting applied in the source document or match the formatting in the destination document.
Job 8-4: Prepare June Salaried Employee Worksheet Jobs List • Applying conditional formatting • Print screens • Using formatting tools to change a graphic • Locking cells and protecting a worksheet • Using Add-ins (optional) • Capturing images and editing pictures
Data Protection Jobs List By default, all cells on a worksheet are locked. Protecting the sheet prohibits users form making changes to the locked cells. To make changes to locked cells or copy a protected sheet, you must first unprotect the sheet.
Unlock Cells Jobs List • Select each cell or range to be unlocked. • On the Home tab, click the Font dialog box launcher. • In the Format Cells dialog box, click the Protection tab, and clear the Locked cell.
Protect a Worksheet Jobs List Review/Changes/Protect Sheet 3. Protect Sheet button 1. Review tab 2. Changes group
Unprotect a Worksheet Jobs List Review/Changes/Unprotect Sheet 1. Review tab 2. Changes group 3. Unprotect Sheet button
Callouts Jobs List Insert/Illustrations/Shapes 3. Shapes button 1. Insert tab 2. Illustrations group
Job 8-5: Add and Sort Records Jobs List • Adding and moving worksheets • Sorting worksheet records
Insert Rows Jobs List Home/Cells/Insert/Insert Sheet Rows 1. Home tab 3. Insert button 2. Cells group
Sort Data Jobs List Data/Sort and Filter/Sort 3. Sort button 1. Data tab 2. Sort & Filter group
Paste Special as Picture Jobs List When you copy data from Excel (the source document) and choose to use Paste Special to paste it as a picture in Word (the destination document), you get an exact copy of the data as it appears in the Excel worksheet.
Job 8-6: Complete Quarterly Summary Report Jobs List • Linking data in multiple worksheets using 3-D references • Applying a style to a worksheet • Using Paste Special options
Apply a Theme Jobs List Page Layout/Themes/Themes 1. Page Layout tab 3. Themes button 2. Themes group
Apply a Style Jobs List END SHOW Home/Styles/Format as Table 1. Home tab 3. Format as table button 2. Styles group