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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
Click solve, and a box should pop up saying a solution was found. Click OK, the solution should be:
In other words, he needs to sell £4,164.44 in merchandise in January to meet his 2.25% Profit margin for the quarter.
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:
Click solve, and a box should pop up saying a solution was found. Click OK, the solution should be:
In other words, he needs to sell £4,164.44 in merchandise in January to meet his 2.25% Profit margin for the quarter.
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
ASKER
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
>>>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
ASKER
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
Sid
ASKER
Oops, I didn't take care of marketing expenses.
Let me retry it.
Sid
Let me retry it.
Sid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
PS - you've got your Expected Profit set to 2.5% instead of the 2.25% you mentioned in your original question - just FYI
ASKER
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
Yes your solution matches mine. I am accepting your solution. Is it ok, if I share points between you and gtgloner?
Sid
absolutely. they're your points to allocate as you wish :)
ASKER
Thanks Guys. Learnt something new today :)
Sid
Sid
ASKER
Sid
Updated.xls