Using solver to determine the selling price and quantity to sell in order to achieve a target amount

[alvinro]

Hello guys,

Am trying to solve a task I was issued by a client yesterday. Am basically trying to solve for what is the best selling price for each unit and how much quantity can one sell in order to reach the target amount that was set. Am not coming from an economics background but an amateur learning through the community. I've checked you tube for the best possible example to help me solve it but no juice. Your assistance will be highly appreciated.

regards.

SolverPrice..xlsx
	A	B	C	D	E	F	G	H
1	Product Name*	Cost Price	Selling Price	Quantity
2	Spinach Bunch	30		1			Target Amount	62500
3	Sukuma Wiki	25		1
4	Green Hoho	80		1
5	Tomatoes	60		1
6	Onions	65		1
7	Cabbage	60		1
8	Carrots	60		1
9	Potatoes Sack 50 Kg	2500		1
10	Fresh Beef Bones	400		1
11	Boneless Fresh Beef	450		1
12	Eggs Tray	305		1
13	Smokies	370		1
14	Green Peas (Shelled)	200		1
15	Green Maize	25		1
16	Green Chillies	90		1
17	Local Green Lemons	110		1
18	Leek Onions	90		1
19	Beef Sausages	570		1
20	Beef Ossubucco	380		1
21	Beef Mince	460		1
Fresh Products

Cell Formulas
Range	Formula
H2	H2	=5/100*1250000

 

[StephenCrump]

Welcome to the Forum!

What exactly is the question that has been asked?

Without constraints, there are infinitely many "solutions", e.g. you could price spinach at $62,500 a bunch, sell one bunch and nothing else. Obviously that's not realistic, so what boundaries does the problem have?

(That's assuming the $62,500 is total gross sales? It looks more like a profit margin, but it's not clear what the hard-coded 1.25 million in H2 represents).

 

[alvinro]

Welcome to the Forum!

What exactly is the question that has been asked?

Without constraints, there are infinitely many "solutions", e.g. you could price spinach at $62,500 a bunch, sell one bunch and nothing else. Obviously that's not realistic, so what boundaries does the problem have?

(That's assuming the $62,500 is total gross sales? It looks more like a profit margin, but it's not clear what the hard-coded 1.25 million in H2 represents).

@StephenCrump
Hey thanks for the prompt reply.. the question is to solve for the selling price and quantity... from my understanding is that I should determine what selling prices are best for teach of the product to reach that target income. Yes it's kind of vague but that's what I was thinking the 1.25 is the gross sales then we get 5% from the gross. So the constraints are only two:
1. quantity > o
2. selling price > cost price

SolverPrice..xlsx
	A	B	C	D	E	F	G
1	Please solve for selling price and quantity to achieve the target amount
2	Constraints
3	1. Selling Price > Cost Price
4	2. Quantity > 0
Instructions

 

[StephenCrump]

Thanks, but this is still too vague.

What was the original question, i.e. as it was posed to you?

I'm guessing this contained more explanation than you're providing here, e.g. what the 1.25 million and 5% numbers represent?

 

[alvinro]

Thanks, but this is still too vague.

What was the original question, i.e. as it was posed to you?

I'm guessing this contained more explanation than you're providing here, e.g. what the 1.25 million and 5% numbers represent?

Hey, imagine that's the question Stephen... "Please solve for the... target amount"... I made a mistake not to inquire more than what I was given, I thought if I asked too much I will look like like am not ready for the task. I just received the 2sheets and I thought it would be easy or just do a simple search and am good to go. How can you go about it in your own way?

 

[StephenCrump]

I made a mistake not to inquire more than what I was given ....

Sorry, without more details, we can't really help.

 

[alvinro]

Cool, I appreciate the time.