Custom Excel Spreadsheet Needed

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).





The spreadsheet must:

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

-Monthly Ad Spend (as a dollar amount)

-Ad Campaign Management Fee (as a percentage of Ad Spend)

-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):**

-Ad Campaign Management Fee (ie: $45,000 Ad Spend = $4,500 Fee)

-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 $1.25 where $1.25 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 Ad Spend

-Total Ad Campaign Management Fees

-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

Fähigkeiten: Ingenieurwesen, Excel, Microsoft Access, Microsoft Exchange, MySQL, PHP, Powerpoint, Softwarearchitektur, Testen von Software, Word

Mehr darüber advertising spend revenue spreadsheet, spreadsheet engineering, simple spreadsheet examples, residual network, network marketing investment, june is the month for, for the month of june, excel sheet for customer details, engineering course how much it cost, custom advertising design, cr design, cpm engineering, cost of sales breakdown, clear capital, capital sales, buy spreadsheet, buy ad clicks, 290-40, 1 dollar advertising, 1 dollar ads, 12 month spreadsheet, sales projections, excel scenario, engineering rates, spreadsheet design

Über den Arbeitgeber:
( 7 Bewertungen ) United States

Projekt-ID: #3800407

Vergeben an:


See private message.

$127.5 USD in 14 Tagen
(5 Bewertungen)

13 Freelancer bieten im Durchschnitt $162 für diesen Job


See private message.

$425 USD in 14 Tagen
(402 Bewertungen)

See private message.

$191.25 USD in 14 Tagen
(158 Bewertungen)

See private message.

$148.75 USD in 14 Tagen
(203 Bewertungen)

See private message.

$85 USD in 14 Tagen
(110 Bewertungen)

See private message.

$85 USD in 14 Tagen
(23 Bewertungen)

See private message.

$85 USD in 14 Tagen
(86 Bewertungen)

See private message.

$85 USD in 14 Tagen
(73 Bewertungen)

See private message.

$412.25 USD in 14 Tagen
(21 Bewertungen)

See private message.

$85 USD in 14 Tagen
(18 Bewertungen)

See private message.

$85 USD in 14 Tagen
(4 Bewertungen)

See private message.

$85 USD in 14 Tagen
(0 Bewertungen)

See private message.

$212.5 USD in 14 Tagen
(0 Bewertungen)