+ Reply to Thread
Results 1 to 2 of 2

Help with best way to automate reporting template with price markup

  1. #1
    Registered User
    Join Date
    06-17-2020
    Location
    Canada
    MS-Off Ver
    2019
    Posts
    27

    Help with best way to automate reporting template with price markup

    HI there, i've attached a reporting template I'm creating for people at my company. Purpose is to make their jobs easier by just copying and pasting an excel output from Facebook Ads manager and the sheet will auto-update graphs. Note that this will be used in Google Sheets, but I just exported it into excel since I have trouble sharing Google Sheets links.

    This workbook has 4 sheets
    • Summary--> People will type in their Primary, secondary, tertiary KPI based on Column I, and also Campaign spend. Rows 12-14 auto populate based on KPI
    • Don't touch- Reference Sheet --> Sheet with formulas that will be hidden. This is the sheet i need help with
    • Charts --> Charts that will auto update based on the PASTE sheet
    • PASTE platform/placement --> People download Facebook Ads manager excel workbook and paste here

    When you download metrics like CPC, CPE, Cost per 1000 people reached, CPM etc from Facebook, the Cost used is what's actually spend. However, in some cases with clients, there's a markup my company charges.

    I want a formula in all the Cost per columns (i.e. Column G, I & K) in "Don't touch-Reference Sheet" that will essentially, re-calculate the metric with this markup price.

    For example, if Primary KPI is Reach, I want the Cost per Primary KPI to be Client Facing Spend*1000/Reach vs. the current method, which is (Media Cost*1000/Reach) — in the workbook, the client facing spend = media cost, but this isn't always the case.

    I also want the formula above to be dynamic so if the PRimary KPI is cost per engagement, it won't have to multiply spend by 1000, so the calculation will just be (Client Facing Spend/ Engagements). I've written the Formulas I want to use in the Summary tab in Column M

    Please let me know what the best way of doing this is. I'm currently spinning my wheels trying to figure out the best way!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Help with best way to automate reporting template with price markup

    I feel as if we need more specific information to be able to help.
    As in cell G2 should display 6.20 because ...
    Also please tell us how to know whether primary KPI is reach or per engagement.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cost price based on different markup rates vs price
    By whunt188852 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-31-2021, 06:15 AM
  2. [SOLVED] show markup % in columns F-K when you put the markup (Col L) for a price (Col E)
    By TAMMY32 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-13-2020, 09:37 PM
  3. Replies: 6
    Last Post: 04-25-2020, 09:24 AM
  4. percentage markup on various price ranges
    By Larry H in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-09-2016, 08:49 PM
  5. percentage markup on various price ranges
    By Larry H in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-09-2016, 04:19 PM
  6. Margin/price markup as %
    By andycannon in forum Excel General
    Replies: 5
    Last Post: 06-17-2010, 06:30 AM
  7. Calculating Markup For Retail Price
    By gaspower in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-13-2007, 07:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1