Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

solver

ResolvedVersion 2010

Peter has attended:
Excel Advanced course
Excel Advanced - For Power Users course

Solver

I'm trying to use solver to work out how much up front sales to increase profitibility.

Income Up Front Sales £250,000
Rev Share £500,000
Total Income £750,000

Expenses Suppliers £100,000
Rent £65,000
Wages £250,000
Marketing £50,000
Total Expenses £465,000

Profit £285,000

If I select the cells to change (Up front sales, rent & marketing) then set constriants as >=£250000, <=£65000 & <=£50000 respectively when I click solve all figures change to either -£2,684,354,810,000 or £2,684,354,810,000

I am clearly doing something wrong here.

Edited on Wed 10 Jul 2013, 12:50

RE: Solver

Hi Peter

Thanks for getting in touch. I've replicated your Solver as described and attached it. You didn't specify the Objective so I assumed Profit was set to Max. I get similar results.

Are there any other formulas within the cells? Otherwise Solver is cold and logical about it, and simply drives up Sales as high as it can go until Excel hits its limit of numbers.

The cells will need to be more dependent on each other to generate a realistic answer, e.g. as Sales increase Suppliers Expenses increase by 5% per £100, or a calculation of that type.

Feel free to amend with more formulas and mail back to me at gary@stl-training.co.uk if you wish.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

GF Profitability Solver.xlsx

Wed 17 Jul 2013: Automatically marked as resolved.

Excel tip:

Using the Quick Access Toolbar in Excel2010

The Quick Access Toolbar is included in virtually every Office product, including Outlook 2010, Word 2010, Excel 2010, and PowerPoint 2010.

You will find the Quick Access Toolbar in the top-left side of the window. To begin, click the Customize button (it's the little black arrow at the end of the toolbar).

Simply click the commands you want to include.

Virtually any command can be added to the Quick Access Toolbar. Click the More Commands option and a new window will open from where you can browse the commands including those not on the ribbon.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.