Excel, how do I do this recalculate a price list on annual increase.

[tea42]

I am a novice user of Excel and I am getting a little stuck with trying to update a list of prices (say a parts list) to include annual percentage increases.

For instance this year may require the prices updating by 4% and next year by 3%.

I was looking to put a value to increase by on the first worksheet of a workbook containing all the price list's (on separate worksheets) for different products and have the prices updated either automatically or following the press of a button.

 

[JimIssI]

Set up a formula that references a price increase cell. Adjust the value in the reference cell to post a price change.

If cell A1 is the ref for the increase and the price is in cell D5, then create a new column, adjusted price and enter the formula SUM(A1*D5)*100 to post the adjustment. Copy the formula to each row with a part.

Jim

 

[tea42]

Thanks for the reply JF, but would this not revert back in the following year if the value to add on this occasion was only 3%, i.e.

Description Part No. Qty Cost Total
Bearing B2343 3 Price+ref Qty+cost

If the reference cell on year one was set to 4% and the cost of the part was £1.00 <--(makes life easy) then the result would be £1.04, thats OK, however the following year a price rise of 3% was to be added unfortunately that would revert the price to £1.03?

When it should have reflected ((price+4%)+3%)

Any ideas?

 

[Dreamboat]

You need more columns, such as Current Year cost, Year 1 cost, and Year 2 cost; and Current Year total, Total year 1, and Total year 2

Year 1: =current year cost*1.04
Year 2: =Year1 cost*1.03

etc.

You could also use scenarios or views.
techsupportgirl@home.com
Brainbench MVP for Microsoft Word

http://www.brainbench.com

 

[tea42]

Ok, I think I have it now, thanks for your assistance it was much appreciated.

 

[rlingen]

If this is a once a year job that, once done, will remain as the new base, I would be inclined to write a straightforward Excel macro (in Visual Basic) that

a) backed up the file as the previous year's price

b) ran down the column of prices and actually changed them by the formula
New Price = old price * (1 + %increase)

this would avoid a very messy spreadsheet. All the new prices would be actual values, not formulae.

I do something very similar, except that mine is a discounting model, whereby I discount 1400 prices from an original price daily. Each item is unique, and it is discounted until it sells.

Roy