Question

A new small manufacturing company, which produces toys, has to decide whether it wants to distribute its product through a wholesale distributor, go directly to department stores, or open its own store. The table below shows the revenue results for each decision. Construct an Excel spreadsheet to answer the following: a, What is the probability value of good economic conditions that will make distributing through a whole- seller and own store? b. Create a sensitivity graph comparing the different alternatives as the probability of good economic conditions changes. Distribution Network Good Economic Condition Not-so-good Economic condition Whole Sale $75.0 Million $ 60 Million Direct $82.5 Million $12.5 Million Own Store $177.5 Million $-112.5 Million

Please show work on excel spreadsheet with sensitivity tables one should be of exact value. thank you

Answer 1

a.)

The probability of good economic condition that will make wholeseller and own store equal is 0.6273.

Explanation:

Let p = probability of good economic conditions,

Then probability of not-so-good economic conditions = 1-p

Expected revenue for wholeseller = 75*p + (1-p)*60 =75p + 60 - 60p = 15p + 60

Expected revenue for own store = 177.5*p + (1-p)*(-112.5) =177.5p - 112.5 + 112.5p = 290p - 112.5

For them to be equal,

15p + 60 = 290p - 112.5

172.5 = 275p

p = 172.5/275 = 0.6273

b.)

For different probability values, we get following Expected Revenue values for each option.

Expected revenue for wholeseller = 75*p + (1-p)*60 =75p + 60 - 60p = 15p + 60

Expected revenue for direct = 82.5*p + (1-p)*12.5 =82.5p + 12.5 - 12.5p = 70p + 12.5

Expected revenue for own store = 177.5*p + (1-p)*(-112.5) =177.5p - 112.5 + 112.5p = 290p - 112.5

Using the above formulas, we can calculate revenue for different probabilities as below:

Probability		EXPECTED REVENUES
Good economic condition	Not-so-Good economic condition	Wholesale	Direct	Own store
0	1	60	12.5	-112.5
0.05	0.95	60.75	16	-98
0.1	0.9	61.5	19.5	-83.5
0.15	0.85	62.25	23	-69
0.2	0.8	63	26.5	-54.5
0.25	0.75	63.75	30	-40
0.3	0.7	64.5	33.5	-25.5
0.35	0.65	65.25	37	-11
0.4	0.6	66	40.5	3.5
0.45	0.55	66.75	44	18
0.5	0.5	67.5	47.5	32.5
0.55	0.45	68.25	51	47
0.6	0.4	69	54.5	61.5
0.65	0.35	69.75	58	76
0.7	0.3	70.5	61.5	90.5
0.75	0.25	71.25	65	105
0.8	0.2	72	68.5	119.5
0.85	0.15	72.75	72	134
0.9	0.1	73.5	75.5	148.5
0.95	0.05	74.25	79	163
1	0	75	82.5	177.5

The below graph shows how the plot of the expected values for each of the options.

150 • Wholesale Direct Own store 0.4 -50- -100 -150