+ Reply to Thread
Results 1 to 2 of 2

Volume Price Mix Calculation

  1. #1
    Registered User
    Join Date
    11-14-2016
    Location
    London
    MS-Off Ver
    365
    Posts
    1

    Volume Price Mix Calculation

    HI,

    not neccesarily an Excel issue specifically, but i'm trying to work out the impact a change in sales has on a ratio.

    the table below shows a loose illustration comparing the year on year movement of Cost vs Sales. I want to show how the impact of price movements for each line contributes to the total.

    As the variable in this instance is a denominator, its not a simple to calculate. (Cost of goods is easier, as you can do (change in cost) / sales to work out the impact)

    Any help would be marvelous, as i've trawled the internet with no solution.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Volume Price Mix Calculation

    As you indicate, this is probably not so much an Excel problem as it is a math problem. It is not clear to me exactly how this should be calculated. Perhaps because I am an engineer and not afraid of such things, but my first thought when trying to show the impact of "sales movement" on the final ratio of total cost to total sales is to use calculus. (T for total, Ci for each cost, Si for each sales).

    Defining total as T=sum(Ci)/sum(Si), it should be fairly straightforward to take the derivative of T relative to any of the Si's dT/dSi. How is your calculus?

    If your calculus is not up to that, then I can see obtaining the same result using simple algebra (Introduce Xi=Si+di, , di for the "sales movement"):
    T=sum(Ci)/sum(Si)
    T1=sum(Ci)/sum(Si)
    T2=sum(Ci)/sum(Xi)=sum(Ci)/[sum(Si)+sum(di)]
    T2-T1=sum(Ci)/[sum(Si)+sum(di)]-sum(Ci)/sum(Si) which describes how the change in each di changes T.

    Your sample does not include any price information, so I cannot see how price figures into this. I would guess that the di's are each a function of price. If you go with the calculus approach, there is another chain rule to show how changes in price impact the individual di's. If you go with algebra, then you need to define how each di is a function of price.

    Once you have done the calculus and/or algebra, then you just need to program those formulas into the spreadsheet.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. [SOLVED] Mix, Volume and Price impact on revenue
    By Stumped in San Francisco in forum Excel General
    Replies: 2
    Last Post: 08-23-2014, 11:39 AM
  2. Brand Map - Price and Volume Mapping - URGENT
    By allyshaik in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-02-2014, 03:43 AM
  3. Does anyone have models for price/volume variance
    By ammartino44 in forum Excel General
    Replies: 1
    Last Post: 03-27-2014, 01:16 PM
  4. Comparative chart for volume and price
    By Aman Bains in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-06-2013, 04:50 PM
  5. adjusting a price based on volume
    By kwkness in forum Excel General
    Replies: 6
    Last Post: 06-17-2013, 03:53 AM
  6. Two Variables of price vs volume calculation concurrently
    By omega0010 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-17-2012, 02:28 PM
  7. [SOLVED] calculate/convert volume price to monthly average price
    By Bultgren in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2006, 05:40 AM

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