Page 1 of 8

INFS 2200 T5-Final Exam Instructions 200pts

Get the “T5-FinallExamV2.xlsx” file and complete the exercises as indicated in these instructions. Save your file as LastName-FinalExam.xlsx”. Submit your file by the deadline.

This is an “open book” exam – use any resources to find information on the DATEDIF() Excel function.

The Case: You are the payroll manager for T5 Warehouse, a retailer of gourmet products with 100 employees. You are in charge of preparing the employees’ payroll and generate some reports for the directors.

1. In the “Documentation” sheet, enter your name and date. (2)

2. The “Work Tables” sheet contains the tables you will use in this exam. Familiarize yourself with the tables’ range names and possible uses.

ServiceDate – the base date for the Years of Service in the report

HeatlhPlanRates – lists the monthly premium per plan

Recognition – lists the award amount based on years of service

Bonus – lists the bonus amount based on pay grade

Intertab – lists the annual interest rate per loan duration (years)

3. The “EmployeeData” sheet contains payroll data. You are tasked with completing the payroll report information by entering all the required formulas in the required columns (M to R). Make sure to use proper formulas with absolute (or range names) and relative address ranges. See sample output below. (66 max)

4. In column M, use a formula to compute the Years of Service as the difference between the Hire Date (column C) and the SERVICE_DATE range name; return the difference in years. Use the DATEDIF() function. (8)

5. Copy the formula down to all employee rows.(8)

6. In column N, use a formula to compute the life insurance premium value. Use the following logic: if the Add Life Ins (column H) cell value equals “Y” then the premium is one-tenth of 1% of the annual salary, otherwise is 0. Hint: use the IF() function. (5)

7. Copy the formula down to all employee rows.(5)

8. In column O, use a formula to compute the 401(k) value. Use the following logic: if the employee Job Status is full time (“FT” in column G) and the employee’s years of service (column M) is greater or equal to 1 then, the 401(k) value is 3% of the annual salary, otherwise is 0. Hint: Use IF() and AND() functions. (5)

9. Copy the formula down to all employee rows.(5)

10. In column P, use a formula to compute the Bonus value. The Bonus value is based on the Pay Grade (column I), see the BONUS range name table. Hint: use the VLOOKUP() function. Make sure you use an exact match. (5)

11. Copy the formula down to all employee rows.(5)

Page 2 of 8

12. In column Q, use a formula to compute the Health Cost value. The Health Cost is based on the Health Plan code in column L. Use the VLOOKUP function to get the monthly premium from the HealthPlanRates range name table and multiply this value by 12 to get the annual cost. Make sure you use an exact match. (5)

13. Copy the formula down to all employee rows.(5)

14. In column R, use a formula to compute the Award value. The Award is based on the years of service (column M). Use the VLOOKUP function to get the award value from the Recognition range name table. Make sure you use an approximate match. (5)

15. Copy the formula down to all employee rows.(5)

16. Create a Pivot Table, based on the “EmployeeData”, in a New Sheet and label it “PVT Salary” (26 max.)

a. Use the following settings for the “PVT Salary” pivot table:

b. Location and Sex in the “Rows” section (2)

c. In the Values section add:

i. Count of Annual Salary: (2)

1. Custom name: Num. Employee (2)

2. Format value with 0 decimals (2)

ii. Sum of Annual Salary: (2)

1. Custom name: Sum of Salary (2)

2. Format Currency with 2 decimals (2)

iii. Average of Annual Salary: (2)

1. Custom name: Average of Salary (2)

2. Format Currency with 2 decimals (2)

d. Set the Report Layout to Show in Outline form (2)

e. Add the title “SALARY STATISTICS” in cell A1, Style Heading 1 (2)

f. Make column A1 width AutoFit (2)

17. Create a pivot chart graph. (16 max) * Must use Excel Windows version to create Combo Style Chart.

a. Style Combo (2)

b. Sum of Salary series, clustered column (2)

c. Average Salary series, clustered column (2)

d. Num. Employee series, Line with markers, use secondary axis (2)

e. Chart Style 8 (2)

f. Move the top left corner of the graph to cell A18 and lower right corner to cell E32 (2)

18. Move the “PVT Salary” sheet after the “EmployeeData” sheet. (2)

19. Reflection: In cell C1, explain the impact of the work from home (Home) company policy and the implications among our work force? Please explain. (2)

Page 3 of 8

20. See sample below.

Page 4 of 8

21. Create a Pivot Table, based on the “EmployeeData”, in a New Sheet and label it “PVT Pay Grade”. (26 max)

a. Use the following settings for the “PVT Pay Grade” pivot table:

b. Pay Grade in the “Rows” section (2)

c. In the Values section add:

i. Count of Pay Grade: (2)

1. Custom name: Count (2)

2. Format value with 0 decimals (2)

ii. Sum of Bonus: (2)

1. Format Currency with 2 decimals (2)

iii. Sum of 401(k): (2)

