Excel:

Quality Sweaters direct mail model

Catalog inputs Model of responses

Fixed cost of printing Q $$20,000\mathrm{.}00$ Response rate Q

Variable cost of printing & mailing Q $$0\mathrm{.}25$ Number of responses Q

Decision variable Model of revenue, costs, and profit

Number mailed Q $100000$ Total Revenue Q

Total Costs

Order inputs Total Fixed cost of printing Q

Average order Q $$40\mathrm{.}00$ Total variable cost of printing & mailing Q

Variable cost per order Q $$32\mathrm{.}20$ Total variable cost of orders Q

Total cost Q

Profit Q

For Q$1,$ Suppose now that the response rate is known to be $10\%,$ and the company wants to perform a sensitivity analysis on the number mailed. After all, this is a variable under direct control.

Create one$-$way data table and a corresponding line chart of profit versus the number mailed

In quality sweater example, we discussed how to create one way data table between profit and response rate. For Q$1,$ you need to create another oneway data table between profit and the number mailed with number mailed having an increment of $10,000$

Number mailed

Profit

$100,000$

$110,000$

$120,000$

$130,000$

$140,000$

$150,000$

$160,000$

$170,000$

$180,000$

For Q$2,$ you're required to use the goal seek for each value of number mailed and find the response rate that allows the company break even. And then create a line chart for these values

$$ For each value of number mailed with number mailed having an increment of $10,000($ranging from $100,000$ to $180,000),$ you will need to run Goal Seek to get the break$-$even value. For example, you run the goal analysis to find out what response rate is required if the number mailed is $100,000.$ And then, you will repeat the same goal seek analysis for $110,000,$

$120,000,...,180,000.$

$$

Number mailed

Breakeven response rate

$100,000$

$110,000$

$120,000$

$130,000$

$140,000$

$150,000$

$160,000$

$170,000$

$180,000$