The Krazy Knits Company sells hand-knit hats. The company is planning to print a catalog of its products and undertake a direct mail campaign.

The fixed cost of printing the catalog is $20,000. Variable costs are $0.10 per catalog for printing and $0.15 per catalog for mailing.

In addition, the company will include direct reply envelopes in its mailings. An extra cost of $0.20 is incurred for each direct reply envelope that is used by a customer to place an order.

The average value of a customer order is about $40 (sales revenue) and the company’s variable cost per order averages about 80% of the order’s value.

The company plans to mail between 50000 and 150,000 catalogs but wants to be able to experiment with different values. Response rate (orders placed) may vary from 1% to 8% of the number of catalogs mailed.

Create a spreadsheet model to determine the company’s profit, and use your model to answer the following questions:

Number of catalogs mailed and Response rate are the decision variables.

a. How does a change in the response rate affect profit?

i. Create a one-way table using Excel Data/What-If Analysis/Data Tables and response rates of 1%, 2%, 3%, 4%, 5%, 6%, 7%, 8%

ii. Graph your results

b. For what response rate does the company break even if number of catalogs mailed is 100,000?

Use the Excel Goal-Seek function

c. If the company estimates a response rate of 3%, should it proceed with the mailing of 100,000 catalogs?