Simple Supply Demand Formula

[Andretti]

I've been racking my brain trying to figure out a way to show a simple supply and demand. ALL I want is just 3 cells.

A1 Could be Supply
B1 would be the formula showing a price
C1 Could be Demand


Here is an example. I have 1,000,000 units. Lets say of food. That would be my A1 Supply
I have 100 People. That would be my demand, C1.
Now, for the price, I would like the static state, with 1,000,000 Supply and 100 people to be a price of 10 which would be between those two cells.

I would like so that if I increase the people(C1) to say 300 but kept the Supply(A1) the same, for the price in the middle to show an increase as expected since the supply stayed the same.

In the same respect if I increased the supply to 10,000,000 and kept the People(demand) at 100, I would want that middle price to decrease since there is more supply than normal. My goal is to query a database to find total supply currently and total people and show the price of the formula. Both Supply and Demand would fluctuate throughout the day. However, if by some odd chance they returned to the static state of 1,000,000 and 100 I'd like it to show 10 again. But any other combination show a correct price.

Thanks so much in advance. I have yet to figure this out.

[dilipandey]

Hi Andretti,

Welcome to the forum.

I guess..it's the cell B1 where you need the formula.
Also when the demand is from 100 people, you need price in b1 to be 10 and when demand increases to 300 people, you need price in b1 to increase?

So if the demand is 100 -> price is 10
when demand is 300 -> price would be =(10/100*300) = 30

Supply of 1000000 is same.


Regards,
DILIPandey


<click on below 'star' if this helps>

[Andretti]

Hi, and thank you. What I really need is the formula to read what is put in to both A1 and C1 and compute a price with the relationship that B1 is equal to 10 when A is 1,000,000 and C is 100. In other words, I just want to be able to change A1 and C1 to whatever numbers I choose and have a price based on new numbers show up in B1. I don't want to have to change the formula at all if A1 or C1 is changed. There may be times where A1 drops to 800,000 and C1 increases to 432. Or they both may drop to 248,000 and 60. I want the formula to give me a price based on demand. It needs to figure out new prices based on current values with the default being 10 as a price.

I was able to get price to go the opposite way with this in B1 =(A1/1000)/C1 which also shows 10 as my base price when default values are entered.
For instance, if I increase C1 to 110 the result in B1 is 9.090909 but it would not make sense in a real world situation. As if demand(people) increase and the supply stays the same, the price should increase. But the formula I put above lowers that price. I want it to increase with the example I just listed. A1 and C1 would change independently at different times like a market.

If I'm wrong on the economics part here I ask everyone to ignore it. lol I just need a formula that would work properly.

[dilipandey]

compute a price with the relationship that B1 is equal to 10 when A is 1,000,000 and C is 100. In other words, I just want to be able to change A1 and C1 to whatever numbers I choose and have a price based on new numbers show up in B1.

I believe you need to have a table from where a formula will pick up value of B as per A and C....
This will show the criteria which shows that what should be B when A is x and C is y... what should be the price when demand increases/ decreases when the supply remains the same


Regards,
DILIPandey

<click on below 'star' if this helps>

[Andretti]

I understand what you are saying. However, I'm not sure I could create a table with that many possibilities. I mean I would have to 1,000,000 with 100 different prices, then 999,999 with 100 different prices and so on? or do I do 1,000,000 for both and set the price at 100 at 10? also the combinations could be different, it won't always both raise. I think I'm further confusing myself. lol

[oeldere]

@andretti

Please post a few lines of data (without confidential information).

Please also post the expected result.

Then I can look for the formula.

[Andretti]

I can make up arbitrary numbers and guess what the price would be that makes sense. but I'm completely guessing at the price with the variables. But for a further example I've included the table below. I just want if you have more supply than demand the price should go lower. If you have more demand than supply the price should raise. But I'd like a base to set at where supply(food) is 1,000,000 and demand(people) is 100.

Supply	Price	People
1,000,000	10	100
1,000,000	10.1	101
999,999	10.1	100
100	10,000	100,000
1,000,000	1.0	10
1,000,000	0.5	5.0
1,500,000	18.56	6789
850,000	8.3	639
750,000	9.4	639

[oeldere]

I prefered a exel-file (without confidential information).

But another question; I don't see:

1) whitch value is demand an witch value is supply.

2) i don't see the expected answer in your table
.

[Andretti]

Supply is labeled supply at the top, and demand is the amount of people. Let's make the supply food and people are the demand for food. Each person(demand) consumes the food(supply). Not sure how to get you an excel file, and I think I've given everything I can on it.

Edit: forgot, expected answer is when Supply is 1,000,000 and People(demand) is 100 the price should be 10.