Question

For this learning activity, use the “MailCampaign.xlsx” file. Use the Goal-Seek tab. Perform a goal-seek analysis that shows the average order size necessary to break even (i.e., have a net income of zero).

Mailing Campaign Analysis
Costs			Mailing inputs
Printing	24,000.00		Fixed cost of printing	10,000
Mailing	14,000.00		Variable cost for printing	0.10
Total Costs	38,000.00		Variable cost for mailing	0.28
			Number of catalogs mailed	50,000
Net order revenue
Revenues	220,000.00		Responses and orders
Variable costs	176,000.00		Response rate	0.08
Net revenue	44,000.00		Average order	55
			Order processing costs (%)	15%
Net income	6,000.00		Cost of goods sold (%)	65%

Answer 1

A	B	C	D	E	F	G	H
2
3		Mailing Campaign Analysis
4
5		Costs			Mailing inputs
6		Printing	24000		Fixed cost of printing	10000
7		Mailing	14000		Variable cost for printing	0.1
8		Total Costs	38000		Variable cost for mailing	0.28
9					Number of catalogs mailed	50000
10		Net order revenue
11		Revenues	190,000.00	=G9*G12*G13	Responses and orders
12		Variable costs	152,000.00	=D11*(G15+G14)	Response rate	0.08
13		Net revenue	38,000.00	=D11-D12	Average order	47.5
14					Order processing costs (%)	15%
15		Net income	0.00	=D13-D8	Cost of goods sold (%)	65%
16
17		Hence Average order size to beakeven is	47.5
18

Goal Seek settings