Increase your marketing efficiency with Microsoft Excel Formulas and
Solutions from InfoTech Marketing. We provide two types of solutions:
Microsoft Excel formulas for common marketing problems, and
database solutions
for more efficient program execution. "Do more with less" with our
Microsoft Excel solutions for marketing.
| Task |
Use |
Formula |
Cell Example |
Numeric Example |
Numeric Results |
|
Interpretation |
| Compound Annual Growth Rate |
Examine growth between two periods |
=(Last Period / First Period)^(1/Number of Periods)-1 |
=(f1/a1)^(1/5)-1 |
=(1000/500)^(1/5)-1 |
14.87% |
|
The market has been growing by 14.87% per year over the past 5 years. |
| Short-Run Break-even Quantity |
Find quantity needed to achieve 0 profits in short-run |
=Fixed cost/(Unit Revenue - Unit Variable Cost) |
=g2/(a2-b2) |
=6000/(100-40) |
100 |
|
We need to sell 100 units to break-even |
| Profit Impact |
Determine contribution to profits |
=((Unit Revenue - Unit Cost)*Proected Unit Sales)-Fixed Cost |
=((a2-b2)*c2)-g2 |
=((100-40)*500)-6000 |
$24,000 |
|
At our projected sales levels, we will contribute $24,000 to profits |
| Selling Price Based on Margin |
Set price based on cost paid and margin |
=Selling Price / (1 - Margin) |
=d2/(1-d3) |
=10/(1-0.5) |
$20 |
|
At a cost of $10 and desired margin of 50%, our selling price should be $20. |
| Suggested Retail Price |
Establish suggested retail price based on distribution chain margins |
=Manufacturer's Selling Price per Unit/(1-Wholesale Percent
Margin)/(1-Retail Percent Margin) |
=e2/(1-e3)/(1-e4) |
=5/(1-0.2)/(1-0.5) |
$12.50 |
|
If the manufacturer sells for $5, and the wholesale margin is 20% and retail
margin 50%, the MSRP should be $12.50 |
| Target-Return Price |
Set price based on desired ROI |
=Variable Unit Cost+((Desired Return % * Investment)/Projected Unit Sales) |
=b2+((f2*f3)/c2) |
=40+((0.15*200000)/500) |
$100 |
|
To earn 15% on the $200,000 investment with $40 unit cost and 500 projected
sales, our price must be $100 |
| Total Customer Lifetime Value |
Estimate a customer's contribution to corporate value including acquisition
cost |
=Present Value(Discount Rate per Period,Lifetime Number of Periods,Unit
Revenue-Unit Variable Cost per Period)-Acquisition Cost |
=-pv(f2,h3,a2-b2)-h4 |
=-PV(0.15,5,100-40)-125 |
$76.13 |
|
At a 15% discount rate with a lifetime of 5 years with purchases of $100 per
year and $40 variable unit cost and acquisiton cost $125, a customer
contributes $76.13 of value |
| Post Acquisition Customer Lifetime Value |
Evaluate a customer's contribution to corporate value after they are
acquired |
=Present Value(Discount Rate per Period,Lifetime Number of Periods,Unit
Revenue-Unit Variable Cost per Period) |
=-pv(f2,h3,a2-b2) |
=-PV(0.15,5,100-40) |
$201.13 |
|
At a 15% discount rate with a lifetime of 5 years with purchases of $100 per
year and $40 variable unit cost, a customer contributes $201.13 of value |
We've put 3 database solutions into a whitepaper, "3 Excel Database Tips
for Marketing Efficiency". This 14 page guide clearly shows 3 ways to do
more with less. 40 figures illustrate how to do it. Includes 4 sample
worksheets. Click here to download as a PDF.
The paper uses the sample data sets to illustrate the Excel formulas, tips,
and techniques. You can enter them, or you can download them from here.
Just click on the one you'd like.
To learn more about how we might help you with Microsoft
Excel formulas and database solutions, call
720-732-4588 or email
tim@infotechmarketing.net.
