What-if - Two Variable Data Table showing incorrect values

[DMfba]

Hi Everyone,

I have a data table to test the profit at different price points. I seem to be getting incorrect values from a two variable data table. The 1st row of values comes out correct but the rows below it are incorrect. After searching online for a bit, I can’t see to find a way to correct this.

Here is the set up. I have cells that hold the Price, Item Cost, and Qty sold. Then I have separate cells for Sales (Sale price * Qty sold), Cost (Item Cost * Qty Sold), and Profit (Sales – Cost).

My data table has changing Sale Price at the top going across the row and changing amount of Qty Sold going down the column. The top left cell of the data points to the Profit cell that contains the formula (Sales – Cost). Inside the data table options, I entered the Qty Sold value cell for the Column Input Cell and the Price value cell for the row input cell. This returned the below value. I highlighted some of the incorrect values

A couple of odd things, at $45.00 selling two or more items somehow becomes negative. This should have returned all positive values for 2+ items at $45. At $40.00 it returns the correct profit for selling 1 and 2 items, however, selling 3 items it shows the profit for selling 4 items.

Does anyone have any suggestions on figuring out this issue?

Thank You all for the help.
DM

 

[offthelip]

Where is the cost of the item held?? what equations have you got in the second column?? If you use XL2BB to load your worksheet to the BB I am sure somebody can help you.

 

[DMfba]

HI Offthelip,

Thank you for the reply. The cost of the item is stored in cell C7, which is the Cost cell described above. I was able to add XL2bb to excel. I am going to try upload the mini-sheet. Hopefully this will work

Sale Price Test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
5					Current	New
6	Price	$45.00		Sales	$45.00	$39.00
7	Item Cost	$35.64		Cost	$35.64	$35.64
8	New Price	$39.00		Profit	$9.36	$3.36
9	Qty Sold	1
10	GM	20.8%
11
12
13	Change in Qty	5
14	Change in Price	$1.00
15							How many more units do I have to sell to make the same profit with a lower the price
16
17	Change in Gross Margin							Price Changes
18		20.8%				$9.36	$45.00	$39.00	$38.00	$37.00	$36.00	$35.00	$34.00	$33.00	$32.00
19	$45.00	20.8%			Units Sold	1	$9.36	$3.36	$2.36	$1.36	$0.36	-$0.64	-$1.64	-$2.64	-$3.64
20	$39.00	8.6%				2	-$7.28	$6.72	$4.72	$2.72	$0.72	-$1.28	-$3.28	-$5.28	-$7.28
21	$38.00	6.2%				3	-$14.56	$13.44	$9.44	$5.44	$1.44	-$2.56	-$6.56	-$10.56	-$14.56
22	$37.00	3.7%				4	-$25.48	$23.52	$16.52	$9.52	$2.52	-$4.48	-$11.48	-$18.48	-$25.48
23	$36.00	1.0%				5	-$40.04	$36.96	$25.96	$14.96	$3.96	-$7.04	-$18.04	-$29.04	-$40.04
24	$35.00	-1.8%				10	-$72.80	$67.20	$47.20	$27.20	$7.20	-$12.80	-$32.80	-$52.80	-$72.80
25	$34.00	-4.8%				15	-$123.76	$114.24	$80.24	$46.24	$12.24	-$21.76	-$55.76	-$89.76	-$123.76
26	$33.00	-8.0%				20	-$192.92	$178.08	$125.08	$72.08	$19.08	-$33.92	-$86.92	-$139.92	-$192.92
27	$32.00	-11.4%				25	-$280.28	$258.72	$181.72	$104.72	$27.72	-$49.28	-$126.28	-$203.28	-$280.28
28	$31.00	-15.0%				30	-$385.84	$356.16	$250.16	$144.16	$38.16	-$67.84	-$173.84	-$279.84	-$385.84
29	$30.00	-18.8%				35	-$509.60	$470.40	$330.40	$190.40	$50.40	-$89.60	-$229.60	-$369.60	-$509.60
30	$29.00	-22.9%				40	-$651.56	$601.44	$422.44	$243.44	$64.44	-$114.56	-$293.56	-$472.56	-$651.56
31	$28.00	-27.3%				45	-$811.72	$749.28	$526.28	$303.28	$80.28	-$142.72	-$365.72	-$588.72	-$811.72
32	$27.00	-32.0%				50	-$990.08	$913.92	$641.92	$369.92	$97.92	-$174.08	-$446.08	-$718.08	-$990.08
33	$26.00	-37.1%				55	-$1,186.64	$1,095.36	$769.36	$443.36	$117.36	-$208.64	-$534.64	-$860.64	-$1,186.64
Sheet1

Cell Formulas
Range	Formula
F6	F6	=C6*$C$9
G6	G6	=C8*C9
F7	F7	=C7*C9
G7	G7	=C7*C9
F8:G8	F8	=F6-F7
C10	C10	=1-(C7/C6)
C18	C18	=1-(C7/C6)
G18	G18	=F8
H18	H18	=C6
I18	I18	=C8
J18:P18	J18	=I18-$C$14
G19	G19	=C9
H19:P33	H19	=TABLE(C6,C9)
G20:G23	G20	=G19+1
G24:G33	G24	=G23+$C$13
B19	B19	=C6
C19:C33	C19	=TABLE(,C6)
B20	B20	=C8
B21:B33	B21	=B20-$C$14
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[shift-del]

Hi

The cells G19, H18 and I18 must not reference to your model but must contain fixed values.

 

[DMfba]

Hi Shift-del,

Thank you for the help. That change did the trick. I thought I could make it dynamic and reference the starting price, so the user would not have too change that cell. If it must be a fixed value then there is no way around making the end user change that cell.

Thank You,
DM

 

[shift-del]

You can reference to other cells but not to cells form your model which contribute to your table formula.

 

[DMfba]

Perfect thank you. Would making a helper cell do the trick.

Since I can't directly reference the cells used in the model. Create a VLookup on a hidden tab that finds the price, then have the cell in the table reference the hidden tab that contains the vlookup. I'm not sure if excel would still see that as referencing the cells used in the model.

 

[shift-del]

Just try it.

 

[DMfba]

It does not work. Which I kinda expected as it just evaluates the lookup which references the model. I am going to try making the model point to something a different cell. Testing if I can find a way have the use enter 1 price but the model not reference that exact cell the user inputs.

 

[shift-del]

Use "Trace Precedents" (in the formula tab) from cell G18 backwards to find the cells you cannot use as a reference either in the rows or the columns.