EXCEL WORKBOOK technology management

 

As supervisor for a retail company, you supervise six people in your location. You are responsible for their payroll and commissions each week. This task would normally take a couple of hours on paper, but you now have the expertise needed to automate the process by using formulas and functions in an Excel spreadsheet.

Use the data provided to create a worksheet described below:

You must create a workbook with separate sheets for each week that would allow sales managers to compare sales figures and commissions from one week to the next. Each worksheet should calculate the payroll amount for each of your six employees. If sales are below $1,000, then the commission paid is 5% of the sales. If sales are between $1,000 and $3,999.99, the commission paid is 10% of the sales. If sales are $4,000 or higher, the sales person receives a 12.5% commission rate.

Sales people will be paid either their commission or hourly pay earned amount—whichever is higher. Hourly employees receive 150% of their hourly rate for any hours worked over 40 hours per week (time and a half for overtime worked).

Each worksheet should contain the following headings:

  • Employee
  • Sales
  • Hours Worked
  • Hourly Pay
  • Commission Earned
  • Hourly Pay Earned
  • Payroll Amount

To complete this workbook, you must write specific formulas and functions. The Commission Earned, Hourly Pay Earned (for the two hourly employees), and Payroll Amount columns require you to use IF functions. Remember, the payroll amount for salespeople will be either the commission earned or hourly pay earned—whichever is greater. Do not calculate commission earned for hourly employees or overtime for sales employees (this is anyone who has a sales figure in the Sales column).

Remember to format your worksheets, rename and change color on the tabs, and submit your workbook to your instructor using the following naming convention: LastnameFirstnameIP5.xls.

As a reminder: 

1) Workbook must have separate worksheets for each week to calculate the payroll amount for each employee. 

2) If sales are below $1,000, commission paid is 5% of the sales. If sales are between $1,000 and $3,999.99, commission paid is 10% of the sales. And if sales are $4,000 or higher, commission rate is 12.5%. 

3) Sales people will be paid either a commission or a hourly pay earned amount—whichever is higher. And  only hourly employees should receive 150% (time and a half) of their hourly rate for hours over 40 worked per week.  Do not calculate commission earned for hourly employees or overtime for sales employees

4) Specific formulas and functions or required in completing the payroll amounts. 

5) Commission Earned, Hourly Pay Earned (only calculated for the two hourly employees), and Payroll Amount columns require use of IF function. 

6) Name workbook with format of  LastnameFirstnameIP4.xls

Each worksheet should contain the following headings:

Employee    Sales   Hours Worked   Hourly Pay   Commission Earned   Hourly Pay Earned   Payroll Amount

Fred     $5,500             30         $10.00             $687.50              $300.00                   $687.50

Maddie          0               45           12.50                      --                   593.75                     593.75  

Also in calculating overtime pay you should:

1) in parenthesis calculate the regular hrs x regular pay

2) add the overtime pay by placing in a separate parenthesis

3) calculate the overtime pay by multiplying the OT hours X regular pay x 1.5 

So for example: (A1*E1)+(B1*E1*1.5)

Wherein:

reg hrs is in A1

reg pay in E1

overtime (OT) hrs in B1

1.5 multiplies the OT hours by 1.5 to calculate the OT pay for the OT hours 

Also the IF the function should calculate the figure in cells outlined in assignment.  The IF Function returns one value if a specified condition is true, and another if it is false.  Also all required conditions should be in IF Statements. 

See also for reference:  www.homeandlearn.co.uk/excel2007/excel2007s6p1.html 

Answers

Related Questions

Business Finance : Data Mining...

In a 1-2 page paper explain the difference in how you would mine data based on the 3 categories; Prediction, Clustering, and Association. Within the p...

Humanities : 5-1 Discussion: Picking Sides...

I am needing a 2-3 paragraph post written on the following: To complete this assignment, use the resources found on the Final Project Topics and Resou...

Writing : History Essay...

