Excel In-Class Project Exercise - INFS 3250

Excel/Excel - Exercise.doc INFS 3250 In Class Project – Exercise 3 Complex IF, SumIF & Charts

Excelexcel Exercisedocinfs 3250in Class Project Exercise 3comple

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 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!! 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 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 Altenburger, Ross .00% Blaisdell, Nick .00% Chernitsky, Kevin 9 9...00% Clark, Gregory .00% Comer, Todd 8.5 8..00% Cunningham, Stephen .00% Currie, Anton .00% Fedorovich, Julie .00% Flemming, Todd .00% Hammond, Nia 9....00% Hange, Michael .00% Howell, Marie .00% Johnson, Ebony .00% Johnson, Steven .00% Jondro, Julianne 8....00% Joo, Suzan 9....00% Krzeminski, Keri .00% Litch, Tricia 9...00% Miller, Sara .00% Nicholson, Anthony .00% Patel, Nehal .00% Ripley, Alan .00% Sacco, Anthony .00% Sheskey, Jason 10 9....00% Shirey, Adam ....00% Spilar, John ....00% Sulfaro, Steven ....00% Trombley, Anna ....00% Venkatesh, Ramanuj 9....00% Wannemacher, Matthew ....00% Wright, Amy .00% Yosick, Joshua ....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./1/13 Baltimore O's $2,750./29/13 Baseball & More $2,345./12/13 Canadian Ski Club $27./1/13 Everything Gold $378./25/13 Sports & Stuff $121./22/13 North Face $10,275./19/13 Nike $2,750./22/13 adidas $2,345./30/13 Columbia $74./11/13 Title Nine $7,488./3/13 Eurosport $5,555./19/13 Athletica Gear Corp $18,869./16/13 Baltimore O's $4,944./13/13 Baseball & More $8,550./9/13 Canadian Ski Club $5,066./18/13 Everything Gold $12,910./18/13 Sports & Stuff $5,806./20/13 North Face $642./8/13 Nike $19,057./14/13 adidas $1,925./19/13 Columbia $16,203./10/13 Title Nine $14,662./27/13 Eurosport $6,918./17/13 Athletica Gear Corp $6,355./1/13 Baltimore O's $12,021./6/13 Baseball & More $12,408./22/13 Canadian Ski Club $19,881./11/13 Everything Gold $9,264./19/13 Sports & Stuff $11,112./7/13 North Face $981./24/13 Nike $8,628./26/13 adidas $7,789./16/13 Columbia $15,202./14/13 Title Nine $17,035./29/13 Eurosport $7,379./25/13 Athletica Gear Corp $2,628./19/13 Baltimore O's $6,958./14/13 Baseball & More $18,638./28/13 Canadian Ski Club $1,689./11/13 Everything Gold $19,740./8/13 Sports & Stuff $15,253./9/13 North Face $16,216./10/13 Nike $14,665./19/13 adidas $5,397./13/13 Columbia $15,819./21/13 Title Nine $11,625./18/13 Eurosport $13,296./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. 21ST CENTURY PLAN st Century Plan Evelyn F. Eashmond EDU 645 June 22, 2015 Instructor Orlando Course : Simple Mathematics Grade level: 3rd Grade Population: Twenty-Seven Students Simple mathematics covers the basic topics like fractions, ratios, percentage, and decimals in seventy minutes, equivalent to one lecture. The class has twenty-seven students enhancing their knowledge in mathematics, fourteen males, and thirteen female. The assessment shows the students are average and a few above average, only a case of two students with special cases –one with a case of dyslexia. The lesson plan has core learning standards, which enhance the comprehension of working out problems that involve fractions, ratios, decimals, and percentage. The other standards involve teaching students how to work out problems involving fractions and decimals as well as relating them to percentages and ratios. Lastly, learners were taught how they can use fractions and decimals in real life scenarios.

“All lessons must have an aim, purpose or objective. The teacher must be clear about the objective to get a successful learning outcome for any block of time. To choose an appropriate lesson aim, the teacher must work "backwards" in each curriculum area†(Golland, 1998). If a student needs extra attention or has issues with fine motor skill, the best outcome is to pair them with an over-achieving student to work the (bubby system). It allows the special needs student to establish a relationship with a classmate with whom they can feel comfortable asking for assistance and decreases dependency on the teacher.

The materials used for learning are a whiteboard, pie charts, fractional visuals models like ribbons, as well as triangular, square, and rectangular models. The pie charts enabled students to relate fractions to real life situations like the various percentage or fractions of goods imported by a country. Triangular, square, and rectangular models were used to help students to draw various fractions and also relate percentages to fractions. The whiteboard is useful for solving problems and writing in various fractions and decimals numbers. Utilization of Webb’s Depth of Knowledge allows students to have a strong recalling power.

Group discussions also allow students to develop teamwork skills and short-term strategic thinking. The problem with the plan is that it is hard to understand if students can apply the concepts in real life situations. It is assumed if students grasp the content in class; then the can use the knowledge acquired to apply to real life scenarios. The “shift in†instruction employed in the lesson plan is the gate to the complexity that entails scaffolding the learners from simpler problems to a more complex problem. As a result, this ensures all students whether a fast or slow learner can solve problems that involve fractions and decimals.

Conclusion The lesson instruction cognitively engages students through learning procedures that allow a student to have a master memory. Therefore, allowing them to apply the lesson learned to real-life situations. The lesson plan also allows students to learn systematically from one concept towards another as they develop their skills and knowledge in working out problems that involve fractions and decimals. The learnt skills allow the learners to enhance their degree of cognitive responsibility. Furthermore, the students can learn independently and enhance their knowledge of the 21st-century technology and tools.

Lastly, critical thinking allows learners to apply the knowledge learnt to solve problems and also apply it to real-life situations.

References Golland, J. H. (1998). A lesson plan model for the supervision of student teaching. Education, 118(3), 376.

International Society for Technology in Education. (n.d.). ISTE standards for students. Retrieved from Lefrançois, G. R. (2013). Of learning and assessment. San Diego, CA: Bridgepoint Education.

Paper For Above instruction

The integration of Excel functions such as IF, SUMIF, and charting capabilities into practical projects offers a comprehensive understanding of their application in real-world scenarios. This paper explores how these Excel tools facilitate efficient data analysis, reporting, and visualization, particularly within payroll management, financial analysis, and educational assessment contexts, illustrating their importance in contemporary data-driven decision making.

Introduction

Microsoft Excel remains a vital tool in diverse fields for data entry, analysis, and visualization. Among its extensive features, logical functions like IF, conditional aggregations such as SUMIF, and dynamic charting play fundamental roles in transforming raw data into meaningful insights. The purpose of this paper is to analyze their practical application through a comprehensive case study involving payroll calculations, financial summaries, and educational grading systems. These applications underscore the significance of Excel functions in streamlining complex calculations, enhancing clarity, and supporting visual storytelling in business and educational environments.

Using IF Functions for Payroll and Grading

One of the primary uses of the IF function in Excel is to perform conditional calculations, a necessity in payroll processing and grading systems. In payroll management, the IF function determines overtime hours based on hours worked, differentiating between salaried and hourly employees. For instance, if an employee’s hours exceed 40, overtime pay is calculated; otherwise, regular pay applies. The nested IF functions allow for handling multiple conditions efficiently, such as applying different tax rates or health insurance deductions based on employee classifications.

In grading systems, similar nested IFs categorize student scores into letter grades according to a standardized scale. For example, scores above or equal to 90% receive an 'A', scores between 80% to 89% earn a 'B', and so forth. The nested IF structure ensures precise