Part V: Analyzing Data with Excel
752
l (^) Mailing costs per unit: A fixed cost, $0.28 per unit mailed.
l Responses: The number of responses, calculated from the response rate and the number
mailed. The formula in this cell is the following:
=B4B5
l (^) Profit per response: A fixed value. The company knows that it will realize an average
profit of $18.50 per order.
l (^) Gross profit: This is a simple formula that multiplies the profit-per-response by the num-
ber of responses:
=B10B11
l Print + mailing costs: This formula calculates the total cost of the promotion:
=B4*(B8+B9)
l 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 36.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 con-
tains a formula that references the Net Profit cell:
=B14
FIGURE 36.8
Preparing to create a two-input data table.
To create the data table
- Enter the response rate values in F4:M4.
- Enter the number mailed values in E5:E14.