M4 Assignment 2 Banking on a Beard Award

b6025_module_4_m4assignment_2_treeplantemplate.xls

Home >Business & Finance homework help >Financial markets homework help >M4 Assignment 2 Banking on a Beard Award

Notes

Real Options and the Value of Information

Did you know that roughly 60 percent of new restaurant businesses fail within the first three years of operation (Abrams, 2004)?

Suppose you have a close friend who is employed in a high paying position in the banking industry with tremendous potential for both professional and financial growth. Nevertheless, your friend wants to leave and start a little restaurant. It is your job to help your friend make a sound decision. What do you do?

Often the valuations on which decisions are based require the input of information neither easily deduced nor accurately available. There are so many options and so little time. The values of these options can be clouded in uncertainty. The likelihood of each outcome—both those that are dependent and those that are independent—is shrouded in a variety of likely scenarios.

Like your friend, you must assess the value of certain options, including those choices foregone. If your friend, the restaurateur, leaves her job, what is the income she has given up? What is the probability that her business will flourish for a year or two years? What is the likelihood today that she will be in business three years from today (you would immediately think 40 percent!)? What if she creates a great restaurant that is widely acclaimed but the market, well beyond her control, suddenly crashes? How might you have incorporated that information in the past?

As you can see, decision points combine with scenarios, including events beyond the chooser’s control, to increase the complexity of choosing. Fortunately you have a tool, scenario analysis, which works in conjunction with decision trees where multiple outcomes and the likelihood of those outcomes can be evaluated in light of an uncertain future and the need for a choice today. As much as you need to rationalize your choices and incorporate information accurately and reasonably, you must also learn to forecast reasonably and identify those biases that undermine your choices.

Reference:

Abrams, R. (2004, July 5). Focus on success, not failure. USA Today. Retrieved from: http://www.usatoday.com/money/smallbusiness/columnist/abrams/2004-05-06-success_x.htm

Now assume your friend, Jennifer the banker (formerly a bank teller), has asked for your advice as to whether she should quit her job and pursue her passion in order to become a restaurateur.

Assume the following facts as well as the above information:

1) As a banker, Jennifer makes $135,000 a year with up to a 25 percent bonus. Her maximum raise per year is 10 percent (raises are skewed to capture inflation). She has $250,000 in savings. Her expenses are $5000 a month after taxes.

2) Jennifer is eligible for a promotion in twelve months; the promotion comes with a 50 percent increase in pay and 25 percent bonus. She is competing with three other employees for the position. If Jennifer does not receive the promotion, she will either be promoted to the same position after another twelve months, asked to stay in her current role for the foreseeable future, or be asked to leave the bank.

3) Jennifer enjoys her current job but she wants to manage her own business at some point in her career. She is an avid chef, having had some experience in college and afterwards. She is also personally inclined towards more entrepreneurial ventures. Jennifer gets enormous utility out of the pursuit of her personal and professional goals.

4) Jennifer’s current employer focuses exclusively on small- and medium-sized business clients along with their families. If Jennifer left the bank, her former employer would be a ready and willing source of financing with a reasonable business plan in place.

5) Opening a restaurant will require a $200,000 cash investment for capital improvements and materials. The bank normally provides new restaurants with access to a $100,000 rolling line of credit—at an 8 percent cost for the first $50,000 and a 12 percent cost for the second $50,000.

6) The restaurant will, in the case of modest success, lose $25,000 in the first twelve months, generate a 20 percent net profit in months 12–24, and 25 percent in months 24–36.

Abrams, R. (2004, July 5). Focus on success, not failure. USA Today. Retrieved from: http://www.usatoday.com/money/smallbusiness/columnist/abrams/2004-05-06-success_x.htm

Base Case

Sample only. You may select other options based on your model, financing, income and expense projections
NOTE: The numbers are samples FOR ONE YEAR and shows how large a "huge success" must be to make Jennifer indifferent in the short term. Your cash flows will need to incorporate at least three years (and likely more!).
Jennifer's Career				0.1		www.TreePlan.com
				Huge Success
						1200000
				0	1200000
				0.3
		Open a restaurant		Modest Success
						-25000
		-260000	112500	0	-25000
				0.6
				Closure
						0
				0	0
	1
