Today I’d like to share with you a video tutorial by Isaac Gottlieb on supply-chain decision making from his book Next Generation Excel.

Isaac teaches over 2,000 MBA students a year at Columbia, NYU and Temple U and over 3,000 undergrads on-line.

See how he uses the Solver to find out the ideal location for a manufacturing plant. He then takes the output and plots it on a scatter chart, adds some scroll bars and a sprinkling of magic dust and makes it do this (watch the red marker on the map):

Excel Solver

Here is the video:

Now I know it’s not every day you need to calculate the ideal location of a manufacturing plant, but take a few minutes to think about how you could use one or more of the tips in this video and apply it to your work.

Download the Excel workbook. Note: this is a .xlsx file, please ensure your browser doesn’t change the file extension on download.

If you’d like to learn how to incorporate interactive features like this in your Excel reports and charts check out my Excel Dashboard course.

Where’s the Solver?

The Solver is an add-in. If you don’t already have it on the Data tab of your ribbon you can add it by going to Excel Options (Office button or File tab) > Add-ins:

Excel Solver Add-in

Then you should have this:

Excel Solver Add-in

Where is the Scroll Bar?

On the Developer tab of the ribbbon.

Excel Solver Add-in

If you don’t have the Developer tab available you can add it:

File Tab (2010/13), or Windows button (2007) > Options > Customize Ribbon; check the Developer box under Main Tabs in the right selection pane.

Thanks

Thank you Isaac for allowing me to share this tutorial.

If you liked this please share it with your friends and colleagues on LinkedIn, Google +1, Facebook or Twitter, or leave a comment below.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 13 comments… read them below or add one }

Darryl July 3, 2013 at 8:49 pm

Great Help from you many thanks to you. Also our co in kSA is going to start E-commerce courses on MS office..can you assit me on anything like this if possible…best regd

Reply

Mynda Treacy July 3, 2013 at 9:04 pm

Cheers, Darryl. Glad you liked Isaac’s video.

Please contact me at website @ myonlinetraininghub.com about the E-commerce courses as I’m not sure what you mean.

Kind regards,

Mynda.

Reply

Yaa July 3, 2013 at 7:50 pm

Hi, do you know how to use the Index function in Google spreadsheets? I used it on an Excel spreadsheet that I needed to share with others, bu when I upload to Google Drive, the index function doesn’t work. This is the function I have: =INDEX($G$10:$CQ$10,1), =INDEX($G$10:$CQ$10,9), and so on. Increasing the digit one by increments of 8. Don’t know if that makes sense :/

Reply

Mynda Treacy July 3, 2013 at 8:01 pm

Hi Yaa,

Your formula is missing the column number argument. It should be like this:

=INDEX($G$10:$CQ$10,,1)

See the second comma? This is a placeholder for the row argument. You don’t need a row argument in this case because your reference is only one row high so Excel knows you must be looking in that row, but you still need to put a comma in so that it knows the next argument is for the column.

Kind regards,

Mynda.

Reply

chanchal July 3, 2013 at 2:37 pm

Hi Isaac
Great application tutorial.
Regards

Reply

Michael Rempel July 3, 2013 at 4:06 am

I am just amazed at what people are doing with Excel! Thanks for sharing this video!

Reply

Mynda Treacy July 3, 2013 at 8:01 am

Yes, it’s quite inspirational :)

Glad you liked it.

Reply

Brenda July 3, 2013 at 3:25 am

Wow, incredible tutorial! I’ve never used solver before and this was a nice intro to using it. I didn’t understand why the weights were different; one would think they would be the same.

Reply

Mynda Treacy July 3, 2013 at 8:05 am

Hi Brenda,

On behalf of Isaac, thank you.

The weights were all different as this is the amount each location requires from the plant. The materials produced by the plant aren’t evenly distributed.

Kind regards,

Mynda.

Reply

Nkhoma Kenneth July 2, 2013 at 11:49 pm

Hi Isaac,
It is a great piece of genius thinking and a master-piece techinque and Solution.
such people like you provide inspiration and motivation for others to also delve more into reseach and thinking to try and get close to your thinking.
Iam delighted to see what you have done as i still try to grasp the whole idea.

Cheers,
Nkhoma

Reply

Mynda Treacy July 3, 2013 at 8:01 am

On behalf of Isaac, thanks.

Reply

abdul rahman July 2, 2013 at 10:51 pm

It’s really awesome tutorial

Reply

Mynda Treacy July 2, 2013 at 10:53 pm

On behalf of Isaac, thanks :)

Reply

Previous post:

Next post: