Use the provided Excel sheet to work through the problem. Fill in the following tables.

We have a product that costs $$5$ to produce and retails for $$15.$ Average demand is Normally distributed with an average of $600$ and a standard deviation of $150.$

$1.$ Basic production perspective $($part $1$ of $$Part $1$ and $2$a$$ sheet$)$

Imagine the supplier can sell directly to the customer, so that the only relevant costs are production and retail price. Calculate the resulting costs of excess and shortage, optimal service level $($SL$*),$ and the amount that should be provided to the market $($Q$*)$

Ce $4\mathrm{.}0$

Cs $$15\mathrm{.}00$

SL $3\mathrm{.}0$

Q$*600$

$2.$ Insert double marginalization wholesale

a$.$ Supplier$$s Decision: $($part $2$a of $$Part $1$ and $2$a$$ sheet$)$

If the supplier sells to a retailer who then meets the demand, the supplier will need to set a wholesale price. Using the green box in K$7,$ set a wholesale price and then calculate the retailer$$s perspective on this product $($cost of excess, shortage, optimal service level, and order quantity$).$

Knowing this, the supplier will strategically choose a wholesale price that is best for themselves. Identify this as the $$supplier$$s optimal contract.$$

Supplier$$s Optimal Plan

W $$12\mathrm{.}00$

Resulting Q $600$ units

Supplier$$s Profit $$156\mathrm{.}00$

b$.$ Retailer$$s Decision

The retailer needs to account for uncertainty to understand their profits.

Given a wholesale price $($E$17),$ the retailer$$s optimal order amount is shown in the blue cell. The resulting expected $($average$)$ results are also shown on the excel sheet.

In columns N:R there is an explanation of what these averages mean. There is also an explanation of the concept of Service Level.

Calculate the resulting profits in U$25$ and U$26.$ This should automatically fill in the table in T$36$:Z$57$ with the profits for a range of wholesale prices.

Identify the Supplier$$s ideal contract $($as in $2$a$)$ but this time include the retailer$$s profit too:

Supplier$$s Optimal Plan

W $$12\mathrm{.}00$

Resulting Q $600$

Supplier$$s Profit $$90$

$3.$ Buy Back Contract $($Part $3$ Buy Back$$ sheet$)$

Calculate the retailer$$s perspective on cost of excess and shortage for a buy back contract in E$12$ and E$13.$ The rest of the page should update. Calculate the supplier and retailer$$s profits in O$14$ and O$15.$

Use the wholesale price of $$12$ in E$9$ and then at the results for various buyback prices. Our goal is to get the system to do what we found in part $1($Q$*).$ Try varying the w $($E$9)$ if needed to find a $$coordinating$$ contract that achieves the SL and Q$*$ from part $1.$

Is there a combination of Wholesale price and buy back that achieves this order size?

Suggest contract parameters to reach part $1$s Q$*$

W $4\mathrm{.}81$

B $$10$

SL $30\%$

Q$*600$

Supplier profit $$180$

Retailer Profit $$90$

$$

$4.$ Revenue Sharing $($Part $4$ Revenue Sharing$$ sheet$)$

Do the same thing here: calculate ce and cs and the supplier and retailer profits. Setting w $=$ $$2\mathrm{.}00$ in E$9,$ review the effect of the revenue sharing percentage in the table of N$25$:T$41.$ Try varying the w $($E$9)$ if needed to find a $$coordinating$$ contract that achieves the SL and Q$*$ from part $1.$

W $$4\mathrm{.}81$

R$\%50\%$

SL $30\%$

Q$*600$ units

Supplier profit $$135$

Retailer Profit $$135$

$5.$ Pareto Improving:

The results you found in part $3$ and $4$ will coordinate the contract but may not be acceptable to the supplier or retailer if they do not meet the minimum profits from part $2$b$.$ Adjust the parameters on both sheets $$part $3$ and on $$part $4$ until you find a contract that is $$pareto improving$$i$.$e$.$ both parties are as good or better off than they were in the contract from part $2$b$.($there are infinitely many combinations that can do this so just identify $1$ for each contract$)$

W

b

SL

Q$*$

Supplier profit

Retailer Profit

W

R$\%$

SL

Q$*$

Supplier profit

Retailer Profit