此课程是为影响转变数据成为更好的决定的想法而设计。最近在数据采集技术上的显著提升改变了公司进行有效决定的方式。

Loading...

来自 University of Pennsylvania 的课程

运营分析

个评分

此课程是为影响转变数据成为更好的决定的想法而设计。最近在数据采集技术上的显著提升改变了公司进行有效决定的方式。

从本节课中

Prescriptive Analytics, Low Uncertainty

In this module, you'll learn how to identify the best decisions in settings with low uncertainty by building optimization models and applying them to specific business challenges. During the week, you’ll use algebraic formulations to concisely express optimization problems, look at how algebraic models should be converted into a spreadsheet format, and learn how to use spreadsheet Solvers as tools for identifying the best course of action.

- Senthil VeeraraghavanAssociate Professor of Operations, Information and Decisions

The Wharton School - Sergei SavinAssociate Professor of Operations, Information and Decisions

The Wharton School - Noah GansAnheuser-Busch Professor of Management Science, Professor of Operations, Information and Decisions

The Wharton School

In our main video for session two, we have looked at how to set up and solve and

optimization problem in Excel 2013 on the Windows platform.

Please make sure that you watch that main video before looking at this one.

In this video, we will go over the differences in the way the Excel and

the solver is set up on Mac versus Windows.

Here's a spreadsheet for the Zooter problem loaded on to Excel for Mac 2011.

On the spreadsheet, we have entered a trial solution 500 and 500.

And all the formulas for the objective function and for

the used resource amounts.

First, let's find where the Solver is.

Solver is an Excel add-in, and in order to locate it, you must go to Tools,

Add-Ins, and make sure that the Solver is checked here.

After that the Solver will either appear on the tools right here.

Or you can find Solver directly under Data tab.

There will be a button Solver right there.

Before we look at the Solver,

let's go over a couple differences in the shortcuts used on Windows versus Mac.

Let's first see how we can display and edit a formula in a particular cell.

On the Windows platform, we have used the shortcut F2 for this purpose.

On the Mac, we use the shortcut Ctrl + U instead.

For example, if I go to the cell F10, where the profit formula is located.

I can display and edit this formula by pressing Ctrl + U.

Okay.

The second difference we want to point out is the way you introduce absolute cell

references.

On the Windows platform the shortcut for this was F4, on Mac it is Cmd + T.

For example, if I go into the cell E14 and display the formula using Ctrl + U.

I can highlight the decision variable cells, C10 and D10.

And then I can change the addresses of the cells between absolute and

relative by repeatedly pressing Cmd + T.

Okay, let's call the Solver.

I will do it from the Data tab.

As you can see, the Solver dialogue box looks pretty much the same as

the Solver dialogue box for Windows.

We specify constraints and

objective function and decision variables in the same way.

We click Make Unconstrained Variables Non-Negative,

we select the same solving method.

We click on Options to make sure that, for

example, the Ignore Integer Constraints is unchecked.

And now we're actually ready to click the Solve button.

Let's hope it starts working.

And then displays the optimal solution right there on the spreadsheet.