CSE200 Midterm Exam – TR 1:30pm
Lecture
Winter 2009
Name _______________________________________________________________
Seat_______________
Lab
Time (check one): _________ F 1:30-3:18pm ________
F 3:30-5:18pm
Instructions:
· Put away all books,
papers, and calculators.
· Turn off all beepers and cell phones.
· Read each question carefully and fill in
the answer in the space provided. Answers must be legible or they will be
marked incorrect. If there are multiple answers to choose from, please
CIRCLE the correct answer. The question will not be graded at all if there are
multiple answers to choose from.
· When time has run out you will be told to
put all pens/pencils down.
· Be sure to use values as determined by
previous problems and do not use values from problems that have not yet been
solved per the ordering of the questions.
· Use cell references whenever possible.
· Don’t use a $ if NOT copying
· Only use the functions given.
· Your answer should update correctly when
additional input data is added to the problem or when input data is changed.
·
All answers given should be “updatable” unless otherwise noted (see
above bullet).
·
Do not use extra functions when unnecessary. For example, =and(not(a1)) uses an extra AND
and using extra IF structure is IF(cond,true,false) when just the condition
would be the correct answer.
·
Only use quotes when necessary – do NOT use quotes around Boolean
values.
·
You MUST turn in all pages of your exam or you will receive a zero for
the test.
Problem
Description
As the controller for a medium sized business services firm, you
have been charged with calculating the cost of a government mandated benefit of
providing a $5000 term life insurance policy to all qualified full time employees.
One possible option is to go with an insurance carrier that will price
out this life insurance for each individual employee. To estimate the premium costs
of this option you have compiled a list of all full time employees and their
required health information. You have
also incorporated into your workbook the base rate premiums from a low cost
bidder, and various factors needed in the analysis.
Sheet analysis! contains information by employee including their age,
height (height is given in feet and inches ie: an employee who is 5 foot 9
inches would have the value 5 listed in column D and the value 9 in column E),
and weight. From this information you
will calculate their body mass index and whether or not (TRUE/FALSE) they are
at health risk due to their weight. Additional
health information is also listed including; whether the employee is a smoker
(TRUE for smoker), has had cancer (TRUE for did have cancer) or if they are
diabetic (TRUE if diabetic). This
information will be combined to determine risk factors by this insurance
company.
Sheet rates! contains the monthly premium cost of $1000 worth of life
insurance for given age ranges.
Employees who are under 25 years of age have a base rate monthly premium
of $2.25 per $1000 of insurance. Employees who are at least 25 years of age but
under 35 will have a base rate monthly premium of $3.50 per $1000 of insurance,
etc. Insurance costs scale linearly, so $5000 worth of insurance will cost five
times the cost of a $1000 policy). This
factor is provided in cell misc!B5.
Sheet misc! contains some values that will be needed in your
calculations including unit conversion factors needed for calculating Body Mass
Index (BMI), a scaling factor for $5000 of life insurance premiums vs. $1000
quoted rate (named pfactor), and loan
option terms.
Sheet risk! contains risk factor multipliers that numerically assess
risk based on the number of risk indicators of an employee. These risk factor multipliers
will eventually be used in calculating an employee’s final premium. An employee
with no risk indicators (Weight Risk, Smoker, Had Cancer, Has Diabetes are all
FALSE) would have a risk factor of 0, employees with one risk indicator will
have a risk factor of 1.75, employees with 2 or 3 risk indicators will have a
risk factor of 3.5 and employees with all 4 risk indicators will have a risk
factor of 10.
Sheet summary! contains a summary of annual premiums and fees by
salary grade.
For your convenience, the following
ranges have been named. Be sure to use them when appropriate:
·
risk!$B$2:$E$3 has been named rfactor
·
misc!$B$5 has been named pfactor
Note:
Cells shaded in gray represent data that is given in the problem.
Name _______________________________________________________________
Seat_______________
Lab
Time (check one): _________ F 1:30-3:18pm ________
F 3:30-5:18pm
|
1.
(30 points) Write an Excel formula for cell
analyis!G3
to be copied down to determine the Body Mass Index (BMI) for this
employee. To calculate BMI divide the employee’s
weight in kilograms by the square of their height in meters as follows. Unit
conversions are given on worksheet misc!. NOTE: the ^ symbol is used for
exponentiation. Weight in kilograms/(Height in
meters)2 |
|
|
2.
(5 points) Write an Excel formula for cell analysis!H3
to be copied down to determine if this employee has a weight risk. BMI’s of over 30 pose a weight risk (TRUE
if weight risk). Note the value 30 is
not provided in the workbook. |
|
|
3.
(30 points) Write an Excel formula for cell
analysis!L3
to determine the risk factor for this employee. Risk factors vary
based on the number of risk indicators of a given employee (see problem
description). Assume you will copy
this formula down the column to determine this factor for each corresponding
employee. |
|
|
4.
(25 points) Write an Excel formula for cell
analysis!M3
to determine the base rate annual premium that will be
charged for this employee for $5000 worth of life insurance.
Premiums vary by age as given in sheet rates!. Remember that premium rates as quoted are
the monthly rate for $1000 worth of
insurance. Assume you will copy
this formula down the column to determine this rate for each corresponding
employee. |
|
|
5.
(10 points) Write an Excel formula for cell
analysis!N3
to be copied down to determine (TRUE/FALSE) if this employee is automatically
accepted for insurance by this life insurance provider. If an employee has no risk factors, then
they will be automatically approved. |
|
|
6.
(5 points) Write an Excel formula for cell analysis!O3
to determine (TRUE/FALSE) if this employee will be automatically
rejected for insurance by this life insurance provider. Employees with all
four risk indicators will be automatically rejected. Assume you will copy
this formula down the column to determine this for each corresponding
employee. |
|
|
7.
(15 points) Write an Excel formula for cell
analysis!P3
to determine (TRUE/FALSE) additional medical information needs to be provided
for this employee. Additional
information is required only for employees who have had cancer but have none
of the other risk indicators. Assume you will copy this formula down the
column to determine if additional medical information is needed for the
corresponding employee. |
Name _______________________________________________________________
Seat_______________
Lab
Time (check one): _________ F 1:30-3:18pm ________
F 3:30-5:18pm
|
8.
(30 points) Write an Excel formula for cell
analysis!Q3
(which can be copied down the column) to determine the adjusted annual
premium for this employee, rounded to the nearest dollar,
based on the following: ·
If an employee is automatically rejected
the government will not require your company to pay for this insurance. So their annual premium will be $0. ·
Premiums for employees who are
automatically accepted will simply be the base premium you previously
calculated with no adjustments. ·
Premiums for all other employees will be
the base premium multiplied by the risk factor previously calculated. |
|
|
9.
(10 points) In addition to the annual
premium (from the above problem), in the first year there will be an
additional policy setup fee of 5% of the annual premium. If this fee is more than $25 then only a maximum
amount of $25 will be charged (note: this value is not provided in the
workbook). Write and Excel formula in
cell analysis!R3 to calculate this initial fee for Sam. Assume you will copy this formula down the
column. For full credit, write this
formula without using an IF function. |
|
|
10.
(5 points) Write an Excel formula for cell analysis!S3
to calculate the first year total cost of a policy for this employee
including the additional initial fee.
Assume you will be copying this formula down the column. |
|
|
11.
(25 points) Write an Excel formula in cell summary!B2
that totals the Annual Premium w/risk factors included (analysis! column Q)
for all employees of salary grade 1.
Write the formula such that it can be copied down the column to
determine this value for employees with a salary grade of 2, 3, etc. This formula should also work when copied
across the row to return the corresponding total values by salary grade for
initial fees and total cost of first year premiums with the initial fee. |
|
|
12.
(20 points) The total cost of annual
premiums excluding the fees has already been calculated in cell analysis!Q12
(it can be directly referenced in your formula). This amount is due to the
insurance company at the beginning of each year. An alternate payment plan by the insurance
company will allow you to pay back this total amount over a period of one
year plus interest. The terms require
equal monthly payments of $859 (assume the loan is compounded monthly). The
payment value can be found on sheet misc! (please use cell references). Write an Excel formula in cell analysis!K17
(not shown) to determine the annual interest rate being charged on
this loan. |
|
|
13.
(20 points) Another alternative is to take
out a loan from a bank instead of via the insurance company payment
plan. Bank terms, as given on sheet
misc!, require a 4% annual interest rate compounded monthly and a balloon
payment at the end of the loan of $1000. The loan duration is one year. Write
an Excel formula in analysis!K19 (not shown) to
determine the monthly payment that would be required on this loan. |
|
|
15.
(5 points) You’d like to do is to present a
chart to the chief operating officer showing the trend between age and
monthly insurance premiums of your employees. What type of chart is best
suited to display this type of information? |
TOTAL SCORE _____________/250