Newbie - please help!!

[rockatubc]

Hi,

From my browsing of these boards, I am certain that one of the more enlightened members can help me with my problem. Here is my problem:

1. I have 3 stores - all with the same products but with varying price differences to the base price (due to store location)

2. I have one worksheet that has all the sales/cost of goods/price differentials of each of the three stores forcasted out 5 years.

3. Each store has its own worksheet - I use the offset function to pull values from the master data sheet. Pulling sales volumes and costs is not a problem...

4. The problem is pulling sales prices.....there is one last "pricing" worksheet that has a base price for products and adjusts this price for the store differentials found in the master data worksheet. However....obviously this is static and can calculate only one differential at a time ( I put the number 2 in the top left hand corner, the worksheet recognizes that this is store number 2 and pulls the price differentials for store 2 from the master data sheet)....however, since all 3 stores are linked to the same pricing sheet, all thee stores get the same pricing differentials.

phew...

My question: Is there a way, other than copying the pricing sheet 3 times over for the number of stores, to somehow from each store worksheet, "force" the pricing sheet to spit out correct differentials depending on the store sheet I am on....

i am sorry if this is confusing but please help..

thank you very much

 

[ken2step]

Load the Colo HTML maker at the bottom of the page and post some example data so i can more clearly understand. It sounds very workable, but still alittle unclear of what you desire.

 

[rockatubc]

Hi,

thanks for your reply - i really can't post the information b/c it is confidential. However, please tell me what u need me to clarify and i'll try my best....

 

[rockatubc]

can anyone assist me?

 

[Tazguy37]

Would something like this do?:

Book1
	A	B	C	D	E
1	Store	Store2		Store	Differential
2	Product	Widget-B15-1		Store1	5%
3	BasePrice	$15.00		Store2	6%
4	Adjustedprice	$15.90		Store3	7%
Sheet1

Formula in B4 is: =(1+VLOOKUP(B1,D2:E4,2))*B3

Hope that helps!

 

[daniels012]

If you won't give a sample of the sheets, at least give examples written out and what you want to get.

IE: Washington, Dallas, New York (3 places)
Dolls, rubix cubes, GI Joes, Balls, Blocks (6 toys)
Prices per city written out how you have it now on each sheet with prices from each city, etc. This will help us help you!!!!!!!!!!!!!


Michael

 

[rockatubc]

Ok - i've tried to illustrate an example below:

1st worksheet is an input page with my store's inventories and prices forcecasted out.

book2
	A	B	C	D
2	1	Inglewood	TotalInventory	Price
3		2004	3	$6.50
4		2005	4	$6.76
5		2006	5	$7.03
6		2007	6	$7.31
7		2008	7	$7.60
8		2009	8	$7.91
9		2010	9	$8.22
10		2011	10	$8.55
11		2012	11	$8.90
12		2013	12	$9.25
13
14	2	Laroche	TotalInventory	Price
15		2004	3	$6.50
16		2005	16	$3.57
17		2006	17	$3.71
18		2007	18	$3.86
19		2008	19	$4.01
20		2009	20	$4.18
21		2010	21	$4.34
22		2011	22	$4.52
23		2012	23	$4.70
24		2013	24	$4.88
25
26	3	Fileez	TotalInventory	Price
27		2004	3	$1.10
28		2005	28	$1.17
29		2006	29	$1.24
30		2007	30	$1.31
31		2008	31	$1.39
32		2009	32	$1.47
33		2010	33	$1.56
34		2011	34	$1.65
35		2012	35	$1.75
36		2013	36	$1.86
Store Inputs

 

[rockatubc]

Master pricing sheet:

book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	STORE#	1
2
3		2004	2005	2006	2007	2008	2009	2010	2011	2012	2013
4	BasePricing	23.54	24.717	25.9529	27.2505	28.613	30.0437	31.5459	33.1231	34.7793	36.5183
5	Differential	$6.50	$6.76	$7.03	$7.31	$7.60	$7.91	$8.22	$8.55	$8.90	$9.25	**IUSETHEOFFSETFUNCTIONTOPULLTHE
6	Netprice	$17.04	17.957	18.9225	19.9389	21.0089	22.1354	23.3213	24.5696	25.8836	27.2667	DIFFERENTIALSFROMTHEFIRSTPAGE
Pricing

 

[rockatubc]

Store income statement - each store has its own:

book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	STORENUMBER	1
2
3		2004	2005	2006	2007	2008	2009	2010	2011	2012	2013
4	Products	3	4	5	6	7	8	9	10	11	12	**IUSETHEOFFSETFUNCTIONTOPULLTHE
5	Pricing	$17.04	$17.96	$18.92	$19.94	$21.01	$22.14	$23.32	$24.57	$25.88	$27.27	INVENTORYFROMTHEFIRSTPAGE
6	Income	$51.12	71.828	94.612	119.63	147.06	177.08	209.89	245.7	284.72	327.2	PRICESDIRECTLYLINKEDTOPRICINGTAB
Inglewood

The main problem I am having is that one can see that the same "net price" is being applied to each store. How can I automate this so that depending on the store worksheet I am using, it is using the correct store differential and therefore getting the right net price. I know I could probably have 3 seperate pricing tables and pull seperately - but I am dealing with 250+ stores and that may get extremely complicated.

Please help again!!!