1) Complete and submit Review Assignments as explained in EX 412. Always add own **Name** in the Header of document. "Tracs" is attached.

2. Go to the Employee Data worksheet, and then rename the Excel table as EmpData. 3. Employees who elect additional life insurance coverage ( Add Life Ins) pay 0.1 per-cent times their annual salary. The life insurance premium rate of 0.1 percent is entered in cell Y5. Employees who do not elect additional coverage pay nothing. In the Life Ins Premium column, enter an IF function to calculate the life insurance premiums; use a reference to cell Y5 to obtain the life insurance rate and enter 0 for employees who do not elect coverage. 4. All full- time ( Job Status) employees over the age of 30 ( Age) are eligible for the 401( k) benefi t. In the 401( k) column, enter the IF and AND functions to calculate the 401( k) benefi t as 3 percent of annual salary; use a reference to cell Y6 to obtain the 401( k) matching percent rate ( 3 percent). If the employee is not eligible, enter 0. 5. All employees with one or more years of service ( Years Service) are eligible for a bonus. Pay Grade 1 employees receive $ 3,000 ( cell Y2), Pay Grade 2 employees receive $ 6,000 ( cell Y3), and Pay Grade 3 employees receive $ 8,000 ( cell Y4). In the Bonus column, enter nested IF functions to calculate the bonus. For employees not eligible for a bonus, display the label NE. 6. In the Salary Increase column, enter IF and OR functions to insert the text Not Eligible if the individual’s job status is a consultant ( CN) or the pay grade is 4. Leave the cell blank if the individual is eligible for a salary increase. 7. In the Health Cost column, enter the HLOOKUP function to do an exact match lookup to calculate the health plan cost. The layout of HealthPlanRates data ($ B$ 2:$ F$ 3) in the Lookup Tables worksheet has been revised to work with the HLOOKUP function. Use the range address reference in the HLOOKUP function to reference HealthPlanRates data. 8. In the Award column, modify the VLOOKUP function to incorporate the IFERROR function and display the message Invalid hire date. Test the modifi ed formula by changing the date in cell C2 from 3/ 13/ 2008 to 13/ 13/ 2008. Increase the column width to view the entire message. 9. In the Emp ID column, change the format color of the duplicate value conditional formatting rule to Green ( the sixth color in the last row of the Background Color pal-ette) using the Conditional Formatting Rules Manager dialog box. 10. Go to the Reports worksheet, and then enter the COUNTIF function in cells C4 and C5 to count the number of female and male employees. 11. In cells D4 and D5, enter the AVERAGEIF function to calculate the average salary of female employees and the average salary of male employees. 12. Save and close the workbook. Submit the fi nished workbook to your instructor, either in printed or electronic form, as requested.

2) Complete **Case Problem** 1 as explained in EX 413. Always add own **Name** in the Header of document. "Modem" is attached.

Open the Modem workbook located in the Excel7\ Case1 folder included with your Data Files, save the workbook as PC Modem in the same folder, and then, in the Documentation worksheet, enter your name and the date. 2. Go to the Purchase Order worksheet. In cell B5, enter the Product ID 1050. In cell C7, create a lookup function to display the product type. In cell C8, create a lookup function to display the model name. In cell C9, create a lookup function to display the price. Product information is displayed in the Product List worksheet. 3. If an incorrect Product ID is entered in cell B5, cells C7, C8, and C9 will display the # N/ A error value. Modify the formula in these cells so that Product ID not found is displayed in the cell if the ID entered is not found. 4. In the range D40: E42, enter labels and shipping costs based on the following infor-mation: Standard, $ 9.50; Express, $ 14.50; and Overnight, $ 18.50. In cell B15, enter Express. 5. In cell C17, use nested IF functions to display the shipping costs for the option entered in cell B15. If the shipping option in cell B15 is blank, then cell C17 should be blank. ( Hint: The IF functions should reference the cells in the range D40: E42.) 6. In cell C19, enter a formula to display the total cost of the product ( price times quan-tity) plus shipping. In cell B12, enter 4. 7. In cell C19, modify the formula so that if the cell equals an error value (# Value!), the message Check Product ID, Quantity, or Shipping option is displayed. 8. Go to the Product List worksheet, and then use conditional formatting to highlight all duplicate values in the ID column with the formatting you choose. To test that conditional formatting is working, temporarily change the Product ID in cell A119 to 1121. When the test is completed, return the value in cell A119 to 1122. 9. In the Product List worksheet, use conditional formatting to highlight all prices above $ 300 with the formatting you choose. 10. Go to the Summary worksheet, and then enter appropriate functions in the range B5: C7 to determine the average modem price and count for each modem type. 11. Save and close the workbook. Submit the fi nished workbook to your instructor, either in printed or electronic form, as requested.

3) Complete **Case Problem** 2 as explained in EX 413-414. Always add own **Name** in the Header of document. "Leave" file is attached.

