Taylorsallie.com



NAME_____________________________________________________________________________

Instructions:

1. Import the file at the location: into Microsoft Excel. Name your file First_Last_X, where First and Last are your first and last names.

MAKE SURE YOU SAVE YOUR FILE AS EITHER .xls or .xlsx.

2. 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.

3. Calculate what December sales each 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 |2016 Average |

| | | |

|Uma | | |

| | |Increase the current average by $125 |

| | | |

|Igor | |Increase the current average by $125 |

| | | |

| | | |

|Quint | |Increase the current average by $125 |

| | | |

| | | |

|William | |Increase the current average by $125 |

| | | |

4. For all the other employees, set their December sales to be $1000 more than the individual’s November sales.

5. 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.)

6. Calculate the total sales for each employee for the year . Title the column ‘Total Sales’ on 2 lines.

7. Base salary for employees is calculated as the sum of 10% of the their total sales for the first four months (January through April) and 20% of the their total sales for the second four months (May through August) and 40% of their total sales for the third four months (September through December) . Title the column ‘Base Salary’ on 2 separate lines. Calculate the base salary for each employee.

8. The bonus for each employee is calculated as the sum of the following 3 figures:

A. 10% of the second smallest sales month

B. 12% of the fifth smallest sales month

C. 15% of the third highest sales month

Calculate the bonus for each employee. Title the column ‘Bonus’.

9. The Holiday Bonus for each employee is 1% of the total sales for the entire company across the entire year. Calculate the Holiday Bonus for each employee. Title the column ‘Holiday Bonus’ where the two words are on separate lines.

10. The total compensation for an employee is the sum of the Base Salary, Bonus and Holiday Bonus. Calculate the total compensation of each employee and title the column ‘Total Comp’ where Total and Comp are on 2 separate lines.

11. Determine Rank 1 for each employee as follows:

A. If the employee’s average sales across the 12 months is within $10,000 of the overall average across all employees for the 12 months, “KEEP” that employee.

B. If the employee’s average sales across the 12 months is more than $10,000 from the company’s average, “LAYOFF” the employee.

C. If the employee’s average sales across the 12 months is more than $10,000 greater than the company’s average, “PROMOTE” the employee.

Determine the Rank 1 of each employee.

12. Implement the ability for a manager to select an action for each employee. The possible actions are:

A. LAYOFF

B. PROBATION

C. KEEP

D. PROMOTE

(This exercise utilizes a 'list' from with the user can select.)

Title the column “Mgr. Dec.” on 2 separate lines. Make sure that each action occurs at least once. You may copy paste the other values to make the process go faster.

13. Sort the data by Total Sales.

14. Find the sub-totals for Rank 1.

15. Create a pie and bar chart with data labels on a second worksheet using the subtotalas. Label the worksheet '2016 Sales Charts.’

Send your completed work to the address written on the blackboard. (taylorsallie@) CLOSE your file before sending.

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. Determine the monthly payment on a $30,000 loan at 5.5% over 5 years. (You do NOT need to write the formula. Write the value.)

_____________________________________________________

2. Using the figures in the above question, what is the principle payment at the 20th installment (payment). (You do NOT need to write the formula. Write the value.)

_______________________________________________________

3. Using the figures in the above question, what is the interest payment at the 20th installment (payment). (You do NOT need to write the formula. Write the value.)

_________________________________________________________

4. What is the file extension of an MS Excel file?

.xlsx or .xls

5. What is the file extension of a delimited text file?

.txt or .csv

6. While of the following statements is true? (A-C)

A. Over time, the amount of interest you pay each month on a fixed rate loan increases.

B. Over time, the principle part of the monthly payment on a fixed rate loan decreases.

C. The sum of the principle payment and the interest payment on a fixed rate loan is the same each month for a fixed rate loan.

D. All the above statements are false.

7. Put the following terms in ‘decreasing’ order by size by placing a 1 next to the largest, 2 next to the second largest etc.

A. Worksheet _2_____

B. Workbook 1______

C. Cell _4_____

D. Table _3_____

8. Rewrite the below statement in two equivalent ways.

=MIN(A2:A10)

Equivalent expression 1: __=small(A2:A10,1)____________________________

Equivalent expression 2: __=large(A2:A10,9)____________________________

9. Identify a specific task for which an Excel spreadsheet would be used at a business such as a local,

mom and pop (locally owned) bookstore.

Inventory of books, vendors, etc. Books sold by book associates.

10. Suppose that a person wants to calculate the amount of money s/he must save each month in an

account earning 2% interest to end up with a certain amount of money at the end of 24 months. Briefly explain how one might perform this calculation. Your answer should include a reference to at least 2 specific Excel formula/operations.

One could create a ‘reverse amortization’ schedule where each row uses the total of the ‘principle’ and interest to calculate the ‘new’ interest and principle. One could also use the PMT formula with the optional FV value in the formula

11. (Short Answer) A blood enzyme is categorized as follows:

• Less than 100 Low

• 100 to 300 inclusive of 100 and 300 Normal

• Greater than 300 High

Write the formula in the space provided to determine how to categorize the blood enzyme.

Assume that the value is in A2

= if(A2 < 100, “Low”, if(A2 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download