Excel expert only

profileKattie-zack
excel.zip
Home>Computer Science homework help>Excel expert only

Excel/Excel - Exercise.doc

INFS 3250

In Class Project – Exercise 3

Complex IF, SumIF & Charts

Name: ____________________________ Points: ______ / 25

Due: See Blackboard! (2.5 pts deduction for each class period late)

Download the Excel workbook titled Excel Exercise 3.xlsx. The following rubric will be used to score your submission:

Points Earned

Points

Possible

Assignment 3 – Grading Report

2

1

3

2

8 pts Total

Payroll Calculations

Correct formulas for non conditional cells

Proper use of Absolute addressing

OT & Reg Hours using IF function

HI deduction with IF function

4

4 pts total

Format the Data, Print and attach the Payroll Report

Consistent decimal places

Data centered where appropriate

Column headings accented

Report Heading and Totals added,

where appropriate

Proper orientation

PRINT to FIT – repeating column headings

THE REPORT JUST LOOKS GOOD!!

2

2

2

6 pts Total

Charts

Use SumIF function to build summary table

Pie Chart – legends, titles, formatting

Bar Chart – legends, titles, formatting

2

2 pts Total

Grading Worksheet

Correct IF function to calculate final letter grade

2

1

2

5 pts Total

AP Cash Flow Analysis

Use IF and DAY functions to display the A/P

invoices in the corresponding columns.

Format the worksheet as needed.

Create a properly formatted Bar Chart that displays

the invoice totals for each week.

25 pts Total

Total Score

Excel/Excel Exercise.xlsx

New Material

Complex IF Tests
SUMIF
Charts
Vlookup

Payroll Master Data

Health Insurance Deduction Tax Rates
Employee Contribution Rates
Code Coverage Rate Federal 28.0%
F Family $ 100.50 State 4.5%
I Individual $ 25.00 Local 2.2%
X Exempt $ - 0 FICA 15.3%
Payroll Instructions
Employees are coded as either hourly or salary. Hourly employees are paid OT for any hours worked over 40;
Salary employees receive their salaried amount regardless of the number of hours worked.
Calculations
Regular Hours: If a salaried employee, Regular hours = Hours worked;
If an hourly employee, regular hours = hours worked (up to 40------ try using the MIN() function)
HINT: You may find it easier to calculate the OT hours first and then
OT Hours: If a salaried employee, OT Hours = 0 back into the Regular Hours
If an hourly employee, OT hours = hours above 40 (try using the MAX() function)
Regular Pay: If a salaried employee, Regular pay = Rate of Pay
If an hourly employee, Regular pay = Regular Hours * Rate of Pay
OT Pay: If a salaried employee, OT Pay = 0
If an hourly employee, OT Pay = OT Hours * Rate of Pay * 1.5
Gross Pay: Regular Pay + OT Pay
Federal Tax: Gross Pay * Federal Tax Rate
State Tax: Gross Pay * State Tax Rate
Local Tax: Gross Pay * Local Tax Rate
FICA Tax: Gross Pay * FICA Tax Rate
HI Deduction: Use the rate corresponding to the table above
Total Deductions: Sum all taxes and HI Deduction
Net Pay: Gross Pay less Total Deductions
1) Create the formulas to perform the above calculations
2) Format the page and data to generate a well designed report (report headings, column headings, totals) (I will be picky!)
3) Print the Payroll Report
4) Create a Summary Section that lists all departments and gives the total of the salaries (both gross pay and net pay) by department
use the SumIF() Function
4) Create multiple charts: 1) Pie Chart showing percentage of gross pay by department
2) Bar Chart - showing net pay by department
5) Print the Charts (be sure to provide legends, titles and appropriate formatting)

Payroll Weekly Data

