Hello everyone! I need some expert advice regarding this.
I already tried searching price increase/decrease but the threads found are not exactly what I need.
If this has been previously answered, I would appreciate if you could post the link.
I need to know if I used the correct formula and what formula to use for the bottom line/summary of the attached spreadsheet…
So here it goes:
Since Year 2’s total material cost is greater than Year 1’s total,
My boss asked me to compare the unit price and qty of the materials we bought from Year 1 to Year 2.
The main goal of this spreadsheet is to show what caused the difference between Year 1 and Year 2’s material cost.
How much is caused by the Increase (Decrease) in quantity,
How much is caused by the Increase (Decrease) in Unit price.
For example if
5 pcs of Item A is bought @ $2.00 on Year 1 and 7 pcs is bought at the same Unit Price on Year 2,
The total cost would be: Year 1: $10 and Year 2: $14
So there is a difference of $4 and this entire cost (100%) is caused by increase in Quantity bought for Year 2.
Another scenario is if
55 pcs of Item D is bought @ $0.82 on Year 1 and 60 pcs is bought @ 1.25 on Year 2,
The total cost would be: Year 1: $45.10 and Year 2: $75
So there is a difference of $29.90 and this difference is caused by
$4.10 (13.71%) due to Increase in Quantity and
$25.8 (86.29%) due to increase in Unit price…
If my calculation for the 2 scenarios above is correct, then I do not have a problem calculating per Item…
I’m actually having a trouble showing the bottom line results. The summarized data…
Pls see the attached xls file. Thank you so much in advance.