Use the Solver Add-in for Excel to Produce a Specific Formula Result

This is Part 2 of my series of videos demonstrating how to use the SOLVER Add-in for Excel. Remember that SOLVER is not activated by default. I show you how to activate it in Part 1 of this series.

Here, I want SOLVER to automatically change the Units Sold so that the resulting Gross Profit Sales are $25,000.

This is similar to the way that I used SOLVER in Part 1. What is different? I, first, used the SUMPRODUCT Function to determine what would be a "reasonable target" for SOLVER to produce. Even though SOLVER is a very powerful tool to use to produce Business Intelligence Reports, you must use your own intelligence - and understanding of your business - to make the proper decisions.

I invite you to visit my online shopping website - http://shop.thecompanyrocks.com - to view all of my videos and to take advantage of the many resources that I offer you.

Danny Rocks
The Company Rocks

---

Closed Caption:

hello hand welcome back to took some
time savers on Danny rocks
this is part true my series right
demonstrate the power
that the Excel solver adentro or can
give you
to provide data and analysis here in
part 1
the series I use solver to produce a
specific results I wanted solver
true tell me how many units I have to
sell
in order to achieve a specific result 56
percent
gross profit return so I set my goal
for the cell is that I wanted the result
over here to be a fifty
6 percent gross profit I wanted to a
while
solver to change specific cells in other
words tell me
how many copies a each you need to sell
in order to achieve a specific result
and I could
to constraints I didn't want to be
selling
phantom inventory so the units left
after I sold the they'd be units on this
promotion
how to be a positive number it could be
it could not be a negative number could
not be less than zero
and I also wanted to make sure that the
by changing sells for solver
were also positive numbers art so
I recommend that when you were with
solver that should work with a copy of
the original so
here with the worksheet test one is
where I had solver
produce these results on the Data tab on
the ribbon on going to bring up the
solver
properties are Parameters dialog box o
my target cell
my goal was to have this cell over here
am 15 produced a specific result I'm
want the result to be 56 percent which
is what you see
over there the variable cells that I
what it's all over to work on work to
tell me how many units the cell so this
range over here
i5 through I 14
the constraints as I mentioned I did not
want to be selling phantom
inventory so I wanted to make sure that
the
items that I sold I we're I was selling
a positive number I also wanted to make
sure that
subtracting the quantity
the units sold from the quantity on hand
was also a positive number
now inside look at the results nice for
the scratch my chin and say well
why could solve per se cell no copies
this and I have a hundred and
twenty-five copies at this
if you look over here on my inventory I
have more
units on hand for product eight then I
have for any other unit
well remember my goal was to maximize
the gross profit per cent and over here
I have a
really low gross profit per cent or on
the other hand
it carries a high selling price so
while it cost me a lot of money to
purchase it I also achieve
good amount of gross profit dollars my
cellar
so now we want to create different
scenario and in this case
the target cell is I wanna optimize Mike
gross profit dollars
rather than gross profit per cent so
once again I want to make sure that I'm
working on a copy
love my original data so up here in the
header a minute
change this I want to use all over to
optimize
the gross
profit dollars
soul
fight and again I wanna come down here
and say
optimize sales to achieve
a specific GP
dollar result
part the cells from go to What song for
change remain in place
I want solver to change these variable
cells which will feed in
to my ending result over here I'm gonna
leave the constraints in place the
quantity available
after I sell the product cannot be a
negative number
I also want to make sure that the units
and I'm selling are also
a positive number now before I do that I
have to say well
what's a realistic number twitchy for
gross profit dollars so
this now is going to become might target
cell
and just a high like this throughout I'm
just going to apply some shading here so
this will become
might target cell but what I want to do
is like when I come over here and to my
current
inventory and I wanna see well what is
the potential
gonna put over here potential
for one my cell every copy every unit on
hand
at the selling price how many dollars's
to I have a potential to sell I want to
make sure that if I sell
every copy what is wiped all
of pasta good sold by unicorn so that I
can
tell what my total gross profit is a mic
total gross profit percentage so
I'm going to use the sama product
function
so equal some product
now do understand some product some
performs addition product performs
multiplication
so some product in one cell
will add up the result
multiplying the quantity on hand by the
selling price
so what use control a to bring up the
function arguments I like parts were
talking about rate
so it works on mobile cell so my first
to rate is going to be my quantity on
hand my current inventory now I'm gonna
be copying this formula over
so this array I wanna make absolute so
if I use the
F for the function for keyboard shortcut
in applies dollar signs to freeze the
columns
dollar signs two free throws for this
range
my second array that I would wanna
addition
and multiply some product to will be my
selling price
so now click OK and let's apply some
formatting to this let's use the
mini toolbar over here and apply
currency
with 0 decimal places so if I sell
every copy that I have on hand I have
the potential to sell
a hundred and twenty-six thousand one
hundred and eighty dollars
now remember that I applied absolute
references to the quantity on hand
so when I copy some product over here
might cost a good soul will be sixty
eight thousand dollars when I copy this
over with autofill
my potential gross profit is 58,000
thirty-eight dollars so I don't want to
copy some product over here
in this case I would wanna copied down
before we look for Bros profit
percentage
which is the say take your gross profit
dollars
and divide that by your revenue so I'm
gonna copy
this stamp so I would have a 46 percent
gross profit percentage
if I sold every unit that I have on hand
for
the selling price all right now that I
understand that
I realize I'm not going to sell every
copy so rather than having
gross profit sales love 17,000 I'm gonna
set a
target in there work twenty five
thousand dollars so I'm gonna put in
here 25
000
as my goal now I still I'm going to be
using
this cell as my target self
art so open up the solver by going on to
the Data tab on the ribbon
again remember solver is an added
program if you don't see it
look at them part 1 of the series so
my car console over here I wanna set to
a specific
value of $25000
an by changing cells will be I wants all
for to tell me how many units the cell
in this rain just settles the units the
cell
the constraints that I'm going to use I
wanna add and my first constraint
I want to make sure that the units that
solver tells me to settle
will be a positive number so I wanna say
greater than or equal to
0 I wanna add him a second constraint
I want to make sure that the inventory
that I have left after Excel for
yourself how many copies per cell
is also a positive number greater than
or equal to
0 and now click OK so I'm ready to have.
solver
produce the results for me quick solve
and their
I have my result now I wanna put in and
answer
report over here so my result is 25,000
dollars which is what I said I wanted to
set as my target
notice over here that this product
product a text telling me to sell
13 copies are still have a considerable
about
attendant or soak this again is going to
guide you toward your
decisions that you make as a business
person so
I'm going to keep this as a result and
produce
a report so click OK and now
this is what Excel automatically change
with some hope for to give me a specific
result I wanted to achieve
gross profit dollar sales a twenty five
thousand dollars
by changing the cells subjected to
conditions at the unit should I sell
must be a positive number the units that
I have left in inventory
must also be greater than or equal to 0
I also establish my goal twenty-five
thousand dollars by using the some
product function so the some product
function is a great way
it with one function to create
a result where you some more add
and multiplied within a race so do your
brain but I wanted
use was the quantity on hand the cells
and I wanted to match the number of
cells
in the second break the selling price I
wanted to match
the units on hand again remember but I
made that an absolute some preference
by the unit cost I wanted to use
sumproduct
to multiply and had
the fixed rangehood the quantity on hand
my inventory
by by gross profit per unit I did not
use sumproduct over here instead
I copied out this for bill to achieve
gross profit percentage
it's taking gross profit dollars to buy
did
by revenue so there's an example
love how you can use the analysis tools
will be an Excel solver is an added
it's a fantastic program it requires
that number one you right out
goal and the goal has to be a single
cell which contains a formula
which sells the by changing cells he
wants all over to change
automatically for you and how many
constraints if you want to establish in
this is typical of the trips on offer in
my syriza dvd-rom
the 50 best tips for Excel 2007
and I will look for you im an explosion