How to calculate lateness in excel

Please name your workbook as

“YourLastName FirstName Final.xlsx”
It is very important to follow this naming convention when you name your workbook, and also to follow the naming rules whenever applicable in the test described below. I will deduct a point for each incidence that you do not follow these guidelines. But I might deduct a lot more points if you do not follow these naming guidelines on a major part. Deduction for lateness is 2 points per hour (or partial hour) after the due time.

The final is 100 points in total. The percentage shown for each part is the percentage of the total final points for that part.

For each part or subpart, if it does not work, you might not get any partial credit for it, even though you have done some work.

Description: The plan is scheduled to be terminated as of the valuation date and the plan is to purchase immediate annuity contracts for all participants. Please see the handouts for details and for clarification about each part or each step.

Part I - The Test Workbook (5% in total with 1% for each 2 points below): Set up the workbook. Special requirements are:

· Name the workbook as indicated above

· Create the following sheets: Record; Qx; Rate; Annuity; Age Dist; Statement. The workbook should also include three more sheets (“Sort DB”, “Filter DB”, “Filter Advance”) created from Part II.

· Hide unnecessary information for intermediate calculations properly

· When printing each sheet, the bottom of the page should show

a) The workbook name and sheet name

b) The date and time

· However, if you have a link to other workbooks, you will get 0 point for Part I.

Part II - Employee Database (10% in total with 1% for each 2 points below): Create 10 records and format them properly. Some requirements are:

· Create 10 records on the sheet "Record". The records should be the same as in the sample.

· Other than the additional fields for Age and Service and the fields (and information on valuation date) in the sample, you can not have anything else on this sheet. You can create something else (on other sheets if needed), and hide it before your turn in the test. You could have another sheet for intermediate calculations if needed.

· Format the sheets and records as in the example.

· Name should have the input format “LastName,FirstName”. Please keep in mind that there is no space between the last name and first name.

· There could be a middle name or a middle name initial. It is also possible some records do not have middle name at all.

· You need to add two more fields to the handout: Age and Service.

· Calculate the age at last birthday as of the valuation date (4 points)

· Calculate service and round it to the nearest integer. Service is the elapse time from hire date to valuation date. You can estimate it by dividing the number of days by 365.25 for simplification. (2 points)

· Sort the database (one sort) by Age last birthday (descending) then by Name (ascending). Keep the sorted one on a separate sheet, called “Sort DB”. (3 points)

· Make a copy of the records on a separate sheet, called “Filter DB”. (the total is 15 points with the breakdown below.)

· Set up a filter on the database and have a section for summary information for each potential filter. (6 points)

· Summary information includes the number of records, total salary, average salary, maximum age, minimum age, average service. (7 points)

· Make a copy of the records on a separate sheet, called “Filter Advance”. Show only records with Age>50 or Age<40, and Current Year Salary > 80,000 using “Advanced Filter” approach. Do not hide anything on the sheet. (7 points)

· Do not hide the criteria. If you do, I will deduct additional 2 points for it.

· Set the print range properly so that it is printed on one page nicely.

Part III - Age by Service with Salary Information (20% in total): There should be two sheets in Part I. The first sheet is meant to be the employee database, called Record. The second sheet is for the distribution, called Pivot Table.

You can re-create the records on the Record sheet as shown in the sample.

Use Pivot Table to generate the Age by Service distribution with total two-year salary, based on the Employees Database. Other requirements include:

· Generate a distribution of Age by Service with count and the sum of total salary and total PY salary.

· Format the table nicely. The format of the table should match that as shown in the example as close as possible, but the numbers might be different.

· The fields Age and Service are considered as part of the database. Other than that, the database cannot be modified. For example, you cannot insert any other additional fields.

Part IV – Immediate Annuity Calculation (20% in total with 1% for each 2 points below): Calculate the annuity value on sheet Annuity. Special requirements and instructions are as follows:

· For Sheet Annuity, format (or merge cells if necessary) all cells as in handout.

· Age should be linked to the one on sheet Statement.

· Annual Benefit should be equal to Early Retirement Benefit on sheet Statement.

· Interest Rate should come from sheet Rate, using one of the lookup functions (Index, Match, Vlookup, etc.)

