Instructions for Data Input on Marketing ROI Worksheet Considering Net Present Value

  1. Open the “Data” tab on the bottom of the document.
  2. Enter the different “campaigns” you will participate in. Include everything you do that exposes your company to a potential client.
  3. If there is a cost to this activity, list it in the investment column.
  4. From your CRM report, total the project revenue that was “won” during the designated timeframe and input that total in the “project revenue” column. Project revenue = non-recurring/transactional.
  5. From your CRM report, total the recurring revenue that was “won” during the designated time frame. Input what you estimate for the total revenue for that campaign in year 1 – 5.

Example

·  Client engages us for estate planning = Project Revenue of $2,500

·  Same client engages us to do their annual tax preparation = Recurring revenue of $1,000.

·  $1,000 is entered in column 1 through 5 (accumulative of all recurring revenue for that campaign, not just one client)

  1. The tool assumes a net present value discount of 10%. If you want to utilize a different discount number, input that number in cell C2 on the data tab of the worksheet.
  2. Click on the report tab and your input data will have populated the main screen.
  3. The Revenue cells and New Lead cells are optional.

·  YTD = Year to Date Revenue

·  PPYTD = Prior Period Year to Date (same data for same time frame – one year prior)

·  % to PPYTD = will indicate the percentage of growth for that period

·  % to YTD Goal = will indicated the percentage to goal for the revenue period

  1. Leads data will be taken from the CRM report. How many leads were generated from that campaign? How many converted to actual clients? The spreadsheet will calculate the conversion ratio.
  2. Brand impressions: During the campaign, how many times did people see your logo, message, etc.? Include number of ads in paper, number of impressions on social media, email messaging, etc.

The sheet is unprotected. Save a clean copy and then utilize the tool go forward. Additionally, if you do find an error, or have feedback for improvement, please let us know. We want to improve the process for all involved. As such, we will send out occasional updates.

Add to the bottom of the worksheet other metrics you want to monitor and you will have a complete marketing ROI report.