I am looking to have a spreadsheet created that will be used to do internet marketing revenue projections based on several different variables over the course of a 12 month period. Most of the calculations are standard for internet marketing but the campaigns I am trying to do projections for have a recurring revenue model and will have a specific sales season and customer lifespan.

**I basically need 3 key areas addressed by the sheet:**

**1)** It will let me quickly and easily see important metrics and determine whether or not a certain Advertising scenario will be profitable and by how much. Inputs include things like CTR, Conversion Rate, CPM for ads, Recurring Sales Percentage, Avg Customer Lifespan, etc

**2)** Be able to take into account recurring sales revenue, sales cycles of different lengths and allow me to change the inputs for each month individually.

**3)** I need to know at what point the Advertising Campaign becomes self funding. In other words, how long do I have to finance the Ad Campaign out of pocket before the revenue covers the following month's Ad Spend.

## Deliverables

Below is a basic breakdown (I apologize if some of the examples are overly simple, I'm just trying to be clear).

**DESIGN IS COMPLETE - JUST NEED PROPER CALCULATIONS

**

**EXAMPLE SPREADSHEET ATTACHED ALONG WITH DETAILS OF CALCULATIONS

**

**A) Allow Me to Enter (and change) The Following Independently For Each Month:**

-Monthly Ad Spend (as a dollar amount)

-Cost Per Thousand (CPM) rates for advertising (as a dollar amount)

-Click Through Rate (CTR) for advertising creatives (as a percentage)

-Conversion Rates (as a percentage)

-Sales Commission Amount (as a dollar amount)

-Re-Investment %

-Recurring Sales Rate (as a percentage of total customers who buy again)

-Average Customer LIfespan (in months, as a decimal based number - ie: 4.5 months)

-Active Sales Season (in months - ie: 9 months, 6 months, etc)

-Network Fee (as a percentage)

-Estimated Taxes Due (as a percentage)

-Commission Payment Schedule (in days after end of month - ie: Sales for the month end on the 30th and payment of all revenue is made 15 days later)

**B) Based On The Above Information, The Following Needs To Be Calculated Automatically (and pretty much in this order):**

-Monthly Breakdown of Ad Spend into days (ie: 3 Days in the month, \$45,000 Ad Spend, \$1,500/Day)

-Number of Ad Impressions Per Day That Are Purchased (ie: \$1,500/Day = 1,200,000 at a CPM of \$[url removed, login to view] where \$[url removed, login to view] buys 1000 Ad Impressions)

-Number of Ad Clicks expected based on the total impressions and the CTR (ie: 1,200,000 impressions, CTR of .0020 = 2,400 Clicks)

-Number of Sales Per Day based on Conversion Rate (ie: 2,400 Clicks, Conversion Rate of 3% = 72 Sales per day)

-Daily Revenue based on daily sales and Commission Amount (ie: 72 Sales x \$25 = \$1,800 per day)

-Total Monthly New Sales based on Sales per day and Total Days in the Month

-Total Monthly Revenue of New Sales

-Number of Recurring Sales and Recurring Sales Revenue For Each Month Individually, based on Recurring Sales Rate and Average Customer Lifespan

-Raw Sales Revenue: Total New Monthly Sales Revenue + Total Recurring Sales Revenue

-Return on Investment (ROI) calculated as: [(Raw New Sales Revenue + Recurring Sales Revenue - (Ad Spend + Management Fee) / (Ad Spend + Management Fee))] * 100

-Gross Revenue: Monthly Sales Revenue Minus Ad Spend

-Network Fee as a Percentage of Monthly Sales Revenue Minus Adspend

-Net Revenue: Monthly Sales Revenue Minus Ad Spend and Minus Network Fee

-Estimated Taxes Owed as a Percentage of Net Revenue

-Net Profit: Net Revenue Minus Taxes

-Total Capital at The End of the Month (calculated as: Net Profit (if any) + Ad Spend + Unspent Capital from Previous Month)

**C) Calculated For the Entire Campaign (12 months)**

-Total Sales

-Total Raw Sales Revenue (Up Front + Residual Sales)

-Total Number of Up Front Sales

-Total Number of Recurring (Residual) Sales

-Number of Customers Acquired (front end sales only)

-Total Network Fees

-Total Estimated Taxes

-Total Net Profit

-Total ROI

Here is an Example of the calcuations as I've been doing them manually (this is just one month that includes some residual sales from previous months

June 2008 Monthly Ad Spend = \$120,000

10% Management Fee = \$12,000

\$4,000 = 3,600,000 ad impressions per day

.0025% CTR = 9,000 Clicks

4% CR = 360 Sales/day

Daily Revenue = \$9,000 (\$25/sale)

New Sales = 10,800

Total Monthly New Sales Revenue = \$270,000

Residual Sales:

April 2008 (1,620 x \$25) = \$40,500

May 2008 (4,380 x \$25) = \$87,600

Total Monthly Revenue (new sales + residuals) = \$398,100

Return on Investment (ROI) = 201%

Gross Revenue (raw sales minus Ad Spend) = \$278,100

10% Network Fee = \$27,810

Net Revenue (Gross minus Ad Spend + Fee) = \$250,290

Estimated Taxes (25%) = \$59,572

Net Profit = \$190,718

Total Capital Available (Profits + Ad Spend + Unused Capital from Previous Month) = \$310,718

Project ID: #3800407

## Awarded to:

vw6694515vw

See private message.

\$127.5 USD in 14 days
(5 Reviews)
2.8

## 13 freelancers are bidding on average \$162 for this job

pShankar2

See private message.

\$425 USD in 14 days
(402 Reviews)
7.1
schandraram

See private message.

\$191.25 USD in 14 days
(158 Reviews)
6.9
shikhamehta

See private message.

\$148.75 USD in 14 days
(203 Reviews)
6.8
yubbsoftware

See private message.

\$85 USD in 14 days
(110 Reviews)
6.3
stsolhouse

See private message.

\$85 USD in 14 days
(23 Reviews)
5.3
GishaJames

See private message.

\$85 USD in 14 days
(86 Reviews)
5.2
iLoveCoding

See private message.

\$85 USD in 14 days
(73 Reviews)
5.1
prateekjain83

See private message.

\$412.25 USD in 14 days
(21 Reviews)
4.7
cyberdeveloper

See private message.

\$85 USD in 14 days
(18 Reviews)
3.5
ignacio1980

See private message.

\$85 USD in 14 days
(4 Reviews)
2.2
sethwebster

See private message.

\$212.5 USD in 14 days
(0 Reviews)
0.0
walidzohair

See private message.

\$85 USD in 14 days
(0 Reviews)
0.0