# inventory, summary, inflation, average, percent

• Sep 10th 2008, 09:16 AM
damsel.blue
inventory, summary, inflation, average, percent
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. (Happy)