ABC Company Payroll
Week Ending 8/26/14
Name Department HI Enrollment Rate of Pay / Salary Pay Basis Hours Worked Regular Hours OT Hours Regular Pay OT Pay Gross Pay Federal Tax State Tax Local Tax FICA Health Ins Deduction Total Deductions Net Pay
Hurley, Stuart Sciences F 15 Hourly 21
Hendrickson, Mel Geography I 250 Salary 31
Horton, Samantha Geography F 1200 Salary 55
Walsh, Moira Business Admin X 13 Hourly 43
Schuler, Presley Business Admin F 11.75 Hourly 44
Edsell, Camilla Business Admin F 11.75 Hourly 40
Earle, Jenna Computer Science X 550 Salary 42
Lemiski, Ryan Business Admin X 650 Salary 40
Murray, Dwayne History X 450 Salary 43
McLean, Barb Sciences I 7.5 Hourly 43
Smith, Albert Education F 10 Hourly 40
Hamazaki, Jeff Business Admin X 10 Hourly 40
Tarlton, Victor Sciences F 10 Hourly 47
Cameron, Jacqueline Sciences I 1200 Salary 25
Suel, Ingvar Education X 275 Salary 54
MacPherson, Jack Sciences I 1200 Salary 30
Pitt, Dennis Education I 7.3 Hourly 40
Guerroro, Tamara Computer Science F 125 Salary 40
Lockwood, Shaine Education X 1200 Salary 43
Morris, Alice Sciences X 1200 Salary 48
Singh, Balwant History F 12 Hourly 40
Hillman, Frances Business Admin I 7.5 Hourly 54
Morgan, Cindy Sciences F 600 Salary 40
Skubiak, Jaime Education F 350 Salary 40
Modano, Mike Sciences F 9.75 Hourly 44
Yap, Stevey Business Admin I 500 Salary 40
Garcia, Rosa History I 10 Hourly 48
Chan, Dorothy Business Admin F 9.5 Hourly 46
Henderson, Kela Business Admin I 12 Hourly 40
McLeish, Cindy Education F 600 Salary 40
DeFleur, Gregg Business Admin F 7.5 Hourly 40
Bows, Graham Geography X 7.5 Hourly 36
Goode, Barb Sciences I 12 Hourly 43
Tang, Kim Sciences I 125 Salary 40
Flanders, Dawn Geography I 350 Salary 40
Maki, Ed Business Admin F 18.75 Hourly 25
Gray, Stacey Education F 12 Hourly 22
Todd, Shig Sciences F 12.5 Hourly 44
Williams, Rich Business Admin X 750 Salary 40
Adams, Cecilia Business Admin F 250 Salary 40
Farris, Linda Sciences X 12 Hourly 27
Aziz, Nashir Geography F 8 Hourly 32
Bertin, Julia Geography F 550 Salary 40
Cooper, Sara Business Admin F 450 Salary 40
Brown, Raymond Geography I 600 Salary 40
Swift, Gilles Education F 12 Hourly 40
Williams, Peter Sciences X 1200 Salary 40
Palma, Emerald Computer Science I 7.5 Hourly 40
Bose, Denise Sciences X 18.75 Hourly 40
Booth, Wesley History X 1200 Salary 42
Tanaka, Brian Sciences X 12 Hourly 40
Brewski, Randy Business Admin I 600 Salary 44
Thompson, Murray Business Admin F 11 Hourly 40
Drummond, Chris Business Admin F 7.3 Hourly 55
Mulvaney, Marcey Education X 9.5 Hourly 40
Provost, Everett Computer Science I 550 Salary 40
Hedges, John Education X 150 Salary 40
Lewis, Nathan Business Admin F 8.75 Hourly 44
Hamel, Darryl Sciences X 11 Hourly 44
Arston, Jean Business Admin I 7.5 Hourly 50
Ackerman, Jerry Education I 12 Hourly 32
Lopez, Victor Sciences I 550 Salary 40
Getz, Anthoney Sciences F 275 Salary 40
Winkler, Janice Business Admin X 5.5 Hourly 40
Huberty, Tessa Business Admin F 750 Salary 40
Withers, Martin History I 800 Salary 40

Grades

