Employees Central Sierra Employee ID First Name Last Name Br

Employeescentral Sierraemployee Idfirst Namelast Namebranchtitledepart

Employees central Sierra Employee ID First Name Last Name Branch Title Department Start Date Base Monthly Salary Bonus Total Monthly Salary 101 Bob Lingle Cameron Park CEO Administration 01-Jan- Lanita McCartney Cameron Park Chief Operating Office Administration 01-Jan- Eleesha Santos Cameron Park Insurance Agent Health and Benefits 10-Feb- Roy Baxter Granite Bay Insurance Agent Property and Casualty 11-Feb- Jennifer Alaro Folsom Administrative Assistant Administration 13-Apr- Tami Chan Cameron Park Administrative Assistant Health and Benefits 07-May- Charlene Althouse Granite Bay Large Group Specialist Property and Casualty 01-Jul- Juan Taylor Granite Bay Insurance Agent Insurance Sales 01-Sep- Wayne Reza Cameron Park Insurance Agent Health and Benefits 19-Nov- Cheryl Nevens Cameron Park Chief Information Officer Administration 07-Jan- Branch Totals Cameron Park Folsom Granite Bay Total Branch Address City State Zip Phone Fax Cameron Park 3360 Coach Lane Cameron Park CA Folsom 75 Natoma St. #B1 Folsom CA Granite Bay 6132 Del Oro Road Granite Bay CA Bonus Central Sierra Base Monthly Salary Bonus % % % % % Excel 2013 Chapter 2 Working with Functions and Formulas Last Updated: 2/4/15 Page 1 USING MICROSOFT EXCEL 2013 Independent Project 2-4 Independent Project 2-4 Central Sierra Insurance (CSI) is a multi-office insurance company that handles all lines of commercial and personal insurance policies. CEO Eliana Lingle is planning to pay a bonus to employees calculated on each employee’s base monthly salary. For this project, you create a spreadsheet for Ms. Lingle to summarize the bonus payments. This project has been modified for use in SIMnet®. Skills Covered in This Project ï‚· Create and copy formulas. ï‚· Apply mathematical order of operations. ï‚· Use relative, absolute, and mixed cell references. ï‚· Use AutoSum. ï‚· Apply the VLOOKUP function. ï‚· Apply the SUMIF function. 1. Open the CentralSierra-02.xlsx start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. NOTE: If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify it. 2. Create a VLOOKUP function to calculate the bonus amount for each employee. a. Click the Employees sheet tab, and select I4. b. Type a formula to reference the Base Monthly Salary amount as the lookup_value. c. Click the Bonus sheet tab for the table_array argument. d. Use the second column of the table for the col_index_num. e. Verify that the formula syntax is =VLOOKUP(H4,Bonus!A4:B8,2). 3. Select the formula in I4 and apply absolute values to the cell references. (Hint: Not all references or ranges may require the absolute values.) 4. Copy the formula in I4 to I5:I13. 5. Type a formula in J4 to calculate the total monthly salary. a. Use parentheses in the formula to calculate the amount of the bonus and then add the bonus to the Base Monthly Salary. b. Copy the formula in J4 to J5:J13. 6. Select J15 and use AutoSum to calculate a total for the Total Monthly Salary column. a. Edit the range to not include the empty cell. b. Apply the Total cell style to J15. 7. Create a SUMIF function to calculate the total monthly salary for each office. a. Select B26. b. Use the information in column D (Branch) for the range argument. c. Select D4 (Cameron Park) for the criteria argument. d. Use the information in column J (Total Monthly Salary) for the sum_range argument. 8. Edit the formula in B26 to include absolute references in all cell references and ranges. 9. Copy and edit a formula. a. Select the formula in B26 and copy the formula through B28. b. Select B27 and change the reference from Cameron Park ($D$4) to Folsom ($D$8). c. Select B28 and change the reference from Cameron Park ($D$4) to Granite Bay ($D$7). 10. Select cell B29 and create a formula to add the salary amounts for each branch. 11. Format the data in column I using the Percent Style button. Step 1 Download start file Excel 2013 Chapter 2 Working with Functions and Formulas Last Updated: 2/4/15 Page 2 USING MICROSOFT EXCEL 2013 Independent Project . Format the salary amounts in columns H and J and the “Branch Totals†section to the Accounting style with no symbol. 13. Select the data from A3:J30 and change the font to Calibri and the font size to 11 pt. 14. Select E26 and type Highest Salary. 15. Type Lowest Salary in E27 and Average Salary in E28. 16. Select F26 and type =ma. When you see MAX, double-click to insert the function. Drag to select J4:J13. Press Enter to complete the formula. 17. Select F27 and use the MIN function to calculate the lowest salary. 18. Select F28 and use the AVERAGE function to calculate the average salary. 19. Select E25 and type Salary Summary. 20. Merge and center “Salary Summary†in cells E25 and F25. 21. Format the “Salary Summary†section to match the “Branch Totals†section. a. Apply a fill color and Bold the title. Apply Accounting Format without the symbol to the salary results. b. Apply All Borders from the Borders button drop-down list. (Hint: Use the Format Painter button to copy all formats at once.) 22. Format the two total amounts (B29 and J15) to include a dollar sign. (Hint: Click the button in the Number group.) 23. Adjust column widths if necessary. 24. Save and close the workbook (Figure 2-73). 25. Upload and save your project file. 26. Submit project for grading. 2-73 Excel 2-4 completed Step 2 Upload & Save Step 3 Grade my Project Excel 2013 Chapter 1 Creating and Editing Workbooks Last Updated: 2/4/15 Page 1 USING MICROSOFT EXCEL 2013 Independent Project 1-6 Independent Project 1-6 You have been hired as the accounts receivable clerk for a privately owned accounting company called Livingood Income Tax & Accounting. It is your job to track all the payments from clients every day. Your supervisor has requested that you convert your payment table to an Excel spreadsheet. Skills Covered in This Project ï‚· Create and save a workbook. ï‚· Enter text and numbers. ï‚· Change font size and attributes. ï‚· Use AutoSum. ï‚· Adjust column width and row height. ï‚· Spell check. ï‚· Apply Freeze Panes. ï‚· Change zoom level. ï‚· Apply a theme and Cell Styles. ï‚· Apply page layout options. ï‚· Hide a row. ï‚· Rename and apply color to sheet tabs. 1. Open EX2013-IndependentProject-1-6 start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it. Note: If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify it. Important: The start file for this project is intentionally blank. 2. Apply the Organic theme to the worksheet. Change the theme font to Calibri. Important: To ensure accurate grading for later instructions involving applying colors, instruction 2 must be completed. 3. Select A1 and type Livingood Income Tax and Accounting, press Enter, type Payment Schedule, and press Enter again. 4. Type in the remaining worksheet data from Figure 1-108. 5. Edit the title in A1 to replace the word “and†with the symbol &. 6. Edit the value in cell B5 to 451.25. Change “Over Due†in cell F4 to Overdue. 7. Apply the Title style to A1. 8. Apply formatting to cell ranges. a. Increase the font size of A4:G11 to 12 pt. b. Select cells B5:B11 and display the Format Cells dialog box. Select the Accounting format and change the Symbol to None. 9. Add the title Total in cell A13 and calculate the total for B13 using AutoSum. Adjust the cell range reference in the Formula bar. 10. Apply additional formatting. a. Apply the Total cell style to cells A13:G13. b. Select A13:G13 and increase the font size to 12 pt. c. Bold the entries in A4:G4. d. Center the data in A4:G4, A5:A13, D5:D13, and F5:F13 Step 1 Download start file Excel 2013 Chapter 1 Creating and Editing Workbooks Last Updated: 2/4/15 Page 2 USING MICROSOFT EXCEL 2013 Independent Project 1-6 e. Select A4:G4 and open the Format Cells dialog box. Add a thick Green, Accent 1, Darker 50% bottom border and a Green, Accent 1, Lighter 80% fill color using the second color in the fifth column. f. Select the cells in rows 6, 8, and 10 and apply the same fill color. g. Use the Border button and apply a bottom border to cells A2:G2. 11. Adjust column width and row height. a. Change the width of columns A:G to 14.0. b. Change the row height for rows 4 and 13 to 19.50. 12. Hide row 12. 13. Rename Sheet and color the sheet tab Green, Accent 1 (first color in the fifth Theme Color column). 14. Spell check the worksheet. 15. Apply Freeze Panes to B5. 16. Increase the magnification of the view to 125%. 17. Apply page layout options. a. Change the orientation to Landscape and scale the page to fit on one page. b. Center the worksheet horizontally on the page. c. Click the Custom Header button and add the Insert Sheet Name field in the Left Header section. Click the Format Text button and apply the font color Green, Accent 1, Darker 50% to the header field. d. Add the page number to the right section of the footer. e. Select print preview in the Page Setup dialog box to view your settings. 18. Save and close the workbook (Figure 1-109). 19. Upload and save your project file. 20. Submit project for grading. 1-109 Excel 1-6 completed Step 2 Upload & Save Step 3 Grade my Project