Tasks:

Cells labeled in yellow as designated for user input in Ad Campaign planning

Insert the correct formula / equation into the following cells to complete this ROI calculator:

Cell Description

E4 Total Number of Impressions (Customers reached)

E8 Number of Ad Clicks Received

E12 Number of Purchases Completed

J4 Total Sales $

J6 Total Cost of Goods Sold (COGS)

J8 Gross Profit

J12 Return on Investment (include cost of Advertising $ Spent)

(Hint: Youll need to calculate (Profit resulting from Ads / Amount Investment in Ads ) * 100%

Scenarios to Calculate

Scenario Advertising $ Impressions/$ CTR VCR ASP COGS ROI

1 $1000 10 .25 .05 20 12 ?

2 $5000 5 .10 .10 50 35 ?

3 $2500 2 .15 .15 100 50 ?

Advertising ROI Activity Money spent Sales Funnel Estimates Financial Results Advertising Spend Impressions Total Sales $ Click through Rate Total COGS Impressions per $ # of possible consumers reached Clicks Gross Profits (Sales - COGS - Social Media Ad $) Visitor Conversion Rate Notes: Yellow Cells indicate inputs Number of Purchases Return on Investment Average Selling Price Cost of Goods Sold (COGS) Using the Advertising ROI Template (as pictured above) created in Excel as a starting point, complete the following activities. Advertising ROI Activity Money spent Sales Funnel Estimates Financial Results Advertising Spend Impressions Total Sales $ Click through Rate Total COGS Impressions per $ # of possible consumers reached Clicks Gross Profits (Sales - COGS - Social Media Ad $) Visitor Conversion Rate Notes: Yellow Cells indicate inputs Number of Purchases Return on Investment Average Selling Price Cost of Goods Sold (COGS) Using the Advertising ROI Template (as pictured above) created in Excel as a starting point, complete the following activities