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). **DESIGN IS COMPLETE - JUST NEED PROPER CALCULATIONS ** **EXAMPLE SPREADSHEET ATTACHED ALONG WITH DETAILS OF CALCULATIONS ** 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