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 ?

Use the numbers above to calculate RETURN ON INVESTMENT

The formulas need to be entered on the EXCEL worksheet picture Above. All the Yellow Boxes and the Cells indicated need to be Filled out.

Thanks!

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