INFS 3250
Summer Semester Grades
Name (Last, First) Chapter 4 Assignment 1 Chapter 5 Assignment 2 Chapter 6 Assignment 3 Chapter 7 Assignment 4 Chapter 8 Assignment 5 Chapter 9 Assignment 6 Chapter 10 Assignment 7 Totals Access 1-3 Access 5 Access 6 Access 7 Access 4 Totals T. Projects Midterm 1 Final Exam Attendance Homework Grade Assigned Letter Grade
Jordan Keefe: Instructions: Display the Assigned Letter Grade using a standard grading scale -- >= 90 = A, >= 80 = B, >= 70 = C, >=60 = D, below 60 = F Use the IF function -- one IF nested inside another to accomplish the task. Note that grades are % thus the value to compare to is in the form of .90, .80, .70, .60 etc.
Total Scores 10 10 10 10 20 20 10 90 20 20 20 20 10 90 180 55 39 15 16
Altenburger, Ross 10 10 10 10 18 20 10 88 20 19 18 20 10 87 175 49 33 15 16 94.00%
Blaisdell, Nick 9 8 9 10 12 18 0 66 0 66 46 0 0 26.00%
Chernitsky, Kevin 9 9.5 10 9.5 17 20 9 84 20 19 18 17 9 83 167 46 29 15 16 90.00%
Clark, Gregory 10 8 9 18 17 9 71 18 19 17 16 5 75 146 41 27 15 14 80.00%
Comer, Todd 8.5 8.5 9 10 18 20 10 84 20 20 18 17 9 84 168 46 39 15 15 93.00%
Cunningham, Stephen 10 10 8 28 20 20 18 17 9 84 112 37 23 13 14 71.00%
Currie, Anton 7 10 9 10 13 17 10 76 19 15 34 110 46 19 15 10 65.00%
Fedorovich, Julie 10 10 9 19 20 10 78 19 19 19 19 76 154 47 32 14 14 86.00%
Flemming, Todd 10 10 10 10 19 10 69 20 20 19 20 9 88 157 50 37 15 16 94.00%
Hammond, Nia 9.5 10 10 19 20 10 78.5 18 20 19 18 9 84 162.5 41 35 15 16 90.00%
Hange, Michael 10 10 9 18 20 10 77 20 19 19 20 78 155 54 25 14 12 81.00%
Howell, Marie 9 9 9 10 10 16 10 73 19 20 16 11 9 75 148 49 22 14 12 77.00%
Johnson, Ebony 10 10 10 10 17 19 10 86 20 20 17 20 8 85 171 48 26 15 16 90.00%
Johnson, Steven 10 19 20 49 19 20 18 18 75 124 48 34 15 7 70.00%
Jondro, Julianne 8.5 9 10 9 20 19 9 84.5 20 20 19 10 69 153.5 54 23 15 15 86.00%
Joo, Suzan 9.5 9 10 10 19 16 10 83.5 20 20 17 19 8 84 167.5 49 38 15 16 96.00%
Krzeminski, Keri 10 10 10 10 19 18 10 87 20 19 19 58 145 49 29 15 16 88.00%
Litch, Tricia 9.5 10 10 9.5 20 18 10 87 20 20 19 20 8 87 174 36 38 15 16 92.00%
Miller, Sara 10 10 10 10 20 17 10 87 20 20 18 19 9 86 173 53 37 15 16 97.00%
Nicholson, Anthony 10 10 10 10 18 20 10 88 20 20 19 20 8 87 175 55 36 15 16 98.00%
Patel, Nehal 9 10 10 10 20 7 66 17 20 17 54 120 50 34 13 10 75.00%
Ripley, Alan 9 10 10 10 16 18 9 82 18 10 18 19 9 74 156 48 33 15 14 87.00%
Sacco, Anthony 0 0 0 0 0 0.00%
Sheskey, Jason 10 9.5 10 10 12 17 10 78.5 17 19 13 17 9 75 153.5 41 19 14 16 81.00%
Shirey, Adam 9 9 10 9.5 13 19 69.5 20 20 15 18 9 82 151.5 28 24 15 15 77.00%
Spilar, John 10 10 10 9.5 15 20 0 74.5 0 74.5 0 0 10.00%
Sulfaro, Steven 10 10 10 9.5 12 16 0 67.5 20 20 20 17 9 86 153.5 44 38 15 16 92.00%
Trombley, Anna 10 10 10 9.5 19 18 9 85.5 20 20 19 20 9 88 173.5 51 21 15 16 88.00%
Venkatesh, Ramanuj 9.5 9 10 9 17 17 10 81.5 19 20 18 14 71 152.5 39 31 15 16 86.00%
Wannemacher, Matthew 9 10 9.5 14 16 10 68.5 12 16 17 18 10 73 141.5 32 22 15 16 78.00%
Wright, Amy 10 10 8 10 19 11 10 78 20 19 18 18 9 84 162 48 24 15 15 85.00%
Yosick, Joshua 10 10 10 9.5 19 20 10 88.5 19 20 18 20 9 86 174.5 53 33 15 16 95.00%

