Using Solver in Excel

I have never used Solver in Excel before :(

Here is the scenario for a company say "Company1". Sample File Attached.

1) I want to increase the profit margin for the quarter as a whole to 2.25%.

Say I plan to do this by reducing expenses and increasing sales, as follows:-

Reducing expenses
a)      Reducing payroll expenses in both February and March to £7350
b)      Reducing marketing expenses in March to £750

Increasing sales
a)      Increasing January Merchandise sales by means of a post-Christmas Sale.

The owner of "Company1" believes he will have to spend an additional £250 on marketing in January in order to promote the Sale. He needs to know the answer to the following question:-

How much Merchandise does he need to sell in January in order to achieve a quarterly profit margin of 2.25%? (In other words, what sales are needed in January to achieve his target, given the above constraints?).

I need to create an Report comparing the original and final values for the Target and Adjustable.

How should I go about it? I am using Excel 2003.

Sid
Sample.xls
LVL 30
SiddharthRoutAsked:
Who is Participating?
 
m4trixConnect With a Mentor Commented:
Sid: You can do that, but you need to include those cells in the "By Changing Variable Cells" field... Basically, your constraints are not affecting the cell you've give solver permission to change. Consider this change to your sheet. I believe it solves your problem: Forecast.xls
0
 
SiddharthRoutAuthor Commented:
Ok I have done this. Can someone tell me if it is correct?

Sid
Updated.xls
0
 
gtglonerConnect With a Mentor Commented:
I put in your expense reduction, used Goal Seek and came up with this:
Sample-1-.xls
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
m4trixCommented:
I'm using Excel 2010, but the method is the same.

First, change the fields you know you need to change - so D11 and E11 become 7350 each, E14 becomes 750 and C14 increases to 1250

Next, open the solver dialog (Tools -> Solver). If it is not there, then you need to add in the plugin (Tools -> Add-Ins, and select Solver), then it should appear in the menu. Once there, set it up like so:
 Solver setup
Click solve, and a box should pop up saying a solution was found. Click OK, the solution should be:
 Solver Result
In other words, he needs to sell £4,164.44 in merchandise in January to meet his 2.25% Profit margin for the quarter.
0
 
m4trixCommented:
gtgloner is correct, for this problem you could use goal seek as well. His answer is different than mine because he didn't increase the marketing expense in January by £250
0
 
SiddharthRoutAuthor Commented:
gtgloner: Thanks but the marketing value is still 1000. Shouldn't it be 1250 since

>>>The owner of "Company1" believes he will have to spend an additional £250 on marketing in January in order to promote the Sale.

Also I need to use Tools~~>Solver. If you click on the cell F20 and then click on Tools~~>Solver, you will see I made some progress. However, I am not sure If I am correct. Could you please have a look?

Sid
Forecast.xls
0
 
SiddharthRoutAuthor Commented:
m4trix: Thanks for confirming my doubts, however do i need to physically change the values in the payrol row? I thought I could specify that in the solver condition?

Sid
0
 
SiddharthRoutAuthor Commented:
Oops, I didn't take care of marketing expenses.

Let me retry it.

Sid
0
 
m4trixCommented:
Of course, as soon as Solver finds a solution and you accept it, then all those constraint cells permanently change the main table... so you end up with them changed at the end anyway.

PS - you've got your Expected Profit set to 2.5% instead of the 2.25% you mentioned in your original question - just FYI
0
 
SiddharthRoutAuthor Commented:
m4trix: Yeah I realized it and was about to post the solution when you did it :)

Yes your solution matches mine. I am accepting your solution. Is it ok, if I share points between you and gtgloner?

Sid
0
 
m4trixCommented:
absolutely. they're your points to allocate as you wish :)
0
 
SiddharthRoutAuthor Commented:
Thanks Guys. Learnt something new today :)

Sid
0
All Courses

From novice to tech pro — start learning today.