112500				0.3333333333
				Promotion
						-60000
				0	$ 202,500
				0.3333333333
		Stay at the bank		Stay in current job
						-60000
		-60000	112500	0	$ 135,000
				0.3333333333
				Termination
						-60000
				0	$ - 0
							ID	Name	Value	Prob	Pred	Kind	NS	S1	S2	S3	S4	S5	Row	Col	Mark
							0	TreePlan	0	0	0	D	2	1	2	0	0	0	14	1
							1			0	0	E	3	6	7	8	0	0	7	5
							2			0	0	E	3	3	4	5	0	0	22	5
							3				2	T	0	0	0	0	0	0	17	9
							4				2	T	0	0	0	0	0	0	22	9
							5				2	T	0	0	0	0	0	0	27	9
							6				1	T	0	0	0	0	0	0	2	9
							7				1	T	0	0	0	0	0	0	7	9
							8				1	T	0	0	0	0	0	0	12	9

&l&bThis Version Not Licensed For Commercial Or Academic Use

&l&bTreePlan Tryout, Only For Evaluation&r&bwww.TreePlan.com

Jennifer still has expenses if she stays at the bank!

TreePlan Help

TreePlan for Excel (Unregistered) 1.73

Decision Tree Software

for Windows and Macintosh

TreePlan helps you build and modify decision tree diagrams

in Excel worksheets. TreePlan automatically puts formulas

on your worksheet for evaluating these trees.

To build a new decision tree, first create a new worksheet.

Then start TreePlan in one of two ways:

(1) Choose Decision Tree... from the Options menu

(Excel 4) or the Tools menu (Excel 5, 7, and 97), or

(2) Press the shortcut key: Control + t.

When the TreePlan...New dialog box appears, choose

New Tree. TreePlan builds a tree diagram with an initial

decision node and two branches. The tree diagram begins

with the upper left corner of the diagram near the active

cell at the time New... is chosen. TreePlan assigns the

name TreeDiagram to the range of the tree diagram and

initially sets Excel's Print_Area equal to TreeDiagram.

To change the structure of the tree diagram, select a node,

start TreePlan, and choose commands from a TreePlan

dialog box. To select a terminal node in Excel 97, select

the square cell next to the vertical line at the end of a

terminal branch.

On the left side of each branch of the tree diagram, there is

a name field above the branch line and a partial-cash-flow

value field below the line. On the left side of event

branches, there is also a probability field above the name

field. Decision nodes also have a number in them indicating

the optimal branch.

On the right side of each branch of the tree diagram, there

is a rollback Expected Value field below the line near

each node. TreePlan automatically puts the rollback EV

formula into this cell: maximum of successor EVs at a

decision node, and expected value of successor EVs at an

event node.

To the right of the terminal nodes, there is an endpoint

value field that sums all of the partial cashflows in the tree.

Internally, TreePlan uses the TreeData range, near cell

GV1000, to construct the TreeDiagram.

CAUTION: Do not insert or delete rows or columns in the

TreeDiagram or TreeData ranges. Modify the tree diagram

only by using TreePlan's menu options.

TREEPLAN...DECISION and

TREEPLAN...EVENT dialog boxes

Add branch

Adds a single branch after the selected node .

(No more than 5 branches are allowed.)

Copy subtree

Copies the selected node and all its successors to the

TreePlan clipboard.

Insert decision

Inserts a decision node and single branch before the

selected node.

Insert event

Inserts an event node and single branch before the

selected node.

Change to decision

Changes the selected event node to a decision node and

erases the probability fields from the event branches.

Change to event

Changes the selected decision node to an event node.

Shorten tree

Removes the selected node and its single successor

branch.

Change to terminal

Changes the selected node to a terminal node. All

successor branches are erased.

Remove branch

Erases the selected node, the previous branch, and any

successor branches and nodes.

TREEPLAN...TERMINAL dialog box

Change to decision node

Changes the selected terminal node to a decision node

with one to five successor branches.

Change to event node

Changes the selected terminal node to an event node

with one to five successor branches.

Paste subtree

Pastes the subtree onto the selected terminal node from

the TreePlan clipboard.

Remove previous branch

Erases the selected terminal node and the previous

branch.

TREEPLAN...SELECT dialog box

Cells

To select a set of nonadjacent cells for subsequent

formatting, select the appropriate option button.

(In large trees, this option may not be available.)

Objects

To select all objects, choose Select Special from the

Formula menu, and select the Objects option button.

(In large trees, this option may not be available.)

Columns

To select a set of nonadjacent columns for subsequent

formatting, select the appropriate option button.

TREEPLAN...OPTIONS dialog box

Certainty Equivalents

The default is to rollback the tree using expected values.

If you chose to use exponential utilities, TreePlan will

compute utilities and certainty equivalents at each node.