Open the Leave workbook located in the Excel7\ Case2 folder included with your Data Files, save the workbook as Baltic Leave in the same folder, and then, in the Documentation worksheet, enter your name and the date. 2. Go to the LeaveData worksheet. Create an Excel table in the range A5: J107, name the Excel table Leave, and then remove the fi lter arrows. Set the column width for columns B through J to 10. 3. In column D, enter a formula to calculate Years Employed. Use the Date Hired cell and the current date ( assume 7/ 1/ 2013, which is stored in cell Z6), and express the length of time employed in years. ( Hint: Use the formula ( current date– Date Hired)/ 365.) 4. In column E, enter a formula using a nested IF function to determine the number of vacation days ( based on the vacation rules above) each employee is eligible for based on the employee’s job status in column B. 5. In column G, enter a formula to subtract the amount of vacation used from the avail-able vacation time, which displays the remaining vacation time for each employee. 6. In column H, enter a formula to determine each employee’s total family leave time ( based on the Family leave rules above). 7. In column J, enter a formula to subtract used family leave from the total family leave to determine the remaining family leave available for each employee. 8. In column J, apply conditional formatting that highlights the remaining family leave of any employee who has not used any family leave days. You decide the formatting. 9. Go to the Leave Summary worksheet. In the range C4: C6, use a function to count the total number of employees eligible for each vacation plan. ( Hint: Any employee who is eligible for 15 vacation days will have the value 15 in column E of the LeaveData worksheet.) 10. In the range D4: D6, use functions to calculate the total number of vacation days for each vacation plan. 11. In the range E4: E6, use functions to calculate the total vacation days remaining for each vacation plan. 12. In the range C7: E7, calculate totals for Number Eligible, Total Days, and Days Remaining. 13. Save and close the workbook. Submit the fi nished workbook to your instructor, either in printed or electronic form, as requested.

4) Complete **Case Problem** **3** as explained in EX 415-416. Always add own **Name** in the Header of document. "M-Fresh" file is attached.

Open the M- Fresh workbook located in the Excel7\ Case3 folder included with your Data Files, save the workbook as Water Bill in the same folder, and then, in the Documentation worksheet, enter your name and the date. 2. In the Quarterly Data worksheet, create an Excel table for the range A1: F73, and name the table WaterData. Remove the fi lter arrows. Format the data in the Gal Used column with the Comma Style number format and no decimal places. 3. Add a column named Gal Billed to the table, and then enter a formula to calculate the number of gallons billed based on the following rules: If a customer’s bill is waived ( Bill Waived column) or the number of gallons used is less than 25,000 ( Gal Used column), display 0 in Gal Billed column; otherwise, display the value from the Gal Used column in the Gal Billed column. 4. Add a column named Water Bill to the table, and then enter a formula to calcu-late the water bill based on the following rules: The billing rate varies based on the type of customer ( Cust Type column). The billing rate is $ 3, $ 2, or $ 1.50 per thousand gallons billed, depending on the customer type ( see the lookup table in cells B2: C5 of the Billing Rate worksheet). For example, a commercial customer using 75,000 gallons has a water bill of $ 225 ( 75 X $ 3), whereas a government cus-tomer using 100,000 gallons pays $ 150 ( 100 X $ 1.50). A commercial customer using 15,000 gallons has a water bill of $ 0 ( see Gal Used rule in question 3). 5. Add a column named Tax to the table, and then enter a formula to calculate the tax based on the following rule: If a customer pays tax ( Taxable column), then multiply the Water Bill by 3.5 percent; otherwise, the tax is 0. The tax rate is stored in cell T1. 6. Add a column named Total Bill to the table, and then enter a formula to calculate the bill amount by adding the Water Bill column and the Tax column. 7. Improve the formatting of the number fi elds in the WaterData table. 8. Make a copy of the Quarterly Data worksheet, rename the copied worksheet as Q2- Q8, and then add a Totals row to the Excel table displaying totals for the Gal Used, Gal Billed, Tax, and Total Bill columns. 9. Make another copy of the Quarterly Data worksheet, rename the copied worksheet as Q9, and then use conditional formatting to highlight the top 15 percent of the Total Bill amounts with a yellow background color. Filter the table so that only the top 15 percent are displayed. Sort the fi ltered table by largest to smallest. 10. Insert a new worksheet, rename the worksheet as Billing Summary, and then create the report shown in Figure 7- 43, using COUNTIF, AVERAGEIF, and SUMIF functions to prepare the report. Make another copy of the Quarterly Data worksheet, and then rename the copied worksheet as Q11. Add a custom fi lter to the table to display only those organiza-tions that have “ church” or “ center” as part of the customer name. 12. Make another copy of the Quarterly Data worksheet, and then rename the copied worksheet as Q12. Use the lookup table in cells E2: H3 of the Billing Rate worksheet and the HLOOKUP function to modify the formula to calculate the Water Bill col-umn ( column H). 13. Save and close the workbook. Submit the fi nished workbook to your instructor, either in printed or electronic form, as requested.

Subject | General |

Due By (Pacific Time) | 10/23/2013 10:00 pm |

Tutor | Rating |
---|---|

pallavi Chat Now! |
out of 1971 reviews More.. |

amosmm Chat Now! |
out of 766 reviews More.. |

PhyzKyd Chat Now! |
out of 1164 reviews More.. |

rajdeep77 Chat Now! |
out of 721 reviews More.. |

sctys Chat Now! |
out of 1600 reviews More.. |

sharadgreen Chat Now! |
out of 770 reviews More.. |

topnotcher Chat Now! |
out of 766 reviews More.. |

XXXIAO Chat Now! |
out of 680 reviews More.. |