THIS EXAM CONSISTS OF SEVERAL STEPS AND A SINGLE QUESTION. IF YOU COMPLETE ALL OF THE STEPS AND ARRIVE AT THE CORRECT ANSWER TO THE QUESTION, YOU WILL HAVE EARNED A 100%. OTHERWISE YOU WILL RECEIVE PARTIAL CREDIT FOR ALL OF THE CORRECT WORK THAT YOU SHOW.

IT 150  EXCEL EXAM 2

THIS EXAM CONSISTS OF SEVERAL STEPS AND A SINGLE QUESTION.  IF YOU COMPLETE ALL OF THE STEPS AND ARRIVE AT THE CORRECT ANSWER TO THE QUESTION, YOU WILL HAVE EARNED A 100%.  OTHERWISE YOU WILL RECEIVE PARTIAL CREDIT FOR ALL OF THE CORRECT WORK THAT YOU SHOW.

FIRST, A LITTLE BACKGROUND:  YOU WORK DIRECTLY UNDER THE CEO OF IT’S SO SWEET, INC WHERE YOU PRODUCE THE FINEST ORGANIC ARTISINAL MARSHMALLOWS IN THE WORLD.  ONE DAY YOUR BOSS COMES TO YOU TO DISCUSS WHAT HE CALLS A “LITTLE PROBLEM.”  A NEW EMPLOYEE ACCIDENTALLY ADDED A FEW HUNDRED POUNDS OF PEANUT BUTTER TO SEVERAL HUNDRED BATCHES OF THE COMPANY’S GRAPE-JELLY-FLAVORED MARSHMALLOWS.  IF (A) A PERSON WITH A PEANUT ALLERGY (B) ATE THE TAINTED MARSHMALLOWS (C) CONTAINING PEANUT BUTTER THAT WAS NOT COOKED TO AT LEAST 300 DEGREES, THEY WOULD PROBABLY SUFFER AN UNFORTUNATE ALLERGIC REACTION.

TO DEAL WITH THIS PROBLEM, YOUR BOSS FIRST ASKS YOU TO EVALUATE INTERNAL DATA AND DECIDE WHICH WILL COST THE COMPANY LESS MONEY – (1) TO RECALL AND REPLACE THE TAINTED MARSHMALLOWS NOW OR (2) TO WAIT UNTIL LATER FOR VICTIMS WHO SUFFERED ALLERGIC REACTIONS TO SUE THE COMPANY.   ONCE YOU FIGURE OUT WHICH OPTION IS CHEAPER, SET UP AN ANNUITY TO COVER THAT COST.  YOUR TASK ON THIS EXAM IS TO WORK THROUGH THE GIVEN SPREADSHEETS ACCORDING TO THE FOLLOWING INSTRUCTIONS TO ARRIVE AT THE ANSWER TO THIS QUESTION:

“HOW MUCH DOES THE COMPANY HAVE TO PAY EACH MONTH FOR THE ANNUITY?”

INSTRUCTIONS

1. Actuaries at your company prepared some data concerning the statistical likelihood of your customers eating the bad marshmallows and suffering an allergic reaction. To see this data better, in the LIABILITY spreadsheet, format the table ANALYSIS as follows:

1. Merge cells D1:J1 and center the title “GRAPE-JELLY FLAVORED MARSHMALLOW CUSTOMER DATA.”
2. Format the ANALYSIS table so that it has banded rows.
3. Autofit all the columns of the LIABIBILITY
4. Change the number formatting of cells B3 and B4 to “Percentage” and to show precisely 2 decimal places after the decimal point.
5. Change the number formatting of cells M12:M13 to “Accounting”

2. To figure out the company’s potential liability, we first estimate how many people could suffer allergic reactions if we do not recall the tainted marshmallows. Start in the ANALYSIS table:

2. In cell F3 of the “CUSTOMER HAS PEANUT ALLERGY?” column, enter a formula that checks whether the value of the number in cell E3 is equal to 1 (corresponding with the one in 40, or 2.5%, chance that a customer has a peanut allergy), and if true returns a “Y”; otherwise returns an “N”.

1. In cell H3 of the “CUSTOMER ATE MARSHMALLOWS?” column, enter a formula that checks whether the value of the number in cell G3 is equal to 1 (corresponding with the 1 in 2, or 50%, chance the customer ate the marshmallows), and if true returns a “Y”; otherwise returns an “N”.

3. Next, look at column “COOKING TEMPERATURE” of the ANALYSIS table, along with the other two columns you filled in above, “CUSTOMER HAS PEANUT ALLERGY?” and “CUSTOMER ATE MARSHMALLOWS?” and in cell J3 enter a formula that will determine whether an allergic reaction is likely to occur.

1. The formula you enter into cell J3 should check IF all 3 conditions for an allergic reaction are true – in other words, whether a customer: (A) who HAS A PEANUT ALLERGY (B) ATE THE MARSHMALLOWS (C) CONTAINING PEANUT BUTTER THAT WAS COOKED TO LESS THAN 300 DEGREES, THEY WOULD PROBABLY SUFFER AN ALLERGIC REACTION

1. If A and B and C are all true then the formula should return the value “ALLERGIC REACTION”

1. Otherwise, the formula should return the value “SAFE”.

4. Now, we will extrapolate the above analysis from our study to our actual sales data:

1. In cell M2, enter a formula to count the number of expected allergic reactions from the column “ALLERGIC REACTION?” of the ANALYSIS HINT: COUNT a cell from that column only IF it contains the value “ALLERGIC REACTION”.

1. In cell M3, enter a formula to count the total number of records in the ANALYSIS HINT: COUNT all the cells that are not empty in ANY column of the table.

3. Now in cell M4, enter a formula to estimate the % allergic reactions; divide M2 by M3.

5. Now that we have an estimate of the % of allergic reactions from our actuarial data, we can estimate the actual number of allergic reactions among our customers.

1. First, in cell M9, enter a formula to calculate the estimated number of tainted bags of grape-jelly-flavored marshmallows (“GJM”) sold by multiplying the total number of bags of GJM we sold, M7, by M8, the actuaries’ estimate of the percent that were tainted. Round the calculated result up to the next integer.

1. Next, in cell M11, enter a formula to compute the estimated number of allergic reactions by multiplying the estimated number of tainted bags of GJM, M9, by M4, the estimated percent of allergic reactions for people who bought the tainted bags of GJM. Round the result up as above.

6. Finally (for this sheet), enter a formula in cell M13 to estimate our total liability if we choose option 2 (allergic reactions + lawsuits) by multiplying the expected number allergic reactions in M11 by M12, our lawyers’ estimates of how much each lawsuit settlement will cost.

(BONUS Q:  What’s the average temperature to which tainted bags of GJM that would probably cause an allergic reaction were cooked?  __________ ANSWER HERE OR IN THE LIABILITY SHEET)

7. Next, on the RESPONSIBILITY sheet, we consider the cost of recalling the tainted marshmallows.

1. Open the RESPONSIBILITY spreadsheet and show the scenario entitled “REASONABLE”. Observe that the TOTAL COST OF the RECALL, D17, is the sum of fixed costs like “LOSS OF GOODWILL” and variable costs necessary to retrieve and replace the tainted GJM.

8. In cell D9, enter a formula to calculate the TOTAL VARIABLE COST FOR RECALL by multiplying the estimated NUMBER (TAINTED UNITS) CONTAINING PEANUT BUTTER, D4, by the sum of VARIABLE COSTS PER BAG, D7 + D8.

8. Based on the current sales figures, D2, and actuarial estimates as to the percentage of tainted bags of GJM, D3, we arrive at one result: doing the recall is less expensive than letting our customers eat until they suffer allergic reactions and getting sued.  However, because the cost of a recall depends on the actual number of bags of tainted GJM, we want to consider WHAT happens to our costs IF our actuaries guessed wrong and the percentage of tainted GJM, D3, changes.

1. Conduct a What-If Analysis on the data table in cells F2:I34 to see what happens to the “TOTAL COST LIABILITY”, the “TOTAL COST RECALL”, and the “DIFFERENCE” between the two when you change the “PERCENT (TAINTED UNITS) CONTAINING PEANUT BUTTER”, D3, from 0.25% to 8.00%.

34. Next, to help you evaluate the trends add a chart plotting the “TOTAL COST LIABILITY” in cells G3:G34 against the % TAINTED BAGS in cells F3:F34.  Hint: Add a chart that plots one data column against another and uses smooth lines.  Resize and/or move the chart so it approximately spans the cells K2:S17.

1. Select the data for the chart you made in (c) above and:

1. Edit the data series from (c) so that it has the name “Liability”.
2. Add a new data series whose “x” values are F3:F34, the “% TAINTED BAGS” column, and whose “y” values are H3:H34, the “TOTAL COST RECALL” column.

• Name the 2d data series from (ii) above “Responsibility”.

1. Add a descriptive title and a legend to your chart.

1. Examine your data table and/or your chart and identify – either by entering a percent into cell K19 OR by drawing a pointed arrow on the chart itself – at approximately what percentage does it first become cheaper to take responsibility and conduct a recall?

9. Now turn to the last spreadsheet in your exam workbook, ANNUITY. According to our calculations, it would cost the company more to allow people to eat the tainted GJM and settle the resulting lawsuits than it would cost us to be responsible and recall them.   However, this might not be true if we could pay out the lawsuits in future dollars but would need to pay for the recall using present money.  To better compare one cost to the other,

1. Assume the dollar value of legal liability in cell C2 is in future dollars, 5 years from now. Compute the present value of C2 assuming an 8% APR compounded monthly.

12. First, in cell C12 enter a formula to compute the per period (monthly) percentage rate by dividing the APR, C11, by 12.
13. Next, in cell C14, enter a formula to compute the number of periods by multiplying the number of years, C13, by 12.

• Finally, in cell C15 using either your own formula or the Excel PV function, compute the present value of C2 under the given conditions.

2. If the APR changes, so does the present value of our future legal liability, C2. The APR will change according to the economy, so in cell I11 enter a formula that will find an exact match for the value “RECESSION” in the data range G3:J4 and return the corresponding APR from the 2nd row of that table.  Next, in cell I15 enter a formula that uses the automatically updated rate from cell I12 and the number of periods from I14 to compute the present value of

10. Finally, choose to be responsible and conduct the recall. In cell M14, using the “COST OF RECALL”, C3, as your present value basis, use the PMT function to compute the monthly payment on a 5 year annuity to cover that cost, assuming 1% APR compounded monthly.