AP Cash Flow

TheZone Customer Accounts Payable Report
Cash Flow Analysis
Customer Name Invoice Amount Due Invoice Date Week 1 Payables Week 2 Payables Week 3 Payables Week 4 Payables
Athletica Gear Corp $8,612.00 12/1/13
Baltimore O's $2,750.00 12/29/13
Baseball & More $2,345.00 12/12/13
Canadian Ski Club $27.50 12/1/13
Everything Gold $378.99 12/25/13
Sports & Stuff $121.60 12/22/13
North Face $10,275.00 12/19/13
Nike $2,750.00 12/22/13
adidas $2,345.00 12/30/13
Columbia $74.08 12/11/13
Title Nine $7,488.10 12/3/13
Eurosport $5,555.55 12/19/13
Athletica Gear Corp $18,869.00 12/16/13
Baltimore O's $4,944.00 12/13/13
Baseball & More $8,550.00 12/9/13
Canadian Ski Club $5,066.00 12/18/13
Everything Gold $12,910.00 12/18/13
Sports & Stuff $5,806.00 12/20/13
North Face $642.00 12/8/13
Nike $19,057.00 12/14/13
adidas $1,925.00 12/19/13
Columbia $16,203.00 12/10/13
Title Nine $14,662.00 12/27/13
Eurosport $6,918.00 12/17/13
Athletica Gear Corp $6,355.00 12/1/13
Baltimore O's $12,021.00 12/6/13
Baseball & More $12,408.00 12/22/13
Canadian Ski Club $19,881.00 12/11/13
Everything Gold $9,264.00 12/19/13
Sports & Stuff $11,112.00 12/7/13
North Face $981.00 12/24/13
Nike $8,628.00 12/26/13
adidas $7,789.00 12/16/13
Columbia $15,202.00 12/14/13
Title Nine $17,035.00 12/29/13
Eurosport $7,379.00 12/25/13
Athletica Gear Corp $2,628.00 12/19/13
Baltimore O's $6,958.00 12/14/13
Baseball & More $18,638.00 12/28/13
Canadian Ski Club $1,689.00 12/11/13
Everything Gold $19,740.00 12/8/13
Sports & Stuff $15,253.00 12/9/13
North Face $16,216.00 12/10/13
Nike $14,665.00 12/19/13
adidas $5,397.00 12/13/13
Columbia $15,819.00 12/21/13
Title Nine $11,625.00 12/18/13
Eurosport $13,296.00 12/28/13

INSTRUCTIONS: Write the formulas for columns D, E, F and G to display the Invoice Amount Due for each invoice in the column corresponding to the Invoice Due Date. Consider only the DAY portion of the date (using the DAY() function that returns only the DAY portion of the date) and place the invoice amount in the columns based upon the following business rules: Week 1 Payables are Due on the 1st thru the 7th day Week 2 Payables are due on the 8th thru the 14th day Week 3 Payables are Due on the 15th thru the 21st day Week 4 Payables are due after the 21st through the end of the month For example: The Invoice in row 6, due on the 1st day of the month, would display $8,612.00 in cell D6, the invoice in row 7, due on the 29th day of the month, would display $2,750.00 is cell G7, and so on.