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.
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
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 |
| 2019 | 2019M7 | Supplier 13 | $25 | 1 | $25.02 | Part 4255 |
| 2020 | 2020M1 | Supplier 13 | 27.108 | 1882.8 | 0.01 | Part 5000 |
| 2019 | 2019M7 | Supplier 13 | $28 | 1 | $27.61 | Part 4505 |
| 2020 | 2020M1 | Supplier 13 | 29.136 | 1.2 | 24.28 | Part 4506 |
| 2020 | 2020M4 | Supplier 13 | 29.136 | 1.2 | 24.28 | Part 4506 |
| 2020 | 2020M1 | Supplier 13 | 29.46 | 72 | 0.41 | Part 5012 |
| 2020 | 2020M1 | Supplier 13 | 30.156 | 2079.6 | 0.01 | Part 4997 |
| 2019 | 2019M5 | Supplier 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
Last edited: