EXCEL TEST TERM1 2018

The information below is driven from TESLA Motors selected staff. The staff include JACK, ALICIA, MASK, BROWN, XAVIER, RONALD, SONIA, CHRISTINE, PHILIPS and INNOCENT. They earn the following amount as Basic Salary respectively: 860500, 860000, 270000, 880000, 600000,310000, 630000, 440000, 650000 and 755000 and they have worked for 3, 8, 25, 11, 17, 9, 4,18,22 and 14 years respectively. Enter the above information into a spreadsheet application using column names name, basic salary and duration. [3 marks]

Add new column titles for Transport, Meals, Bonus, Gross and Netpay.[1 marks]

Insert new column between employee name and basic salary for department anduseeither IF or LOOKUP function to assign departments using the information below. [2 marks]

100,000 – 300,000 / Security
301,000 – 600,000 / Accounts
601,000 – 900,000 / Managers
  • Bonus is obtained by using the following conditions:[2 marks]

Managers: 450000 + (Years worked multiplied by 10000).

If Accounts: 250000 + (Years Worked multiplied by 7500).

And the rest, 100000 + (Years Worked multiplied by 5500).

  • Transport is calculated as 27% of Basic Salary.[1 marks]
  • Meals allowances are 42% of Basic Salary. [1 mark]
  • Determine theGross Payfor each employee which is the sum of all allowances and Basic Salary. [1 mark]
  • Add a column after gross for PAYE and calculate PAYE which is 13% of the employee’s gross pay. [1 mark]
  • Net Pay is calculated by subtracting PAYE from Gross.[1 mark]
  • Add your name and class number as header.[1 marks]
  • Add arrow above the titles for the title: TESLA MOTORS LIMITED. it should be in font size 18.5, color red. Increase the row 1 height to 30. [1 marks]
  • Add thousand separators (,) in all your work.[1 mark]
  • In cell H20, determine Average Gross Pay for accounts workers.[2 marks]
  • Add a new worker named Boris between RONALD AND SONIA, 290,000, worked for 9 years and in security department. [1 mark]
  • Create a labelled doughnut chart showing basic salary for each employee. Place your chart just below your work. [3 marks]
  • Use the COUNTIF function to get how many employees earn 600000 and above of their Basic Salary [2 marks]
  • Insert a thick boarder outside your work with grids inside.[1 mark]
  • Insert your full names and registration number to the left and right header section of the worksheet. [1 mark]
  • Format your worksheet in landscape orientation. [1 marks]
  • Save your work as Original Work in a folder on the desktop. [1 marks]

END