I want to use Solver to solve the following problem, but I can’t work out how and its driving me crazy.
I have to organise a prize giving dinner so I know I need to work out my expenses and make sure I cover those.
I have to break even with 200 ticket sales, but I can sell a maximum of 300 tickets.
Non members pay full ticket price and member pay 75% of the ticket price.
Can someone explain to me how I can set the ticket price using solver?
Thank you
LJK
Re: Solver - To Set Ticket Prices
If total expenses are in A1, =A1/200/.75. This will get you the lowest full price of a ticket you can have and still break even, even if you sell only 200 tickets and all are to members.
Re: Solver - To Set Ticket Prices
I assume your tickets are set and the number of member tickets and non member tickets will not change? I ask this because your break even would probably change if only non members came and purchased 200 tickets.
Regards,
-Dude 8-)
Re: Solver - To Set Ticket Prices
Yes - the ticket price will be set as I can make a profit if I sell more than 200 tickets. I need to use solver (or goal seek or something) as I can't use forumlae.
Thank you both.
LJK
Re: Solver - To Set Ticket Prices
I'm a little confused why you feel you can't use formulas. If you know the number of member and non-member tickets, then the formula is =expenses/(<lower of 200 or # of member tickets>*.75+200-that number of tickets). This will get you the price of a full ticket. 75% of this number would, of course, be the cost of a member ticket.
For example, if expenses of $5000 are entered in A1, and 150 member tickets are available (with this number in B1), then =A1/(B1*.75+200-B1) gets you $30.77 per non-member ticket. 30.77*.75*150+30.77*50=$5000.
Re: Solver - To Set Ticket Prices
Hello Darkyam
It's a class project and I've been told I can't -have to use "advanced" stuff for it.
Thank you for your help so far though.
LJK
Don’t have an account yet? Register yourself now and be a part of our community!
