TOTAL SALARY= BASIC SALARY + ANNUAL BONUS. 15%*Basic Salary) otherwise it will be 5% of the basic salary. Find ANNUAL BONUS if the BASIC SALARY is smaller than 900, the ANNUAL BONUS is 15% of the Basic Salary (i.e. Ensure that the worksheet looks like the one above. Create the worksheet shown above and rename it as EMPLOYEE RECORDS. Find Count of Items, Average of Taxes, Min Item PRICE and Max Item PRICE. RATE (If TOTAL PRICE AFTER TAX > 3500 then the rate is “HIGH”, otherwise it is REASONABLE. TOTAL PRICE AFTER TAX = TOTAL PRICE BEFORE TAX + TAX. TAX (If ITEM PRICE is less than 100, TAX is 50, and otherwise it should be 100). Save the file as Excel 4.įor the above table find the following: 1. Center the worksheet vertically and horizontally on the page.
Create Footer with page number in the center section. Create a Header that includes your ID in the left section and Name in the right section.
Format column E to include % and two decimal places. Enter a formula to find TOTALS, AVERAGE, HIGHEST, and LOWEST values. Enter a formula to find %Change for the first item where %Change = Change / Last Year. Enter a formula to find CHANGE for the first item where Change = This Year - Last Year. Create footer with DATE in the left section and TIME in the right section. Create a Header that includes your name in the left section, page number in the center section, and your ID number in the right section. Align all column title labels horizontally and vertically at the center. Format numeric data to include commas and two decimal places. Enter formula to find TOTALS, AVERAGE, HIGHEST, LOWEST, and COUNT values. Copy the formula to the remaining employees. Enter the formula to find TOTAL SALARY for the first employee where Total Salary = SALARY + COMMISSION. Enter the formula to find COMMISSION for the first employee. Set the column widths as follows: Column A: 8, Column B: 14, Columns C & D: 15, Columns E & F: 14. Create the footer that includes the current Date in the center. Create a header that includes your name in the left section, and your ID number in the right section. Set the worksheet vertically and horizontally on the page. Using AutoFill, copy the formula to the remaining cells. Enter a formula in cell E4 to multiply ‘Calls Per Hours’ by the fixed Bonus Rate in cell B12. Calculate the Calls per Hour, enter a formula in cell D4 to divide numbers of calls by hours worked. Format cell B12 to include % sign with 0 Decimal places. Format column E to include euro (€) sign with two decimal places. Apply border, gridlines and shading to the table as desired. Use Warp Text, Orientation and merge cells as desired. Align all labels vertically and horizontally at the center. Set the row height of rows 1 & 3 to size 30 and rows 4 until 10 to size 20. Enter the labels and values in the exact cells locations as desired. Open a new workbook delete Sheet 2 & 3, and rename Sheet 1 to (Call Statistics). Set the work sheet vertically and horizontally on the page. Calculate the Net Pay enter a formula in cell G4 to subtract Social Security Tax from Gross Pay. Calculate the Social Security Tax (S.S Tax), which is 6% of the Gross Pay enter a formula in cell F4 to multiply Gross Pay by 6%. Calculate the Gross Pay for employee enter a formula in cell E4 to multiply Hourly Rate by Hours Worked. Format cells E4:G8 to include dollar sign with two decimal places. Use the following format for the date in cell B2 (Saturday, January 1, 2011) 10. Apply borders, gridlines and shading to the table as desired. Use warp text and merge cells as desired. Use AutoFill to put the Employee Numbers into cells A6:A8. Open a new workbook and rename sheet1 with the name “Payroll”. Scenario Manager using What-if Analysis Linking worksheetsġ.
Working with the Horizontal Lookup Function Working with the Count If and Sum If StatementsĪbsolute Cell Referencing Working with the Vertical Lookup Function Number, Commas and Decimal numeric formats Working with Formulas ( Maximum, Minimum, Average, Count and Sum) Introduction to Excel files, Worksheets, Rows, Columns, Row/Column Headings Inserting, Deleting and Renaming Worksheets Inserting and Deleting Rows and Columns Changing Column Width and Row Height Merging Cells, Cell range Format Cells Fonts, Alignment, Warp Text, Text Orientation, Border and Shading Auto Fill Currency Numeric formats Previewing Worksheet Center the worksheet horizontally and vertically on the page Saving and excel file Table of Contents Exercise 1 University of Bahrain College of Information Technology Department of Information Systems