+ Reply to Thread
Results 1 to 4 of 4

Calculating cost changes needed to maintain margins

  1. #1
    Registered User
    Join Date
    10-27-2011
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Question Calculating cost changes needed to maintain margins

    Hi there,
    I have a price book for our products and currently use the following:

    A1 - 2018 Cost
    B1 - 2018 Retail
    C1 - 2018 Margin % calculation as follows =1-(A1/B1)

    I am adding the following columns:
    D1 - 2019 Cost
    E1 - 2019 Retail
    F1 - 2019 Margin % calculation as follows =1-(D1/E1)

    Our cost prices have gone up and our retail prices will change so I need to have a final calculation that tells me how much we would need to reduce our cost prices by to maintain the current 2018 margin.
    It's not as simple as just working out the difference between the two cost prices as our retail prices will change in 2019...

    Any help greatly appreciated!

    Many thanks

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: Calculating cost changes needed to maintain margins

    if you are entering a new retail price for 2019 and want to keep the same 2018 ,margin

    does
    =(1-C1)*E1
    =(1-2018 margin) * 2019 retail = cost needed in 2019 to maintain the 2018 margin
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    10-27-2011
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Calculating cost changes needed to maintain margins

    Hi Wayne,

    Thanks for that. I think I managed to get it to work using the following:
    =2019 retail-(2019 retail * 2018 margin)

    Seems to have done the trick, however having checked, your formula does the same thing.
    More than one way to do it.

    All the best

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,732

    Re: Calculating cost changes needed to maintain margins

    yep, just rearrangement of a formula really

+ 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. Creating a cost and revenue pivot. Gross margins % possible?
    By thedon_1 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-03-2013, 06:45 PM
  2. Calculating total cost from range of cell tally's by cost of item.
    By patrickdjames in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 11-04-2012, 10:27 AM
  3. Help required for calculating profit margins please.
    By Allgermanparts in forum Excel General
    Replies: 6
    Last Post: 07-02-2012, 07:22 AM
  4. Calculating margins with two tax values
    By coxy1983 in forum Excel General
    Replies: 3
    Last Post: 01-14-2011, 11:54 AM
  5. calculating cost
    By coccoster in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 04-13-2010, 03:03 AM
  6. Calculating cost
    By TMCEWAN259 in forum Excel General
    Replies: 5
    Last Post: 04-17-2009, 08:15 AM
  7. Calculating cost per second
    By Zarunil in forum Excel General
    Replies: 19
    Last Post: 10-29-2007, 04:24 PM
  8. Calculating a cost from hh:mm:ss
    By mattandrew in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2007, 08:39 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