Quantitative Methods: Lab #3

Chapter 6: Normal Probability Distributions and Profit Calculations

You will have only one file for this lab.

Specialty Toys

Read the Case Problem “Specialty Toys” that begins on page 294 of the textbook. You will not have data for this case problem, but you will need to use Excel to do some computations of both probabilities and profits.

Your first step should be to enter Excel and begin a file called "Stat Lab 3 Specialty, yourname" and save it. Throughout the case problem you will be asked to do some work in Excel and some in your separate lab report. Be sure that you do not omit important aspects of this assignment and, as always, thoroughly report your results for maximum credit.

Complete all of the case problem parts as they appear in the book. Additional explanations and/or tasks are given below.

#1. Complete in your lab report. Make sure that your sketch of the probability distribution is correctly and completely labeled. (3 points)

#2. The probability of a “stock-out” is basically the probability that sales will exceed the quantity of toys that were originally ordered. For example, you will need to compute the probability that sales exceed 15,000 and then repeat for the three other quantities suggested by the management team.

a. First compute these four probabilities in your lab report using Z-scores and the probability tables. Clearly show your work and include a diagram of the probability distribution in each case, including the area that corresponds to the probability of stock-out. (8 points)

b. Next compute the same probabilities in Excel. Instructions for using this formula appear on pages 296-297 of your book. In B1 create the label “Prob stockout 15K”. In B2, use the =Normdist function where x is 15000, the mean is 20,000 and the standard deviation is what you found in #1. Notice that if you include the word “true” in the formula that this will provide the cumulative probability of sales being 15,000 or less. You need to find the probability that sales exceed 15,000 so you need to modify your formula in B2 accordingly.

Repeat this process in C1/C2, D1/D2 and E1/E2 to complete the other three sales estimates and their stock-out probabilities. (4 points)

#3. You are given three sales scenarios and need to compute profit for each of the four estimates from the management team. This will entail four tables and a total of 12 profit calculations.

a. First compute these profits in your lab report. Create tables that look similar to the one below. I have computed the first row for an order of 15,000 and the worst-case scenario of only 10,000 units sold. With a calculator you can fairly quickly replicate the other profit calculations. (12 points)

Example table for lowest order quantity and worst-case scenario

Order Quantity: 15,000

		Sales
Unit Sales	Total Cost	at $24	at $5	Profit
10,000	240,000	240,000	25,000	25,000
20,000
30,000

b. This can be done quickly in Excel if you create formulas that are flexible enough to adapt to changes in critical pieces of information. Try following these instructions.

In A8 create the label “Order Quantity”, in A9 input 15000.

In B8 create the label “Cost Per Unit” and in B9 input $16.

In C8 create the label “Full Sale Price” and in C9 input $24.

In D8 create the label “Surplus Sale Price” and in D9 input $5.

Now we want to replicate a table like the one provided above, only using flexible formulas that will allow instant computations of profit if we change in the information in A9, B9, C9, or D9. In the formulas below the use of the $ signs is important so the formulas will always pull information from those reference cells. If I have left off a $ sign below, that’s intentional.

In F8 through J8 paste the column headings from the sample table above.

In F9 through F11 input the three sales levels from the table above.

In G9 create the following formula: =A$9*B$9 and this should compute total cost of $240,000. Drag and copy below to fill in the next two cells.

Even if our sales exceed our ordered quantity we only earn revenue on the toys we ordered. We need a formula that determines whether we exceeded the ordered units. In H9 create the following formula: =IF(A$9>f9,F9*C$9,A$9*C$9) and this should compute revenue of $240,000 (10,000*$24). Drag and copy below to fill in the next two cells.

We only have an entry in I9 if sales fall below the order quantity so we need to again use the =IF formula: =IF(f9<A$9,(A$9-f9)*D$9,0) Drag and copy below to fill in the next two cells.

In J9 create a simple formula that adds the revenues and subtracts the costs. Drag and copy below to fill in the next two cells.

You should now have a completed profit table for the worst-case scenario. It should be flexible enough so that I can change the order quantity in A9 and get perfect profit tables for the most likely and best case scenarios. Although the case doesn’t ask for different profits for different costs or prices, the table should also be able to automatically handle a change in costs or prices if I were to make them in B9-D9.

(9 points)

#4. You are looking for an order quantity that allows for only a .30 probability of a stock-out event.

a. First compute this value in your lab report using Z-scores and the probability table. Clearly show your work and include a diagram of the probability distribution, including the area that corresponds to the probability of stock-out. (2 points)

b. Create a label in a cell like G1 that says “Order for 30% Stock-out Prob” Now compute this in Excel with the =Norm.inv formula. An example is provided on page 295. (2 points)

5. Complete this part in your lab report. Providing a thorough and thoughtful rationale, including specific values from your calculations, is preferred. (10 points)