1. Format Currency with 2 decimals (2)

iv. Sum of Health Cost: (2)

1. Format Currency with 2 decimals (2)

d. Set the Report Layout to Show in Outline form (2)

e. Add the title “PAY BENEFITS ANALYSIS” in cell A1, Style Heading 1 (2)

f. Make column A1 width AutoFit (2)

22. Create a pivot chart graph. (18 max) * Must use Excel Windows version to create Combo Style Chart

a. Style Combo (2)

b. Count series, line with markers, use secondary Axis (2)

c. Sum of Bonus series, clustered column (2)

d. Sum of 401(k) series, clustered column (2)

e. Sum of Health Cost series, clustered column (2)

f. Chart Style 8 (2)

g. Move the top left corner of the graph to cell A10 and lower right corner to cell E24 (2)

23. Move the “PVT Pay Grade” sheet after the “PVT Salary” sheet (2)

24. Reflection: In cell C1, explain any inferences of the Bonus and the Heath Costs among the different pay grades? Please elaborate. (2)

Page 5 of 8

25. See sample below.

Page 6 of 8

26. Create a Pivot Table, based on the “EmployeeData”, in a New Sheet and label it “PVT HealthPlans”. (10 max)

a. Use the following settings for the “PVT HealthPlans” pivot table:

b. Health Plan in the “Rows” section (2)

c. In the Values section add:

i. Sum of Health Cost: (2)

1. Format Currency with 2 decimals (2)

d. Add the title “HEALTH PLAN COSTS” in cell A1, Style Heading 1 (2)

e. Make column A1 width AutoFit (2)

27. Create a pivot chart graph. (10 max)

a. Style Pie, 3 D Pie (2)

b. Change title to “Health Plan Costs” (2)

c. Move the top left corner of the graph to cell A11 and lower right corner to cell E24 (2)

28. Move the “PVT HealthPlans” sheet after the “PVT Pay Grade” sheet (2)

29. Reflection: In cell C1, explain any inferences of the cost ratio of family plans vs the individual plans. Family plans are HMOF and PPOF, individual plans are HMOI and PPOI. Please elaborate. (2)

See picture below

Page 7 of 8

30. The following exercises are located in the “Functions” sheet. The cells with shaded orange background represent input values deducted from the problems’ descriptions. (26 max)

31. Employee Bonus Statistics Problem. Enter the respective Excel functions (in cells B2, B3, B4, B5, and B6) to compute the appropriate values using the Bonus column, in the “EmployeeData” sheet. (2×4= 8)

32. Trip Transport Problem. Enter the formulas required using the template shown. The variables are the number of van seats available, the number of kid campers and the rate of campers to chaperones required. In cells, B13 and B14 enter the formulas required to compute the number of chaperones needed and the actual number of vans needed for the trip based on 12 seats per van, 30 kid campers and 1 chaperone per every 6 kids. In cell B14 use the ROUND() function. (2+2= 4)

33. Home Loan Payment Calculation Problem. Compute the Monthly Loan Payment using the template shown. You want to loan 150,000 for a 30-year term. The variables are loan amount and number of years. In B20, enter VLOOKUP formula to get the annual interest rate using the table INTERTAB range name (“Work Tables” sheet.) In cell B21 enter formula to compute the monthly interest rate. In cell B22 enter formula to compute the total number of payments. In cell B23 enter PMT() function to compute the monthly loan payment amount. (2×4= 8)

34. Calculating a periodic payment with a goal Problem. You want to figure out how much you need to deposit monthly in an account with $300,000 so in 20 years it reaches $1,000,000 dollars (saving goal). Using the template shown. In cell B29 enter the current balance in the account. In cell B30 enter the savings goal. In cell B31 enter the interest rate, in this case is 3%. In cell B32 enter the number of years. In cell B33 enter the formula to compute the total number of monthly payments. In cell B34 enter the formula to compute the monthly interest rate. In cell B35 enter the function to calculate the monthly deposit. Hint: use the PMT function. (2+2+2= 6)

35. See sample below.

Page 8 of 8

 

Assignment Description

Assignment Details

The Alset Corporation wants to begin marketing electric vehicles (EVs) to customers in the nation of Climatonia. Because the company is brand new, models and options are very limited. The user can only select from one of three types of models, and the only available option is an extra charging cable. Your job is to write a program in Java that will allow employees to see the configurations that are currently available for purchase.

Alset EVs come in three types:
Type 1: Rear wheel drive, small battery pack (short range)
Type 2: Rear wheel drive, medium battery pack (medium range)
Type 3: All wheel drive, large battery pack (long range)

All cars feature four doors and come with a charging cable. Type 1 vehicles are painted green and have no other options. Type 2 vehicles are painted blue and have GPS navigation. Type 3 vehicles are painted red, have GPS navigation, and also have a sunroof.

After meeting with the client, the design team determined the following custom class requirements for this program:

A class called Alset with the following:

