John Jacobs works for the Fresh Toothpaste Company and has recently been assigned to investigate a new type of toothpaste dispenser. The traditional tube of toothpaste uses a screw-off cap. The new dispenser uses the same type of tube, but there is now a flip-top cap on a hinge. John believes this new cap is easier to use, although it is a bit messier than the screw-off cap—toothpaste tends to accumulate around the new cap. So far, the positive aspects appear to outweigh the negatives. In informal tests, consumers reacted favorably to the new cap.
The next step was to introduce the new cap in a regional test market. The company has just conducted this test market for a six-month period in 85 stores in the Cincinnati region. The results, in units sold per store, appear in Figure 9.42.


John has done his homework on the financial side. Figure 9.43 shows a break-even analysis for the new dispenser relative to the current dispenser. The analysis is over the entire U.S. market, which consists of 9530 stores (of roughly similar size) that stock the product. Based on several assumptions that we soon discuss, John figures that to break even with the new dispenser, the sales volume per store per six-month period must be 3622 units. The question is whether the test market data support a decision to abandon the current dispenser and market the new dispenser nationally.


We first discuss the break-even analysis in Figure 9.43.The assumptions are listed in rows 4 through 8 and relevant inputs are listed in rows 11 through 17. In particular, the new dispenser involves an up-front investment of $1.5 million, and its unit cost is two cents higher than the unit cost for the current dispenser. However, the company doesn’t plan to raise the selling price. Rows 22 through 26 calculate the net present value (NPV) for the next four years, assuming that the company does not switch to the new dispenser. Starting with any first year sales volume in cell C30, rows 29 through 35 calculate the NPV for the next four years, assuming that the company does switch to the new dispenser.
The goal of the break-even analysis is to find a value in cell C30 that makes the two NPVs (in cells B26 and B35) equal. The trickiest part of the analysis concerns the depreciation calculations for the new dispenser. You find the before-tax contribution from sales in row 31 and subtract the depreciation each year (one-quarter of the investment) to figure the after-tax profit. For example, the formula in cell C33 is = (C31-C32)*(1-$C$16)
Then you add back the depreciation to obtain the cash flow, so that the formula in cell C34 is = C33 + C32 Finally, you can calculate the NPV for the new dispenser in cell B35 with the formula = B34 + NPV(C17,C34:F34) Note that the initial investment, which is assumed to occur at the beginning of year 1, is not part of the NPV function, which includes only end-of-year cash flows.
You can then use Excel’s Goal Seek tool to force the NPVs in cells B26 and B35 to be equal. Again, begin by entering any value for first-year sales volume with the new dispenser in cell C30.Then select Goal Seek from the What-If Analysis dropdown menu on the Data ribbon and fill out the dialog box as shown in Figure 9.44. The file Toothpaste.xlsx does not yet contain
the break-even calculations. Your first job is to enter



The appropriate formulas, using any year 1 sales volume figure in cell C30. Next, you should use Excel’s Goal Seek tool to find the break-even point. Finally, you should test the alternative hypothesis that the mean sales volume over all stores (for a six-month period) will be large enough to warrant switching to the new dispenser. This hypothesis test should be based on the test-market data from Cincinnati. Do you recommend that the company should switch to the new dispenser? Discuss whether this decision should be based on the results of a hypothesis test.