• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Purchase Price Variance analysis setup.

trianna

New Member
Excel Purchase Price Variance analysis setup.

Hello!

I’m in desperate need of understanding/direction and would be extremely appreciative if someone could help me to understand how to set this up. I am using MS Excel 365

I am working on an Excel project for a perspective employer. I fine with the Excel portion but am having problems with the Accounting/Supply Chain portion. I’m not familiar.

The ask is, Create a tab that shows the following: For supplier 13, please conduct a quick Purchase Price variance analysis for those parts. This should compare the purchase price in 2019 to the Purchase Price in 2020. I’d like to know which part saw the largest decrease in price from 2019 to 2020 (excluding discontinued parts of course).

By Purchase price, that would be the average price paid for a given item (you could also use the last price paid).

They are looking for price per item variance analysis at the part level from 2019 to 2020. Which will show when a price reduction occurs, since the total spend (Qty * Price) needs to be normalized for qty changes. The Purchase price could only decrease by 100%.

The sample data table would be is as follows.

Year
Period
Supplier
Amount
Quantity
UnitPrice
ItemNumber
20192019M7Supplier 13
$25​
1​
$25.02​
Part 4255
20202020M1Supplier 13
27.108​
1882.8​
0.01​
Part 5000
20192019M7Supplier 13
$28​
1​
$27.61​
Part 4505
20202020M1Supplier 13
29.136​
1.2​
24.28​
Part 4506
20202020M4Supplier 13
29.136​
1.2​
24.28​
Part 4506
20202020M1Supplier 13
29.46​
72​
0.41​
Part 5012
20202020M1Supplier 13
30.156​
2079.6​
0.01​
Part 4997
20192019M5Supplier 13
$31​
1​
$30.55​
Part 4254

Can anyone help me understand how the price per item variance should be set-up?

I am very appreciative of any help and guidance you can give.

Tri
 

Attachments

  • Purchase Price Variance Sample.xlsx
    149.7 KB · Views: 0
Last edited:
trianna
You should refresh Your memory and reread Forum Rules
especially How to get the Best Results at Chandoo.org
  • For the best/fastest results, Upload a Sample File ...
 
Back
Top