Price Sheet Formula Question

[abyron]

Excel 2010

I am creating a price spreadsheet for my husband's company and have set it up so that when inflation occurs, I can insert a percentage amount, and all the base prices will increase. I want to be able to make the after inflation prices the new base prices in case I need to adjust for inflation again in the future. Is there a way to do this without having to change the code each time a price increase occurs?

 

[bryonwoods30]

well since your base price cells contain the formula to read from the % you enter to give you the new base price, one thing you can do is somewhere else, another sheet or another column on that sheet, just start a list of your inflation % increases and then in the cell where you normally enter the inflation increase make it the sum of all your increases.

 

[abyron]

I hadn't thought of that! I will give it a try. Thank you

 

[scottylad2]

It can be done by setting the inflation in a cell and having the table of prices look at that in the below manner, and when inflation increases (as it always does!) just change the rate of increase in that cell. Start it off by having 0% so you can have todays prices, By having Supplier price and sale price seperate, you can alter the price your suppliers charging if they make a raise and make your increases for inflation accordingly. The percentages in the inflation are just a guess and can be adjusted at any time, but looking at the recent years inflation it seems to increase at small increments so having a table that goes to 9 or10% should just about cover most inflations for the forseeable

EDIT ignore the 75% in above example, the inflation is set at 0.08

your suppliers price will be your base, the inflation column only comes into play when you raise your own sale price, so if you leave the 0.08 at 0 then no increase on your base.

Excel Workbook
	H	I	J	K	L
4	Annual Increase	75.00%	0.08		0.015
5					0.02
6	Stationary	Supplier Price	Sale Price		0.025
7	Pen	0.75	0.81		0.03
8	Pencil	0.65	0.70		0.035
9	ruler	1.00	1.08		0.04
10	Eraser	0.55	0.59		0.045
11	Staple Gun	2.50	2.70		0.05
12	Staples	1.25	1.35		0.055
13	Paper Clips	1.05	1.13		0.06
14	Writing Pad	0.99	1.07		0.065
15	Paper	3.75	4.05		0.07
16					0.075
17					0.08
18					0.085
19					0.09
20					0.095
21					0.1
Sheet1

 

[abyron]

Thank you for your help! I will try that, too