A firm wishes to analyze the relationship between the number of promotional pieces mailed and the anticipated response rate. It wants you to perform a sensitivity analysis of the effect on net profit using the following parameters:
Unit costs for printing the mailer are related to the number printed as follows: $0.20 per unit for quantities less than 200,000; $0.15 per unit for quantities from 200,000 to through 300,000; and $0.10 per unit if print more than 300,000.
Mailing costs are fixed at $0.24 per unit mailed.
The company knows that it will realize an average gross profit per response of $18.50
Their net profit for this example consists of the gross profit minus the printing and mailing costs.
Create a sensitivity analysis model in Excel that allows the firm to analyze the following:
They wish to explore the joint effects of allowing response rates to vary from 1.50 % to 3.25% (in steps of 0.25%) while the number mailed varies from 100,000 to 325,000 (in increments of 25,000).
Present your results as a table that shows the interaction response rate and number mailed on net profit. Be sure to clearly label all elements of your model. Highlight key findings that are of use to the firm.