The Essay can be of any choosing from the topics list. It will need to have a "Formal Outline," I have also included a checklist to make sure all is m...

Business Finance : Read the article below entitle...

Discuss a specific context in which you are a "more valuable customer." Explain what leads you to believe this to be the case. Discuss a specific cont...

Sherron Watkins - Decision Point Case Study...

In the case study located on page one and two of the textbook details a portion of the memo Sherron Watkins, is the former Vice President of Enron Cor...

Humanities : Major powers in WWI...

I already have a 5 pages essay about this topic. it would be great if you could add a little to each paragraph and extend it to 6 pages or 6 and a hal...

Programming : Cyber Security...

CYBER SECURITYGo to this website:http://www.us-cert.gov/cas/tips/ and briefly answer the following questions using the MLA format. Do not copy and pas...

Business Finance : Help with writing a paper...

[04] Assignment 4 Hide Assignment InformationInstructionsDirections: Be sure to save an electronic copy of your answer before submitting it to Ashwort...

Business Finance : Help with Writing a paper...

ASSIGNMENT 08BU490 Business EthicsDirections:Be sure to save an electronic copy of your answer before submitting it to Ashworth College for grading. U...

Business Finance : Help with Writing a paper...

ASSIGNMENT 04BU490 Business EthicsDirections:Be sure to save an electronic copy of your answer before submitting it to Ashworth College for grading. U...

Business Finance : Health communication Engaged l...

Engaged Learning Project Portfolio Please include the following documents in your portfolio in the following order:1. Cover Page—Your Name, Organiza...

Business Finance : reflection paper...

Three reflections below, please follow the instruction. I already uploaded the textbooks and the recording in class. 1.5 page per one(three in total)T...

Humanities : english literature...

The phrase "summer's sentinel," meaning a cuckoo, is an example ofa kenning.a predicate.a scop.In Sir Gawain and the Green Knight a sense of super...

Humanities : Setting Classroom...

Question 1. View the “Roller Coaster Physics: STEM in Action,” “Animal Patterns: Integrating Science, Math & Art,” and “Table for 22: A Real...

Business Finance : Help with Writing a paper...

[08] Assignment 8 Hide Assignment InformationInstructionsDirections: Be sure to make an electronic copy of your answer before submitting it to Ashwort...

Business Finance : Help with writing a paper...

[04] Assignment 4 Hide Assignment InformationInstructionsDirections: Be sure to make an electronic copy of your answer before submitting it to Ashwort...

Writing : Latin studies class writing assignment...

All writing assignments are double spaced, Times New Roman, size 12 font and minimum 800-1000 words long.The topic for this month is "Write about a cu...

Mathematics : Week 2 Statistics hello...

Unit 2: Graphing Data Evaluation Title: Assignment 2 RubricInstructions:In this assignment, you will be required to use the Heart Rate Dataset to comp...

Humanities : Museum Visit Assignment...

This assignment will consist of visiting a museum that focuses on an ethnic group or culture. This assignment is worth 10% of your grade and will be g...

Programming : python am hour to 24 hour format.wh...

The hours of a day can be represented in the am/pm format or in 24 hour format. Your employer wants a program that takes an hour in am/pm format and p...

Writing : part 2 Topic 2 DQ 1...

Proverbs 1:7 The fear of the LORD is the beginning of knowledge, but fools despise wisdom and instruction. Prov 1:7, NIV Research & experience is how...

Humanities : how and why tupac was considered a g...

How and why Tupac is considered a good role-model after he was released from prison. What are the things Tupac went through that made him into the per...

Writing : write a 1200 words argumentation essay...

Write a 1,200-word essay arguing your side of one of the following topics:1) High School athletes should be given drug tests. 2) Violent video games c...

Business Finance : Please do the slides and write...

This Assessment Task will address the following Learning Outcomes:LO1: Evaluate the skills required to manage the ongoing demands associated with smal...

If you didn't find the right answer

Ask Your Questions, We'll notify you once someone answers it