For the Maximize option, the rollback formulas are

U=A-B*EXP(-X/RT) and X=-LN((A-U)/B)*RT, and for the

Minimize option, U=A-B*EXP(X/RT) and

X=LN((A-U)/B)*RT.

NOTE: TreePlan uses the name RT to represent the

risk tolerance parameter of the exponential utility

function; the names A and B determine scaling.

If the names A, B, and RT don't exist, they are initially

defined as A=1, B=1, and RT=999999999999. The

name UseExpUtility is a flag indicating whether to use

exponential utilities or expected values.

Decision Node EV/CE Choices

The default is to Maximize profits. If you choose to

Minimize costs instead, the cash flows are interpreted

as costs, and decisions are made by choosing the

minimum expected value/CE rather than the maximum.

TreePlan uses the name MinimizeCosts as a flag

indicating whether to maximize profits or minimize

costs.

ACKNOWLEDGMENT

Sincere thanks to Professor James E. Smith, Duke

University, for adding exponential utility and several

other features.

TREEPLAN REGISTRATION

TreePlan is distributed as shareware and may be provided

at no charge to the user for evaluation. If you find this

program useful and continue to use TreePlan after a 30-day

evaluation period, you must make a registration payment to

Decision Support Services. The registration fee will license

one copy for use on any one computer at any one time.

Upon registering TreePlan, you will be sent by postal mail a

disk containing the most recent version of the software and

documentation. The registered TreePlan package has a

30-day money-back guarantee. Registered users will be

notified of the next major version and are entitled to

unlimited technical support via postal mail, Compuserve

71330,3445, or Internet: [email protected].

A site license agreement is required to use TreePlan on a

computer network, and academic pricing is available.

Please contact Decision Support Services for details.

An evaluation copy of TreePlan and other information may

be obtained from the Decision Support Services web site:

http://ourworld.compuserve.com/homepages/decision

TREEPLAN REGISTRATION BY CHECK

The registration fee for TreePlan is $29.00 (California

residents, add sales tax) plus $4.00 shipping to U.S. and

Canada, $6.00 elsewhere. Make money order or check (in

U.S. funds drawn on a U.S. bank) payable to Decision

Support Services, and mail to:

Decision Support Services Voice/Fax (415) 673-6217

2105 Buchanan Street, #1 Compuserve 71330,3445

San Francisco, CA 94115

TREEPLAN REGISTRATION BY CREDIT CARD

You can order with MC, Visa, Amex, or Discover from

Public (software) Library by calling 800-2424-PsL or

713-524-6394 or by fax to 713-524-6398 or by Compuserve

Email to 71355,470. You can also mail credit card orders

to PsL at P.O. Box 35705, Houston, TX 77235-5705.

The above numbers are for orders only. The author cannot

be reached at these PsL phone numbers.

Any questions about the status of the shipment of the order,

refunds, registration options, product details, technical

support, volume discounts, dealer pricing, site licenses,

non-credit card orders, etc., must be directed to

415-673-6217 (Voice/Fax), Compuserve 71330,3445, or

Decision Support Services, 2105 Buchanan Street, #1,

San Francisco, CA 94115.

To insure that you get the latest version, PsL will notify us

the day of your order, and we will ship the product directly

to you. PsL has assigned item/product #11325 to

TreePlan for Excel. The prices are $33 US/Canada and

$35 overseas.

TREEPLAN DISTRIBUTION

Please share copies of TreePlan freely with prospective

users. You are granted permission to make as many

copies as you wish. Do not distribute copies that have

been altered.

Disk vendors, bulletin boards, other distributors, and

individuals may distribute TreePlan as long as you clearly

explain the shareware concept, the need for users to

your disks/CD-ROM/service is a copying fee only and does

not constitute payment for the product.

Please direct questions about volume discounts, dealer

pricing, rack vending, book bundling, or other distribution

methods to 415-673-6217 (Voice/Fax), Compuserve

71330,3445, or Decision Support Services, 2105 Buchanan

Street, #1, San Francisco, CA 94115.

ABOUT SHAREWARE

Shareware is a method of distributing software that gives

you a chance to try software before buying it. If you try a

shareware program and continue using it, you are expected

to register. Individual programs differ on the details about

the evaluation period, registration fee, and support.

software, and the copyright holder may choose to retain all

rights. The only meaningful difference between shareware

and commercial software is the method of distribution.

Shareware authors specifically grant the rights to copy and

distribute the software and documentation, either to all

users and by all methods, or with some restrictions.

You should try to find software that suits your needs and

