1 Attachment(s)

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)