• A property called numDoors (integer) with a value of 4
• A property called chargeCable (integer) with a value of 1
• A property called paintColor (String) with a value of Green
• A property called freeCharge (String) with a value of No
• A method called displayInfo() that prints each of these properties on screen like this example: “Number of doors: 4”

A class called Type1 with the following:

• Inherits from Alset class
• This model does not have any additional features

A class called Type2 with the following:

• Inherits from Alset class
• An integer property called gpsNav with a value of Yes
• A method that overrides displayInfo(), adding a line to report the gpsNav property

A class called Type3 with the following:

• Inherits from Alset class
• An integer property called gpsNav with a value of Yes
• An integer property called sunroof with a value of Yes
• A method that overrides displayInfo(), adding lines to report the gpsNav and sunroof properties

When the program runs, present the user with a simple explanation of the program (one sentence), followed by a menu that asks them to enter 1 for Type 1, 2 for Type 2, or 3 for Type 3. Use the Scanner class to process input and use IF/ELSE statements to handle their choice. Once the choice has been made, instantiate a member of the appropriate class, set the correct paint color (if type 2 or 3), then execute the displayInfo() method to display the properties of the vehicle on screen. Once the information has been displayed, the program should end.

Write code comments in both the project class and each of the custom classes to document your program. In your custom classes, use JavaDoc comments to document the features of the class.

NOTE: The paint color may not be set within the Type2 or Type3 classes.

NOTE: This program must be submitted as a NetBeans project in a ZIP. Separate files or code are not acceptable.

  The purpose of this paper is to analyze four (4) instructor provided articles that represent the argument of both sides of the dispute over whether the process of hydraulic fracturing is responsible for the increased number of earthquakes occurring in the United States, including Texas.  You will use the information in these articles to help write the research paper.  

Link to the 4 articles:

Two articles supporting Hydraulic fracturing IS NOT responsible for causing earthquakes:

– More Fracking few Earthquake

– Hidden Biases Towards the Fracking Industry 

Two articles supporting  Hydraulic Fracturing IS responsible for earthquakes“

– What’s known – and suspected – about induced earthquakes 

– Earthquakes triggered by fracking, not just wastewater disposal, study finds 

 What to include in the “Why Some Agree That Hydraulic Fracturing IS NOT causing earthquakes” section:

o This should be at least two 6-sentence paragraphs.

o Write in 3rd person. (Please do not use any 1st or 2nd person statements such as “you”, “I”, “we”, “us”) 

  

· What to include in the “Why Some Agree That Hydraulic Fracturing IS causing earthquakes” section:

o Like the directions above: This should be at least two 6-sentence paragraphs.

o Write in 3rd person. (Please do not use any 1st or 2nd person statements such as “you”, “I”, “we”, “us”) 

      

· What to include in the “Summary” section: 

o This should be at least two 5-sentence paragraphs.

o Please write in 1st person.

o This is a “critical thinking paragraph”. Answer the following questions inside of this paragraph, but DO NOT include the questions:

§ What preconceived ideas about hydraulic fracturing-earthquake debate did you have BEFORE reading the articles that you cited in this paper?

§ How have your ideas changed after reading the cited articles? For example, what is YOUR opinion/position on whether or not hydraulic fracturing is causing earthquakes? Explain what specifically influenced your position and any items that limit your understanding and ability to form an opinion on this topic.

• Why do you believe that        continued research by experts on this subject is important? (Explain the        relevance and complexity of the research findings.)
• What question(s) has this        research paper raised for you?  What are you still wondering about?        (NOTE:  nothing is not an acceptable answer.)

THE PAPER IS TO BE IN APA FORMAT ATLEAST 3-4 PAGES. 

father enters the room with his 17-month-old daughter, Sandra. When the caregiver greets him, he tells her that he is upset because his daughter’s behavior has become challenging since she started coming to the center several weeks ago. He says that she will not listen to her parents and other family members, and she has started biting her older brother. The parent explains that he believes Sandra learned this behavior from the other young children at the center and that the caregivers are too permissive.

The caregiver asks the parent how he responds to Sandra when she behaves in the ways he has described, and he tells her that she is sent to her room alone for up to 30 minutes each time she misbehaves. The caregiver quickly responds that the center does not condone that kind of punishment and that it is not appropriate for older infants.

Then, with some hesitation, the caregiver explains that she needs to go outside to set up for outdoor play and suggests that they schedule a conference to discuss the issue further. Later that day, the caregiver asks if you overheard the conversation and requests your advice on how to handle the situation.

As you think about how to support your colleague in dealing effectively with this situation, notice what emotions, if any, come up for you. Consider this situation from the parent’s perspective, as well as the caregiver’s perspective. Also bring to mind what you have learned about emotional and social development. Then:

By Day 3:

Post suggestions or advice you would offer your colleague for dealing effectively with this situation. Describe at least three principles, practices, or ideas from the Learning Resources and use your own experiences to support your response.