NAME______

Instructions:

  1. Import the file at the location: 2009FallA.txt into Microsoft Excel. Name your file First_Last_X, where First and Last are your first and last names and X is your lab section (A or B),For example, Sallie_Taylor_A or Sallie_Taylor_B. Name the first worksheet 2009 Sales Stats.
  1. Format the file as demonstrated in class. (Bold, Boarders, Color, Dates etc.) YOU SHOULD ALSO SET CELLS WITH DOLLAR FIGURES TO BE OF TYPE CURRENCY. To do this, select the cells, right click, select format, and then select currency.
  1. Calculate what December saleseach of the below employees must achieve to attain the average specified. The value should be entered into MS Excel and written below.

Employee / December Sales / 2009 Average
Molly / $49,000
Candis / $52,000
Elaine / $61,000
Mark / $50,500
  1. Set the December sales for all other employees to be 52000.
  1. Calculate the MIN, Max, Total, and Average of all columns as appropriate for the completed solution. (The attached screen shot is for reference only to demonstrate what your completed solution might look like. Your numbers may differ from those in the figure.)
  1. Calculate the total sales for each employee for the year and write the details for Molly in the space provided. Title the column Total Sales.

FORMULA ______

VALUE______

  1. Assume that employees are paid on a commission basis. Base salary is calculated as 15% of the total sales for all the months of 2009. Calculate the base salary of each employee and write the details for Molly in the space provided. Title the column ‘Base Salary’.

FORMULA ______

VALUE______

  1. Assume that the bonus of each employee is calculated as the sum of 10% of the second largest sales month (of all 12 months), 20% of the third largest sales month (of all 12 months), and 30% of the third smallest sales month(of all 12 months). Calculate the bonus of each employee and write the details for Molly in the space provided. Title the column ‘Bonus’.

FORMULA ______

VALUE______

  1. The total compensation for an employee is the sum of the Base Salary and the Bonus. Calculate the Base Salary of each employee and write the details for Molly in the space provided. Title the column Comp.

FORMULA ______

VALUE______

  1. Determine Rank 1 for each employee as follows: If an employee's bonus for the year is greater than the average bonus across all employees, rank the employee as "Above" otherwise rank the employee as "Below". Write the details for Molly in the space provided. Title the column Rank 1.

FORMULA ______

VALUE______

  1. Determine Rank 2 for each employee as follows: If an employee's total sales for the year is greater than 700000, rate the employee as "HIGH". If the employee's total sales for the year is greater than 600000, rate the employee as "ACCPT". Otherwise, rank the employee as "BELOW". Write the details for Molly in the space provided. Title the column Rank 2.

FORMULA ______

VALUE______

  1. Implement the ability for a manager to select an action for each employee. The possible actions are Promotion, Probation, Keep and Lay-off. Randomly select an action for each employee. (This exercise utilizes a 'list' from with the user can select.) Title the column Mgr. Dec.
  1. Sort the data by Rank 2.
  1. Find the sub-totals for Rank 2.
  1. Create a pie and bar chart with data labels on a second worksheet. Label the worksheet '2009 Sales Charts.’
  1. On the table below, select the Rank 1 and Rank 2 of each employee.

Kurt / Below Above / Below Accpt High
Ursula / Below Above / Below Accpt High
George / Below Above / Below Accpt High
Bob / Below Above / Below Accpt High
Molly / Below Above / Below Accpt High
Greg / Below Above / Below Accpt High
Neil / Below Above / Below Accpt High
Max / Below Above / Below Accpt High
Wes / Below Above / Below Accpt High
Harry / Below Above / Below Accpt High
Candis / Below Above / Below Accpt High
Alex / Below Above / Below Accpt High
Mark / Below Above / Below Accpt High
Paula / Below Above / Below Accpt High
Sam / Below Above / Below Accpt High
Lester / Below Above / Below Accpt High
Elaine / Below Above / Below Accpt High
Tina / Below Above / Below Accpt High
Chuck / Below Above / Below Accpt High
Quint / Below Above / Below Accpt High

Send your completed work to the address written on the blackboard.

DO NOT leave class until the instructor confirms that your solution has been received. KEEP A COPY of your work both on your jump drive and in your 'Sent email'.

Answer the questions on the next pages.

MAKE SURE YOUR NAME IS ON THIS PAPER and hand it to the instructor.

Short Answer Questions

  1. (True/False) Excel is a type of productivity software that can be used to turn data into information.

Circle OneTrueFalse

2. (True/False) An Excel file is also sometimes referred to as a flat file.

Circle OneTrueFalse

3. (True/False) Goal Seeking is an operation with in Microsoft Excel that calculates the input required to achieve a particular output.

Circle OneTrueFalse

4. (Fill in the blank) A ______in MS Excel is uniquely identified by the intersection of a row and a column.

5. (Multiple Choice) Which list places the Microsoft Excel objects in order from largest to smallest.

  1. Workbook, Worksheet, Cell
  2. Worksheet, Workbook, CellAnswer ______
  3. Cell, Worksheet, Workbook
  4. Cell, Workbook, Worksheet
  5. None of these

6. (Multiple Choice) Which of the following expressions are equivalent?

ExpressionsAnswer Choices

  1. Max(A1:A10) A. None of the expressions are equivalent
  2. Large(A1:A10,10)B. A,B,C ONLY
  3. Small(A1:A10,1)C. B,C ONLY
  4. Min(A1:A10)D. B,C,D ONLY

E. C,D ONLY

F, A and B are equivalent and C and D are equivalent.

7. (Short Answer) Assume the below cells contain the values shown. Find the value of Average(Large(A1:A5,3),Small(A1:A5,3)).

A15

A26

A37Answer ______

A48

A59

8. (Multiple Choice) Which of the following equations (referring to MS Excel formulae) is true?

  1. PPMT = PMT - IPMT
  2. PMT = PPMT - IPMTAnswer ______
  3. IPMT = PMT + PPMT
  4. PPMT = PMT + IPMT
  5. None of the above are true

9. A ______cell reference adjusts to its new location when the formula is copied and pasted.

10. (Short Answer) The pH value of a chemical is represented as:

  • between 0 and 7Acid
  • 7Neutral
  • between 7 and 14Base

Assuming that the pH of a chemical is located in cell A7, write the formula in the space provided to determine whether the chemical is Acid, Neutral, or Base.

______

11. (Short Answer) Write the formula in the space below to find the sum of the values in cells A1 , A7, and Z4.

______

12. (Short Answer) Write the formula in the space below to find the sum of the values in cells A2 to A12 and B2 to B12. (There are multiple possible answers that will be accepted, but an answer should not include more than one '+' sign.)

______

13.Use MS Excel to find the monthly payment of a 15 year fixed home loan of $150,000 at 8% annual interest.

Formula (#’s, not references to cells)

______

Value______

14.Use MS Excel to find the interest payment for the first installment of a 15 year fixed home loan of $150,000 at 8% annual interest.

Formula (#’s, not references to cells)

______

Value______

15.Use MS Excel to find the principle payment for the first installment of a 15 year fixed home loan of $150,000 at 8% interest.

Formula (#’s, not references to cells)

______

Value______