Question

Can someone please help me with this, it's supposed to be done in Excel

Geico is looking at using an ad in Roadrunner Magazine to reach motorcycle owners to sell them insurance. Facts: Selling Price: $750.00 Profit Contribution Margin: 35% of selling price Advertising Cost: 231,000 readership @ $9,100 ad placement cost, plus ad production cost of $3,500 Historical Response Rate:.04% Questions: 1. Will this be profitable using the assumptions above? 2. What would ROI (ROMI) be? 3. How many units would we need to sell to breakeven?

Answer 1

1. Yes the business will be profitable using the assumption. Refer to the below calcuation showing the profitability

Calculation of Profit
Particulars	Amount	Note
a	Selling Price	$        750.00
b	Readership	231000
c	Historical Response rate	0.04%	b x c
d	So the number of customers would be ( As number of customer cannot be fraction , rounded)	92.00
e	So the sale revenue would be	$ 69,000.00	d x a
f	Contribution at 35%	$ 24,150.00	e x 35%
g	Less-Advertisement Production Cost	$    3,500.00
h	Less- Advertisement Placement Cost	$    9,100.00
i	Profit	$ 11,550.00	f-g-h

2. ROMI = ((Income from marketing – cost of goods – marketing expenditures) / marketing expenditures) x 100.

Income from marketing	$ 69,000.00
Less - Contribution	$ 24,150.00
Cost of goods Sold	$ 44,850.00
marketing expenditures
Advertisement Production Cost	$   3,500.00
Advertisement Placement Cost	$   9,100.00
	$ 12,600.00

So ROMI = (( 69000-44850-12600)/12600)x 100 = (11550/12600) x 100 = .9167 x 100 = 91.67

3.

Break even point = Fixed Cost /Contribution per unit
a	Selling Price	$       750.00
b	Contribution per unit at 35%	262.5
c	Fixed Cost (Marketing Expenses)	$ 12,600.00
d	Break even Point (c/b)	48