Microsoft Office Tutorials and References

In Depth Information

want to use what we have learned in earlier chapters to help Fr. Eﬁa understand the

congruency of his decisions and the goals he has for
Vegas Night at OLPS
. In other

words, the model should be user friendly and useful for those decisions relating to

his eventual implementation of
Vegas Night at OLPS.

Let’s examine Exhibit 7.6 and determine what functions will be used in the

model. Aside from the standard algebraic mathematical functions, there appears

to be little need for highly complex functions. But, there are numerous opportuni-

ties in the analysis to use functions that we have not used or discussed before, for

example control buttons that can be added to the
Quick Access Toolbar
menu via

the Excel Options Customize tool menu—
Scroll Bars
,
Spinners
,
Combo Boxes
,

Option Buttons
, etc. We will see later that these buttons are a very convenient way

to provide users with control of spreadsheet parameter values such as attendee entry

fee and the value of a bet. Thus, they will be useful in sensitivity analysis.

So how will we begin to construct our workbook? The process steps shown in

Exhibit 7.6 represent a convenient layout for our spreadsheet model. It also makes

good sense that spreadsheets should ﬂow either left-to-right or top-to-bottom in a

manner consistent with process steps. I propose that left-to-right is a useful orien-

tation and that we should follow all of our Feng Shui inspired best practices for

workbook construction. The uncertain weather conditions will be dealt with deter-

ministically, so the model will provide Fr. Eﬁa outcomes for the event
given
one

of the three weather conditions: rainy, cloudy, or sunshine. In other words, the

model will not generate a weather event; a weather event will be
assumed
and

the results of that event can then be analyzed. The uncertainty associated with

the games also will be handled deterministically through the use of
expected val-

ues
. We will assume that precisely 20% of the attendees playing OTSD will win,

35% of the attendees playing WOD will win, and 55% of those playing BT will

win. Note that in reality these winning percentages will rarely be exactly, 20, 35,

and 55%, but if there are many attendees, the percentages should be close to these

values.

Exhibit 7.7 shows the layout of the model. For the sake of simplicity, I have

placed all analytical elements—brain, calculations, and sensitivity analysis on a sin-

gle worksheet. If the problem were larger and more complex, it probably would be

necessary to place each major part of the model on a separate worksheet. We will

discuss aspects of the spreadsheet model in the following order: (1) the basic model

and its calculations, (2) the sensitivity analysis that can be performed on the model,

and (3) the controls that have been used in the spreadsheet model (scroll bars and

options buttons) for user ease of control.

7.5.1 Basic Model

Let us begin by examining the general layout of Exhibit 7.7. The
Brain
is contained

in the range B1 to C13. The
Brain
for our spreadsheet model contains the values

that will be used in the analysis: Entry Fee, Attendance, Player (odds), and Bets.

Search JabSto ::

Custom Search