I am completely lost how to do this in excel: Adrian and Zed run a small bicycle shop called A to Z Bicycles. They must order bicycles for the coming season. Orders for the bicycles must be placed in quantities of twenty $(20).$ The cost per bicycle is $$170$ if they order $20,$ $$167$ if they order $40,$ $$165$ if they order $60,$ and $$164$ if they order $80.$ The bicycles will be sold for $$250$ each. Any bicycles left over at the end of the season can be sold $($for certain$)$ at $$145$ each. If Adrian and Zed run out of bicycles during the season, then they will suffer a loss of "goodwill" among their customers. They estimate this goodwill loss to be $$15$ per customer who was unable to buy a bicycle. Adrian and Zed estimate that the demand for bicycles this season will be $10,30,50,$ or $70$ bicycles with probabilities of $0\mathrm{.}2,0\mathrm{.}4,0\mathrm{.}3,$ and $0\mathrm{.}1$ respectively.

There are four decision alternatives available to Adrian and Zed. They would like to know which option is best using decision rules.

$$ Buy $20$ bicycles

$$ Buy $40$ bicycles

$$ Buy $60$ bicycles

$$ Buy $80$ bicycles

Adrian and Zed have control over which action they choose. That is the whole point of decision theory $-$ deciding which action to take. There are four possible states of nature. A state of nature is an outcome.

$$ The demand is $10$ bicycles

$$ The demand is $30$ bicycles

$$ The demand is $50$ bicycles

$$ The demand is $70$ bicycles

Adrian and Zed have no control over which states of nature will occur. They can only plan and make the best decision based on the appropriate decision criteria. Construct a payoff table and use the table to determine how many bikes should be order using following decision rules.

$1.$ Calculate the payoff matrix for this business scenario.

a$.$ NOTE: You may create extra $$helper$$ tables if you wish to calculate the payoff values. If you do so$,$ please add the tables above the payoff table.

$2.$ What decision alternative is selected using the maximax criterion?

$3.$ What decision alternative is selected using the maximin criterion?

$4.$ What decision alternative is selected using the expected monetary value criterion?

$5.$ Calculate the maximum payoff value given each state of nature.

$6.$ Calculate the regret matrix for this business scenario.

$7.$ What decision alternative is selected using the minimax regret criterion?

$8.$ What decision alternative is selected using the expected regret $($or expected opportunity loss$)$ criterion?

Adrian and Zed are risk averse decision makers. Determine how many bikes Adrian and Zed will order if their risk tolerance is $$400$ and $$800$ respectively.

$1.$ Calculate the utility table given the calculated payoff values, the expected utility function, and a risk tolerance $($i$.$e$.$ R$)$ of $$400?$

$2.$ What decision should be selected using the expected utility criterion with an R value of $$10,000?$

$3.$ Calculate the utility table given the calculated payoff values, the expected utility function, and a risk tolerance $($i$.$e$.$ R$)$ of $$800?$