· Contract Year should come from “Contract Year” on sheet Rate.

· Some concepts needed for the calculation of Qx, px, tpx and Discount value are:

i. i is the interest rate used to discount the cash flows

ii. v is the interest discount factor

iii. qx = mortality rate at age x

iv. px = Surviving probability at age x

v. tpx = Surviving probability starting age x for t years

vi. Discounted Value: It is the discounted value of tpx.

· Calculation: Some basic formula are:

i. v = 1/(1 + i)

ii. px = 1 – qx

iii. tpx =1 for the first year and tpx = prior year px * prior year tpx

iv. Discounted Value = tpx * v to the proper power t, where t is 0 for the first year, 1 for the second year, 2 for the third year, etc.

v. Annuity factor is the sum of all the discounted values.

vi. Annuity value is the product of annuity factor times the annual payment.

· Make sure all columns should always run from current age through age 120.

· Remember that the age column is worth 10 points. (Hint: It should have some IF functions in order to work properly)

· Freeze the panes properly.

· You can set the printing to be on 3 pages in a portrait layout, and the heading rows should be on all three pages.

· You can not have any hard coded numbers after the column headings for the table.

· The highlighted cells are input cells.

· See handout for the point value distribution.

Part V – Curve for Historical Interest rates Graph (20% in total with 1% for each 2 points below): The graph shows the progress of the historical interest rates. Some requirements are:

· The sheet should have a table of the interest rates (see the sample). The years may run more than 20 years, but the rates might not fill up the whole column in that table since we do not know all of those rates.

· The sheet should have the current year summary information as in the sample. Year is an input item while the rate should match the corresponding one in the table.

· y-axis: Rate, which should be linked to the column heading for rates. (1 point)

· x-axis: Year, which should be linked to the column heading for years. (1 point)

· The title is “Interest Rate”, which should be linked to the box with that text. (2 point)

· The curve shows the interest rate for the last six years. The curve should be dynamic in the sense that when you add one year rate, the graph should update automatically. (25 points)

You earn any of the points for this part if the requirements below are not followed:

· You have to use Offset functions to define Named Ranges

· You have to use your name as part of those named ranges.

· You have to use those named ranges as chart series in your chart.

· The chart should be located on sheet Rate.

· The overall appearance and everything else will be awarded the remaining. (6 points in total )

· Highlighted cells (green) are the input information and are hard coded.

· When applicable, you have to have your last name as part of the range name if you ever define a range name. The deduction for not doing that is at least 10 points.

Part VI - Benefit Statement (25% in total with 1% for each 2 points below): Create the statement as indicated in the example. Requirements are:

· Create the statement on Sheet "Statement" that is to be distributed to each plan participant.

· ID (highlighted cell) is an input cell (hard coded). You should use this information to locate and/or calculate other information on the sheets.

· Name should be "FirstName LastName". For the first record, for example, it is "Roxanne Klendshoj" (10 points)

· Date of Participation: 1/1 or 7/1 on or following the first anniversary of employment. (4 points)

· Date of Normal Retirement: The first day of the month on or following the later of 65th birthday or the 5th anniversary of participation (5 points)

· Age last birthday: It is the age on his last birthday as of the valuation date (2 points)

· Service since the participation date (Service): It is the completed years and months since the date of participation as of the valuation date. (5 points)

· Annual benefit: It is also called as Accrued Benefit or accrued benefit at normal retirement date. It is equal to 2% * Salary * Service (2 points)

· Early retirement factor: ERF is 100% reduced by 3% for each whole year (or a partial year) that the age at valuation date precedes his normal retirement age (or that the valuation date precedes the normal retirement date). But it should never be less than 0. (5 points)

· Early retirement benefit = Accrued benefit * ERF (1 point)

· Annuity purchase value: It is the annuity value calculated on sheet Annuity. (1 point)

· Retirement Goal = 10 times salary (1 point)

· Achieved Goal Percentage = Annuity purchase value divided by the goal (1 point)

· The preparation date is the 8th working day in May in the contract year. By the way, there are no Federal holidays in May other than the memorial holiday. The year should come from “Contract Year” on sheet Rate. (6 points)

· You can not hard code any numbers. Everything must be linked to the database and annuity calculation.