budget, whether it's commercial or shareware. Both types

have good programs and bad, but shareware makes finding

the right program easier, because you can try before you

buy. Also, because shareware distribution costs are much

lower, shareware prices are often lower, too. Finally,

shareware has the ultimate money-back guarantee: if you

don't use it, you don't pay for it.

ASP OMBUDSMAN INFORMATION

This program is produced by a member of the Association

of Shareware Professionals (ASP). ASP wants to make

sure that the shareware principle works for you. If you are

unable to resolve a shareware-related problem with an ASP

member by contacting the member directly, ASP may be

able to help. The ASP Ombudsman can help you resolve a

dispute or problem with an ASP member, but does not

provide technical support for members' products. Please

write to the ASP Ombudsman at 545 Grover Road,

Muskegon, MI 49442-9427, Fax 616-788-2765, or send a

Compuserve message via Compuserve Mail to ASP

Ombudsman 70007,3536.

WARRANTY

Users of TreePlan must accept this disclaimer of warranty:

TreePlan copyrighted software and documentation are

provided on an 'as is' basis. The author and Decision

Support Services disclaim all warranties, expressed or

implied, including, without limitation, the warranties of

merchantability and of fitness for any purpose. The user

assumes all risks as to the quality and performance of this

software and documentation. The author and Decision

Support Services assume no liability for indirect,

consequential, or incidental damages which may result from

the use or misuse of TreePlan. Some states do not allow

the exclusion of the limit of liability for consequential or

incidental damages, so the above limitation may not apply

to you. This agreement shall be governed by the laws of the

State of California and shall inure to the benefit of the author

and Decision Support Services and any successors,

administrators, heirs, and assigns. Any action or

proceeding brought by either party against the other arising

out of or related to this agreement shall be brought only in a

state or federal court of competent jurisdiction located in the

city and county of San Francisco, California. The parties

hereby consent to in personam jurisdiction of said courts.

USE OF SHAREWARE VERSION BEYOND 30 DAYS

If you purchase one of the following textbooks, you may

continue to use the shareware version of TreePlan as long

as you own the book:

Ragsdale, Spreadsheet Modeling and Decision Analysis

Winston & Albright, Practical Management Science

If you are a student, faculty, or staff at a university that has

an academic site license, you may continue to use the

shareware version of TreePlan for the academic year.

AUTHOR INFORMATION

Please send comments about TreePlan and suggestions

for improvements to:

Michael R. Middleton

McLaren School of Business

University of San Francisco

2130 Fulton Street

San Francisco, CA 94117

Internet: [email protected]

TreePlan for Excel (Unregistered) 1.61

Michael R. Middleton

Page &P

Hints

Hints: TreePlan and Excel 5
Hiding Selected Cells
To hide all rollback EV/CE cells:
*	select one rollback EV/CE cell
*	from the Tools menu choose Decision Tree...
*	in the TreePlan...Select dialog box select (Cells) Rollback EVs/CEs
*	click OK
*	from the Format menu choose Cells...; in the Format Cells dialog box select the Number tab
*	at the bottom of the Number tab select the Code field
*	type three semicolons as a custom code
*	click OK
Use similar steps to hide all cells of any specific type.
Pasting Into Word
To paste a copy of a decision tree into Word:
*	from Excel's Tools menu select Options...; in the Options dialog box select the View tab
*	in the View tab clear the check boxes for Formulas and Gridlines
*	click OK
*	from Excel's Edit menu select Go To...
*	in the Go To list box select TreeDiagram
*	click OK
*	hold down the shift key and from Excel's Edit menu select Copy Picture...
*	in the Copy Picture dialog box select (Appearance) As Shown when Printed
*	click OK
*	switch from Excel to Word
*	select an empty paragraph
*	from Word's Edit menu select Paste
Probability Sensitivity Analysis (Two Branches)
To see how expected value of the optimal strategy depends on a probability of interest:
*	select the probability cell of the other branch of the event set
*	enter the formula "=1-P" where P is a reference to the probability cell of interest
*	enter a list of probabilities in a column
*	select the cell in the row above the first probability in the column to the right
*	enter the formula "=R" where R is a reference to the rollback cell of the initial decision node
*	select the entire data table which includes the blank cell above the probability list,
	the cells containing the list of probabilities, the cell with the formula referencing the
	rollback cell, and the empty cells below the formula cell
*	from the Data menu select Table...
*	in the Table dialog box select the Column Input Cell field
*	enter a reference to the probability cell of interest
*	click OK

Page &P