Excel 2019 Bible

Chapter 31: Performing Spreadsheet What-If Analysis

3131

This workbook, named direct mail data table.xlsx, is on this book’s website at

http://www.wiley.com/go/excel2019bible.

This model uses two input cells: the number of promotional pieces mailed and the antici-

pated response rate. The following items appear in the Parameters area:

Printing costs per unit The cost to print a single mailer. The unit cost varies with the

quantity: $0.20 each for quantities less than 200,000; $0.15 each for quantities of 200,001

through 300,000; and $0.10 each for quantities of more than 300,000. The following formula

is used:

=IF(B4<200000,0.2,IF(B4<300000,0.15,0.1))

Mailing costs per unit A fixed cost, $0.28 per unit mailed.

Responses The number of responses, calculated from the response rate and the number

mailed. The formula in this cell is as follows:

=B4*B5

Profit per response A fixed value. The company knows that it will realize an average

profit of $18.50 per order.

Gross profit This is a simple formula that multiplies the profit-per-response by the num-

ber of responses:

=B10*B11

Print + mailing costs This formula calculates the total cost of the promotion:

=B4*(B8+B9)

Net Profit This formula calculates the bottom line—the gross profit minus the printing

and mailing costs.

If you enter values for the two input cells, you see that the net profit varies quite a bit,

often going negative to produce a net loss.

Figure 31.8 shows the setup of a two-input data table that summarizes the net profit at

various combinations of quantity and response rate; the table appears in the range E4:M14.

Cell E4 contains a formula that references the Net Profit cell:

=B14

To create the data table, follow these steps:

1. Enter the response rate values in F4:M4.


2. Enter the number mailed values in E5:E14.


3. Select the range E4:M14, and choose Data ➪ Forecast ➪ What-If Analysis ➪
   Data Table. The Data Table dialog box appears.