+ Reply to Thread
Results 1 to 5 of 5

ROI calculation

  1. #1
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    ROI calculation

    I'm having a brain freeze and am going round in circles trying to figure this one out. Hopefully someone can help me out in terms of what I need to include and how to set it out on Excel.

    EG:
    I have a 72 page catalogue of products. Each page has anything from 1 to 40 products on it.
    I pay £31,000 for 50,000 catalaogues which generates £1m sales in the year

    Page 1 shows 20 products (Toy1-Toy20), and generates £11,720 for the year
    Toy1 takes up 0.035 of the page space and generates £1,793 for the year.
    Toy2 takes up 0.045 of the page space and generates £750 for the year.

    I'm trying to figure out what each product contributes in terms of the overall catalogue but Im struggling to get started on this one? Any suggestions/ideas greatly appreciated (not sure if this is an Excel query or a maths query)?

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,501

    Re: ROI calculation

    So you're trying to figure out based on space in a catalogue how much ROI you get for a product, if the product takes up 0.035 space on a page, and the page is 1 of 72 in a catalogue? I would think the traditional way to calculate ROI would be based on how much the (your example) the toy cost you (your costs) plus overhead (shipping, handling, labor, taxes, building, electricity etc) then how much you sold it for.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: ROI calculation

    So do you have an Excel spreadsheet that details all of these toys, money generated, page space etc?
    If someone has helped you then please add to their Reputation

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,501

    Re: ROI calculation

    Alright, using your example, if you have £31,000 for 50,000 catalogues, the price per copy is £0.62, if your catalogue is 72 pages, the price per page is £0.01 and if the space on one page taken up by a toy is (toy 1 in this instance) 0.035, assuming all else is equal, then your price for toy 1 on the page is £0.00030 (72/£0.62)*0.035 and I would probably distribute that cost over the 50,000 copies and that would equal £15.07 for your "costs" for toy 1. Now if the sales generates £1,793.00 AND you have no additional costs, you're just acting as a middleman, you don't buy it, you don't stock it, you don't have any holding or transfer costs, then I would think your ROI would be your (£1793-£15.07)/£15.07 or about 118 ROI. I'll be interested in seeing how others answer this.

  5. #5
    Registered User
    Join Date
    12-17-2009
    Location
    Kirkcaldy, Scotland
    MS-Off Ver
    Microsoft® Excel® for Microsoft 365 MSO (Version 2201) 64-bit
    Posts
    75

    Re: ROI calculation

    This was a sample of the spreadsheet I had so far.roi test.xls

    Not sure if this helps? If anyone can adapt/add relevant formulae it would be appreciated.

+ 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. Replies: 0
    Last Post: 05-17-2014, 10:18 PM
  2. Replies: 1
    Last Post: 09-24-2013, 03:03 AM
  3. Replies: 1
    Last Post: 01-15-2013, 08:51 AM
  4. [SOLVED] Conditional statement calculation based on result of previous calculation.(Need some help)
    By takeprofit in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-01-2012, 01:45 AM
  5. Add an amount from a calculation to a cell with a calculation (Sample attached)
    By aiwnjoo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2010, 10:41 AM

Tags for this Thread

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